Master the use and principle of dynamic SQL of MyBatis in one article – HUAWEI CLOUD Developer Alliance’s personal space – News Fast Delivery

Summary:Working with dynamic SQL is not an easy task, but MyBatis significantly improves the ease of use of this feature with the powerful dynamic SQL language that can be used in any SQL mapped statement.

This article is shared from Huawei Cloud Community “MyBatis Detailed Explanation – Dynamic SQL Usage and Principles”, author: Long Ge Notes.

Dynamic SQL is one of the powerful features of MyBatis. If you have used JDBC or other similar frameworks, you should understand how painful it is to concatenate SQL statements according to different conditions. For example, when concatenating, make sure not to forget to add necessary spaces, and also pay attention to remove the comma in the last column name of the list. Using dynamic SQL, you can completely get rid of this pain.

Working with dynamic SQL is not an easy task, but MyBatis significantly improves the ease of use of this feature with the powerful dynamic SQL language that can be used in any SQL mapped statement.

If you’ve used JSTL or any XML-like language-based text processor before, the dynamic SQL element may feel familiar to you. In previous versions of MyBatis, it took time to understand a large number of elements. With the help of powerful OGNL-based expressions, MyBatis 3 replaces most of the previous elements and greatly simplifies the types of elements. Now the types of elements to learn are less than half of the original.

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

if

The most common scenario for using dynamic SQL is to include part of a where clause based on conditions. for example:

<select id="findActiveBlogWithTitleLike"
 resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
 <if test="title != null">
    AND title like #{title}
 </if>
</select>

This statement provides optional find text functionality. If no “title” is passed in, then all BLOGs in the “ACTIVE” state will be returned; if the “title” parameter is passed in, then a fuzzy search will be performed on the “title” column and the corresponding BLOG results will be returned (careful readers You may find that the parameter value for “title” needs to contain lookup mask or wildcard characters).

What if you want optional searches via the “title” and “author” parameters? First, I want to change the name of the statement to something more realistic; next, I just need to add another condition.

<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>

choose, when, otherwise

Sometimes, we don’t want to use all the conditions, but just want to choose one from multiple conditions. For this situation, MyBatis provides the choose element, which is a bit like the switch statement in Java.

The above example is still the same, but the strategy becomes: search by “title” if “title” is passed in, and search by “author” if “author” is passed in. If neither is passed in, return the BLOG marked as featured (this may be because the administrator thinks that instead of returning a large number of meaningless random Blogs, it is better to return some Blogs selected by the administrator).

<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>

trim, where, set

The previous few examples have conveniently solved a notorious dynamic SQL problem. Now go back to the previous “if” example, this time let’s make “state = ‘ACTIVE'” a dynamic condition and see what happens.

<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>
</select>

What happens if there are no matching conditions? In the end, this SQL will become like this:

This causes the query to fail. What if only the second condition is matched? The SQL would be like this:

SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’

This query also fails. This problem cannot be solved simply with conditional elements. The problem is so intractable that no one who has solved it will want to run into it again.

MyBatis has a simple and suitable solution for most scenarios. While in other scenarios, it can be customized to suit the needs. And this requires only one simple change:

<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>

The where element will only insert a “WHERE” clause if the child element returns nothing. Also, if clauses begin with “AND” or “OR”, the where element strips them out as well.

If the where element is not what you expected, you can also customize the function of the where element by customizing the trim element. For example, the custom trim element equivalent to the where element is:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

The prefixOverrides attribute ignores sequences of text separated by pipes (note that spaces are necessary in this case). The above example will remove all the content specified in the prefixOverrides attribute and insert the content specified in the prefix attribute.

A similar solution for dynamic update statements is called set. The set element can be used to dynamically include columns that need to be updated, ignoring other columns that do not update. for example:

<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>

In this example, the set element will dynamically insert the SET keyword at the beginning of the line and will remove the extra commas that were introduced when assigning values to columns using conditional statements.

Let’s take a look at the custom trim element equivalent to the set element:

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

Note that we override the suffix value setting and customize the prefix value.

foreach

Another common use case for dynamic SQL is traversing collections (especially when building IN conditional statements). for example:

<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>

