1.5 mybatis动态sql_mybatis动态sql中的主要元素包含有-CSDN博客

1.5 mybatis动态sql

MyBatis 的强大特性之一便是它的动态SQL，其主要作用是进行SQL拼接。mybatis提供的动态sql元素包括：if、choose (when, otherwise)、trim (where, set)、foreach、bind。

1 if

if主要用于条件判断。例如动态的判断要使用哪些查询条件：

<select id="select" parameterType="hashmap" resultType="User">
    SELECT id,name,age FROM user
    WHERE 1=1
    <if test="name != null">
        AND name like #{name}
    </if>
    <if test="age != null and age > 0">
        AND age > #{age}
    </if>
</select>

注意：这里使用的parameterType类型是hashmap，对应的java类就是java.util.HashMap。对于这种情况，mybatis会调用map.get(key)的方式来替换#{}中的变量，而不再是getter方法。

上面的元素中的sql主要是作用是根据用户名和age作为查询条件，从数据库筛选用户。当用户名不为空时，使用用户名查询条件，当age不为空且>0时，那么用户年龄需要大于这个条件。

测试1：设置用户名查询条件

@Test
public void testSelect1() {
    //查询条件
    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("name","tianshouzhi");
    List<User> userList = userMapper.select(params);
}

运行这个测试代码，控制台打印的sql日志如下所示：

00:19:38.250 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.select - ==> Preparing: SELECT id,name,age FROM user WHERE 1=1 AND name like ?
00:19:38.297 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.select - ==> Parameters: tianshouzhi(String)
00:19:38.313 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.select - <== Total: 1

测试2：设置用户名和age查询条件

@Test    
public void testSelect2() {
    //查询条件
    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("name", "tianshouzhi");
    params.put("age", 27);
    List<User> userList = userMapper.select(params);
}

控制台打印sql日志如下所示：

00:22:35.154 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.select - ==> Preparing: SELECT id,name,age FROM user WHERE 1=1 AND name like ? AND age>?
00:22:35.201 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.select - ==> Parameters: tianshouzhi(String), 27(Integer)
00:22:35.295 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.select - <== Total: 0

通过上面两个案例可以看到，if可以帮助我们动态的判断是否使用某一个查询条件，拼接成不同的sql。

有一点需要注意的是：为了使得我们的语法总是正确的，我们在sql的查询WHERE条件中首先设置了 1=1，此时如果hashmap中没有设置任何查询条件，生成的SQL如下所示：

SELECT id, name, age FROM user WHERE 1=1

考虑一下，如果没有1=1，则sql变为

SELECT id, name, age FROM user WHERE

这明显是一个错误的语法，后面我们将介绍如何解决这个问题。

2 choose, when, otherwise

有些时候，我们不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是这次变为提供了“name”就按“name”查找，提供了“age”就按“age”查找，若两者都没有提供，就返回所有id>5的用户。

<select id="selectChoose" parameterType="hashmap" resultType="User">
	SELECT id, name, age FROM user WHERE 1=1
	<choose>
        <when test="name != null">
       	 	AND name like #{name}
        </when>
        <when test="age != null and age > 0">
            AND age>#{age}
        </when>
        <otherwise>
            AND id>5
        </otherwise>
    </choose>
</select>

测试4：不设置任何查询条件

@Test    
public void testChoose1(){
    //查询条件
    HashMap<String, Object> params = new HashMap<String, Object>();
    List<User> userList = userMapper.selectChoose(params);
}

运行测试代码，控制台打印sql日志如下：

00:45:04.355 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - ==> Preparing: SELECT id,name,age FROM user WHERE 1=1 AND id>5
00:45:04.386 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - ==> Parameters:
00:45:04.495 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - <== Total: 0

测试5：设置用户名查询条件

@Testpublic
void testChoose2(){
    //查询条件
    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("name", "tianshouzhi");
    List<User> userList = userMapper.selectChoose(params);
}

运行测试代码，控制台打印sql日志如下：

