MyBatis 动态 SQL
foreach
用法
SQL语句中有时会使用IN
关键字,例如id in (1,2,3)
。可以使用${ids}
的方式直接获取值,但这种写法不能防止 SQL 注入,想避免 SQL 注入就需要用#{}
的方式,这时就要配合使用 foreach
标签满足需求。
foreach
可以对数组、Map
或实现了 Iterable
接口(如 List
、Set
)的对象进行遍历。数组在处理时会转换为 List
对象进行处理,因此 foreach
遍历的对象可以分为两大类:Iterable
类型和 Map
类型。这两种类型在遍历循环时情况不一样,这一节会通过3个例子来讲解 foreach
的用法。
foreach
实现 in 集合
foreach
实现 in
集合(或数组)是最简单和常用的一种情况,下面介绍如何根据传入的用户 id
集合查询出所有符合条件的用户。首先在 UserMapper
接口中增加如下方法。
/**
* 根据用户id集合查询
*
* @param idList
* @return
*/
List<SysUser> selectByIdList(List<Long> idList);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
在 UserMapper.xml 中增加如下代码。
<select id="selectByIdList" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where id in
<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
foreach
包含以下属性。
collection
:必填,值为要迭代循环的属性名。这个属性值的情况很多。item
:变量名,值为从迭代对象中取出的每一个值。index
:索引的属性名,在集合数组情况下值为当前索引值,当迭代循环的对象是Map
类型时,这个值为Map
的key(键值)。open
:整个循环内容开头的字符串。close
:整个循环内容结尾的字符串。separator
:每次循环的分隔符。
collection
的属性要如何设置呢?来看一下 MyBatis 是如何处理这种类型的参数的。
1. 只有一个数组参数或集合参数
以下代码是 DefaultSqlSession
中的方法,也是默认情况下的处理逻辑。
private Object wrapCollection(final Object object) {
if (object instanceof Collection) {
StrictMap<Object> map = new StrictMap<Object>();
map.put("collection", object);
if (object instanceof List) {
map.put("list", object);
}
return map;
} else if (object != null && object.getClass().isArray()) {
StrictMap<Object> map = new StrictMap<Object>();
map.put("array", object);
return map;
}
return object;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
当参数类型为 Collection
的时候,默认会转换为 Map
类型,并添加一个 key 为 collection
的值(MyBatis 3.3.0版本增加),如果参数类型是 List
集合,那么就继续添加一个 key 为 list
的值(MyBatis 3.2.8及低版本只有这一个key),这样,当collection="list"
时,就能得到这个集合,并对它进行循环操作。
当参数类型为 Array
的时候,也会转换成 Map
类型,默认的 key 为 array
。当采用如下方法使用数组参数时,就需要把foreach
标签中的 collection
属性值设置为 array
。
/**
* 根据用户id集合查询
*
* @param idArray
* @return
*/
List<SysUser> selectByIdList(Long[] idArray);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
上面说是数组或集合类型的参数默认的名字,推荐使用@Param
来指定参数的名字,这时 collection
就设置为通过@Param
注解指定的名字。
2. 有多个参数
第 2 章中讲过,当有多个参数的时候,要使用@Param
注解给每个参数指定一个名字,否则在 SQL 中使用参数时就会不方便。因此将collection
设置为 @Param
注解指定的名字即可。
3. 参数是 Map
类型
使用 Map
和使用@Param
注解方式类似,将 collection
指定为对应 Map
中的 key 即可。如果要循环所传入的 Map
,推荐使用@Param
注解指定名字,此时可将 collection
设置为指定的名字,如果不想指定名字,就要使用默认值_parameter
。
4. 参数是一个对象
指定为对象的属性名即可。当使用对象内多层嵌套的对象时,使用属性.属性
(集合和数组可以使用下标取值)的方式可以指定深层的属性值。
先来看一个简单的测试代码,验证以上说法。
@Test
public void testSelectByIdList(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Long> idList = new ArrayList<Long>();
idList.add(1L);
idList.add(1001L);
//业务逻辑中必须校验idList.size() > 0
List<SysUser> userList = userMapper.selectByIdList(idList);
Assert.assertEquals(2, userList.size());
} finally {
//不要忘记关闭sqlSession
sqlSession.close();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
该测试输出的日志如下。
DEBUG [main] - ==> Preparing: select id, user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where id in ( ? , ? )
DEBUG [main] - ==> Parameters: 1(Long), 1001(Long)
TRACE [main] - <== Columns: id, userName, userPassword, userEmail,
userInfo, headImg, createTime
TRACE [main] - <== Row: 1, admin, 123456, admin@mybatis.tk,
<<BLOB>>, <<BLOB>>, 2016-06-07 00:00:00.0
TRACE [main] - <== Row: 1001, test, 123456, test@mybatis.tk,
<<BLOB>>, <<BLOB>>, 2016-06-07 00:00:00.0
DEBUG [main] - <== Total: 2
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
可以观察日志中打印的SQL语句,foreach元素中的内容最终成为了in ( ? , ? )
,根据这部分内容很容易就能理解 open
、item
、separator
和 close
这些属性的作用。
关于不同集合类型参数的相关内容,建议大家利用上面的基础方法多去尝试,帮助更好地理解。
foreach
实现批量插入
如果数据库支持批量插入,就可以通过 foreach
来实现。批量插入是SQL-92新增的特性,目前支持的数据库有 DB2、SQL Server 2008 及以上版本、PostgreSQL 8.2 及以上版本、MySQL、sqlite 3.7.11 及以上版本、H2。批量插入的语法如下。
INSERT INTO tablename (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
('value-2a', ['value-2b', ...]),
...
- 1
- 2
- 3
- 4
从待处理部分可以看出,后面是一个值的循环,因此可以通过 foreach
实现循环插入。
在 UserMapper
接口中增加如下方法。
/**
* 批量插入用户信息
*
* @param userList
* @return
*/
int insertList(List<SysUser> userList);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
在 UserMapper.xml 中添加如下 SQL。
<insert id="insertList">
insert into sys_user(
user_name, user_password,user_email,
user_info, head_img, create_time)
values
<foreach collection="list" item="user" separator=",">
(
#{user.userName}, #{user.userPassword},#{user.userEmail},
#{user.userInfo}, #{user.headImg, jdbcType=BLOB}, #{user.createTime, jdbcType=TIMESTAMP})
</foreach>
</insert>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
注意:通过 item
指定了循环变量名后,在引用值的时候使用的是属性.属性
(如 user.userName
)的方式。
针对该方法编写测试如下。
@Test
public void testInsertList(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//创建一个user对象
List<SysUser> userList = new ArrayList<SysUser>();
for(int i = 0; i < 2; i++){
SysUser user = new SysUser();
user.setUserName("test" + i);
user.setUserPassword("123456");
user.setUserEmail("test@mybatis.tk");
userList.add(user);
}
//将新建的对象批量插入数据库中,特别注意,这里的返回值result是执行SQL影响的行数
int result = userMapper.insertList(userList);
Assert.assertEquals(2, result);
} finally {
//为了不影响数据库中的数据导致其他测试失败,这里选择回滚
sqlSession.rollback();
//不要忘记关闭sqlSession
sqlSession.close();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
为了使输出的日志不那么长,这里就测试插入两条数据的情况,输出的日志如下。
DEBUG [main] - ==> Preparing: insert into sys_user(
user_name, user_ password,
user_email, user_info,
head_img, create_time)
values ( ?, ?,?, ?, ?, ?) ,
( ?, ?,?, ?, ?, ?)
DEBUG [main] - ==> Parameters: test0(String), 123456(String),
test@mybatis.tk(String),
null, null, null,
test1(String), 123456(String),
test@mybatis.tk(String),
null, null, null
DEBUG [main] - <== Updates: 2
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
从日志中可以看到通过批量SQL语句插入了两条数据。
从 MyBatis 3.3.1 版本开始,MyBatis 开始支持批量新增回写主键值的功能(该功能由本书作者提交),这个功能首先要求数据库主键值为自增类型,同时还要求该数据库提供的 JDBC 驱动可以支持返回批量插入的主键值(JDBC 提供了接口,但并不是所有数据库都完美实现了该接口),因此到目前为止,可以完美支持该功能的仅有 MySQL 数据库。由于 SQL Server 数据库官方提供的 JDBC 只能返回最后一个插入数据的主键值,所以不能支持该功能。
如果要在 MySQL 中实现批量插入返回自增主键值,只需要在原来代码基础上做如下修改。
<insert id="insertList" useGeneratedKeys="true" keyProperty="id">
- 1
和单表一样,增加了useGeneratedKeys
和keyProperty
两个属性,增加这两个属性后,简单修改测试类,输出 id
值。
//在调用insertList之后
for(SysUser user : userList){
System.out.println(user.getId());
}
- 1
- 2
- 3
- 4
执行测试后,可以看到id部分的日志如下。
1023
1024
- 1
- 2
关于批量插入的内容就介绍这么多,对于不支持该功能的数据库,许多人会通过 select...union all select...
的方式去实现,这种方式不同数据库的实现也不同,并且这种实现也不安全,这里不再提供示例。
foreach
实现动态UPDATE
这一节主要讲当参数类型是 Map
时,foreach
如何实现动态 UPDATE 。
当参数是 Map
类型的时候,foreach
标签的 index
属性值对应的不是索引值,而是 Map
中的 key,利用这个 key 可以实现动态 UPDATE。
现在需要通过指定的列名和对应的值去更新数据,实现代码如下。
<update id="updateByMap">
update sys_user
set
<foreach collection="_parameter" item="val" index="key" separator=",">
${key} = #{val}
</foreach>
where id = #{id}
</update>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
这里的 key 作为列名,对应的值作为该列的值,通过 foreache
将需要更新的字段拼在 SQL 语句中。
该 SQL 对应在 UserMapper
接口中的方法如下。
/**
* 通过Map更新列
*
* @param map
* @return
*/
int updateByMap(Map<String, Object> map);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
这里没有通过@Param
注解指定参数名,因而MyBatis在内部的上下文中使用了默认值_parameter
作为该参数的key,所以在XML中就使用了_parameter
。编写测试代码如下。
@Test
public void testUpdateByMap(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
//查询条件,同样也是更新字段,必须保证该值存在
map.put("id", 1L);
//要更新的其他字段
map.put("user_email", "test@mybatis.tk");
map.put("user_password", "12345678");
//更新数据
userMapper.updateByMap(map);
//根据当前id查询修改后的数据
SysUser user = userMapper.selectById(1L);
Assert.assertEquals("test@mybatis.tk", user.getUserEmail());
} finally {
//为了不影响数据库中的数据导致其他测试失败,这里选择回滚
sqlSession.rollback();
//不要忘记关闭sqlSession
sqlSession.close();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
测试代码输出日志如下。
DEBUG [main] - ==> Preparing: update sys_user
set user_email = ? ,
user_password = ? ,
id = ?
where id = ?
DEBUG [main] - ==> Parameters: test@mybatis.tk(String),
12345678(String),
1(Long),
1(Long)
DEBUG [main] - <== Updates: 1
DEBUG [main] - ==> Preparing: select * from sys_user where id = ?
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <== Columns: id, user_name, user_password, user_email,
user_info, head_img, create_time
TRACE [main] - <== Row: 1, admin, 12345678, test@mybatis.tk,
<<BLOB>>, <<BLOB>>, 2016-06-07 00:00:00.0
DEBUG [main] - <== Total: 1
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
到这里,foreach
的全部内容就介绍完了,下一节将介绍 bind
的用法。
bind
用法
bind
标签可以使用 OGNL 表达式创建一个变量并将其绑定到上下文中。在前面的例子中,UserMapper.xml 有一个selectByUser
方法,这个方法用到了 like
查询条件,部分代码如下。
<if test="userName != null and userName != ''">
and user_name like concat('%', #{userName}, '%')
</if>
- 1
- 2
- 3
使用 concat
函数连接字符串,在 MySQL 中,这个函数支持多个参数,但在 Oracle 中只支持两个参数。由于不同数据库之间的语法差异,如果更换数据库,有些 SQL 语句可能就需要重写。针对这种情况,可以使用 bind
标签来避免由于更换数据库带来的一些麻烦。将上面的方法改为 bind
方式后,代码如下。
<if test="userName != null and userName != ''">
<bind name="userNameLike" value="'%' + userName + '%'"/>
and user_name like #{userNameLike}
</if>
- 1
- 2
- 3
- 4
bind
标签的两个属性都是必选项,name
为绑定到上下文的变量名,value
为 OGNL 表达式。创建一个 bind
标签的变量后,就可以在下面直接使用,使用 bind
拼接字符串不仅可以避免因更换数据库而去修改 SQL,也能预防 SQL 注入。大家可以根据需求,灵活使用 OGNL 表达式来实现功能。