Mybatis's <where><foreach><set> label details

In the where condition of sql statement, some security judgments are needed, such as searching by sex. If the incoming parameters are empty, then the query results are likely to be empty. Perhaps we need the parameters to be empty, which is to find all the information. This is how we can use dynamic sql to add a judgment, when the parameters do not meet the requirements, we can not judge the query conditions.

1 if tags
A very common query:

Xml code

  1. <!-- Query students list,like Full name -->     
  2. <select id="getStudentListLikeName" parameterType="StudentEntity" resultMap="studentResultMap">     
  3.     SELECT * from STUDENT_TBL ST       
  4. WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')      
  5. </select>     

But at this point, if studentName is null or an empty string, the statement is likely to report an error or the query result is empty. At this point, we use if dynamic sql statement to make a judgment first. If the value is null or equal to an empty string, we will not make a judgment on this condition.

Revised to:

Xml code

  1. <!-- Query students list,like Full name -->     
  2. <select id=" getStudentListLikeName " parameterType="StudentEntity" resultMap="studentResultMap">     
  3.     SELECT * from STUDENT_TBL ST      
  4.     <if test="studentName!=null and studentName!='' ">     
  5.         WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')      
  6.     </if>     
  7. </select>     
At this point, when the studentName value is null or'', we do not judge where condition, so when the studentName value is null or'', without this condition, the query results are all.

Since the parameters are Java Entity class, so we can attach all conditions, use more flexible, such as a new entity class, we need to restrict that condition, just attach the corresponding value will be where this condition, on the contrary, without assignment can not be judged in where.

Xml code

  1. <!-- Query students list,like Full name,=Gender,=Birthday,=Class, Use where,parameter entity type -->     
  2. <select id="getStudentListWhereEntity" parameterType="StudentEntity" resultMap="studentResultMap">     
  3.     SELECT * from STUDENT_TBL ST      
  4.     <where>     
  5.         <if test="studentName!=null and studentName!='' ">     
  6.             ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')      
  7.         </if>     
  8.         <if test="studentSex!= null and studentSex!= '' ">     
  9.             AND ST.STUDENT_SEX = #{studentSex}      
  10.         </if>     
  11.         <if test="studentBirthday!=null">     
  12.             AND ST.STUDENT_BIRTHDAY = #{studentBirthday}      
  13.         </if>     
  14.         <if test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">     
  15.             AND ST.CLASS_ID = #{classEntity.classID}      
  16.         </if>     
  17.     </where>     
  18. </select>     


2 where, set, trim Tags

2.1 where
Such a combination can lead to errors when there are more if tags. For example, like name, equal to specified sex, etc.

Xml code

  1. <!-- Query students list,like Full name,=Gender -->     
  2. <select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">     
  3.     SELECT * from STUDENT_TBL ST      
  4.         WHERE      
  5.         <if test="studentName!=null and studentName!='' ">     
  6.             ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')      
  7.         </if>     
  8.         <if test="studentSex!= null and studentSex!= '' ">     
  9.             AND ST.STUDENT_SEX = #{studentSex}      
  10.         </if>     
  11. </select>     
If the parameter studentName is null or'', in the above example, it may result in incorrect SQL with redundant keywords such as WHERE AND.
We can use where dynamic statements to solve this problem. The "where" tag knows that if it contains a return value in the tag, it inserts a "where". In addition, if the content returned by the tag begins with an AND or OR, it will be removed.
The above example is amended to read:

Xml code

  1. <!-- Query students list,like Full name,=Gender -->     
  2. <select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">     
  3.     SELECT * from STUDENT_TBL ST      
  4.     <where>     
  5.         <if test="studentName!=null and studentName!='' ">     
  6.             ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')      
  7.         </if>     
  8.         <if test="studentSex!= null and studentSex!= '' ">     
  9.             AND ST.STUDENT_SEX = #{studentSex}      
  10.         </if>     
  11.     </where>     
  12. </select>     

