Using JDBC or similar frameworks, dynamic concatenation of SQL is a pain. For example, select the data table according to the current date, decide which fields to update according to the parameters passed in, etc., are more commonly used dynamic queries. MyBatis has powerful dynamic SQL capabilities that can be used in any SQL statement. Dynamic SQL of MyBatis mainly uses the following elements to achieve:

  • Single condition: if
  • Choose, when, otherwise
  • Auto wrap: trim, WHERE, set
  • Traverse the collection: foreach

if

If is used to detect conditions and dynamically contains SQL statements. For example, in the following statement, the title search criteria are added to the query criteria based on whether the entered title is null.

<select id="select" resultType="Person">
    SELECT * FROM Person WHERE state = ‘ACTIVE’  
    <if test="title != null">
        AND title like #{title}  
    </if>
</select>
Copy the code

choose, when, otherwise

The Choose element provides a function similar to switch in the Java language, allowing you to select one of several options to add to an SQL statement.

For example, the following statement uses two WHEN and an otherwise, similar to case and default in switch.

<select ID ="select" resultType="Person"> select * FROM Person WHERE state = 'ACTIVE' <choose> <when test="title! = null"> AND title like #{title} </when> <when test="sex ! = null"> AND sex = #{sex} </when> <otherwise> AND height = 170 </otherwise> </choose> </select>Copy the code

trim, where, set

The trim element can be used to add a prefix, suffix, and remove a specified beginning and end to an SQL statement. There are four attributes:

  • Prefix: the prefix of the statement;
  • PrefixOverrides: remove the beginning;
  • Suffix: the suffix of a statement;
  • SuffixOverrides: removes the end.

In the following statement, trim removes the start and end of SOMesQL before adding the prefix and suffix.

Suffix =" suffix "suffixOverrides=" suffix "> somesql </trim>Copy the code

Suppose you use an if statement to add two dynamic conditions, as shown below. If only the second condition is satisfied, or neither condition is satisfied, you end up with an invalid SQL with WHERE but no condition.

<select id="select" resultType="Person"> SELECT * FROM Person WHERE <if test="sex ! = null"> sex = #{sex} </if> <if test="title ! = null"> AND title like #{title} </if> </select>Copy the code

You can avoid this by using the WHERE element, as shown below. The WHERE element can detect whether internal query conditions are valid, such as empty OR too many AND/OR, AND process the SQL statement to be valid. After processing the WHERE element, we can get a valid SQL statement regardless of whether the internal if element holds.

<select id="select" resultType="Person"> SELECT * FROM Person <where> <if test="sex ! = null"> sex = #{sex} </if> <if test="title ! = null"> AND title like #{title} </if> </where> </select>Copy the code

Similarly, set elements can be used in update statements to perform functions similar to where elements. In the following example, the set element ensures that a set in an SQL statement is always valid, automatically handles null sets, multiple trailing commas, and automatically concatenates set strings.

<update id="update"> update Person <set> <if test="title ! = null"> title=#{title}, </if> <if test="sex ! = null"> sex=#{sex}, </if> </set> where id=#{id} </update>Copy the code

foreach

Foreach is used to process collections, such as List, Array, Set, and Map. In the following example, the query condition is a list, which is queried using in.

<select id="select" resultType="Person">
    SELECT * FROM Person WHERE id in  
    <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
    </foreach>
</select>
Copy the code

The attributes of foreach are defined as follows:

  • Item: alias for the iteration of the elements in the set, value if the set is map;
  • Index: the iterated index of the elements in the set. If the set is map, this is key;
  • Collection: The type of a collection;
  • Open: the prefix of the statement;
  • Separator: The separator of a statement;
  • Close: suffix of a statement.

Share study notes and technical summary, covering Java advanced, architecture design, cutting-edge technology, algorithm and data structure, database, middleware and other fields, welcome to follow. This article was first published on the wechat public account “Back-end development that matter”.