Mybatis SQL示例

1. 概述

Mybatis XML配置文件中常用的SQL写法。

1.1 #{} 和 ${} 区别

#{} 方式

解析为SQL时，取出，并自动给其添加引号
可以防止SQL注入

${} 方式

解析为SQL时，取出，直接拼接显示在SQL中
存在SQL注入隐患
适用场景：表名 / 排序字段名传参

1.2 转义字符 < >

符号	原符号	替换符号
小于	<	<
小于等于	<=	<=
大于	>	>
大于等于	>=	>=

1.3 CDATA 区段

CDATA 区段（CDATA section）中的文本会被解析器忽略。

大于等于     <![CDATA[ >= ]]>  
小于等于     <![CDATA[ <= ]]> 
不等于       <![CDATA[ <> ]]>

1.4 test 字符串比较

注：比较的值为固定字符串时，只能是双引号

mybatis是使用的OGNL表达式来进行解析的，在OGNL的表达式中，'note'会被解析成字符，因为java是强类型的，char 和一个String 会导致不等。所以if标签中的sql不会被解析。

<if test='note == "4"'>
     name like concat('%',concat(#{name},'%'))
</if>

2. 循环 forEach

主要属性如下：

item：集合中元素迭代时的别名，
index：集合中元素迭代时的索引
open：常用语where语句中，表示以什么开始，比如以'('开始
separator：表示在每次进行迭代时的分隔符，
close 常用语where语句中，表示以什么结束
collection 指代入参集合，必须指定
- 单参数，且类型为List，则 collection 值为 list
- 单参数，且类型为array，则 collection 值为 array
- 指定参数名

	// list    List<String> ids       collection="list"
    // array   String[] ids           collection="array"
    // 自定义   @Param("codeList") List<String> codeList   collection="codeList"

	public List<Entity> queryById(List<String> ids);      

    //对应的xml中如下
    <select id="queryById" resultMap="BaseReslutMap" >
        select * FROM entity
        where id in 
        <foreach collection="list" item="userid" index="index" open="(" separator="," close=")">
            #{userid}
        </foreach>
    </select>

3. 模糊查询 concat

<select id="queryById" resultMap="BascResultMap" parameterType="entity">
    SELECT *  from entity
    <where>
        <if test="name!=null">
            name like concat('%',concat(#{name},'%'))
        </if>
    </where>
  </select>

4. 条件判定 choose (when, otherwise)

按顺序判定 when 标签中的 test 条件是否成立，如果有一个成立，则 choose 结束
都不成立，则执行 otherwise

<select id="getUserList" resultMap="resultMapUser" parameterType="User">  
    SELECT *  
      FROM User u   
    <where>  
        <choose>  
            <when test="username !=null ">  
                u.username LIKE CONCAT(CONCAT('%', #{username, jdbcType=VARCHAR}),'%')  
            </when >  
            <when test="sex != null and sex != '' ">  
                AND u.sex = #{sex, jdbcType=INTEGER}  
            </when >  
            <otherwise>  
            </otherwise>  
        </choose>  
    </where>    
</select>

5. 主键插入 selectKey

查找生成主键Key，并应用到 Insert 语句中。

<insert id="createStudentAutoKey" parameterType="StudentEntity" keyProperty="studentId">  
    <selectKey keyProperty="studentId" resultType="String" order="BEFORE">  
        select nextval('student')  
    </selectKey>  
    INSERT INTO STUDENT_TBL(STUDENT_ID,  
                            STUDENT_NAME,  
                            STUDENT_SEX,  
                            STUDENT_BIRTHDAY,  
                            STUDENT_PHOTO,  
                            CLASS_ID,  
                            PLACE_ID)  
    VALUES (#{studentId},  
            #{studentName},  
            #{studentSex},  
            #{studentBirthday},  
            #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},  
            #{classId},  
            #{placeId})  
</insert>

6. 多条件拼接 if + where

where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’
如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">  
    SELECT ST.STUDENT_ID,  
           ST.STUDENT_NAME,  
           ST.STUDENT_SEX,  
           ST.STUDENT_BIRTHDAY,  
           ST.STUDENT_PHOTO,  
           ST.CLASS_ID,  
           ST.PLACE_ID  
      FROM STUDENT_TBL ST   
    <where>  
        <if test="studentName !=null ">  
            ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')  
        </if>  
        <if test="studentSex != null and studentSex != '' ">  
            AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}  
        </if>  
        <if test="studentBirthday != null ">  
            AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}  
        </if>  
        <if test="classId != null and classId!= '' ">  
            AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}  
        </if>  
        <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">  
            AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}  
        </if>  
        <if test="placeId != null and placeId != '' ">  
            AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}  
        </if>  
        <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">  
            AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}  
        </if>  
        <if test="studentId != null and studentId != '' ">  
            AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}  
        </if>  
    </where>    
