Mybatis学习笔记(六) —— 动态sql

　　通过mybatis提供的各种标签方法实现动态拼接sql。

　　需求：根据性别和名字查询用户

　　查询sql：

　　SELECT id, username, birthday, sex, address FROM `user` WHERE sex = 1 AND username LIKE '%张%'

一、if标签

1.1 Mapper.xml文件

　　UserMapper.xml配置sql，如下：

<!-- 根据条件查询用户 -->
    <select id="queryUserByWhere" parameterType="user" resultType="user">
        SELECT id, username, birthday, sex, address FROM `user`
        WHERE sex = #{sex} AND username LIKE
        '%${username}%'
    </select>

1.2 Mapper接口

　　/**
     * 根据条件查询用户
     * @param user
     * @return
     */
    List<User> queryUserByWhere(User user);

1.3 测试方法

　　@Test
    public void testQueryUserByWhere() throws Exception {
        // mybatis和spring整合，整合之后，交给spring管理
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 创建Mapper接口的动态代理对象，整合之后，交给spring管理
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 使用userMapper执行根据条件查询用户
        User user = new User();
        user.setSex("男");
        user.setUsername("张");
        List<User> list = userMapper.queryUserByWhere(user);
        for (User user2 : list) {
            System.out.println(user2);
        }
        // mybatis和spring整合，整合之后，交给spring管理
        sqlSession.close();
    }

1.4 效果

　　

　　如果注释掉 user.setSex("男")，测试结果如下图：

　　

　　测试结果二很显然不合理。

　　按照之前所学的，要解决这个问题，需要编写多个sql，查询条件越多，需要编写的sql就更多了，显然这样是不靠谱的。

　　解决方案，使用动态sql的if标签

1.5 使用if标签

　　改造UserMapper.xml，如下：

<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
    SELECT id, username, birthday, sex, address FROM `user`
    WHERE 1=1
    <if test="sex != null and sex != ''">
        AND sex = #{sex}
    </if>
    <if test="username != null and username != ''">
        AND username LIKE
        '%${username}%'
    </if>
</select>

1.6 效果

　　

　　测试OK

 

　　注意：判断是否为空字符串时，单（双）引号里面不能填空格，否则会报错

　　

二、Where标签

　　上面的sql还有where 1=1 这样的语句，很麻烦

　　可以使用where标签进行改造

 

　　改造UserMapper.xml，如下

　　<!-- 根据条件查询用户 -->
    <select id="queryUserByWhere" parameterType="user" resultType="user">
        SELECT id, username, birthday, sex, address FROM `user`
        <!-- where标签可以自动添加where,同时处理sql语句中第一个前AND关键字 -->
        <where>
            <if test="sex!=null and sex != ' '">
                AND sex = #{sex}
            </if> 
            <if test="username!=null and username != ''">
                 AND username LIKE
                '%${username}%'
            </if>
        </where>
    </select>

三、sql片段

　　Sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的。

　　把上面例子中的id, username, birthday, sex, address提取出来，作为sql片段，如下：

<!-- 声明sql片段 -->
    <sql id="userFields">
        id, username, birthday, sex, address
    </sql>

    <!-- 根据条件查询用户 -->
    <select id="queryUserByWhere" parameterType="user" resultType="user">
        SELECT <include refid="userFields"/> FROM `user`
        <!-- where标签可以自动添加where,同时处理sql语句中第一个前AND关键字 -->
        <where>
            <if test="sex!=null and sex != ' '">
                AND sex = #{sex}
            </if> 
            <if test="username!=null and username != ''">
                 AND username LIKE
                '%${username}%'
            </if>
        </where>
    </select>

 　　如果要使用别的Mapper.xml配置的sql片段，可以在refid前面加上对应的Mapper.xml的namespace

　　

四、foreach标签

　　向sql传递数组或List，mybatis使用foreach解析

 

　　需求：根据多个id查询用户信息

　　查询sql：SELECT * FROM user WHERE id IN (1,10,24)

4.1 改造QueryVo

public class QueryVo {
    // 包含其他的pojo
    private User user;
    
    private List<Integer> ids;
    
    get/set...
}

4.2 Mapper.xml文件

　　UserMapper.xml添加sql：

<!-- 根据ids查询用户 -->
<select id="queryUserByIds" parameterType="queryVo" resultType="user">
    SELECT * FROM `user`
    <where>
        <!-- foreach标签，进行遍历 -->
        <!-- collection：遍历的集合，这里是QueryVo的ids属性 -->
        <!-- item：遍历的项目，可以随便写，，但是和后面的#{}里面要一致 -->
        <!-- open：在前面添加的sql片段 -->
        <!-- close：在结尾处添加的sql片段 -->
        <!-- separator：指定遍历的元素之间使用的分隔符 -->
        <foreach collection="ids" item="item" open="id IN (" close=")"
            separator=",">
            #{item}
        </foreach>
    </where>
</select>

4.3 Mapper接口

　　在UserMapper接口中添加方法：

    /**
     * 根据ids查询用户
     * @param queryVo
     * @return
     */
    List<User> queryUserByIds(QueryVo queryVo);

4.4 测试方法

    @Test
    public void testQueryUserByIds() throws Exception {
        // mybatis和spring整合，整合之后，交给spring管理
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 创建Mapper接口的动态代理对象，整合之后，交给spring管理
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        QueryVo queryVo = new QueryVo();
        
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(10);
        ids.add(16);
        queryVo.setIds(ids);
        List<User> list = userMapper.queryUserByIds(queryVo);
        for (User user : list) {
            System.out.println(user);
        }
        
        // mybatis和spring整合，整合之后，交给spring管理
        sqlSession.close();
    }

　　效果：