A Detailed Explanation of MyBatis Dynamic SQL Tag Functions

One of MyBatis's powerful features is its dynamic SQL. In JDBC or other similar frameworks, when constructing SQL statements based on different conditions, one not only cannot forget necessary spaces but must also pay attention to omitting the trailing comma in a list of column names; the handling process is troublesome and messy. MyBatis's dynamic SQL avoids these issues.

In versions prior to MyBatis 3, using dynamic SQL required learning and understanding many tags. Now, MyBatis has adopted the powerful OGNL expression language to eliminate many other tags. Below are the several tags supported by MyBatis dynamic SQL in XML:

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

if

In MyBatis, the <if> tag is used for conditional judgment within SQL statements. It allows you to dynamically generate part of a SQL statement based on specified conditions.

The <if> tag is typically used in <select>, <insert>, <update>, <delete>, and other SQL statements to determine whether to include a specific SQL fragment based on conditions. Its syntax is as follows:

<select id="getUserList" parameterType="User" resultType="User">
  SELECT * FROM users
  WHERE 1=1
  <if test="username != null">
    AND username = #{username}
  </if>
  <if test="age != null">
    AND age = #{age}
  </if>
</select>

In the example above, the <if> tag is used to determine whether to include the corresponding SQL fragment based on conditions. The test attribute specifies a conditional expression; if this condition is true, the SQL fragment within the <if> tag will be included in the generated SQL statement.

Within the <if> tag, you can use any valid OGNL expression for conditional judgment. In the example, we used two conditional judgments: username != null and age != null. If both conditions are true, the corresponding SQL fragments will be included in the generated SQL statement.

Note that the <if> tag can be combined with other tags, such as <where>, <set>, and <choose>, to build more complex conditional judgment logic.

choose

In MyBatis, the <choose> tag is used to implement conditional selection. It is similar to a switch statement in Java language, used to select different branches for processing based on conditions.

The <choose> tag is usually used in conjunction with <when> and <otherwise> tags to build multiple conditional branches. Its syntax is as follows:

<select id="getUserList" parameterType="User" resultType="User">
  SELECT * FROM users
  WHERE 1=1
  <choose>
    <when test="username != null">
      AND username = #{username}
    </when>
    <when test="age != null">
      AND age = #{age}
    </when>
    <otherwise>
      AND status = 'ACTIVE'
    </otherwise>
  </choose>
</select>

In the example above, the <choose> tag contains multiple conditional branches, each represented by a <when> tag. The test attribute specifies a conditional expression; if this condition is true, the corresponding branch will be executed. In the example, when username != null, the first branch is executed; when age != null, the second branch is executed.

If none of the <when> branches satisfy the condition, the <otherwise> tag can be used to specify a default branch. In the example, if the previous two conditions are not met, the SQL fragment within the <otherwise> tag will be executed.

Note that the <choose> tag will only execute the first matching branch and ignore subsequent branches. This behavior is similar to that of a switch statement in Java language.

where set trim

where

In MyBatis, the <where> tag is used for constructing the WHERE clause in dynamic SQL statements. It can dynamically generate the WHERE clause based on conditions and automatically handles the connection relationship between conditions.

The <where> tag is typically used in <select>, <update>, <delete>, and other SQL statements to dynamically generate the WHERE clause based on conditions. Its function is to omit the WHERE keyword when generating the SQL statement and automatically add the starting position of the WHERE clause and the connection relationship according to conditions.

Here is an example:

<select id="getUserList" parameterType="User" resultType="User">
  SELECT * FROM users
  <where>
    <if test="username != null">
      AND username = #{username}
    </if>
    <if test="age != null">
      AND age = #{age}
    </if>
  </where>
</select>

In the example above, the <where> tag contains multiple conditional judgments, each represented by an <if> tag. The <if> tag is used to determine whether to include the corresponding SQL fragment based on conditions. If the condition is true, the corresponding SQL fragment will be included in the generated SQL statement.

The <where> tag automatically handles the connection relationship between conditions. It ignores AND or OR keywords before the conditions, retains only the conditions themselves, and adds the starting position of the WHERE clause and the connection relationship as needed when generating the SQL statement.

In the example, if both username and age are not empty, the generated SQL statement will contain a WHERE clause similar to WHERE username = #{username} AND age = #{age}. If one of the conditions is empty, the corresponding condition and connection relationship will be automatically omitted.

set

MyBatis is a popular Java persistence framework that provides a simple yet powerful way to combine database operations with Java object mapping. In MyBatis, the <set> tag is used to specify fields to be updated and their corresponding values in an update statement.

The <set> tag is typically used in <update> statements to specify fields to be updated and their values. Its syntax is as follows:

<update id="updateUser" parameterType="User">
  UPDATE users
  <set>
    <if test="username != null">username=#{username},</if>
    <if test="password != null">password=#{password},</if>
    <if test="email != null">email=#{email},</if>
  </set>
  WHERE id=#{id}
</update>

In the example above, the <set> tag contains a series of <if> conditional judgment statements. The test attribute in each <if> tag is used to determine if the corresponding field is empty; if it is not empty, that field and its value are added to the update statement. Note that a comma is added after each field to ensure the correctness of the generated update statement.

In actual usage, you can add more <if> conditional judgment statements as needed to meet update requirements. Additionally, you can use other conditional judgment tags, such as <choose>, <when>, and <otherwise>, to build update statements more flexibly.

trim

In MyBatis, the <trim> tag is used to control the text format of the generated SQL statement. It can be used to add dynamic prefixes, suffixes, and separators between prefixes and suffixes in the generated SQL statement, and decide whether to include these texts based on conditional judgments.

The <trim> tag is typically used to concatenate dynamic WHERE clauses or SET clauses so that different SQL statement fragments can be generated based on different conditions. Its syntax is as follows:

<trim prefix="SET" prefixOverrides=",">
  <if test="username != null">username=#{username},</if>
  <if test="password != null">password=#{password},</if>
  <if test="email != null">email=#{email},</if>
</trim>

In the example above, the <trim> tag contains a series of <if> conditional judgment statements. Its prefix attribute specifies the prefix to be added (here "SET"), and the prefixOverrides attribute specifies the prefix to be removed (here comma ","). Thus, when any conditional judgment statement in it is true, the corresponding field and its value will be added to the generated SQL statement, and a comma separator will be automatically added.

In addition to the prefix and prefixOverrides attributes, the <trim> tag provides other attributes such as suffix and suffixOverrides for adding suffixes and removing suffixes. You can choose to use these attributes according to specific requirements.

Furthermore, the <trim> tag supports nested usage; it can perform multiple concatenations and conditional judgments as needed to build complex SQL statement fragments.

foreach

In MyBatis, the <foreach> tag is used to iterate over collections or arrays in SQL statements and generate corresponding SQL fragments. It can conveniently handle batch operations or dynamic IN clause scenarios.

The <foreach> tag is typically used in <select>, <insert>, <update>, <delete>, and other SQL statements to generate corresponding SQL fragments based on elements in a collection or array. Its syntax is as follows:

<select id="getUserList" parameterType="List" resultType="User">
  SELECT * FROM users
  WHERE id IN
  <foreach item="item" collection="ids" open="(" separator="," close=")">
    #{item}
  </foreach>
</select>

In the example above, the <foreach> tag is used to generate an IN clause, iterating over a collection named ids. The item attribute specifies the reference name for the current element during each iteration, the collection attribute specifies the collection to iterate over, the open attribute specifies the beginning of the IN clause (here left parenthesis "("), the separator attribute specifies the separator between elements (here comma ","), and the close attribute specifies the end of the IN clause (here right parenthesis ")").

Within the <foreach> tag, you can use #{item} to reference the current iterated element and insert it into the generated SQL statement. Through this method, you can dynamically generate an IN clause containing multiple elements without manually concatenating strings.

In addition to the attributes in the example above, the <foreach> tag supports other attributes such as the index attribute for obtaining the index of the current element, the open attribute for specifying text at the beginning of iteration, the close attribute for specifying text at the end of iteration, the separator attribute for specifying the separator between elements, and the item attribute for specifying the name of the iteration variable.

bind

In MyBatis, the <bind> tag is used to declare temporary variables in SQL statements and bind them to a specific value. It can conveniently reuse the same expression or calculation result multiple times within an SQL statement.

The <bind> tag is typically used in <select>, <insert>, <update>, <delete>, and other SQL statements to define and assign a temporary variable before executing the SQL. Its syntax is as follows:

<select id="getUserList" parameterType="User" resultType="User">
  <bind name="searchName" value="'%" + name + "%'" />
  SELECT * FROM users
  WHERE username LIKE #{searchName}
</select>

In the example above, the <bind> tag is used to define a temporary variable named searchName and bind it to a specific value. The name attribute specifies the name of the variable, and the value attribute specifies the value of the variable; expressions can be used for calculation.

Within the SQL statement, you can use #{searchName} to reference this temporary variable and insert it into the SQL statement. Through this method, you can use the same expression or calculation result multiple times in the SQL statement, improving code readability and reusability.

In addition to the attributes in the example above, the <bind> tag supports other attributes such as the type attribute for specifying the data type of the variable and the required attribute for specifying whether the variable is required.

Note that temporary variables defined within the <bind> tag are only valid within the current SQL statement and will not affect other SQL statements.