深入了解 MyBatis 动态 SQL

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 表达式来实现功能。