Mybatis[7] - 动态SQL

博由

    动态SQL算是Mybatis一个比较给力的功能了，可以通过一些标签来设置一些超级复杂、功能强大的SQL，本文就是简单概述如何使用一些动态SQL的标签。
    主要包括：
    1. if：条件判断
    2. choose/when/otherwise：类似switch
    3. foreach：循环
    4. trim/where/set：

If

    条件判断：
    <if test="ognl表达式">
        SQL Fragement
    </if>

Case1: 错误异常

<!-- 错误Case -->
<select id="findByName1" parameterType="string" resultType="User">
    SELECT * FROM user WHERE 1 = 1
    <if test="name != null">
        AND name = #{name}
    </if>
</select>

/**
  * 异常：There is no getter for property named 'name' in 'class java.lang.String'
  */
@Test(expected = PersistenceException.class)
public void testIfException(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName1", "admin");
        System.out.println(users);
        session.close();
}

Case2: 正常

<select id="findByName2" parameterType="string" resultType="User">
    SELECT * FROM user WHERE 1 = 1
    <if test="_parameter != null">
        AND name=#{name}
    </if>
</select>

/**
  * 正常case：使用_parameter替代之前的参数
  */
@Test
public void testIfSuccess(){
   SqlSession session = instance.getSession();
   List<User> users = session.selectList(User.class.getName() + ".findByName2", "admin");
   System.out.println(users);
   session.close();
}

Case3: 特殊字符异常

<!--
   xml标签有一些特殊字符，不能使用例如：
   例如：<, &
-->
<select id="findByName3" parameterType="string" resultType="User">
    SELECT * FROM user
    WHERE 1 = 1 AND id<10
    <if test="_parameter != 'admin1'.toString()">
             AND name = #{name}
    </if>
</select>

/**
  * 异常Case
  */
@Test
public void testIfExceptionForNotCDATA(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName3", "admin");
        System.out.println(users);
        session.close();
}

Case4: CDATA处理特殊字符

    <!--
        使用<![CDATA[]]>处理特殊字符
    -->
    <select id="findByName4" parameterType="string" resultType="User">
        <![CDATA[
        SELECT * FROM user
        WHERE 1 = 1 AND id<10
        ]]>
        <if test="_parameter != 'admin1'.toString()">
            AND name = #{name}
        </if>

    </select>

   /**
     * 正常Case: <![CDATA[ 内容 解决]]>
     */
    @Test
    public void testIfSuccessForCDATA(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName4", "admin");
        System.out.println(users);
        session.close();
    }

IF 总结

1. 语法：<if test="ognl表达式">sql fragement</if>;
2. parameterType问题，如果使用属性名称会抛出错误:There is no getter for property named 'name' in 'class java.lang.String'，需要使用_parameter替换属性名；
3. xml特殊字符问题，例如：<,&等字符，需要使用<![CDATA[内容]]>处理。
4. 可以参考源码：DynamicContext.java

Choose

语法&案例

语法:
<choose>
    <when test="">sql fragment</when>
    .
    .
    .
    <otherwise>
    </otherwise>
</choose>

    <!--
     choose类似于switch
    -->
    <select id="findByName5" parameterType="string" resultType="User">
        SELECT * FROM user WHERE 1 = 1
        <choose>
            <when test="_parameter == 'admin'.toString()">
                AND <![CDATA[
                    id < 10
                ]]> AND name=#{name}
            </when>
            <when test="_parameter == 'admin1'.toString()">
                AND id between 10 and 50 AND name=#{name}
            </when>
            <otherwise>
                AND id >= 50 AND name=#{name}
            </otherwise>

        </choose>

    </select>

   /**
     * choose: When name == 'admin'
     */
    @Test
    public void testChooseForAdmin(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName5", "admin");
        System.out.println(users);
        session.close();
    }

    /**
     * choose: When name == 'admin1'
     */
    @Test
    public void testChooseForAdmin1(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName5", "admin1");
        System.out.println(users);
        session.close();
    }

    /**
     * choose: otherwise
     */
    @Test
    public void testChooseForOtherwise(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName5", "admin6");
        System.out.println(users);
        session.close();
    }

choose总结

与java的switch很相似，通常使用在多重选择判断的情况。

foreach

语法&案例

   <!-- foreach
        collection: 要遍历的集合类型，一般list, set等
        item: 元素变量
        open: 开始的字符
        close: 关闭的字符
        separator: 分隔符
        index: 索引变量，从0开始
        例如：select * from user where 1=1 and id in (?,?,?,...,?)
    -->
    <select id="findByIds" parameterType="list" resultType="User">
        SELECT * FROM user WHERE 1 = 1 AND id IN
        <!-- 注意的collection表示是的集合的类型 -->
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

   /**
     * foreach
     */
    @Test
    public void tesForeach(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByIds", Arrays.asList(1, 2, 3, 4, 5));
        System.out.println(users);
        session.close();
    }

