MyBatis - 动态 SQL | 小嘉的部落格

前言

动态 SQL 是 MyBatis 的强大特性之一，如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。

如果使用动态 SQL，就可以彻底摆脱这种痛苦。

if

if 就是简单的条件判断，利用 if 语句我们可以实现某些简单的条件选择。

我们先来看一下持久层方法

1	List<User> selectByNameAndPwd(User user);

Mapper.xml 映射文件

<select id="selectByName" resultType="User">
    SELECT *
    FROM user
    <if test="username != null and username != ''">
        WHERE username LIKE #{username}
    </if>

    <if test="password != null and password != ''">
        AND password LIKE #{password}
    </if>
</select>

使用 if 标签来做了条件判断，这样可以根据用户填入的参数来动态加入查询条件。

测试类

@Test
public void test1() throws IOException {
    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    // getMapper(); 会通过动态代理动态的生成 UserMapper 的代理实现类
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

    userMapper.selectByNameAndPwd(new User(null, "%a%", null)).forEach(s -> System.out.println(s));

    // 关闭连接
    sqlSession.close();
}

控制台 SQL 语句输出结果：

DEBUG 03-06 21:02:23,638 ==>  Preparing: select * from user where username LIKE ?  (BaseJdbcLogger.java:159)
DEBUG 03-06 21:02:23,673 ==> Parameters: %a%(String) (BaseJdbcLogger.java:159)
DEBUG 03-06 21:02:23,695 <==      Total: 1 (BaseJdbcLogger.java:159)
[User(id=1, username=admin, password=admin123)]

Process finished with exit code 0

password 为 null，所以最后的 SQL 语句并没有拼接 password 条件

choose

choose 元素的作用就相当于 JAVA 中的 switch 语句，通常都是与 when 和 otherwise 搭配的

持久层方法

1	List<User> selectByNameAndPwd2(User user);

Mapper.xml 映射文件

<select id="selectByNameAndPwd2" resultType="User">
    SELECT *
    FROM user
    <where>
        <choose>
            <when test="username != null and username != ''">
                AND username LIKE #{username}
            </when>

            <when test="password != null and password != ''">
                AND password LIKE #{password}
            </when>

            <otherwise>
            </otherwise>
        </choose>
    </where>
</select>

测试类

这里我吧 username 置为 null 了

@Test
public void test1() throws IOException {
    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    // getMapper(); 会通过动态代理动态的生成 UserMapper 的代理实现类
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    
    userMapper.selectByNameAndPwd2(new User(null, null, "%123%")).forEach(s -> System.out.println(s));

    // 关闭连接
    sqlSession.close();
}

控制台 SQL 语句输出结果：

DEBUG 03-06 21:14:20,477 ==>  Preparing: SELECT * FROM user WHERE password LIKE ?  (BaseJdbcLogger.java:159)
DEBUG 03-06 21:14:20,519 ==> Parameters: %root%(String) (BaseJdbcLogger.java:159)
DEBUG 03-06 21:14:20,542 <==      Total: 1 (BaseJdbcLogger.java:159)
[User(id=2, username=root, password=root123)]

如果 username 不为空，则拼接 username 的条件。如果 password 不为空，则拼接 password 的条件。但是 otherwise 的条件可以为空

where

where 语句的作用主要是简化 SQL 语句中 where 中的条件判断的。MyBatis 会智能的帮你处理这些情况，如果所有的条件都不满足那么 MyBatis 就会查出所有的记录，如果输出后是 AND 开头的，MyBatis 会把第一个 AND 忽略，当然如果是 OR 开头的，MyBatis 也会把它忽略，此外，在 where 元素中你不需要考虑空格的问题，MyBatis 会智能的帮你加上，简直不要太爽！

持久层方法

1	List<User> selectByNameAndPwd3(User user);

Mapper.xml 映射文件

<select id="selectByNameAndPwd3" resultType="User">
    SELECT *
    FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE #{username}
        </if>

        <if test="password != null and password != ''">
            AND password LIKE #{password}
        </if>
    </where>
</select>

测试类

@Test
public void test1() throws IOException {
    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    // getMapper(); 会通过动态代理动态的生成 UserMapper 的代理实现类
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

    userMapper.selectByNameAndPwd3(new User(null, "%a%", null)).forEach(s -> System.out.println(s));

    // 关闭连接
    sqlSession.close();
}

控制台 SQL 语句输出结果：

DEBUG 03-06 21:20:47,610 ==>  Preparing: select * from user WHERE username LIKE ?  (BaseJdbcLogger.java:159)
DEBUG 03-06 21:20:47,648 ==> Parameters: %a%(String) (BaseJdbcLogger.java:159)
DEBUG 03-06 21:20:47,671 <==      Total: 1 (BaseJdbcLogger.java:159)
User(id=1, username=admin, password=admin123)

可以看到，username 前面的 AND 关键字被自动删掉了

sql

MyBatis 中 sql 标签定义 SQL 片段，include 标签引用，可以复用 SQL 片段。

持久层方法

1	List<User> selectByNameAndPwd4(User user);

Mapper.xml 映射文件

<sql id="userField">
    SELECT id, username, password
    FROM user
</sql>

