Dynamic SQL for MyBatis

1-if tag

1 - when querying - tag 1

1. It is often used to selectively update or insert the value of a field in update or insert

2. Case: advanced query: only enter the user name for fuzzy query, only enter the mailbox for full matching query. Enter both for matching query

3. <select id="selectByUserWhere" resultType="sysUser">
          select *
          from sys_user
          <where>
              <if test="userName !=null and userName != ''">
                  and user_name like concat('%',#{userName},'%')
              </if>
              <if test="userEmail != null and userEmail != '' ">
                  and user_email = #{userEmail}
           </if>
          </where>
   

4. Judgment condition property= Null or property ==null is suitable for any type of field to judge whether the property value is empty. Judgment condition property! = '' Or property = = '' is only suitable for String type fields

5. Note the condition of 1 = 1: consider that if both conditions are not met, the sql statement is wrong

2 - if in update

1. Selectively update a field and realize dynamic column update through if

2. Selective update naming convention: the method name will be suffixed with selective

3. update sys_user
   set
   <if test="userName != null and userName != '' ">
   user_name = #{userName},
   </if>
   <if test="userPassword != null and userPassword !='' ">
   	user_password = #{userPassword},
   </if>
   id = #{id}
   where id = #{id}
   

4. ==Note: = = the first point is the comma after the SQL statement in each if element, and the second point is the id=#{id} in front of the where keyword

5. About the second point: when all query criteria are null or empty, if there is id=#{id}, it is: update sys_ User set id=#{id} where id=#{id}. If not, the sql statement is incomplete. If only one condition is true and there is no id=#{id}, the sql statement is incomplete

if used in 3-insert

1. Target: when inserting a field into the database, if the user input is blank, the data in the database will be used; otherwise, the data entered by the user will be used

2. insert into sys_user(
   	user_name, user_password,
   	<if text= " userEmail !=null and userEmail != '' ">
   		user_email
   	</if>
   )values(
   	#{userName},#{userPassword},
   	<if test="userEmail != null and userEmail != '' ">
   		#{userEmail}
   	</if>
   )
   

When using inser, it should be noted that if the if condition is added to the column, the same if condition must be added to the values part. It must be ensured that the top and bottom can correspond to each other.

4-choose usage

The if tag provides basic condition judgment, but it cannot implement the logic of if... else. To implement such logic, you need to use choose... when. . . otherwise label

1. Requirement: when id has attribute value, id is preferred. When id has no attribute value, judge whether the user name has value. When id has no attribute value, sql has no result

2. select id,userName,userPassword,userEmail,userInfo
   from sys_user
   where 1=1
   <choose>
   	<when test =" id !=null">
       	and id = #{id}
       </when>1
       <when test="userName != null and userName != ' '">
       	and user_name = #{userName}
       </when>
       <otherwise>
       	and 1=2
       </otherwise>
   </choose>
   

3. If and only if when is all false, execute the statements in the otherwise tag. Therefore, when using the choose tag, you should think clearly, otherwise unexpected things will happen

4. In the above query, if there is no otherwise restriction, all users will be queried. Because we use sysuser as the return value in the corresponding interface method, an error will be reported when there are multiple actual query results

5-where label vs. label 1

The three tags where, set and trim solve similar problems, and both where and set belong to a specific usage of trim.

1. Function of where tag: if there is a return value in the element contained in the tag, insert a where; If the string after where starts with AND and OR, they are eliminated

2. select id,userName,userPassword,userEmail
   from sys_user
   <where>
   	<if test=" userName != null and userName != ''">
       	and user_name like concat('%',#{userName},'%')
       </if>
       <if test=" userEmail != null and userEmail != ''">
       	and user_email = #{userEmail}
       </if>
   </where>
   

3. When the where tag and if tag are used at the same time, the sql can be more concise without the condition of where 1=1

6-set usage comparison 2

1. The function of the set tag: if there is a return value in the element contained in the tag, insert a set. If the string after the set ends with a comma, remove the comma

2. update sys_user
   <set>
   	<if test = "userName != null and userName != '' ">
       	user_name = #{userName},
       </if>
       <if test="userPassword != null and userPassword != ''  ">
       	user_password = #{userPassword},
       </if>
       where id = #{id}
   </set>
   

3. Note: in the set tag usage, there is no problem with the comma after sql, but if there is no content in the set element, sql errors will still occur. Therefore, in order to avoid errors, it is necessary to retain the inevitable assignment such as id=#{id}

7-trim usage

1. The functions of where and set tags can be implemented with trim tags, and at the bottom, they are implemented through TrimSqlNode

2. trim implements the functions of where tag and set tag

3. <trim prefix="where" prefixOverrides="and |or ">
   	....
   </trim>
   -- there and and or The following space cannot be omitted to avoid matching andes orders Other words
   <trim prefix = "set" suffixOverrides = ",">
   	....
   </trim>
   
   
   

4. trim attribute value

   1. Prefix: when the trim element contains content, the prefix specified by prefix will be added to the content
   2. prefixOverrides: when the trim element contains content, the matching prefix characters in the content will be removed
   3. Suffix: when the trim element contains content, the suffix specified by suffix will be added to the content
   4. suffixOverrides: when the trim element contains content, the matching suffix string in the content will be removed

8-foreach implementation in set

1. Requirement: how to query all qualified users according to user id set

2. select id, userPassword, userEmail
   from sys_user
   where id in 
   <foreach collection = "list" open="(" close=")" separator ="," item="id" index="i">
   	#{id}
   </foreach>
   

3. Note: when there are multiple parameters, add @ param annotation before the method parameters of dao interface to specify a name for each parameter. Otherwise, it will be inconvenient to use parameters in sql, especially in foreach

9-foreach for batch insertion

1. <insert id = "insertList">	insert into sys_user(userName,userPassword,userEmail)    values    <foreach collection = "list" item = "user" separator = ",">\    	(        #{userName},#{userPassword},#{userEmail}        )    </foreach></insert>
   

2. Note: after the circular variable name is specified through item, the method of "attribute. Attribute" is used when referring to the value, such as user.userName

10 foreach implementation of dynamic update

1. How does foreach implement dynamic update when the parameter type is map

2. When the parameter is of map type, the index attribute value of foreach tag does not correspond to the index value, but the key in the map

3. <update id= "updateByMap">	update sys_user    set     <foreach collection="_parameter" item = "val" index="key" separator=",">    	${key} = #{key}    </foreach>    where id= #{id}</update>
   

4. Note: here, the key is used as the column name and the corresponding value is used as the value of the column. The fields to be updated are spliced in the sql statement through foreach