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>