我们在使用mybatis写sql语句的时候,经常会遇到很多sql都会有拥有某些相同的查询条件,如果只是一个两个还感觉不到什么,可是如果查询语句特别多,而且又都拥有共同的查询条件的时候,我们可以考虑将中相同的条件抽取出来,然后封装一下,在需要的时候直接引用就可以了。
以我最近接手的代码为例:
1.未抽取前
<select id="selectVtRecordPage" parameterType="com.webest.guestRegistration.entity.ReqInfo"
resultType="com.webest.guestRegistration.entity.ReqInfo">
SELECT
vi.vtIdentifyNO vtIdentifyNO,vr.vtIdentifyNO vtNO,
vtID,vtName,vtSex,vtAddress,vtNativePlace,vtBirthday,vtIdentifyImgURL,
vtScanImgURL,vtTelephone,vtRank,vtOfficerPost,vtNation,expirationStart,expirationEnd,departmentTelephone,
visitID,vtEventID,vtIdentifyType,vtProperty,vtPeriod,vtSummary,vtDetail,enterpriseID,enterpriseName,departmentID,
departmentName,personID,personName,vtImgURL,examineNO,examineTime,tmpCardNO,tmpInTime,tmpOutTime,
vtStatus,isAttend,deviceID,vtRegisterTime,regionID,regionName,isAbnormal,handlingSuggestion,userID,isRecognition
FROM vis_visitor_info vi RIGHT JOIN vis_visit_record vr
ON vi.vtIdentifyNO = vr.vtIdentifyNO
<where>
<if test="vtName != null and vtName.length()>0">
AND vi.vtName like concat('',#{vtName},'%')
</if>
<if test="vtIdentifyNO != null and vtIdentifyNO.length()>0">
AND vi.vtIdentifyNO = #{vtIdentifyNO}
</if>
<if test="vtIdentifyType != null and vtIdentifyType.length()>0">
AND vi.vtIdentifyType = #{vtIdentifyType}
</if>
<if test="isRecognition != null and isRecognition.length()>0">
AND vr.isRecognition = #{isRecognition}
</if>
<if test="vtPeriod != null and vtPeriod.leng