if标签
if标签可用在许多类型的sql语句中,我们以查询为例。首先看一个很普通的查询:
- <!-- 查询学生list,like姓名 -->
- <select id="getStudentListLikeName" parameterType="StudentEntity" resultMap="studentResultMap">
- SELECT * from STUDENT_TBL ST
- WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
- </select>
但是此时如果studentName或studentSex为null,此语句很可能报错或查询结果为空。此时我们使用if动态sql语句先进行判断,如果值为null或等于空字符串,我们就不进行此条件的判断,增加灵活性。
参数为实体类StudentEntity。将实体类中所有的属性均进行判断,如果不为空则执行判断条件。
- <!-- 2 if(判断参数) - 将实体类不为空的属性作为where条件 -->
- <select id="getStudentList_if" 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>
- </select>