</select>

7. 多参数更新 if + set

update语句中，参数值为 null 时，会报错
set标签可以将动态的配置SET 关键字
剔除追加到条件末尾的任何不相关的逗号

<update id="updateStudent_if_set" parameterType="liming.student.manager.data.model.StudentEntity">  
    UPDATE STUDENT_TBL  
    <set>  
        <if test="studentName != null and studentName != '' ">  
            STUDENT_TBL.STUDENT_NAME = #{studentName},  
        </if>  
        <if test="studentSex != null and studentSex != '' ">  
            STUDENT_TBL.STUDENT_SEX = #{studentSex},  
        </if>  
        <if test="studentBirthday != null ">  
            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},  
        </if>  
        <if test="studentPhoto != null ">  
            STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},  
        </if>  
        <if test="classId != '' ">  
            STUDENT_TBL.CLASS_ID = #{classId}  
        </if>  
        <if test="placeId != '' ">  
            STUDENT_TBL.PLACE_ID = #{placeId}  
        </if>  
    </set>  
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId};      
</update>

8. trim 代替 where/set

8.1 trim 代替 where

<select id="getStudentList_if_trim" resultMap="resultMap_studentEntity">  
    SELECT ST.STUDENT_ID,  
           ST.STUDENT_NAME,  
           ST.STUDENT_SEX,  
           ST.STUDENT_BIRTHDAY,  
           ST.STUDENT_PHOTO,  
           ST.CLASS_ID,  
           ST.PLACE_ID  
      FROM STUDENT_TBL ST   
    <trim prefix="WHERE" prefixOverrides="AND|OR">  
        <if test="studentName !=null ">  
            ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')  
        </if>  
        <if test="studentSex != null and studentSex != '' ">  
            AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}  
        </if>  
        <if test="studentBirthday != null ">  
            AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}  
        </if>  
    </trim>     
</select>

8.1 trim 代替 set

<update id="updateStudent_if_trim" parameterType="StudentEntity">  
    UPDATE STUDENT_TBL  
    <trim prefix="SET" suffixOverrides=",">  
        <if test="studentName != null and studentName != '' ">  
            STUDENT_TBL.STUDENT_NAME = #{studentName},  
        </if>  
        <if test="studentSex != null and studentSex != '' ">  
            STUDENT_TBL.STUDENT_SEX = #{studentSex},  
        </if>  
    </trim>  
    WHERE STUDENT_TBL.STUDENT_ID = #{studentId}  
</update>

9. 引用代码片段 include

<!--定义sql片段-->  
<sql id="orderAndItem">  
o.order_id,o.cid,o.address,o.create_date,o.orderitem_id,i.orderitem_id,i.product_id,i.count  
</sql>  

<select id="findOrderAndItemsByOid" parameterType="String" resultMap="BaseResultMap">  
    select  
    <!--引用sql片段-->  
    <include refid="orderAndItem" />  
    from ordertable o  
    join orderitem i on o.orderitem_id = i.orderitem_id  
    where o.order_id = #{orderId}  
</select>

10. 字段-条件分支 Case When End

参考：SQL语法之CASE WHEN的使用总结

SELECT
  column1,
  column2,
  CASE
    WHEN column3 < 10 THEN '较小'
    WHEN column3 < 20 THEN '中等'
    ELSE '较大'
  END AS column3_level
FROM table_name;