The foreach element is very powerful, it allows you to specify a collection, and declares collection item (item) and index (index) variables that can be used in the element body. It also allows you to specify beginning and ending strings and separators between iterations of collection items. This element also doesn’t mistakenly add extra separators, how smart it is!

Tip: You can pass any iterable object (such as List, Set, etc.), Map object, or array object as a collection parameter to foreach. When using an iterable object or array, index is the serial number of the current iteration, and the value of item is the element obtained by this iteration. When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.

So far, we have completed the discussion related to XML configuration and mapping files. The next chapter will explore the Java API in detail so that you can take full advantage of the mapping configurations you have created.

script

To use dynamic SQL in annotated mapper interface classes, you can use the script element. for example:

 @Update({"<script>",
 "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}",
 "</script>"})
 void updateAuthorValues(Author author);

bind

The bind element allows you to create a variable outside of an OGNL expression and bind it to the current context. for example:

<select id="selectBlogsLike" resultType="Blog">
 <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

Multiple database support

If the databaseIdProvider is configured, you can use a variable named “_databaseId” in the dynamic code to build specific statements for different databases. Like the following example:

<insert id="insert">
 <selectKey keyProperty="id" resultType="int" order="BEFORE">
 <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
 </if>
 <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
 </if>
 </selectKey>
  insert into users values (#{id}, #{name})
</insert>

Insert scripting language in dynamic SQL

MyBatis supports inserting scripting language from version 3.2, which allows you to insert a language driver and write dynamic SQL query statements based on this language.

A language can be plugged in by implementing the following interface:

public interface LanguageDriver {
 ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
 SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
 SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType);
}

After implementing a custom language driver, you can set it as the default language in the mybatis-config.xml file:

<typeAliases>
 <typeAlias type="org.sample.MyLanguageDriver" alias="myLanguage"/>
</typeAliases>
<settings>
 <setting name="defaultScriptingLanguage" value="myLanguage"/>
</settings>

Alternatively, you can specify the language for a particular statement using the lang attribute:

<select id="selectBlog" lang="myLanguage">
  SELECT * FROM BLOG
</select>

Alternatively, add the @Lang annotation to your mapper interface:

public interface Mapper {
 @Lang(MyLanguageDriver.class)
 @Select("SELECT * FROM BLOG")
 List<Blog> selectBlog();
}

Tip: You can use Apache Velocity as a dynamic language. For more details, please refer to the MyBatis-Velocity project.

All the xml tags you saw earlier are provided by the default MyBatis language, which is provided by the language driver org.apache.ibatis.scripting.xmltags.XmlLanguageDriver (alias xml).

Principles of dynamic SQL parsing

When we use mybatis, we will write sql statements in xml. For example, this dynamic sql code:

<update id="update" parameterType="org.format.dynamicproxy.mybatis.bean.User">
    UPDATE users
 <trim prefix="SET" prefixOverrides=",">
 <if test="name != null and name != ''">
            name = #{name}
 </if>
 <if test="age != null and age != ''">
            , age = #{age}
 </if>
 <if test="birthday != null and birthday != ''">
            , birthday = #{birthday}
 </if>
 </trim>
    where id = ${id}
</update>

How does the bottom layer of mybatis construct this sql? With this question in mind, let’s analyze it step by step.

Interfaces and classes about dynamic SQL

SqlNode interface, a simple understanding is each tag in xml, such as the update, trim, and if tags of the above sql:

public interface SqlNode {
 boolean apply(DynamicContext context);
}

SqlSource Sql source interface, which represents the sql content mapped from xml files or annotations, is mainly used to create BoundSql, with implementation classes DynamicSqlSource (dynamic Sql source), StaticSqlSource (static Sql source), etc.:

public interface SqlSource {
 BoundSql getBoundSql(Object parameterObject);
}

The BoundSql class encapsulates the class that mybatis finally generates sql, including sql statements, parameters, parameter source data and other parameters:

XNode, an extension class of the Node interface in the Dom API:

The BaseBuilder interface and its implementation class (attributes and methods are omitted, you can see for yourself if you are interested), the function of these Builders is to construct sql:

Let’s briefly analyze the four Builders below:

XMLConfigBuilder: Parse the global xml file in the configLocation attribute in mybatis, and internally use XMLMapperBuilder to parse each xml file.
XMLMapperBuilder: Traversing the Builder of each node in the xml file in the mapperLocations attribute in mybatis, such as user.xml, internally uses XMLStatementBuilder to process each node in xml.
XMLStatementBuilder: Parse each node in the xml file, such as select, insert, update, delete nodes, internally use XMLScriptBuilder to process the sql part of the node, and the data generated by traversal will be thrown into the mappedStatements of Configuration.
XMLScriptBuilder: Builder for parsing the sql part of each node in xml.

LanguageDriver interface and its implementation class (properties and methods are omitted, you can read it yourself if you are interested), the main function of this interface is to construct sql:

Simple analysis of XMLLanguageDriver (processing sql in xml, RawLanguageDriver processing static sql): XMLLanguageDriver internally uses XMLScriptBuilder to parse the sql part in xml.

Source code analysis starts

When integrating Spring with Mybatis, you need to configure SqlSessionFactoryBean, which will add information such as data source and mybatis xml configuration file path:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
 <property name="dataSource" ref="dataSource"/>
 <property name="configLocation" value="classpath:mybatisConfig.xml"/>
 <property name="mapperLocations" value="classpath*:org/format/dao/*.xml"/>
</bean>

Let’s analyze the details behind this configuration:

SqlSessionFactoryBean implements Spring’s InitializingBean interface. The buildSqlSessionFactory method is called in the afterPropertiesSet method of the InitializingBean interface. This method internally uses the XMLConfigBuilder to parse the path configured in the property configLocation, and uses the XMLMapperBuilder property to parse each xml file in the mapperLocations property. Part of the source code is as follows:

Since XMLConfigBuilder also uses XMLMapperBuilder internally, let’s take a look at the parsing details of XMLMapperBuilder:

Let’s pay attention to the analysis of adding, deleting, modifying and checking nodes:

Analysis of XMLStatementBuilder:

By default, XMLLanguageDriver will be used to create SqlSource (set in the Configuration constructor).

XMLLanguageDriver creates SqlSource:

XMLScriptBuilder parses sql:

After getting the SqlSource, it will be placed in the Configuration. With the SqlSource, you can get the BoundSql, and the BoundSql can get the final sql.

Case Analysis

The following xml analysis roughly describes the analysis process of parseDynamicTags:

<update id="update" parameterType="org.format.dynamicproxy.mybatis.bean.User">
    UPDATE users
 <trim prefix="SET" prefixOverrides=",">
 <if test="name != null and name != ''">
            name = #{name}
 </if>
 <if test="age != null and age != ''">
            , age = #{age}
 </if>
 <if test="birthday != null and birthday != ''">
            , birthday = #{birthday}
 </if>
 </trim>
    where id = ${id}
</update>

The return value of the parseDynamicTags method is a List, which is a collection of Sql nodes. SqlNode has been introduced at the beginning of this article. After analyzing the parsing process, I will talk about the role of each SqlNode type.

First, all child nodes are obtained according to the update node (Node), which are 3 child nodes:

Text node\n UPDATE users
trim child node…
Text Node\n where id = #

Traverse each child node:

If the node type is text or CDATA, construct a TextSqlNode or StaticTextSqlNode;
If the node type is an element, it means that the update node is a dynamic sql, and then NodeHandler will be used to process each type of child nodes. The NodeHandler here is an internal interface of XMLScriptBuilder, and its implementation classes include TrimHandler, WhereHandler, SetHandler, IfHandler, ChooseHandler, etc. You can understand the function of this Handler by looking at the class name. For example, the trim node we analyzed corresponds to TrimHandler; the if node corresponds to IfHandler… Here, the child node trim is processed by TrimHandler, and the parseDynamicTags method is also used inside TrimHandler to parse the node .

If the child node is an element, repeat the above steps:

There are 7 child nodes inside the trim child node, which are text node, if node, is text node, if node, is text node, if node, and text node. Text nodes are processed as before, and if nodes are processed using IfHandler. The traversal steps are shown above, let’s look at the implementation details of several Handlers.