00:47:27.686 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - ==> Preparing: SELECT id,name,age FROM user WHERE 1=1 AND name like ?
00:47:27.733 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - ==> Parameters: tianshouzhi(String)
00:47:27.748 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - <== Total: 1

测试6：同时设置用户名和age查询条件

@Test
public void testChoose3(){
    //查询条件
    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("name", "tianshouzhi");
    params.put("age", 27);
    List<User> userList = userMapper.selectChoose(params);
}

运行测试代码，控制台打印sql日志如下：

00:48:32.705 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - ==> Preparing: SELECT id,name,age FROM user WHERE 1=1 AND name like ?
00:48:32.767 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - ==> Parameters: tianshouzhi(String)
00:48:32.783 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectChoose - <== Total: 1

可以发现和测试2打印出的sql内容是完全一样的，也就是说，在使用choose when otherwise的情况下，只要第一个条件满足了，剩下的条件都不会走了。

3 trim, where, set

3.1 trim

trim从字面的意思上理解，是修剪的意思，也就是说，我们可以明确的指定去sql中的哪些部分。

在前面使用if动态判断where的查询条件时，首先设置了1=1，以保证语法正确。但是对于有理想、有逼格、有追求的程序员来说，这种方式显得有点low，我们可以用mybatis中提供<trim>标签来解决这个问题，如：

<select id="selectTrim" parameterType="hashmap" resultType="User">
    SELECT id, name, age FROM user
    <trim prefix="WHERE" prefixOverrides="AND | OR">
        <if test="name != null">
            AND name like #{name}
        </if>
        <if test="age != null and age > 0">
            AND age>#{age}
        </if>
    </trim>
</select>

其中：

prefix="WEHRE"表示前缀，也就是说，我们需要在trim内部包含的查询条件前面添加一个"WHERE"前缀

prefixOverrides="AND|OR"，去掉标签内部第一个AND或者OR。

测试7：设置name查询条件

@Testpublic
void testTrim(){
    //查询条件
    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("name", "tianshouzhi");
    List<User> userList = userMapper.selectTrim(params);
}

运行程序后，控制打印sql日志如下：

21:34:45.997 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectTrim - ==> Preparing: SELECT id,name,age FROM user WHERE name like ?
21:34:46.044 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectTrim - ==> Parameters: tianshouzhi(String)
21:34:46.060 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectTrim - <== Total: 1

可以看到自动在SQL添加了WEHRE条件。

测试8：不设置查询条件

@Test
public void testTrim2(){
    //查询条件
    HashMap<String, Object> params = new HashMap<String, Object>();
    List<User> userList = userMapper.selectTrim(params);
}

当没有任何查询条件时，打印出的sql中也不会包含prefix，如下所示。

21:38:22.028 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectTrim - ==> Preparing: SELECT id,name,age FROM user
21:38:22.060 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectTrim - ==> Parameters:
21:38:22.091 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectTrim - <== Total: 1

还有一个suffix和suffixoverride属性，作用与prefix、prefixOverrides相对应，这里不再赘述。

值得注意的是元素的各个属性的值，可以是任意的，因此对于任何需要对SQL进行裁剪的情况，都可以使用标签来完成。特别的，对于一些典型的应用场景，例如WHERE查询条件，数据库更新使用SET等，mybatis提供了和标签，事实上，这两个标签的作用都可以使用来完成。

3.2 where

where元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。

也就是说，标签等价于

<trim prefix="WHERE" prefixOverrides="AND | OR ">  ...</trim>

修改后的元素如下所示：

<select id="selectWhere" parameterType="hashmap" resultType="User">
    SELECT id, ame, age FROM user
    <where>
        <if test="name != null">
            AND name like #{name}
        </if>
        <if test="age != null and age > 0">
            AND age>#{age}
        </if>
    </where>
</select>

测试9：

@Test
public void testWhere(){
    //查询条件
    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("name", "tianshouzhi");
    List<User> userList = userMapper.selectWhere(params);
}

运行测试代码，控制台输出：

