Dynamic SQL statement analysis in Mybatis

This article mainly introduces the dynamic SQL statement parsing in Mybatis. The example code in this article is very detailed and has a certain reference value for everyone's study or work. Friends in need can refer to it.

There are two ways to configure SQL in Mybatis, one is to use XML, and the other is to use annotations.

Mybatis uses annotations to configure SQL, but it is rarely used because of its limited configuration capabilities and poor readability for complex SQL.

Mybatis often uses XML configuration method. Using a few simple XML elements, it can complete the function of dynamic SQL. A large number of judgments can be configured in the mapping XML of mybaties to achieve many functions that require a lot of code to implement, which greatly reduces the amount of code and reflects the flexibility, high configurability and maintainability of Mybatis.

The if element

The if element is the most commonly used judgment statement, which is equivalent to the if statement in Java. It is often used in conjunction with the test attribute.

<select id="findRole1" parameterType="string" resultMap="roleResultMap">
    select role_no, role_name, note from t_role where 1=1
    <if test="roleName != null and roleName !=''">
      and role_name like concat('%', #{roleName}, '%')
    </if>
  </select>

When the parameter roleName is passed into the mapper, if the parameter is not empty, a fuzzy query on roleName is constructed, otherwise this condition is not constructed. The if element of Mybaties saves a lot of work on splicing SQL and maintains it in XML.

choose, when, otherwise elements

If there are more options when making judgments, not just two options, it is a statement similar to the switch...case...default... function. In the mapped SQL statement, use the choose, when, otherwise elements to perform this function.

<select id="findRole2" parameterType="role" resultMap="roleResultMap">
    select role_no, role_name, note from t_role
    where 1=1
    <choose>
      <when ​​test="roleNo != null and roleNo !=''">
        AND role_no = #{roleNo}
      </when>
      <when ​​test="roleName != null and roleName !=''">
        AND role_name like concat('%', #{roleName}, '%')
      </when>
      <otherwise>
        AND note is not null
      </otherwise>
    </choose>
  </select>

The above scenario is:

First, if the role number is not empty, only the role number is used as the query condition.

When the role number is empty but the role name is not empty, the role name is used as the condition for fuzzy search.

When both Role Number and Role Number are empty, Role Note is required to be not empty.

trim, where, set elements

Adding "1=1" to the previous SQL statement can achieve its function, but there is a better implementation, which is to use where. When the condition in the where element is met, the where SQL keyword will be added to the assembled SQL, otherwise it will not be added.

<select id="findRole3" parameterType="role" resultMap="roleResultMap">
    select role_no, role_name, note from t_role
    <where>
      <if test="roleName != null and roleName !=''">
        and role_name like concat('%', #{roleName}, '%')
      </if>
      <if test="note != null and note !=''">
        and note like concat('%', #{note}, '%')
      </if>
    </where>
  </select>

Sometimes you need to remove some special SQL syntax, such as the common and, or, etc. Using the trim element will also achieve the desired effect. Prefix represents the prefix of the statement, and prefixOverrides represents which string needs to be removed. It is equivalent to the previous where statement.

<select id="findRole4" parameterType="string" resultMap="roleResultMap">
    select role_no, role_name, note from t_role
    <trim prefix="where" prefixOverrides="and">
      <if test="roleName != null and roleName !=''">
        and role_name like concat('%', #{roleName}, '%')
      </if>
    </trim>
  </select>

In hibernate, if you have to send all fields to the persistent object because of updating a certain field, this will affect the execution efficiency of the SQL statement. The best approach is to pass the primary key and update field values ​​to the SQL to update. The set element can achieve this function. When a set element encounters a comma, it will automatically remove the corresponding comma.

<update id="updateRole" parameterType="role">
    update t_role
    <set>
      <if test="roleName != null and roleName !=''">
        role_name = #{roleName},
      </if>
      <if test="note != null and note != ''">
        note = #{note}
      </if>
    </set>
    where role_no = #{roleNo}
  </update>

foreach element

The foreach element is a loop statement, which is used to traverse the collection. It can well support arrays and collections of List and Set interfaces, and provides traversal functions for them. It is often used for the in keyword in SQL.

<select id="findRoleByNums" resultMap="roleResultMap">
    select role_no, role_name, note from t_role where role_no in
    <foreach item="roleNo" index="index" collection="roleNoList"
      open="(" separator="," close=")">
      #{roleNo}
    </foreach>
  </select>

The roleNoList of the collection configuration is the name of the parameter passed in, which can be an array, List, Set, and other collections.

item configures the current element in the loop.

Index configures the position of the current element in the collection.

Open and close configure what symbols are used to package these collection elements.

Separator is the separator between elements.

Use the test attribute to determine the string

test is used for conditional judgment statements, which is equivalent to judging true or false. In most scenarios, it is mainly used to judge whether something is empty or not.

  <select id="getRoleTest" parameterType="string" resultMap="roleResultMap">
    select role_no, role_name, note from t_role
    <if test="type == 'Y'.toString()">
      where 1=1
    </if>
  </select>

If type='Y' is passed to SQL, Mybatis can add the condition where 1=1, so for string judgment, the toString() method can be added for comparison.

The bind element

The bind element is used to define a context variable through an OGNL expression, which is more convenient to use.

For example, in fuzzy query, if it is MySQL database, concat is often used, which connects the parameters with %. However, there is no such thing in Oracle database. The connection symbol used in Oracle database is "||", so SQL needs two forms to implement it. By using the bind element, there is no need to use the database language.

  <select id="findRole5" parameterType="string" resultMap="roleResultMap">
    <bind name="pattern" value="'%' + _parameter + '%'" />
    SELECT role_no, role_name, note FROM t_role
    where role_name like #{pattern}
  </select>

The above is my summary of common knowledge points about dynamic SQL statements in Mybatis during the learning process. I hope everyone can learn and improve together!

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.