Article

Mybatis's <where><foreach><set> etc. tag details and mybits configuration file resultMap tag configuration

1. Details of Mybatis's <where><foreach><set> and other labels In the where condition of sql statement, some security judgment...
1. Details of Mybatis's <where><foreach><set> and other labels

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.
mysql grammar and functions (such as string link function CONCAT) are used below

3.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('%', #),'%')
  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('%', #),'%')
  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 parameters are Java entity classes, we can attach all conditions to them, and use them more flexibly. For an entity class like new, we need to restrict that condition. We only need to attach corresponding values to the condition wherever, instead of assigning values, we can not judge in where.


Refer to 3.2.1 for where tags in the code.

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('%', #),'%')
  7. </if>
  8. <if test="studentSex!= null and studentSex!= '' ">
  9. AND ST.STUDENT_SEX = #
  10. </if>
  11. <if test="studentBirthday!=null">
  12. AND ST.STUDENT_BIRTHDAY = #
  13. </if>
  14. <if test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">
  15. AND ST.CLASS_ID = #
  16. </if>
  17. </where>
  18. </select>



For enquiries, there are Li, male, students whose birthday is 1985-05-28 and whose class is 20000002.

Java code

  1. StudentEntity entity = new StudentEntity();
  2. entity.setStudentName("plum");
  3. entity.setStudentSex("male");
  4. entity.setStudentBirthday(StringUtil.parse("1985-05-28"));
  5. entity.setClassEntity(classMapper.getClassByID("20000002"));
  6. List<StudentEntity> studentList = studentMapper.getStudentListWhereEntity(entity);
  7. for( StudentEntity entityTemp : studentList){
  8. System.out.println(entityTemp.toString());
  9. }



3.2 where, set, trim Tags

3.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('%', #),'%')
  7. </if>
  8. <if test="studentSex!= null and studentSex!= '' ">
  9. AND ST.STUDENT_SEX = #
  10. </if>
  11. </select>



If the parameter studentName is null or'', in the example above, 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('%', #),'%')
  7. </if>
  8. <if test="studentSex!= null and studentSex!= '' ">
  9. AND ST.STUDENT_SEX = #
  10. </if>
  11. </where>
  12. </select>


3.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 = #,
  5. STUDENT_TBL.STUDENT_SEX = #,
  6. STUDENT_TBL.STUDENT_BIRTHDAY = #,
  7. STUDENT_TBL.CLASS_ID = #
  8. WHERE STUDENT_TBL.STUDENT_ID = #;
  9. </update>


After using the set+if tag to modify, if an item is null, it will not be updated, but will keep the original value of the database. 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 = #,
  7. </if>
  8. <if test="studentSex!=null and studentSex!='' ">
  9. STUDENT_TBL.STUDENT_SEX = #,
  10. </if>
  11. <if test="studentBirthday!=null ">
  12. STUDENT_TBL.STUDENT_BIRTHDAY = #,
  13. </if>
  14. <if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' ">
  15. STUDENT_TBL.CLASS_ID = #
  16. </if>
  17. </set>
  18. WHERE STUDENT_TBL.STUDENT_ID = #;
  19. </update>


3.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('%', #),'%')
  7. </if>
  8. <if test="studentSex!= null and studentSex!= '' ">
  9. AND ST.STUDENT_SEX = #
  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 = #,
  7. </if>
  8. <if test="studentSex!=null and studentSex!='' ">
  9. STUDENT_TBL.STUDENT_SEX = #,
  10. </if>
  11. <if test="studentBirthday!=null ">
  12. STUDENT_TBL.STUDENT_BIRTHDAY = #,
  13. </if>
  14. <if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' ">
  15. STUDENT_TBL.CLASS_ID = #
  16. </if>
  17. </trim>
  18. WHERE STUDENT_TBL.STUDENT_ID = #;
  19. </update>


3.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('%', #),'%')
  8. </when>
  9. <when test="studentSex!= null and studentSex!= '' ">
  10. AND ST.STUDENT_SEX = #
  11. </when>
  12. <when test="studentBirthday!=null">
  13. AND ST.STUDENT_BIRTHDAY = #
  14. </when>
  15. <when test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">
  16. AND ST.CLASS_ID = #
  17. </when>
  18. <otherwise>
  20. </otherwise>
  21. </choose>
  22. </where>
  23. </select>


3.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. #
  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");
  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. #
  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. }

2,Configuration of mybits configuration file resultMap tag

When do you use resultType? When do you use resultMap?

ResultMap: When the query result does not correspond directly to the pojo class, you can use resultMap to map the query result to the pojo class to match it.

Tag: resultMap

Label structure:

<resultMap id="" type="">

<id column="" property=""/>

<result column="" property=""/>

<association property="" javaType="">

<id column="" property=""/>

<result column="" property=""/>

</assocaiation>

<collection property="" ofType="">

<id column="" property=""/>

<result column="" property=""/>

</collection>

Attribute description:

id: uniquely identifies resultMap, which is called by state through id.

type:resultMap maps query results to pojo classes

Subtags: association, collection, id, result

Sublabel description:

1.id: the attribute that uniquely identifies the query result

Properties:

column: The field name of the database query result

property: the attribute name of the pojo class mapped to

2.result: Common columns in query results

Properties:

column: The field name of the database query result

property: the attribute name of the pojo class mapped to

3.association: Used for one-to-one queries, which map the contents of sub-results

Properties:

Property: Which property of pojo corresponds to the type attribute of resultMap mapped to the query result

javaType: The corresponding java class, the pojo class

(There are also id and result subtags under this subtag, similar to id and result under resultMap)

4.collection: Used in one-to-many queries, where a set of sub-results is mapped (List)

Properties:

Property: Which property of pojo corresponds to the type attribute of resultMap mapped to the query result

ofType: Specifies the type of pojo mapped to the list collection property

(Similarly, there are id and result subtags under this subtag, which are similar to id and result under resultMap)



Leave a QQ 71067977. Welcome to Penguin Exchange.

24 December 2018, 14:48 | Views: 4125

Add new comment

For adding a comment, please log in
or create account

0 comments