glmapper

关于 Mybatis 中 SQL 语句的整理

字数统计: 1.8k阅读时长: 9 min
2018/11/10 Share

随着业务的发展,越来越多的应用系统都从一个大的系统分拆成多个小的系统,各个系统之间通过一定的通信协议进行数据交换。这样就会导致一些小的应用系统自己不用去进行数据库的操作,只需要进行一些rpc调用或者缓存就可以拿到数据进行展示。我之前参与的一个项目就是这样的情况,而我也是将近7个多月的时间没有写过一行SQL。

近期参与的一个项目的数据大多都市基于数据库来进行数据交互的,所以免不了的要写大量的SQL,所以本篇就总结一下一些SQL的基本写法,以备后用。

建表

1
2
3
4
5
CREATE TABLE IF NOT EXISTS `user_test` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长id',
`user_name` varchar(128) NOT NULL COMMENT '用户名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

查询

  • 简单的查询
1
2
3
<select id="queryUserByName" resultMap="userMap" parameterType="java.lang.String">
SELECT * FROM user_test WHERE user_name = #{userName}
</select>

需要注意的是如果这里不指定parameterType,则默认会识别处理;如果指定了类型,则传入的值就需要和当前指定的类型保持一致,不然就会出现数据类型转换异常。

  • 简单分页查询
1
2
3
4
5
6
7
<select id="queryUsersList" resultMap="userMap">
SELECT * FROM user_test WHERE 1=1
<if test="keyword != null and keyword != ''" >
AND user_name LIKE concat('%',#{keyword},'%')
</if>
LIMIT #{currentPage},#{pageSize}
</select>
  • left join

app_info表和app_verion表分别存储的是应用信息和应用版本信息。现在要根据appId和versionId查出一个应用的具体信息【包括信息信息和版本信息】

1
2
3
4
5
6
7
8
<select id="getAppDetail" resultMap="appDeatilMap">
select m.id id,
m.app_name appName,
n.version version,
from app_info m
LEFT JOIN app_version n ON m.id = n.app_id
where m.id = #{appId} and n.id = #{versionId}
</select>
  • 查询条件是list
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="queryAppByAppNames" resultMap="AppMap" parameterType="java.util.List">
select
a.app_name appName,
b.version version
from starter_info a,starter_version b
where
a.id = b.app_id
and a.id in
(
select id from app_info where app_name in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
)
</select>

更新

  • 简单的更新
1
2
3
4
5
6
7
<update id="updateApp" parameterType="java.util.List">
UPDATE app_info
SET
app_name = #{appName}
WHERE
app_id = #{appId}
</update>
  • 批量更新

有这样一个需求,把 app_info表中id 为1,2,3的app的app_name改为appName1,appName2,appName3;

使用 case ..when ..then 这样的语法结构来完成:

case 是当前的条件,when表示条件值,then后面是当前目前更新字段的值;

下面的说明:当前id=#{item.appId}时,app_name=#{item.appName}

1
2
3
4
5
6
7
8
9
10
11
<update id="updateApps" parameterType="java.util.List">
UPDATE app_info set app_name =
<foreach collection="applList" item="item" index="index" separator=" " open="case ID" close="end">
when #{item.appId,jdbcType=INTEGER} then #{item.appName,jdbcType=INTEGER}
</foreach>

where id in
<foreach collection="appList" index="index" item="item" separator="," open="(" close=")">
#{item.appId,jdbcType=INTEGER}
</foreach>
</update>

OK,现在于这样的需要:

根据应用类型的不同,更新不同的运行环境配置;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
{
[
{
"appType":"applet",
"cpu":5,
"memory":4,
"card":3,
"nums":2,
"network":1,
"isInUse":1
},
{
"appType":"bs",
"cpu":5,
"memory":4,
"card":3,
"nums":2,
"network":1,
"isInUse":1

},
{
"appType":"cs",
"cpu":5,
"memory":4,
"card":3,
"nums":2,
"network":1,
"isInUse":1
},
//有几个放几个
]
}

trim属性说明

  • 1.prefix,suffix 表示在trim标签包裹的部分的前面或者后面添加内容
  • 2.如果同时有prefixOverrides,suffixOverrides 表示会用prefix,suffix覆盖Overrides中的内容。
  • 3.如果只有prefixOverrides,suffixOverrides 表示删除开头的或结尾的xxxOverides指定的内容。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<update id="updateBatchApp" parameterType="java.util.List">
UPDATE app_info
<trim prefix="set" suffixOverrides=",">
<trim prefix="cpu = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
<if test="item != null">
when app_type =#{item.appType} then #{item.cpu}
</if>
</foreach>
</trim>
<trim prefix="memory = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
<if test="item != null">
when app_type =#{item.appType} then #{item.memory}
</if>
</foreach>
</trim>
<trim prefix="card = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
when app_type =#{item.appType} then #{item.card}
</foreach>
</trim>
<trim prefix="nums = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
when app_type =#{item.appType} then #{item.nums}
</foreach>
</trim>
<trim prefix="network = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
when app_type =#{item.appType} then #{item.network}
</foreach>
</trim>
<trim prefix="is_in_use = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
when app_type =#{item.appType} then #{item.isInUse}
</foreach>
</trim>
</trim>
where app_id = #{appId}
</update>

关于性能问题没做研究,之前看过关于不同更新语句写法的一篇性能的分析,大家有兴趣可以看下:批量更新数据两种方法效率对比

删除

  • 简单删除
1
DELETE FROM app_info where id = #{id}
  • 批量删除
1
2
3
4
5
6
<delete id="deleteApps" parameterType="java.util.List">
DELETE FROM app_info where app_id in
<foreach item="item" collection="appIds" open="(" separator="," close=")">
#{item}
</foreach>
</delete>

时间字符串 order by

不知道各位是否遇到过,之前的前辈们在项目中将时间用字符串的方式存在DB中,而不是使用DATE,然后有一天你的前辈走了,你的主管说查出来按时间来排序….;呵呵,好!!!

1
2
3
4
5
6
7
<select id="querySysParamList" resultMap="sysParamDO">
SELECT * FROM app_info WHERE 1=1
<if test="keyword != null and keyword != ''" >
AND app_name LIKE concat('%',#{keyword},'%')
</if>
ORDER BY DATE_FORMAT(update_time,'%H %k %I %r %T %S %w') DESC
</select>

字符串转为日期格式
SELECT DATE_FORMAT(‘2011-09-20 08:30:45’, ‘%Y-%m-%d %H:%i:%S’);

把日期转为字符串格式
SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’);

附:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
%M 月名字(January……December) 
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

先记录这些,有坑再补!

参考:http://www.runoob.com/sql/sql-tutorial.html

原文作者:GuoLei Song

原文链接:http://www.glmapper.com/2018/11/10/seiri-mybatis/

发表日期:November 10th 2018, 1:26:49 pm

更新日期:December 14th 2019, 11:14:09 am

版权声明:转载请注明出处

CATALOG
  1. 1. 建表
  2. 2. 查询
  3. 3. 更新
  4. 4. 删除
  5. 5. 时间字符串 order by