1. The background

This section describes a scenario in which Mybatis deals with dynamic SQL.

2. Knowledge

Dynamic SQL refers to SQL whose “conditions” are not fixed. Compared with ordinary SQL, dynamic SQL has one or more conditions/parameters.

For example: Scenario: When querying, if the user enters a name, the query will be based on the name. At the same time input mailbox, add a condition by name + mailbox two conditions query.

Traditional concatenation SQL is laborious, making sure you don’t forget to add the necessary Spaces and taking care to remove the comma from the last column name of the list.

With the help of powerful OGNL based expressions, MyBatis 3 has greatly simplified the types of elements, including the following:

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

Example 3.

3.1 Dynamic Query Conditions (Multiple AND conditions after WHERE)

Scenario: If a user enters a name, the query is performed by name. At the same time input mailbox, add a condition by name + mailbox two conditions query.

<select id="selectDynamic" resultType="cn.zyfvir.SysUser"> select * from sys_user <where> <if test="userName ! = null"> user_name like #{userName}"%" </if> <if test="userEmail ! = null"> and user_email like #{userEmail}"%" </if> </where> </select>Copy the code
  • User_name like #{userName}”%”
  • The second condition starts with and.
  • If the first condition is not present, Mybatis will remove the and character itself.

Examples of corresponding Java code:

private static void testSelect(SqlSessionFactory sqlSessionFactory) { try (SqlSession session = sqlSessionFactory.openSession(true)) { SysUserMapper sysUserMapper = session.getMapper(SysUserMapper.class); SysUser para = new SysUser(); para.userName = "jack"; List<SysUser> list1 = sysUserMapper.selectDynamic(para); printf("list=%s", list1.size()); Para2 = new SysUser(); para2.userName = "jack"; para2.userEmail = "jack"; List<SysUser> list2 = sysUserMapper.selectDynamic(para2); printf("list=%s", list2.size()); // only email SysUser para3 = new SysUser(); para3.userEmail = "jack"; List<SysUser> list3 = sysUserMapper.selectDynamic(para3); printf("list=%s", list3.size()); }}Copy the code

3.2 Dynamic Update

Scenario: When the sys_user username has a value (not Null), the username is changed. If the email has a value, change the email at the same time. Example:

<update id="updateUserDynamic"> UPDATE sys_user <set> <if test="userName ! = null"> user_name= #{userName}, </if> <if test="userPassword ! = null"> user_password= #{userPassword}, </if> <if test="userEmail ! = null"> user_email=#{userEmail}, </if> <if test="userInfo ! = null"> user_info= #{userInfo} </if> </set> WHERE id=#{id} </update>Copy the code
  • The set tag is used in conjunction with the if tag.
  • Note that user_email=#{userEmail}, which ends with a comma “, “will not fail if it is the last, mybatis will take care of these commas itself.

3.3 Dynamic Insert

Scenario: Insert the sys_USER object if it has a value for its name, and insert the mailbox address if it has a value. Here’s an example:

<! <insert id="insertDynamic" useGeneratedKeys="true" keyProperty="id"> insert INTO sys_user <trim prefix="(" suffix=")" suffixOverrides=","> <if test="userName ! = null"> user_name, </if> <if test="userPassword ! = null"> user_password, </if> <if test="userEmail ! = null"> user_email, </if> <if test="userInfo ! = null"> user_info, </if> </trim> values <trim prefix="(" suffix=")" suffixOverrides=","> <if test="userName ! = null"> #{userName}, </if> <if test="userPassword ! = null"> #{userPassword}, </if> <if test="userEmail ! = null"> #{userEmail}, </if> <if test="userInfo ! = null"> #{userInfo}), </if> </trim> </insert>Copy the code
  • The trim tag is used, which means ending with (beginning, ending with), trailing comma “, “to be processed.
  • If there is a comma at the end, MyBaits automatically handles it (removes it) to make sure the SQL syntax is correct.

3.4 Batch Insert

Scenario: Suppose you have a list set with many elements and choose a quick way to insert them into the database. We can use the foreach tag to generate a dynamically inserted SQL, as in:

<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id"> INSERT INTO sys_user (user_name,user_password) values  <foreach collection="list" item="item" separator=","> ( #{item.userName}, #{item.userPassword} ) </foreach> </insert>Copy the code
  • This example uses foreach to walk through the collection, with each child element replaced by an item name and each segment separated by a comma.
  • Note that the number of child elements of the collection cannot be empty, otherwise the statement is incomplete and an error will be reported.

3.5 Use foreach to assemble a nested subquery of in.

Scenario: A common usage scenario is traversing collections (especially when building IN conditional statements). Such as:

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>
Copy the code
  • Foreach iterates through the collection, producing a (beginning,) ending, comma-separated string.

4. The extension

4.1 Example of using the IF tag

<select id="findActiveBlogLike" resultType="Blog"> select * FROM Blog WHERE state = 'ACTIVE' <if test="title! = null"> AND title like #{title} </if> <if test="author ! = null and author.name ! = null"> AND author_name like #{author.name} </if> </select>Copy the code

Examples of how to work with WHERE:

<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG <where> <if test="state ! = null"> state = #{state} </if> <if test="title ! = null"> AND title like #{title} </if> <if test="author ! = null and author.name ! = null"> AND author_name like #{author.name} </if> </where> </select>Copy the code

4.2 Example of the Choose label

The example shows that if “title” is passed in, press “title” to find, and if “author” is passed in, press “author” to find. If neither is passed in, the BLOG marked featured=1 is returned.

<select id="findActiveBlogLike" resultType="Blog"> select * FROM Blog WHERE state = 'ACTIVE' <choose> <when test="title" ! = null"> AND title like #{title} </when> <when test="author ! = null and author.name ! = null"> AND author_name like #{author.name} </when> <otherwise> AND featured = 1 </otherwise> </choose> </select>Copy the code

4.3 Example of set tags

The set element can be used to dynamically contain columns that need to be updated, ignoring other columns that are not updated. Such as:

<update id="updateAuthorIfNecessary"> update Author <set> <if test="username ! = null">username=#{username},</if> <if test="password ! = null">password=#{password},</if> <if test="email ! = null">email=#{email},</if> <if test="bio ! = null">bio=#{bio}</if> </set> where id=#{id} </update>Copy the code

4.4 Foreach Label Example

Another common use of dynamic SQL is traversal of collections (especially when building IN conditional statements). Such as:

5. Reference:

Example of my code: github.com/vir56k/java…

Mybatis.org/mybatis-3/z…