MyBatis动态SQL

MyBatis的强大特性之一便是它的动态SQL，可以基于OGNL的表达式生成SQL语句。常用的标签有：

if
choose (when, otherwise)
trim (where, set)
foreach

1. if

需求：根据用户名查询出用户信息

接口

/**
 * 查询男性用户，如果输入了姓名，则按姓名进行查询
 * @param username
 * @return
 */
List<User> findByUsername(@Param("username") String username);

映射文件

<select id="findByUsername" parameterType="java.lang.String" resultType="com.mybatis.pojo.User">
    SELECT *
    FROM user
    WHERE sex = '男'
        <if test="username != null">
            AND username LIKE #{username}
        </if>
</select>

测试类

@Test
public void testFindByUsername() {
    List<User> userList = this.userMapper.findByUsername("张三");
    for (User user : userList) {
        System.out.println(user);
    }
}

测试结果

User{id=41, username='张三', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='北京'}

2. choose (when, otherwise)

需求：根据输入的条件进行查询，如果没有输入，则默认查询用户张三的信息

接口

/**
 * 根据输入的条件查询对象
 * @return
 */
List<User> findByCondition(User user);

映射文件

<select id="findByCondition" parameterType="com.mybatis.pojo.User" resultType="com.mybatis.pojo.User">
    SELECT *
    FROM user
    WHERE 1 = 1
    <!-- choose标签相当于java的switch语句 -->
    <choose>
        <when test="id != null">
            AND id = #{id}
        </when>
        <when test="username != null">
            AND username = #{username}
        </when>
        <when test="birthday != null">
            AND birthday = #{birthday}
        </when>
        <when test="sex != null">
            AND sex = #{sex}
        </when>
        <when test="address != null">
            AND address = #{address}
        </when>
        <otherwise>
            AND username = '张三'
        </otherwise>
    </choose>
</select>

测试类

@Test
public void testFindByCondition() {
    User user = new User();
    user.setId(41);
    List<User> userList = this.userMapper.findByCondition(user);
    for (User u : userList) {
        System.out.println(u);
    }
}

测试结果

User{id=41, username='张三', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='北京'}

3. trim (where, set)

mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

以下是trim标签中涉及到的属性：

属性	描述
prefix	给sql语句拼接的前缀
suffix	给sql语句拼接的后缀
prefixOverrides	去除sql语句前面的关键字或者字符，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffixOverrides	去除sql语句后面的关键字或者字符，假设该属性指定为","，当sql语句的结尾为","，trim标签将会去除该","

3.1 使用trim标签去除SQL语句中多余的AND关键字（tirm替换where）

有这样的一个例子：

<select id="findByUser" resultType="com.mybatis.pojo.User">
    SELECT * 
    FROM user
    WHERE
    <if test="id != null">
        id = #{id}
    </if>
    <if test="username != null">
        AND username = #{username}
    </if>
    <if test="birthday != null">
        AND birthday = #{birthday}
    </if>
    <if test="sex != null">
        AND sex = #{sex}
    </if>
    <if test="address != null">
        AND address = #{address}
    </if>
</select>

如果这些条件没有一个能匹配上会发生什么？最终这条SQL会变成这样：

SELECT * 
FROM user
WHERE

这会导致查询失败。如果仅仅第二个条件匹配又会怎样？这条SQL最终会是这样:

SELECT * 
FROM user
WHERE AND username = #{username}

此时，我们可以使用where标签来解决这个问题，where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。

<select id="findByUser" resultType="com.mybatis.pojo.User">
    SELECT *
    FROM user
    <where>
        <if test="id != null">
            id = #{id}
        </if>
        <if test="username != null">
            AND username = #{username}
        </if>
        <if test="birthday != null">
            AND birthday = #{birthday}
        </if>
        <if test="sex != null">
            AND sex = #{sex}
        </if>
        <if test="address != null">
            AND address = #{address}
        </if>
    </where>
</select>

trim标签也可以完成相同的功能，写法如下：

