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

评:

3.5if+trim代替where/set标签

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

3.5.1trim代替where

Xml代码收藏代码

<!--5.1if/trim代替where(判断参数)-将实体类不为空的属性作为where条件-->

<selectid="getStudentList_if_trim"resultMap="resultMap_studentEntity">

SELECTST.STUDENT_ID,

ST.STUDENT_NAME,

ST.STUDENT_SEX,

ST.STUDENT_BIRTHDAY,

ST.STUDENT_PHOTO,

ST.CLASS_ID,

ST.PLACE_ID

FROMSTUDENT_TBLST

<trimprefix="WHERE"prefixOverrides="AND|ORsuffixOverrides="AND|OR"">

<iftest="studentName!=null">

ST.STUDENT_NAMELIKECONCAT(CONCAT('%',#{studentName,jdbcType=VARCHAR}),'%')

</if>

<iftest="studentSex!=nullandstudentSex!=''">

ANDST.STUDENT_SEX=#{studentSex,jdbcType=INTEGER}

</if>

<iftest="studentBirthday!=null">

ANDST.STUDENT_BIRTHDAY=#{studentBirthday,jdbcType=DATE}

</if>

<iftest="classId!=nullandclassId!=''">

ANDST.CLASS_ID=#{classId,jdbcType=VARCHAR}

</if>

<iftest="classEntity!=nullandclassEntity.classId!=nullandclassEntity.classId!=''">

ANDST.CLASS_ID=#{classEntity.classId,jdbcType=VARCHAR}

</if>

<iftest="placeId!=nullandplaceId!=''">

ANDST.PLACE_ID=#{placeId,jdbcType=VARCHAR}

</if>

<iftest="placeEntity!=nullandplaceEntity.placeId!=nullandplaceEntity.placeId!=''">

ANDST.PLACE_ID=#{placeEntity.placeId,jdbcType=VARCHAR}

</if>

<iftest="studentId!=nullandstudentId!=''">

ANDST.STUDENT_ID=#{studentId,jdbcType=VARCHAR}

</if>

</trim>

</select>

3.5.2trim代替set

Xml代码收藏代码

<!--5.2if/trim代替set(判断参数)-将实体类不为空的属性更新-->

<updateid="updateStudent_if_trim"parameterType="liming.student.manager.data.model.StudentEntity">

UPDATESTUDENT_TBL

<trimprefix="SET"suffixOverrides=",">

<iftest="studentName!=nullandstudentName!=''">

STUDENT_TBL.STUDENT_NAME=#{studentName},

</if>

<iftest="studentSex!=nullandstudentSex!=''">

STUDENT_TBL.STUDENT_SEX=#{studentSex},

</if>

<iftest="studentBirthday!=null">

STUDENT_TBL.STUDENT_BIRTHDAY=#{studentBirthday},

</if>

<iftest="studentPhoto!=null">

STUDENT_TBL.STUDENT_PHOTO=#{studentPhoto,javaType=byte[],jdbcType=BLOB,typeHandler=org.apache.ibatis.type.BlobTypeHandler},

</if>

<iftest="classId!=''">

STUDENT_TBL.CLASS_ID=#{classId},

</if>

<iftest="placeId!=''">

STUDENT_TBL.PLACE_ID=#{placeId}

</if>

</trim>

WHERESTUDENT_TBL.STUDENT_ID=#{studentId}

</update>

相关推荐