mybatis xml写动态 sql where set 自动去除 ， and/or

源：http://limingnihao.iteye.com/blog/782190
评：


3.5 if + trim代替where/set标签

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



3.5.1trim代替where


Xml代码  收藏代码

    <!-- 5.1 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  
       <trim prefix="WHERE" prefixOverrides="AND|OR suffixOverrides="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> 
            <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> 
        </trim>    
    </select> 




3.5.2 trim代替set


Xml代码  收藏代码

    <!-- 5.2 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>