Mybaits(7) Mybatis动态 SQL

1.概述

　　我们在使用JDBC或者类似Hibernate的其他框架时，需要根据需求去拼装sql，这是很烦的一件事情。有时一个查询有许多查询条件，有时需要控制有点条件为空的情况，我们使用其他框架进行大量的Java代码进行判断，可读性差，而Mybatis框架提供了对sql语句动态组装能力，使用xml的几个简单元素便可完成sql相应的功能。大量的判断可以MyBatis的映射配置文件xml中进行配置，大大减少了代码量，同时也可以在注解中配置sql，但由于注解功能受限，对复杂sql可读性差，所以很少使用。

MyBatis的动态SQL包括以下几种元素：

元素	作用	备注
if	判断语句	单条件分支判断
choose（when，others）	相当于Java中的swicch和case语句	多条件分支判断
trim（where，set）	辅助元素，用于处理特定的sql拼装问题，比如去掉多余的and、or等	用于处理sql拼装的问题
foreach	循环语句	在in语句等列举条件常用

2.if元素

if元素是最常用的判断语句，相当于Java中的if语句，常和test属性联合使用。if使用很简单，下面通过实例说明。

我们根据实体类的不同取值，使用不同的sql语句来进行查询，如果username不为空加入该条件查询，如果address不为空加入该条件进行查询。

第一步：持久层dao接口

/**
     * 根据用户信息查询用户
     * 
     * @param user
     * @return
     */
    List<User> findByUser(User user);

第二步：持久层 Dao 映射配置 

<!-- 根据用户信息查询user -->
    <select id="findByUser" resultType="user1" parameterType="user1">
        select * from user where 1=1
        <if test="userName!=null and userName !='' ">
            and username like #{userName}
        </if>
        <if test="userAddress !=null">
            and address like #{userAddress}
        </if>
    </select>

第三步：测试

@Test
    public void findByUser() {
        User u = new User();
        u.setUserName("王%");
        u.setUserAddress("北京%");
        List<User> users  = userDao.findByUser(u);
        for(User user:users) {
            System.out.println(user);
        }
    }

3.where、trim、set元素

（1）where

　　为了简化where 1=1 的条件拼装，我们可以采用<where>标签。

　　在上面例子中，我们使用了where 1=1，如果不这样拼接就会 产生where and这种情况，这样是错误的。

　　上面的例子我们可以改造成：

<!-- 根据用户信息查询user -->
    <select id="findByUser" resultType="user1" parameterType="user1">
        select * from user
        <where>
            <if test="userName!=null and userName !='' ">
                and username like #{userName}
            </if>
            <if test="userAddress !=null">
                and address like #{userAddress}
            </if>
        </where>

这样和拼接where 1=1 效果一样。

（2）trim

　　有时候需要去掉一些特殊的sql语法，比如常见的and、or。我们使用trim元素也可以达到预期效果。

    <select id="findByUser" resultType="user1" parameterType="user1">
        select * from user
        <trim prefix="where" prefixOverrides="and">
            <if test="userName!=null and userName !='' ">
                and username like #{userName}
            </if>
            <if test="userAddress !=null">
                and address like #{userAddress}
            </if>
        </trim>
    </select>

　　trim元素表示要去掉一些特殊字符串，当时prefix代表的是语句的前缀，而prefixOverrides表示去掉的那些字符串，上面的写法基本上和where等效。

（3）set

当我们更新一个对象时，有时需要将全部字段更新，有时更新部分的就可以，我们可以通过set元素进行控制。

<!-- 更新用户 -->
    <update id="updateUser" parameterType="user1">
        update user 
        <set>
            <if test="userName !=null and userName !='' ">
            username=#{userName},
            </if>
            <if test="userBirthday !=null and userBirthday !='' ">
            birthday=#{userBirthday},
            </if>
            <if test="userSex !=null and userSex !='' ">
            sex=#{userSex},
            </if>
            <if test="userAddress !=null and userAddress !='' ">
            address=#{userAddress}
            </if>
        </set>
        where id=#{userId}
    </update>

4.choose、when、otherwise元素

在if元素的例子中我们知道相当于Java语言中的if语句，有时候我们需要第3种选择，甚至更多选择，也就是类似swicth...case...default..功能语句。在映射动态语句中choose、when和otherwise这3个元素承担了这个功能。

