mybatis（11）--动态sql

1.什么是动态sql？
mybatis核心对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接组装。
2.需求
用户综合信息查询和用户综合信息查询总数的查询条件可能为空，那么我们可以增加条件查询条件不为空时才进行查询条件的拼接
3.解决
对其statement使用动态sql
4.具体实现
mapper.xml

<!-- 综合查询用户信息 -->
    <select id="findUserList" parameterType="com.ddd.mybatis.pojo.UserQueryVo" resultType="com.ddd.mybatis.pojo.UserCustom">
        <!-- SELECT * FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}%' -->
        SELECT * FROM USER
        <where>
            <!-- 当第一个条件满足时where可以自动去除这个and -->
            <if test="userCustom!=null">
                <if test="userCustom.sex!=null and userCustom.sex!=''">
                    and user.sex=#{userCustom.sex}
                </if>
                <if test="userCustom.username!=null and userCustom.username!=''">
                    and user.username LIKE '%${userCustom.username}%'
                </if>
            </if>
        </where>
    </select>
    <!-- 综合查询用户信息总数 -->
    <select id="findUserCount" parameterType="com.ddd.mybatis.pojo.UserQueryVo" resultType="int">
        <!-- SELECT COUNT(*) FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}%' -->
        SELECT COUNT(*) FROM USER
        <where>
            <if test="userCustom!=null">
                <if test="userCustom.sex!=null and userCustom.sex!=''">
                    and user.sex=#{userCustom.sex}
                </if>
                <if test="userCustom.username!=null and userCustom.username!=''">
                    and user.username LIKE '%${userCustom.username}%'
                </if>
            </if>
        </where>
    </select>

测试：

@Test
    public void testFindUserList() {
        //由于之前是在UserDaoImpl中每次获取sqlSession，所以这里需要我们手动写
        SqlSession sqlSession=sqlSessionFactory.openSession();
        //mybatis自动生成mapper代理对象，代理对象内部调用selectOne或者selectList
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        //调用userMapper的方法,但这里如果不小心用一个单个user来接收，那会报错
        UserQueryVo userQueryVo=new UserQueryVo();
        UserCustom userCustom=new UserCustom();
        //userCustom.setSex("男");
        userCustom.setUsername("张伟");
        userQueryVo.setUserCustom(userCustom);
        List<UserCustom> list=userMapper.findUserList(userQueryVo);
        System.out.println(list);
    }

这里将性别注释掉，来看看日志打印出来的sql是什么：

SELECT * FROM USER WHERE user.username LIKE '%张伟%' 

也就是说，当查询条件为空时就不拼接sql了

但是仔细发现，上面两个的sql查询条件是重复的我们写了两遍，可不可以把它们提取出来呢，当然了！

5.mybatis还有个sql标签可以定义sql片段

<!-- sql片段 id是它的标识，一般是基于单表来定义sql，这样sql可重用性才高 ，一般不用where-->
    <sql id="query_user_where">
        <!-- 当第一个条件满足时where可以自动去除这个and -->
        <if test="userCustom!=null">
            <if test="userCustom.sex!=null and userCustom.sex!=''">
                and user.sex=#{userCustom.sex}
            </if>
            <if test="userCustom.username!=null and userCustom.username!=''">
                and user.username LIKE '%${userCustom.username}%'
            </if>
        </if>

    </sql>

怎么引用呢？

<!-- 综合查询用户信息 -->
    <select id="findUserList" parameterType="com.ddd.mybatis.pojo.UserQueryVo" resultType="com.ddd.mybatis.pojo.UserCustom">
        <!-- SELECT * FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}%' -->
        SELECT * FROM USER 
        <where>
            <!-- 引用sql片段的id，如果不在本mapper中需要加namespace，在其下边还可以定义其他sql片段，这也就是为什么在sql片段定义中不写where -->
            <include refid="query_user_where"></include>
        </where>

    </select>
    <!-- 综合查询用户信息总数 -->
    <select id="findUserCount" parameterType="com.ddd.mybatis.pojo.UserQueryVo" resultType="int">
        <!-- SELECT COUNT(*) FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}%' -->
        SELECT COUNT(*) FROM USER
        <where>
            <!-- 引用sql片段的id，如果不在本mapper中需要加namespace，在其下边还可以定义其他sql片段，这也就是为什么在sql片段定义中不写where -->
            <include refid="query_user_where"></include>
        </where>
    </select>

测试是一样的效果，在此不多做演示

6.向动态sql中传递数组或者list，mybatis使用foreach解析
例如：这是输入多个id进行查询（两种实现）

SELECT * FROM USER WHERE id=1 OR id=3 OR id=5 OR id=10
SELECT * FROM USER WHERE id IN (1,3,5,10)

那在mybatis中怎么实现呢？

我们可以在输入参数类型中变成List<Integer> ids
这样支持多个id传入
首先在UserQueryVo中添加这样一个属性：

//传入多个id
    private List<Integer> ids;
    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

在查询条件中使用foreach遍历：

<sql id="query_user_where">
        <!-- 当第一个条件满足时where可以自动去除这个and -->
        <if test="userCustom!=null">
            <if test="userCustom.sex!=null and userCustom.sex!=''">
                and user.sex=#{userCustom.sex}
            </if>
            <if test="userCustom.username!=null and userCustom.username!=''">
                and user.username LIKE '%${userCustom.username}%'
            </if>
            <if test="ids!=null">
                <!-- 使用foreach遍历传入ids 
                    collection：指定输入对象中集合的属性名
                    item：每个遍历生成的对象名
                    open：开始遍历时要拼接的字符串
                    例如：SELECT * FROM USER WHERE user.username LIKE '%伟%' AND ( id=10 OR id=9 OR id=3)
                    如果不加括号，sql就错了
                    close：结束遍历时拼接的串
                    separator：遍历的两个对象中需要拼接的串
                -->
                <foreach collection="ids" item="user_id" open="AND(" close=")" separator="OR">
                    id=#{user_id}
                </foreach>

            </if>
        </if>

测试代码：

@Test
    public void testFindUserList() {
        //由于之前是在UserDaoImpl中每次获取sqlSession，所以这里需要我们手动写
        SqlSession sqlSession=sqlSessionFactory.openSession();
        //mybatis自动生成mapper代理对象，代理对象内部调用selectOne或者selectList
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        //调用userMapper的方法,但这里如果不小心用一个单个user来接收，那会报错
        UserQueryVo userQueryVo=new UserQueryVo();
        UserCustom userCustom=new UserCustom();
        List<Integer> ids=new ArrayList<>();
        ids.add(1);
        ids.add(3);
        userQueryVo.setIds(ids);
        userCustom.setSex("男");
        //userCustom.setUsername("张伟");
        userQueryVo.setUserCustom(userCustom);
        List<UserCustom> list=userMapper.findUserList(userQueryVo);
        System.out.println(list);
    }

控制台查询结果：
执行的sql：

Preparing: SELECT * FROM USER WHERE user.sex=? AND( id=? OR id=? ) 
2017-07-18 09:53:02,244 [main] [com.ddd.mybatis.mapper.UserMapper.findUserList]-[DEBUG] ==> Parameters: 男(String), 1(Integer), 3(Integer)

结果：

[User [id=1, username=小明, sex=男, birthday=Fri Sep 09 00:00:00 CST 2011, address=北京市海淀区]]

<!-- 实现
                SELECT * FROM USER WHERE user.username LIKE '%伟%' AND id IN (1,3,5,10)
                将open和close和separator更改即可
                <foreach collection="ids" item="user_id" open="AND id IN(" close=")" separator=",">
                    #{user_id}
                </foreach>
                 -->