<select id="findByUser" resultType="com.mybatis.pojo.User">
    SELECT *
    FROM user
    <trim prefix="WHERE" prefixOverrides="AND">
        <if test="id != null">
            id = #{id}
        </if>
        <if test="username != null">
            AND username = #{username}
        </if>
        <if test="birthday != null">
            AND birthday = #{birthday}
        </if>
        <if test="sex != null">
            AND sex = #{sex}
        </if>
        <if test="address != null">
            AND address = #{address}
        </if>
    </trim>
</select>

3.2 使用trim标签去除多余的逗号（trim替换set）

有如下的例子：

<update id="updateUser" parameterType="com.mybatis.pojo.User">
    UPDATE user
    SET
        <if test="username != null">
            username = #{username},
        </if>
        <if test="birthday != null">
            birthday = #{birthday},
        </if>
        <if test="sex != null">
            sex = #{sex},
        </if>
        <if test="address != null">
            address = #{address}
        </if>
    WHERE id = #{id}
</update>

如果这些条件没有一个能匹配上会发生什么？最终这条SQL会变成这样：

UPDATE user 
SET 
WHERE id = ?

如果第四个条件没有匹配上，sql语句会变成如下：

UPDATE user 
SET username = ?,
    birthday = ?,
    sex = ?,
    address = ?,
WHERE id = ?

此时，我们可以使用set标签来解决这个问题，set标签只会在至少有一个子元素的条件返回SQL子句的情况下才去插入"SET"子句。而且，若语句的结尾为","，set标签也会将它们去除。

<update id="updateUser" parameterType="com.mybatis.pojo.User">
    UPDATE user
    <set>
        <if test="username != null">
            username = #{username},
        </if>
        <if test="birthday != null">
            birthday = #{birthday},
        </if>
        <if test="sex != null">
            sex = #{sex},
        </if>
        <if test="address != null">
            address = #{address}
        </if>
    </set>
    WHERE id = #{id}
</update>

trim标签也可以完成相同的功能，写法如下：

<update id="updateUser" parameterType="com.mybatis.pojo.User">
    UPDATE user
    <trim prefix="SET" suffixOverrides=",">
        <if test="username != null">
            username = #{username},
        </if>
        <if test="birthday != null">
            birthday = #{birthday},
        </if>
        <if test="sex != null">
            sex = #{sex},
        </if>
        <if test="address != null">
            address = #{address}
        </if>
    </trim>
    WHERE id = #{id}
</update>

3.3 使用trim标签去除多余的逗号（插入语句）

有如下的例子：

<insert id="saveUser">
    INSERT INTO user (
        <if test="id != null">
            id,
        </if>
        <if test="username != null">
            username
        </if>
    )
    VALUES (
        <if test="id != null">
            #{id, jdbcType = VARCHAR},
        </if>
        <if test="username != null">
            #{username, jdbcType = VARCHAR},
        </if>
    )
</insert>

如果第二条件没有匹配上，sql语句会变成如下：

INSERT INTO user(id,) VALUES(id,)

插入将会失败，使用trim标签可以解决此问题，只需做少量的修改，如下所示：

<insert id="saveUser">
    INSERT INTO user
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            id,
        </if>
        <if test="username != null">
            username
        </if>
    </trim>
    <trim prefix="VALUES(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            #{id, jdbcType = VARCHAR},
        </if>
        <if test="username != null">
            #{username, jdbcType = VARCHAR},
        </if>
    </trim>
</insert>

4. foreach

场景：按照多个id查询用户信息

接口

/**
 * 按多个Id查询用户信息
 * @param ids
 * @return
 */
List<User> findUserByIds(@Param("ids")ArrayList ids);

映射配置文件

<select id="findUserByIds" parameterType="java.util.ArrayList" resultType="com.mybatis.pojo.User">
    SELECT *
    FROM user
    WHERE id IN
        <foreach collection="ids" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
</select>

测试类

@Test
public void testFindUserByIds() {
    ArrayList<Integer> ids = new ArrayList<Integer>();
    ids.add(41);
    ids.add(42);
    ids.add(43);
    List<User> userList = userMapper.findUserByIds(ids);
    for (User user : userList) {
        System.out.println(user);
    }
}

本文参考：
https://blog.csdn.net/wt_bett...
https://blog.csdn.net/hellozp...

MyBatis动态SQL