How to realize dynamic SQL in mybatis

Time:2021-6-11

One of the most powerful features of mybatis is its dynamic statement function. If you’ve used JDBC or similar frameworks before, you’ll understand how painful it is to connect SQL statement conditions together. Make sure you don’t forget the space or omit a comma after the columns column. Dynamic statements can completely solve these pains.

Although working with dynamic SQL is not a party, mybatis can improve these situations by using powerful dynamic SQL in any mapping SQL statement.

If element

If element conditional judgment, the most common thing dynamic SQL does is conditionally include where clause. For example:


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

Where element

The where element knows to insert “where” if it contains a tag with content returned. In addition, if the returned content starts with “and” or “, it will remove” and “or”.


<select parameterType="hashmap" resultMap="studentInfo">
 select * from t_student
 <where>
 <if test="name!=null">
  and name=#{name}
 </if>
 <if test="age!=null">
  and age=#{age}
 </if>
 </where>
</select>

Choose element

Sometimes we don’t want to apply all the conditions, but we want to choose one from multiple options. Similar to the switch statement in Java, mybatis provides a choose element.

When element

When the condition in when is true, execute the statement in the when tag


<select parameterType="hashmap" resultMap="studentInfo">
 select * from t_student
 <where>
 <choose>
  <when test="name!=null">
  and name=#{name}
  </when>
  <when test="age!=null">
  and age=#{age}
  </when>
 </choose>
 </where>
</select>

Otherwise element

When all when are not established, execute otherwise


<select parameterType="hashmap" resultMap="studentInfo">
 select * from t_student
 <where>
 <choose>
  <when test="name!=null">
  and name=#{name}
  </when>
  <when test="age!=null">
  and age=#{age}
  </when>
  <otherwise>
  and name='jim'
  </otherwise>
 </choose>
 </where>
</select>

Trim element

If the where element doesn’t behave exactly as you think, you can also customize it with the trim element.

​   If any of the if’s in trim is true, add a prefix and define it with prefix =. If not, do not add it.

​   The trim element can also remove the keyword and or or specified after where and define it with prefixoverrides


<select parameterType="hashmap" resultMap="studentInfo">
 select * from t_student
 <trim prefix="where" prefixOverrides="and">
 <if test="name!=null">
  and name=#{name}
 </if>
 <if test="age!=null">
  and age=#{age}
 </if>
 </trim>
</select>

Set element

In the dynamic update statement, a similar solution is called set, which can dynamically update columns.

The set element dynamically configures the set keyword and is also used to remove any irrelevant commas appended to the end of the condition.


<update parameterType="Student">
 update t_student
 <set>
 <if test="name!=null">
  name=#{name},
 </if>
 <if test="age!=null">
  age=#{age},
 </if>
 </set>
 where id=#{id}
</update>

Of course, smart you want to know how to write the equivalent trim element, it’s like this:


<update parameterType="Student">
 update t_student
 <trim prefix="set" prefixOverrides=",">
 <if test="name!=null">
  name=#{name},
 </if>
 <if test="age!=null">
  age=#{age},
 </if>
 </trim>
 where id=#{id}
</update>

Note that we have removed a suffix and added a prefix.

Foreach element

Another function that dynamic SQL often uses is set iteration, which is usually used in in conditional sentences

The foreach element is very powerful, allowing you to specify a collection that declares items and index variables that can be used within an element. It also allows you to specify the start and end characters, or add a separator between iterators. The smart thing about this element is that it doesn’t accidentally append extra separators.


<select resultMap="studentInfo">
 select * from t_student where age in
 <foreach collection="list" item="item" open="(" separator="," close=")">
 #{item}
 </foreach>
</select>

The test method is as follows:


public void findStudentByAge() {
 SqlSession sqlSession = null;
 try {
 sqlSession = MyBatisUtil.getsqlSession();
 StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
 List<Integer> list= new ArrayList<Integer>();
 list.add(21);
 list.add(23);
 List<Student> liststudent =studentDao.findStudentByAge(list);
 System.out.println(liststudent);
 } catch (Exception e) {
 e.printStackTrace();
 }
}
 

Output SQL result: select * from t_ Student where age in (item, item) displays the information of students with age 21 and 23.

Settings element

Under the setting element are some very important setting options for setting and changing the behavior of mybatis in operation. The following table lists the attributes, default values, and functions supported by the setting element.

[external link picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-vuqc9cuw-1576746278488) (E: [JavaEE notes / img / QQ browser screenshot 20191218153217. PNG)]

Complete configuration example:


<settings>
 <setting name="cacheEnabled" value="true"/>
 <setting name="lazyLoadingEnabled" value="true"/>
 <setting name="multipleResultSetsEnabled" value="true"/>
 <setting name="useColumnLabel" value="true"/>
 <setting name="useGeneratedKeys" value="false"/>
 <setting name="enhancementEnabled" value="false"/>
 <setting name="defaultExecutorType" value="SIMPLE"/>
 <setting name="defaultStatementTimeout" value="25000"/>
</settings>

Special characters in XML

If mybatis uses XML configuration, it will inevitably encounter some characters that are special to XML. For example, the less than sign “<“, because the XML parser will think it is the beginning of a new element, so it needs to be escaped. There are two ways:  

1 use escape entity

Here are five predefined escape entities in XML documents:

[external link picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly


<select parameterType="hashmap" resultMap="studentInfo">
 select * from t_student where age $lt; 23
</select>

2 using CDATA components

A CDATA part ends with a “”. The meaning of special characters between “and” does not work, but changes to the content of ordinary string.

In general, in mybatis XML Mapping statement configuration file, if the SQL statement has special characters, use CDTA components to enclose it, such as:


<select parameterType="hashmap" resultMap="studentInfo">
 <![CDATA[
 select * from t_student where age = 23
 ]]> 
</select>

However, in the test statements of dynamic SQL elements, other elements or CDATA components cannot be nested in the attributes of elements, so only escape entities can be used, such as:


<select parameterType="Blog" resultType="Author">
 select * from author
 <where>
 <if test="authorId != null
  and authorId &gt;= 1
  and authorId &lt;= 5">
 id = #{authorId}
 </if>
 </where>
</select> 

The above is the whole content of this article, I hope to help you learn, and I hope you can support developer more.