针对上面的查找用户的例子我们扩展下使用场景：

（1）如果用户姓名（userName）不为空，则用用户姓名作为查询条件

（2）如果用户名为空，地址不为空，则用地址作为条件查询

（3）如果这两个条件都为空，我们用用户性别进行查询。

这个场景或许不是很合适，我们主要是为了练习使用choose...where..otherwise

<select id="findByUser" resultType="user1" parameterType="user1">
        select * from user where 1=1
        <choose>
            <when test="userName!=null and userName !='' ">
                and username like #{userName}
            </when>
            <when test="userAddress ! =null ">
                and address like #{userAddress}
            </when>
            <otherwise>
                and sex = #{userSex}
            </otherwise>

        </choose>

    </select>

5. foreach 元素

 foreach 元素是一个循环语句，它作用是遍历集合，它能够很好的支持数组和List、Set接口集合，对此提供遍历功能。它往往作用于sql中的in关键字。

我们在查询用户是有这样的两个sql：

SELECT * FROM USERS WHERE username LIKE '%王%' AND (id =1 OR id =2 OR  OR id=3)
SELECT * FROM USERS WHERE username LIKE '%王%' AND id IN (1,2,3)

这时候我们需要将需要的参数封装到集合进行传参然后查询。

（1）我们在QueryVo 中加入一个 List 集合用于封装参数 

private List<Integer> ids;
    
    public List<Integer> getIds() {
        return ids;
    }

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

（2）持久层dao添加方法

/**
     *通过ids查询用户
     * @param vo
     * @return
     */
    List<User> findInIds(QueryVo vo);

（3）持久层dao配置映射文件

<!-- 通过ids集合查询用户 -->
    <select id="findInIds" resultType="user1"
        parameterType="queryvo">
        select * from user
        <where>
            <if test="ids !=null and ids.size()>0">
                <foreach collection="ids" open="id in (" close=")"
                    item="uid" separator=",">
                    #{uid}
                </foreach>
            </if>
        </where>
    </select>

<foreach>标签用于遍历集合，它的属性：

collection:代表要遍历的集合元素，注意编写时不要写#{} ,可以是一个数组，List，Set等集合。

item:代表遍历集合的每个元素，生成的变量名。

open:代表语句的开始部分。

close:代表结束部分。

sperator:代表各个元素的分隔符

 

（4）编写测试方法测试

@Test
    public void testFindInIds() {
        QueryVo vo = new QueryVo();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        ids.add(4);
        vo.setIds(ids);
        List<User> users = userDao.findInIds(vo);
        for(User user:users) {
            System.out.println(user);
        }
        
    }

（5）简化编写的sql片段

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

第一步：我们抽取上面例子sql

<!-- 抽取重复的语句代码片段 -->
    <sql id="defaultSql">
        select * from user
    </sql>

第二步：引用sql片段

<!-- 通过ids集合查询用户 -->
    <select id="findInIds" resultType="user1"
        parameterType="queryvo">
        <!-- select * from user -->
        <include refid="defaultSql"></include>
        <where>
            <if test="ids !=null and ids.size()>0">
                <foreach collection="ids" open="id in (" close=")"
                    item="uid" separator=",">
                    #{uid}
                </foreach>
            </if>
        </where>
    </select>

6.bind元素

　　该元素的作用是通过OGNL表达式去定义一个上下文量，这样方便使用。在进行模糊查询时，如果是mysql数据库，我们常常用到一个concat，它可以将“%”和参数进行连接。但是在oracle数据库中没有，那么oracle数据库通过“||”进行连接，这样的话sql需要提供两种形式去实现。有了bind元素我们不用使用数据库语言通过MyBatis动态sql就能完成。

比如我们按照用户姓名模糊查询：

<select id="findByUser" resultType="user1" parameterType="user1">
        select * from user where 1=1
        <bind name="pusername" value="'%'+userName+'%' " />
        <bind name="puseraddress" value="'%'userAddress'%' " />
        <where>
            <if test="userName!=null and userName !='' ">
                and username like #{pusername}
            </if>
            <if test="userAddress !=null">
                and address like #{puseraddress}
            </if>
        </where>
    </select>

注：userName和userAddress是传过来的参数，和“%”进行绑定后我们便可以使用。