MyBatis详解(3)--动态SQL

需求缘起

前面两篇文章，我们进行的SQL操作都是比较简单的，当遇到复杂sql的时候，写起来就会很麻烦且死板，稍微不注意，由于引号，空格等缺失可能都会导致错误。MyBatis 为我们提供了动态 SQL，可以让我们摆脱这种烦恼。这一节我们就来学下动态SQL。

准备工作

为了方便更好的说明动态SQL的使用，对 user_info 数据表多增加几个字段，同时相应的在实体类中也增加这个字段

@Data
public class UserInfo {
    private Long id;
    private String username;
    private String password;
    private Integer high;
    private Integer age;
    private Integer sex;
}

添加数据后如下：

提示： 这一节要在第一节的基础上改动，这样代码改变就很少了。

1.if用法

需求：通过用户名和性别来查询用户，如果用户名为空，则只根据性别查询，

1.1 原生SQL的写法

    <select id="selectUserInfoByUsernameAndSex"
            resultType="UserInfo" parameterType="UserInfo">
        <!-- 这里和普通的sql 查询语句差不多，对于只有一个参数，后面的 #{id}表示占位符，里面不一定要写id,
                写啥都可以，但是不要空着，如果有多个参数则必须写pojo类里面的属性 -->
        select * from user_info where username=#{username} and sex=#{sex}
    </select>

转换成普通的 sql： select * from user_info where username=? and sex=?
测试方法代码

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserInfoMapperTest {
    private static SqlSessionFactory sqlSessionFactory;
    private static SqlSession sqlSession = null;

    @BeforeClass
    public static void init() {
        try {
            //将工具类读入 reader
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //创建 SqlSessionFactory 对象，该对象包含了mybatis-config.xml相关配置信息
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            sqlSession = sqlSessionFactory.openSession();
            reader.close();
        } catch (IOException ignore) {
            ignore.printStackTrace();
        }
    }


    //根据id查询user表数据
    @Test
    public void testSelectUserInfoByUsernameAndSex() {
        try {
        /*这个字符串由 userMapper.xml 文件中 两个部分构成
            <mapper namespace="com.mybatis.demo"> 的 namespace 的值
            <select id="selectUserById" > id 值*/
            /*可以单独写 <select id="selectUserById" > id 值*/

//            String statement = "com.mybatis.demo.selectUserInfoByUsernameAndSex";
            String statement = "selectUserInfoByUsernameAndSex";
            UserInfo userInfo = new UserInfo();
            userInfo.setUsername("Jack");
            userInfo.setSex(1);
            List<UserInfo> result = sqlSession.selectList(statement, userInfo);
            log.info("result={}", result);
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }
}

此时如果用户名或者性别为空，那么结果为空，不符合我们的需求。我们可以使用 if 语句来判断

1.2 动态SQL的写法

    <select id="selectUserInfoByUsernameAndSex" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info where
        <if test="username != null">
            username=#{username}
        </if>
        <if test="username != null">
            and sex=#{sex}
        </if>
    </select>

再次调用上面的测试方法，当用户名为空时，我们发现请求报错了。因为当用户名为空时，SQL语句是：
select * from user where and sex=#{sex}；当性别为空时，sql是：select * from user where username=#{username}。显然前一个语句是错的，那么我们该怎么解决呢？请看 if + where 用法

2.if + where 用法

    <select id="selectUserInfoByUsernameAndSex" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info
        <where>
            <if test="username != null">
                username=#{username}
            </if>

            <if test="sex != null">
                and sex=#{sex}
            </if>
        </where>
    </select>

<where>…</where> 标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

3.if + set 用法

<set> … 、 标签的用法和 <where>…</where> 相似，

 <!-- 根据 id 更新 user_info 表的数据 -->
    <update id="updateUserInfoById" parameterType="UserInfo">
        update user_info u
        <set>
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex}
            </if>
        </set>
        where id=#{id}
    </update>

当username 为空时，SQL语句为：update user_info u SET u.sex = ? where id=?
当sex为空时，SQL语句为：update user_info u SET u.username = ? where id=? ，对没错，** set 标签会剔除最后的"，" **

3.choose 用法

需求：当我们查询时有很多条件，但是只要满足其中一条即可，类似的需求可以使用 choose 标签

    <!--choose(when,otherwise) 标签-->
    <select id="selectUserInfoByChoose" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info
        <where>
            <choose>
                <when test="id !='' and id != null">
                    id=#{id}
                </when>
                <when test="username !='' and username != null">
                    and username=#{username}
                </when>
                <otherwise>
                    and sex=#{sex}
                </otherwise>
            </choose>
        </where>
    </select>

查询总共有三个条件，但是只要有一个满足条件了，后面就不在判断了。
当id 不为空时，sql语句为 select * from user_info WHERE id=?
当id 为空，当username不为空时，sql语句为 select * from user_info WHERE username=?
如果username 也为空时，sql语句为 select * from user_info WHERE sex=?