2.2 set
When the if tag is used in the update statement, if the previous if is not executed, it may cause comma redundancy errors. Using the set tag, you can dynamically configure the SET keyword and eliminate any unrelated commas appended to the end of the condition.
If you do not use the if tag, if you have a parameter of null, it will lead to errors, as shown in the following example:

Xml code

  1. <!-- Update student information -->     
  2. <update id="updateStudent" parameterType="StudentEntity">     
  3.     UPDATE STUDENT_TBL      
  4.        SET STUDENT_TBL.STUDENT_NAME = #{studentName},      
  5.            STUDENT_TBL.STUDENT_SEX = #{studentSex},      
  6.            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},      
  7.            STUDENT_TBL.CLASS_ID = #{classEntity.classID}      
  8.      WHERE STUDENT_TBL.STUDENT_ID = #{studentID};      
  9. </update>     

After using the set+if tag, if an item is null, it is not updated, but maintained. data base Original value. The following example:

Xml code

  1. <!-- Update student information -->     
  2. <update id="updateStudent" parameterType="StudentEntity">     
  3.     UPDATE STUDENT_TBL      
  4.     <set>     
  5.         <if test="studentName!=null and studentName!='' ">     
  6.             STUDENT_TBL.STUDENT_NAME = #{studentName},      
  7.         </if>     
  8.         <if test="studentSex!=null and studentSex!='' ">     
  9.             STUDENT_TBL.STUDENT_SEX = #{studentSex},      
  10.         </if>     
  11.         <if test="studentBirthday!=null ">     
  12.             STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},      
  13.         </if>     
  14.         <if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' ">     
  15.             STUDENT_TBL.CLASS_ID = #{classEntity.classID}      
  16.         </if>     
  17.     </set>     
  18.     WHERE STUDENT_TBL.STUDENT_ID = #{studentID};      
  19. </update>     
2.3 trim
trim is a more flexible way to label redundant keywords, and it can practice where and set effects.

where example's equivalent trim statement:

Xml code

  1. <!-- Query students list,like Full name,=Gender -->     
  2. <select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">     
  3.     SELECT * from STUDENT_TBL ST      
  4.     <trim prefix="WHERE" prefixOverrides="AND|OR">     
  5.         <if test="studentName!=null and studentName!='' ">     
  6.             ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')      
  7.         </if>     
  8.         <if test="studentSex!= null and studentSex!= '' ">     
  9.             AND ST.STUDENT_SEX = #{studentSex}      
  10.         </if>     
  11.     </trim>     
  12. </select>     
The equivalent trim statement of the set example:

Xml code

  1. <!-- Update student information -->     
  2. <update id="updateStudent" parameterType="StudentEntity">     
  3.     UPDATE STUDENT_TBL      
  4.     <trim prefix="SET" suffixOverrides=",">     
  5.         <if test="studentName!=null and studentName!='' ">     
  6.             STUDENT_TBL.STUDENT_NAME = #{studentName},      
  7.         </if>     
  8.         <if test="studentSex!=null and studentSex!='' ">     
  9.             STUDENT_TBL.STUDENT_SEX = #{studentSex},      
  10.         </if>     
  11.         <if test="studentBirthday!=null ">     
  12.             STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},      
  13.         </if>     
  14.         <if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' ">     
  15.             STUDENT_TBL.CLASS_ID = #{classEntity.classID}      
  16.         </if>     
  17.     </trim>     
  18.     WHERE STUDENT_TBL.STUDENT_ID = #{studentID};      
  19. </update>     

3 choose (when, otherwise)
Sometimes we don't want to apply all the conditions, we just want to choose one of the multiple options. MyBatis provides the choose element to determine in sequence whether the conditions in when are valid or not, and if one is valid, the choose ends. When all when conditions in choose are unsatisfactory, sql in otherwise is executed. Similar to Java switch statements, choose is switch, when is case, and other wise is default.
if is a relationship with (and), and choose is a relationship with (or).