The IfHandler processing method also uses the parseDynamicTags method, and then adds the necessary attributes of the if tag:

private class IfHandler implements NodeHandler {
 public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
 List<SqlNode> contents = parseDynamicTags(nodeToHandle);
 MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
 String test = nodeToHandle.getStringAttribute("test");
 IfSqlNode ifSqlNode = new IfSqlNode(mixedSqlNode, test);
 targetContents.add(ifSqlNode);
 }
}

The TrimHandler processing method also uses the parseDynamicTags method, and then adds the necessary attributes of the trim tag:

private class TrimHandler implements NodeHandler {
 public void handleNode(XNode nodeToHandle, List<SqlNode> targetContents) {
 List<SqlNode> contents = parseDynamicTags(nodeToHandle);
 MixedSqlNode mixedSqlNode = new MixedSqlNode(contents);
 String prefix = nodeToHandle.getStringAttribute("prefix");
 String prefixOverrides = nodeToHandle.getStringAttribute("prefixOverrides");
 String suffix = nodeToHandle.getStringAttribute("suffix");
 String suffixOverrides = nodeToHandle.getStringAttribute("suffixOverrides");
 TrimSqlNode trim = new TrimSqlNode(configuration, mixedSqlNode, prefix, prefixOverrides, suffix, suffixOverrides);
 targetContents.add(trim);
 }
}

The above update method finally obtains the SqlNode collection through the parseDynamicTags method as follows:

trim node:

Since this update method is a dynamic node, a DynamicSqlSource is constructed. SQL can be constructed inside DynamicSqlSource:

The SqlNode property inside DynamicSqlSource is a MixedSqlNode. Then we look at the apply method of each SqlNode implementation class. Let’s analyze the implementation of the apply method of each SqlNode implementation class:

MixedSqlNode: MixedSqlNode will traverse and call the apply method of each internal sqlNode.

public boolean apply(DynamicContext context) {
 for (SqlNode sqlNode : contents) {
 sqlNode.apply(context);
 }
 return true;
}

StaticTextSqlNode: directly append sql text.

public boolean apply(DynamicContext context) {
 context.appendSql(text);
 return true;
}

IfSqlNode: The evaluator here is an instance of the ExpressionEvaluator type, which uses OGNL internally to process expression logic.

public boolean apply(DynamicContext context) {
 if (evaluator.evaluateBoolean(test, context.getBindings())) {
 contents.apply(context);
 return true;
 }
 return false;
}

TrimSqlNode:

public boolean apply(DynamicContext context) {
 FilteredDynamicContext filteredDynamicContext = new FilteredDynamicContext(context);
 boolean result = contents.apply(filteredDynamicContext);
 filteredDynamicContext.applyAll();
 return result;
}
public void applyAll() {
 sqlBuffer = new StringBuilder(sqlBuffer.toString().trim());
 String trimmedUppercaseSql = sqlBuffer.toString().toUpperCase(Locale.ENGLISH);
 if (trimmedUppercaseSql.length() > 0) {
 applyPrefix(sqlBuffer, trimmedUppercaseSql);
 applySuffix(sqlBuffer, trimmedUppercaseSql);
 }
 delegate.appendSql(sqlBuffer.toString());
}
private void applyPrefix(StringBuilder sql, String trimmedUppercaseSql) {
 if (!prefixApplied) {
 prefixApplied = true;
 if (prefixesToOverride != null) {
 for (String toRemove : prefixesToOverride) {
 if (trimmedUppercaseSql.startsWith(toRemove)) {
 sql.delete(0, toRemove.trim().length());
 break;
 }
 }
 }
 if (prefix != null) {
 sql.insert(0, " ");
 sql.insert(0, prefix);
 }
 }
}

The apply method of TrimSqlNode is also the apply method of calling the attribute contents (usually MixedSqlNode). According to the example, there are 7 SqlNodes, all of which are StaticTextSqlNode and IfSqlNode. Finally, the prefix and suffix will be filtered out using FilteredDynamicContext.

Click to follow and learn about Huawei Cloud’s fresh technologies for the first time~

#Master #principle #dynamic #SQL #MyBatis #article #HUAWEI #CLOUD #Developer #Alliances #personal #space #News Fast Delivery

if