01:03:42.044 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectWhere - ==> Preparing: SELECT id,name,age FROM user WHERE name like ?
01:03:42.091 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectWhere - ==> Parameters: tianshouzhi(String)
01:03:42.107 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectWhere - <== Total: 1

可以看到，mybatis自动为我们在sql中添加了where字句，并把name查询条件前面的AND给去除了。

3.3 set

在数据库更新时，我们需要使用SET关键字。set元素可以被用于动态包含需要更新的列，而舍去其他的。比如：

<update id="update" parameterType="User">
    update user
    <set>
        <if test="name != null">
            name=#{name},
        </if>
        <if test="age != null">
            age=#{age},
        </if>
    </set>
    where id=#{id}
</update>

这里，set 元素会动态前置 SET 关键字，同时也会消除无关的逗号，因为用了条件语句之后很可能就会在生成的赋值语句的后面留下这些逗号。

测试10：

@Test
public void testUpdate() {
    User user1 = new User();
    user1.setId(1);
    user1.setName("wanghanao");
    user1.setAge(26);
    userMapper.update(user1);
}

运行程序，控制台输出：

22:05:16.388 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.update - ==> Preparing: UPDATE user SET name = ?, age = ? WHERE id = ?
22:05:16.443 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.update - ==> Parameters: wanghanao(String), 26(Integer), 1(Integer)
22:05:16.643 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.update - <== Updates: 1

也就是说，标签等价于

<trim prefix="SET" suffixOverrides=",">  ...</trim>

4 foreach

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select id="selectForeach" parameterType="list" resultType="User">
    SELECT * FROM user WHERE id in
    <foreach item="id" index="index" collection="list" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

foreach元素的功能是非常强大的，它允许你指定一个集合(collection)，声明可以用在元素体内的集合项(item)和索引变量(index)。它也允许你指定开闭(open、close)匹配的字符串以及在迭代中间放置分隔符(separator)。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

注意你可以将任何可迭代对象(实现了java.lang.Iterable接口，如列表(list)、集合(collection)等）和任何的字典(hashmap)或者数组(array)对象传递给foreach作为集合参数。当使用可迭代对象或者数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。当使用字典（或者Map.Entry对象的集合）时，index是键，item是值。

测试11：

@Test
public void testForeach(){
    List<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(2);
    List<User> userList = userMapper.selectForeach(ids);
}

运行程序，控制台输出sql日志如下：

22:23:46.721 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectForeach - ==> Preparing: SELECT * FROM user WHERE id in ( ? , ? )
22:23:46.770 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectForeach - ==> Parameters: 1(Integer), 2(Integer)
22:23:46.844 [main] DEBUG com.tianshouzhi.mybatis.dynamic.mappers.UserMapper.selectForeach - <== Total: 1

5 bind

bind标签可以使用 OGNL 表达式创建一个变量并将其绑定到上下文中。例如下面语句使用到了like进行模糊查询。

<select id="selectLike" parameterType="hashmap" resultType="User">
    SELECT id,name,age FROM user WHERE 1=1
    <if test="name != null">
        AND name like concat('%', #{userName}, '%')
    </if>
</select>

使用 concat 函数连接字符串，在 MySQL 中，这个函数支持多个参数，但在 Oracle 中只支持两个参数。由于不同数据库之间的语法差异，如果更换数据库，有些 SQL 语句可能就需要重写。针对这种情况，可以使用 bind 标签来避免由于更换数据库带来的一些麻烦。将上面的方法改为 bind 方式后，代码如下。

<if test="name != null'">
    <bind name="userNameLike" value="'%' + name + '%'"/>
    AND name like #{userNameLike}
</if>

bind 标签的两个属性都是必选项，name 为绑定到上下文的变量名，value 为 OGNL 表达式。创建一个 bind 标签的变量后，就可以在下面直接使用，使用 bind 拼接字符串不仅可以避免因更换数据库而去修改 SQL，也能预防 SQL 注入。

对于动态sql，读者也可以参考官方文档：http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html