For example, in the following example, all the restrictive conditions are also written and used. Select the order of conditions and the top-down order of when tags:

Xml code

  1. <!-- Query students list,like Name, or=Gender, or=Birthday, or=Class, Use choose -->     
  2. <select id="getStudentListChooseEntity" parameterType="StudentEntity" resultMap="studentResultMap">     
  3.     SELECT * from STUDENT_TBL ST      
  4.     <where>     
  5.         <choose>     
  6.             <when test="studentName!=null and studentName!='' ">     
  7.                     ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')      
  8.             </when>     
  9.             <when test="studentSex!= null and studentSex!= '' ">     
  10.                     AND ST.STUDENT_SEX = #{studentSex}      
  11.             </when>     
  12.             <when test="studentBirthday!=null">     
  13.                 AND ST.STUDENT_BIRTHDAY = #{studentBirthday}      
  14.             </when>     
  15.             <when test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">     
  16.                 AND ST.CLASS_ID = #{classEntity.classID}      
  17.             </when>     
  18.             <otherwise>     
  19.                       
  20.             </otherwise>     
  21.         </choose>     
  22.     </where>     
  23. </select>     

4 foreach
For dynamic SQL, it is essential to iterate over a collection, usually for IN conditions.
List instances will use "list" as the key, and array instances will use "array" as the key.

 

3.4.1 parameter is the writing of list instance:
SQL writing:

Xml code

  1. <select id="getStudentListByClassIDs" resultMap="studentResultMap">     
  2.     SELECT * FROM STUDENT_TBL ST      
  3.      WHERE ST.CLASS_ID IN       
  4.      <foreach collection="list" item="classList"  open="(" separator="," close=")">     
  5.         #{classList}      
  6.      </foreach>         
  7. </select>     
The method declaration of the interface:

  1. public List<StudentEntity> getStudentListByClassIDs(List<String> classList);     
  2. public List<StudentEntity> getStudentListByClassIDs(List<String> classList); Test the code, query the students, in the uuuuuuuuuuu20000002,20000003Students in these two classes:  

  1. List<String> classList = new ArrayList<String>();      
  2. classList.add("20000002");      
  3. classList.add("20000003");      
  4.      
  5. List<StudentEntity> studentList = studentMapper.getStudentListByClassIDs(classList);      
  6. for( StudentEntity entityTemp : studentList){      
  7.     System.out.println(entityTemp.toString());      
  8. }     
  9. List<String> classList = new ArrayList<String>();  
  10. classList.add("20000002");  
  11. classList.add("20000003");  
  12. List<StudentEntity> studentList = studentMapper.getStudentListByClassIDs(classList);  
  13. for( StudentEntity entityTemp : studentList){  
  14.  System.out.println(entityTemp.toString());  
  15. }  

3.4.2 parameter is Array instance:
SQL statement:

  1. <select id="getStudentListByClassIDs" resultMap="studentResultMap">     
  2.     SELECT * FROM STUDENT_TBL ST      
  3.      WHERE ST.CLASS_ID IN       
  4.      <foreach collection="array" item="ids"  open="(" separator="," close=")">     
  5.         #{ids}      
  6.      </foreach>     
  7. </select>     

The method declaration of the interface:

  1. public List<StudentEntity> getStudentListByClassIDs(String[] ids);     
  2. public List<StudentEntity> getStudentListByClassIDs(String[] ids);Test the code, query the students, in the uuuuuuuuuuu20000002,20000003Students in these two classes:  

  1. String[] ids = new String[2];      
  2. ids[0] = "20000002";      
  3. ids[1] = "20000003";      
  4. List<StudentEntity> studentList = studentMapper.getStudentListByClassIDs(ids);      
  5. for( StudentEntity entityTemp : studentList){      
  6.     System.out.println(entityTemp.toString());      
  7. }   

Tags: Attribute xml SQL Java

Posted on Thu, 20 Dec 2018 06:15:06 -0800 by hyp0r