MyBatis常用SQL语句整理笔记

最近使用Mybatis作为持久层框架，自然/动态sql写得也比较多了，最常见的就是在查询语句中使用if标签来动态地改变过滤条件。 
Mybatis强大特性之一就是它的动态sql，免除了拼接sql带来的各种麻烦。 
在项目开发过程中，常见的和不常见的问题都有碰到过，所以在这总结一下。 
if 
choose(when,otherwise) 
trim(where,set) 
foreach 

1.通常用于多条件组合查询 

<select id="productId" parameterType="Product" resultMap="getProduct">
    SELECT * FROM PRODUCT t WHERE (1=1)
    <if test="Product.productId!=null">
        AND t.PRODUCT_ID = #{Product.productId}
    </if>
    <if test="Product.productName!=null">
        AND t.PRODUCT_NAME like '%#{Product.productName}%'
    </if>
    <if test="Product.inStock!=null">
        AND t.PRODUCT.INSTOCK = 0
    </if>
    ORDER BY SEQUENCE_NO
</select>

2.这适用于数据库有默认值的时候可以不让插入空值 

<insert id="addCategory" parameterType="Category">
    INSERT INTO CATEGORY(
    <if test="categoryName!=null and categoryName!='' ">
        CATEGORY_NAME
    </if>
    <if test="categoryId!=null and categoryId!='' ">
        CATEGORY_ID
    </if>
    ADD_TIME)
    VALUES(
        <if test="categoryName!=null and categoryName!='' ">
            #{categoryName, jdbcType=VARCHAR}
        </if>
        <if test="categoryId!=null and categoryId!='' ">
            #{categoryId, jdbcType=NUMERIC}
        </if>
        currentTimeStamp
    )
</insert>

3.这条动态地修改语句用得非常多，是因为很多时候我们在做修改操作时并不确定到底要修改哪些字段（哪些属性），可能有的需要保存原值不变. 
这时候就可以做动态的sql，你新建一个对象后,将需要修改的字段附上新值，这样不用修改的属性在这个对象上表现的是null，调用这个动态的sql时便可以完成部分修改。 

<update id="updateCategory" parameterType="Category">
    update CATEGORY t SET
    <if test="categoryName!=null">
        t.CATEGORY_NAME = #{categoryName, jdbcType=VARCHAR},
    </if>
    <if test="updateUser!=null">
        t.UPDATE_USER = #{updateUser, jdbcType=VARCHAR},
    </if>
    t.UPDATE_TIME = currentTimeStamp
    WHERE t.CATEGORY_ID = #{categoryId, jdbcType=NUMERIC}
</update>

4.choose,when,otherwise 
适用场景：我们不想用到所有的条件语句，而只想从中择其一二。 
针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。 
（我感觉它有点像提供多种条件规则时，而这些规则又可以综合写在一起时） 

<select id="findInStockLike" resultType="Product">
    SELECT * FROM PRODUCT WHERE STATE = 'INSTOCK'
    <choose>
        <when>
            AND TITLE LIKE #{title}
        </when>
        <when>
            AND BRAND_NAME LIKE #{brand.name}
        </when>
        <otherwise>
            AND INMARKETING = 0
        </otherwise>
    </choose>
</select>

5.where, set, trim 
为了避免当if动态条件都不成立时，或者第一个条件不成立第二个条件成立时出现 
诸如"select * from TableA where" 
或者"select * from TableA and where"病态sql， 
我们可以使用where, set, trim标签来解决。 
5.1)where 

<select id="findInStockProductLike" resultType="Product">
    SELECT * FROM PRODUCT
    <when>
        <if    test="state!=null">
            STATE = #{state}
        </if>
        <if test="title!=null">
            AND TITLE LIKE #{title}
        </if>
        <if test="brand!=null and brand.name!=null">
            AND BRAND_NAME LIKE #{brand.name}
        </if>
    </when>
</select>

在实际应用中，我通常是不写where标签，而在where关键字之后加上1=1的条件。即不管有无动态条件，总可以得到完整的sql：select * from A where 1=1。。。 
5.2)set 

<update id="updateUserInfo">
    UPDATE USER
    <set>
        <if test="userName!=null">USER_NAME = #{userName}</if>
        <if test="userPsw!=null">USER_PSW = #{userPsw}</if>
        <if test="userEmail!=null">USER_EMAIL = #{userEmail}</if>
    </set>
    WHERE USER_ID = #{userId}
</update>

6.foreach 
foreach有时候在项目中会遇到，而且不止一次，用的时候是需要动点脑子的。 
通常用于筛选出在多个值组成的一个集合中或者排除多个值的场景. 
说白了，也就是我们之前写sql时用到in、not in的时候：（集合是动态不确定的，需要从前台传值过来） 

<select id="selectProdcutNoInOrder" resultType="String">
    SELECT COUNT(0) FROM PRODUCT a LEFT JOIN ORDER_INFO b
    ON a.PRODUCT_ID = b.PRODUCT_ID
    WHERE a.STATUS in('1', '2', '3', '6')
    <if test="list.size()>0">
        AND b.PHONE_NUM IN
        <foreach item="phoneNumList" collection="list" open="(" separator="," close=")">
            #{phoneNumList.num}
        </foreach>
    </if>
</select>

foreach 元素的功能是非常强大的，它允许你指定一个集合，声明可以用在元素体内的集合项和索引变量。 
它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。 
这个元素是很智能的，因此它不会偶然地附加多余的分隔符。 
注意,你可以将一个 List 实例或者数组作为参数对象传给 MyBatis，当你这么做的时候，MyBatis 会自动将它包装在一个 Map 中并以名称为键。 
List 实例将会以“list”作为键，而数组实例的键将是“array”。 

<select id="selectProductIn" resultType="com.dowik.dowikmall.Product">
    SELECT * FROM PRODUCT p WHERE PRODUCT_ID IN
    <foreach item="productIdList" index="index" collection="list" open="(" separetor="," close=")">
        #{productIdList}
    </foreach>
</select>