MyBatis的动态SQL查询常用几种标签

一:if标签

<!-- 查询学生list，根据like姓名模糊查询 -->   
<select id="getStudentList " parameterType="StudentEntity" resultMap="studentResultMap">   
    SELECT * from STUDENT_TBL ST    
     <if test="studentName!=null and studentName!='' ">   
        WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')    
     </if> 

     <if test="studentSex!= null and studentSex!= '' ">     
            AND ST.STUDENT_SEX = #{studentSex}      
      </if>

</select>  

二：where、set、trim标签

2.1 if + where 的条件判断

    <!--  select - where/if(判断参数) - 将实体类不为空的属性作为where条件 -->  
    <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>  
        </where>    
    </select> 

2.2： if + set 的更新语句

    <!-- if/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>  
        </set>  
        WHERE STUDENT_TBL.STUDENT_ID = #{studentId};      
    </update> 

2.3if + trim代替where/set标签

 trim是更灵活的去处多余关键字的标签，他可以实践where和set的效果。

2.3.1  trim代替where

!--  if/trim代替where(判断参数) - 将实体类不为空的属性作为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   WHERE 1=1
    <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>  
        <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>   
    </trim>     
</select> 

2.3.2  trim代替set

    <!-- if/trim代替set(判断参数) - 将实体类不为空的属性更新 -->  
    <update id="updateStudent_if_trim" parameterType="liming.student.manager.data.model.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>  
            <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>  
        </trim>  
        WHERE STUDENT_TBL.STUDENT_ID = #{studentId}  
    </update> 

三：choose (when, otherwise)

   choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则choose结束。当choose中所有when的条件都不满则时，则执行otherwise中的sql。类似于Java 的switch 语句，choose为switch，when为case，otherwise则为default

    <!--  choose(判断参数) - 按顺序将实体类第一个不为空的属性作为where条件 -->  
    <select id="getStudentList_choose" 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 1=1
        <where>  
            <choose>  
                <when test="studentName !=null ">  
                    ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')  
                </when >  
                <when test="studentSex != null and studentSex != '' ">  
                    AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}  
                </when >  
                <when test="studentBirthday != null ">  
                    AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}  
                </when >  
                <when test="classId != null and classId!= '' ">  
                    AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}  
                </when >  
                <when test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">  
                    AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}  
                </when >  
                <otherwise>  
                </otherwise>  
            </choose>  
        </where>    
    </select> 

四：foreach

对于动态SQL 非常必须的，主是要迭代一个集合，通常是用于IN 条件。List 实例将使用“list”做为键，数组实例以“array” 做为键。

foreach元素是非常强大的，它允许你指定一个集合，声明集合项和索引变量，它们可以用在元素体内。它也允许你指定开放和关闭的字符串，在迭代之间放置分隔符。这个元素是很智能的，它不会偶然地附加多余的分隔符。

注意：你可以传递一个List实例或者数组作为参数对象传给MyBatis。当你这么做的时候，MyBatis会自动将它包装在一个Map中，用名称在作为键。List实例将会以“list”作为键，而数组实例将会以“array”作为键。