5. trim 用法

trim标记是一个格式化的标记，可以完成set或者是where标记的功能
5.1 用 trim 改写上面第二点的 if+where 语句

 <select id="selectUserInfoByUsernameAndSex" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info
        <!--<where>-->
            <!--<if test="username != null">-->
                <!--username=#{username}-->
            <!--</if>-->

            <!--<if test="sex != null">-->
                <!--and sex=#{sex}-->
            <!--</if>-->
        <!--</where>-->
         <!--prefix：标签；prefixoverride：去掉第一个and或者是or -->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="username != null">
                and username=#{username}
            </if>
            <if test="sex != null">
                and sex=#{sex}
            </if>
        </trim>
    </select>

5.2 用 trim 改写上面第三点的 if+set 语句

     <update id="updateUserInfoById" parameterType="UserInfo">
        update user_info u
        <!--<set>-->
            <!--<if test="username != null and username != ''">-->
                <!--u.username = #{username},-->
            <!--</if>-->
            <!--<if test="sex != null and sex != ''">-->
                <!--u.sex = #{sex}-->
            <!--</if>-->
        <!--</set>-->
        <!--prefix：标签； suffixOverrides：去掉最后一个逗号（也可以是其他的标记）-->
        <trim prefix="set" suffixOverrides=",">
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex},
            </if>
        </trim>
        where id=#{id}
    </update>

6. SQL片段

有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。
比如：假如我们需要经常根据用户名和性别来进行联合查询，那么我们就把这个代码抽取出来，如：

6.1 SQL片段

    <!-- 定义 sql 片段 -->
    <sql id="selectUserByUserNameAndSexSQL">
        <if test="username != null and username != ''">
            AND username = #{username}
        </if>
        <if test="sex != null and sex != ''">
            AND sex = #{sex}
        </if>
    </sql>

6.2 引用sql片段

    <select id="selectUserInfoByUsernameAndSex" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info
     
        <!--prefix：标签；prefixoverride：去掉第一个and或者是or -->
        <trim prefix="where" prefixOverrides="and | or">
            <!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
            <include refid="selectUserByUserNameAndSexSQL"></include>
            <!-- 在这里还可以引用其他的 sql 片段 -->
        </trim>
    </select>

** 注意：**
①、最好基于 单表来定义 sql 片段，提高片段的可重用性
②、在 sql 片段中最好不要包括 where

7.foreach 用法

需求：查询id为1,2,4,5的用户，一般的sql语句为：
select * from user where id=1 or id=2 or id=3;
或者
select * from user where id in (1,2,3);

7.1 建立一个 UserForm 类，里面封装一个 List ids 的属性

@Data
public class UserInfoForm {
    private List<Integer> ids;
}

7.2 用 foreach 改写 select * from user where id=1 or id=2 or id=3;

    <select id="selectUserByListId" parameterType="com.mybatis.demo.model.UserInfoForm" resultType="UserInfo">
        select * from user_info
        <where>
            <!--
                collection:指定输入对象中的集合属性
                item:每次遍历生成的对象
                open:开始遍历时的拼接字符串
                close:结束时拼接的字符串
                separator:遍历对象之间需要拼接的字符串
                select * from user where 1=1 and (id=1 or id=2 or id=3)
              -->
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

如果 ids 为空，则sql语句为：select * from user_info
如果ids 不为空，则sqql 语句为 ： select * from user_info WHERE ( id=? or id=? or id=? or id=? )

7.3 用 foreach 来改写 select * from user where id in (1,2,3)

    <select id="selectUserByListId" parameterType="com.mybatis.demo.model.UserInfoForm" resultType="UserInfo">
        select * from user_info
        <where>
            <!--
                collection:指定输入对象中的集合属性
                item:每次遍历生成的对象
                open:开始遍历时的拼接字符串
                close:结束时拼接的字符串
                separator:遍历对象之间需要拼接的字符串
                select * from user where 1=1 and id in (1,2,3)
              -->
            <foreach collection="ids" item="id" open="and id in (" close=") " separator=",">
                #{id}
            </foreach>
        </where>
    </select>

如果 ids 为空，则sql语句为：select * from user_info
如果ids 不为空，则sqql 语句为 ： select * from user_info WHERE id in ( ? , ? , ? , ? )

7.4 测试

    public void testSelectUserByListId() {
        try {
            UserInfoForm uv = new UserInfoForm();
            List<Integer> ids = new ArrayList<>();
            ids.add(1);
            ids.add(2);
            ids.add(3);
            ids.add(50);
            uv.setIds(ids);
            List<UserInfo> listUser = sqlSession.selectList("selectUserByListId", uv);
            for(UserInfo info : listUser){
                log.info("info={}", info);
            }
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

总结

动态的sql其实就是根据不同的条件拼接sql的过程，通常的做法是先写原生SQL，再写动态sql，最后再做单元测试时打印sql语句，尽可能防止出错。