<select id="selectByNameAndPwd4" resultType="User">
    <include refid="userField"/>
    <where>
        <if test="username != null and username != ''">
            AND username LIKE #{username}
        </if>

        <if test="password != null and password != ''">
            AND password LIKE #{password}
        </if>
    </where>
</select>

我们把反复使用的 SELECT 语句使用 sql 标签抽取出来，然后使用 include 标签引用，这样配置的复用性大大地提高啦

测试类

@Test
public void test1() throws IOException {
    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    // getMapper(); 会通过动态代理动态的生成 UserMapper 的代理实现类
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

    userMapper.selectByNameAndPwd4(new User(null, "%a%", null)).forEach(s -> System.out.println(s));

    // 关闭连接
    sqlSession.close();
}

控制台 SQL 语句输出结果：

DEBUG 03-06 21:28:04,592 ==>  Preparing: select id, username, password from user WHERE username LIKE ?  (BaseJdbcLogger.java:159)
DEBUG 03-06 21:28:04,629 ==> Parameters: %a%(String) (BaseJdbcLogger.java:159)
DEBUG 03-06 21:28:04,649 <==      Total: 1 (BaseJdbcLogger.java:159)
User(id=1, username=admin, password=admin123)

foreach

foreach 的主要用在构建 in 条件中，它可以在 SQL 语句中进行迭代一个集合。foreach 元素的属性主要有

属性名	说明
item	集合中每一个元素进行迭代时的别名
index	指定一个名字，用于表示在迭代过程中，每次迭代到的位置
collection	该属性是必须指定的，该值存在 3 种情况：1. 如果传入的是单参数且参数类型是一个 List 的时候，collection 属性值就为 list。2. 如果传入的是单参数且参数类型是一个 `array` 数组的时候，collection 的属性值就为 `array`。 3. 如果传入的参数是多个的时候，我们就需要把它们封装成一个 Map 了，当然单参数也可以封装成 Map，实际上如果你在传入参数的时候，在 MyBatis 里面也是会把它封装成一个 Map 的，Map 的 key 就是参数名，所以这个时候 collection 属性值就是传入的 List 或 array 对象在自己封装的 Map 里面的 key
open	表示该语句以什么开始
separator	表示在每次进行迭代之间以什么符号作为分隔符
close	表示以什么结束

持久层方法

1	void deleteUser(List<Integer> ids);

Mapper.xml 映射文件

<delete id="deleteUser" parameterType="integer">
    DELETE
    FROM user WHERE
    <foreach collection="list" item="id" open="id IN(" close=")" separator=",">
        #{id}
    </foreach>
</delete>

这里传入的参数是 List 类型，所以 collection 属性为 list

测试类

@Test
public void test1() throws IOException {
    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    // getMapper(); 会通过动态代理动态的生成 UserMapper 的代理实现类
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    
    ArrayList<Integer> ids = new ArrayList<>();
    ids.add(2);
    ids.add(3);
    ids.add(4);
    ids.add(5);
    ids.add(6);
    userMapper.deleteUser(ids);

    // 提交事务
    sqlSession.commit();

    // 关闭连接
    sqlSession.close();
}

控制台 SQL 语句输出结果：

1
2
3

DEBUG 03-06 21:44:39,149 ==>  Preparing: DELETE FROM user WHERE id IN( ? , ? , ? , ? , ? )  (BaseJdbcLogger.java:159)
DEBUG 03-06 21:44:39,190 ==> Parameters: 2(Integer), 3(Integer), 4(Integer), 5(Integer), 6(Integer) (BaseJdbcLogger.java:159)
DEBUG 03-06 21:44:39,196 <==    Updates: 5 (BaseJdbcLogger.java:159)

set

既有 set 关键字的作用，还可以自动去掉 <set> 标签拼接后的字符串最后一个 , 号

set 元素可以用于动态包含需要更新的列，忽略其它不更新的列

持久层方法

1	void updateUser(User user);

Mapper.xml 映射文件

<update id="updateUser">
    UPDATE user
    <set>
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="password != null and password != ''">
            password = #{password}
        </if>
    </set>
    WHERE id = #{id}
</update>

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号，比如 WHERE 前面的 , 号，不然就会导致 SQL 语句失败

测试类

@Test
public void test1() throws IOException {
    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    // getMapper(); 会通过动态代理动态的生成 UserMapper 的代理实现类
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 

    User user = new User();
    user.setUsername("garvey");
    user.setPassword("123456");
    user.setId(7);
    userMapper.updateUser(user);

    // 提交事务
    sqlSession.commit();

    // 关闭连接
    sqlSession.close();
}

控制台 SQL 语句输出结果：

1
2
3

DEBUG 03-06 21:57:50,262 ==>  Preparing: UPDATE user SET password = ? WHERE id = ?  (BaseJdbcLogger.java:159)
DEBUG 03-06 21:57:50,295 ==> Parameters: 123456(String), 9(Integer) (BaseJdbcLogger.java:159)
DEBUG 03-06 21:57:50,300 <==    Updates: 1 (BaseJdbcLogger.java:159)

另外也可以使用 trim 标签

<update id="updateUser2">
    UPDATE user
    <trim prefix="set" suffixOverrides=",">
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="password != null and password != ''">
            password = #{password}
        </if>
    </trim>
    WHERE id = #{id}
</update>

更多动态 SQL 参考 MyBatis 3 - 动态 SQL