原文转自:http://www.cnblogs.com/waterystone/p/5073041.html
一、增
1.1 单条
1
2
3
4
5
6
|
<
insert
id=
"addUser"
parameterType=
"com.xxx.model.UserInfo"
useGeneratedKeys=
"true"
keyProperty=
"id"
>
INSERT
INTO
user_info(user_name, account,
password
)
values
(#{userName},#{account},#{
password
})
</
insert
>
|
1.2 批量
<insert id="insert" useGeneratedKeys="true" keyProperty="id"> INSERT INTO my_table(name,create_time,update_time) VALUES <foreach collection="list" item="item" separator=","> (#{item.name},now(),now()) </foreach> ON DUPLICATE KEY UPDATE name=VALUES(name),update_time=now() </insert>
二、删
1
2
3
|
<
delete
id=
"deleteUser"
>
delete
from
user
where
id=#{id}
</
delete
>
|
三、改
3.1 set
1
2
3
4
5
6
7
8
9
10
|
<
update
id=
"updateAuthorIfNecessary"
>
update
Author
<
set
>
<if test=
"username != null"
>username=#{username},</if>
<if test=
"password != null"
>
password
=#{
password
},</if>
<if test=
"email != null"
>email=#{email},</if>
<if test=
"bio != null"
>bio=#{bio}</if>
</
set
>
where
id=#{id}
</
update
>
|
set标签的好处时,可以根据需要动态的更新某些字段。set也会自动消除无关的逗号。
1
2
3
4
5
6
7
8
9
10
11
12
13
|
<
update
id=
"updateBatch"
parameterType=
"java.util.List"
>
<foreach collection=
"list"
item=
"item"
index
=
"index"
open
=
""
close
=
""
separator=
";"
>
UPDATE
my_test
<
set
>
sex = #{item.sex},
age = #{item.age}
</
set
>
WHERE
name
= #{item.
name
}
AND
tt = #{item.tt}
</foreach>
</
update
>
|
四、查
4.1 批量查询
1
2
3
4
|
WHERE
id
IN
<foreach collection=
"ids"
open
=
"("
close
=
")"
separator=
","
item=
"item"
index
=
"index"
>
#{item}
</foreach>
|
1
2
3
4
5
6
|
<if test=
"channelNames!=null"
>
AND
<foreach collection=
"channelNames"
index
=
"index"
item=
"channelName"
open
=
" ("
separator=
" OR "
close
=
")"
>
(d.channel_name
LIKE
CONCAT(
'%'
, CONCAT(#{channelName},
'%'
)))
</foreach>
</if>
|
传入参数为任何可迭代对象(如列表、集合等)和任何的字典或者数组对象。列表时,index为索引值,item为元素;map时,index为key,item为value。
4.2 时间比较
1
2
3
4
|
WHERE
create_time <![CDATA[>=]]> #{startTime}
AND
create_time <![CDATA[<=]]> #{endTime}
WHERE
create_time
BETWEEN
#{startTime}
AND
#{endTime}
WHERE
create_time
BETWEEN
CONCAT(
DATE
(#{startDate}),
" 00:00:00"
)
AND
CONCAT(
DATE
(#{endDate}),
" 23:59:59"
)
WHERE
create_time
BETWEEN
DATE
(#{startDate})
AND
DATE_ADD(
DATE
(#{endDate}),INTERVAL 1
DAY
)
|
4.3 if判断
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
WHERE
1=1
<if test=
"startTime != null and startTime != ''"
>
AND
create_time <![CDATA[>=]]> #{startTime}
</if>
<if test=
"opType == 1"
>
AND
my_filed = 1
</if>
<if test=
"isTop == true"
>
AND
status = 1
</if>
<if test=
"orderBy != null"
>
ORDER
BY
${orderBy}
</if>
<if test=
"isDesc"
>
DESC
</if>
<if test=
"limit != null"
>
LIMIT #{limit.length} OFFSET #{limit.offset}
</if>
|
4.4 choose
1
2
3
4
5
6
7
8
9
10
11
|
<choose>
<
when
test=
"status == 1"
>
AND
status = 1
</
when
>
<
when
test=
"status == 2"
>
AND
status = 2
</
when
>
<otherwise>
AND
status = 0
</otherwise>
</choose>
|
4.5 bind
1
2
3
4
5
|
<
select
id=
"selectBlogsLike"
resultType=
"Blog"
>
<bind
name
=
"pattern"
value=
"'%' + #{title} + '%'"
/>
SELECT
*
FROM
BLOG
WHERE
title
LIKE
#{pattern}
</
select
>
|
1
|
name
LIKE
CONCAT(CONCAT(
'%'
, #{
name
}),
'%'
)
|
4.6 where
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
<
select
id=
"findActiveBlogLike"
resultType=
"Blog"
>
SELECT
*
FROM
BLOG
<
where
>
<if test=
"state != null"
>
state = #{state}
</if>
<if test=
"title != null"
>
AND
title
like
#{title}
</if>
<if test=
"author != null and author.name != null"
>
AND
author_name
like
#{author.
name
}
</if>
</
where
>
</
select
>
|
where标签的好处是,当没有符合的条件时,MyBatis会自动把where去掉。
4.7 #{}与${}
默认情况下,使用#{}格式的语法会使 MyBatis 创建预处理语句属性并安全地设置值(比如?)。这样做更安全,更迅速,通常也是首选做法,不过有时你只是想直接在 SQL 语句中插入一个不改变的字符串。比如,像 ORDER BY,你可以这样来使用:ORDER BY ${columnName},这里 MyBatis 不会修改或转义字符串。
注意:以这种方式接受从用户输出的内容并提供给语句中不变的字符串是不安全的,会导致潜在的 SQL 注入攻击,因此要么不允许用户输入这些字段,要么自行转义并检验。