总结

foreach目的是用于遍历集合拼凑SQL。

bind

绑定：可以从 OGNL 表达式中创建一个变量并将其绑定到上下文

语法&案例

    <!-- bind
        <bind name="名称" value="值"/>，其中value支持ognl表达式。
    -->
    <select id="findLikeByName" parameterType="string" resultType="User">
        <bind name="likePattern" value="'%' + _parameter + '%'" />
        SELECT * FROM user
        WHERE name like #{likePattern}
    </select>

    /**
     * bind
     */
    @Test
    public void testBind(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findLikeByName", "admin1");
        System.out.println(users);
        session.close();
    }

where

在拼接SQL时，最让人受不了的就是WHERE，前后的AND|OR的关键字。为了避免出现这种拼接SQL的情况，使用WHERE。

case1: where测试

    <!-- where for not and -->
    <select id="findByName6" parameterType="string" resultType="User">
        SELECT * FROM user
        <where>
            <if test="_parameter != null">
                name = #{name}
            </if>
        </where>
    </select>

    /**
     * where for null
     */
    @Test
    public void testWhereForNull(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName6", null);
        System.out.println(users);
        session.close();
    }

    /**
     * where for value
     */
    @Test
    public void testWhereForNotNull(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName6", "admin");
        System.out.println(users);
        session.close();
    }

Case2: 测试where and问题

    <!-- where for and -->
    <select id="findByName7" parameterType="string" resultType="User">
        SELECT * FROM user
        <where>
            <if test="_parameter != null">
                AND name = #{name}
            </if>
        </where>
    </select>

     /**
     * where for and
     */
    @Test
    public void testWhereForAnd(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName7", "admin");
        System.out.println(users);
        session.close();
    }

总结

之前我们没有where之前，往往会使用where 1=1 作为前缀，然后后面再使用AND或者OR；如果使用了<where>，那么就不需要1＝1，并且会维护前置AND|OR关键字，可以加可以不加。

set

语法&案例

目的: 用于update的set语法结构。

<update id="updateById" parameterType="User">
    UPDATE user
    <set>
    <if test="name != null">name=#{name},</if>
    <if test="createdAt != null">created_at=#{createdAt},</if>
    <if test="updatedAt != null">updated_at=#{updatedAt},</if>
    </set>
    WHERE id=#{id}
</update>

    /**
     * update
     */
    @Test
    public void testUpdateForSet(){
        SqlSession session = instance.getSession();
        session.update(User.class.getName() + ".updateById", new User(5, "wangzhiping", new Date(), new Date()));
        session.commit();
        session.close();
    }

trim

自定义一些前缀或者后缀的处理方式。

Case1: 实现where

    <!-- trim implement where -->
    <select id="findByName8" parameterType="string" resultType="User">
        <!--
            prefix: 前缀字符
            prefixOverrides: 第一个元素去覆盖
        -->
        SELECT * FROM user
        <trim prefix="WHERE" prefixOverrides="AND | OR">
            <if test="_parameter != null">
                AND name = #{name}
            </if>
        </trim>
    </select>

    /**
     * trim实现where
     */
    @Test
    public void testTrimImplWhere(){
        SqlSession session = instance.getSession();
        List<User> users = session.selectList(User.class.getName() + ".findByName8", "admin");
        System.out.println(users);
        session.close();
    }

Case2: 实现set

    <!-- trim implement set -->
    <update id="updateById2" parameterType="User">
        UPDATE user
        <!-- 前面增加set，删除最后一个"," -->
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null">name=#{name},</if>
            <if test="createdAt != null">created_at=#{createdAt},</if>
            <if test="updatedAt != null">updated_at=#{updatedAt},</if>
        </trim>
        WHERE id=#{id}
    </update>

    /**
     * trim实现set
     */
    @Test
    public void testTrimForSet(){
        SqlSession session = instance.getSession();
        session.update(User.class.getName() + ".updateById", new User(6, "wangzhiping", new Date(), new Date()));
        session.commit();
        session.close();
    }

总结

trim一共四个属性:
1. prefix：设置前缀字符
2. prefixOverrides：设置前缀需要覆盖（删除）的字符
3. suffix：设置后缀字符
4. suffixOverrides：设置后缀需要覆盖的字符；

Github地址

branch v1.7: https://github.com/wzpthq/csdn_mybatis.git