Detailed description of dynamic SQL of mybatis (excellent) and detailed description of dynamic SQL of mybatis

Detailed description of dynamic SQL of mybatis (excellent) and detailed description of dynamic SQL of mybatis

One of the powerful features of MyBatis is its dynamic SQL capability. If you have experience using JDBC or other similar frameworks, you will understand how painful it is to concatenate SQL strings with conditions, make sure that you do not forget the blank space or omit the comma at the end of the list. Dynamic SQL can completely handle this pain.

Generally, dynamic SQL cannot be an independent part. MyBatis certainly uses a powerful dynamic SQL language to improve this situation. This language can be used in any ing SQL statements.

Dynamic SQL elements are similar to XML-based text processors that use JSTL or other similar features. In versions earlier than MyBatis, there are many elements to understand. MyBatis3 greatly improved them, And now it can work with less than half of the original elements. MyBatis uses a powerful OGNL-based expression to eliminate other elements.

In MyBatis, the main elements used to implement dynamic SQL are:

If

Choose (when, otherwise)

Trim

Where

Set

Foreach

If is a simple condition judgment. Using if statements, we can implement some simple conditions.

Let's take a look at the following example:

Xml Code

<select id="dynamicIfTest" parameterType="Blog" resultType="Blog">  select * from t_blog where 11 = 1  <if test="title != null">   and title = #{title}  </if>  <if test="content != null">   and content = #{content}  </if>  <if test="owner != null">   and owner = #{owner}  </if> </select>

The meaning of this statement is very simple. If you provide the title parameter, it must satisfy the requirements of title =#{ title}. If you provide the Content and Owner, they also need to meet the corresponding conditions, and then return all the blogs that meet these conditions. This is a very useful function. In the past, when we used other types of frameworks or directly used JDBC, if we want to achieve the same effect of selection, we need to spell SQL statements, which is extremely troublesome. In comparison, the above dynamic SQL statements are much simpler.

The role of the choose element is equivalent to the switch statement in JAVA. Basically, it is the same as the role and usage of choose in JSTL. It is usually used together with when and otherwise.

See the following example:

Xml Code

<select id="dynamicChooseTest" parameterType="Blog" resultType="Blog">  select * from t_blog where 11 = 1  <choose>   <when test="title != null">    and title = #{title}   </when>   <when test="content != null">    and content = #{content}   </when>   <otherwise>    and owner = "owner1"   </otherwise>  </choose> </select>

The when element indicates that the content is output when the when condition is met. Similar to the switch effect in JAVA, the content is output according to the condition sequence. when the when condition is met, it will jump out of the choose, that is, only one of all the when and otherwise conditions will output the content in otherwise when all the conditions are not met.

Therefore, the preceding statement is very simple! If it is null, and titlte =#{ title} is output. The condition is no longer judged. When the title is empty and the content is displayed! If it is null, and content = # {content} is output. If none of the conditions are met, the content in otherwise is output.

The where statement is mainly used to simplify the where clause in SQL statements. Let's look at an example and explain the advantages of where.

Xml Code

<select id="dynamicWhereTest" parameterType="Blog" resultType="Blog">  select * from t_blog  <where>   <if test="title != null">    title = #{title}   </if>   <if test="content != null">    and content = #{content}   </if>   <if test="owner != null">    and owner = #{owner}   </if>  </where> </select>

The function of the where element is to output a where field where the where element is written. Another advantage is that you do not need to consider what the conditional output in the where element looks like, myBatis will help you intelligently. If all the conditions are not met, MyBatis will find all records. If the output starts with and, MyBatis will ignore the first and, of course, if it starts with or, MyBatis will also ignore it. In addition, you do not need to consider the space issue in the where element. MyBatis will help you intelligently add it. In the preceding example, if title = null and content! = Null, the entire output statement will be select * from t_blog where content =#{ content}, instead of select * from t_blog where and content =#{ content }, because MyBatis intelligently ignores the first and or.

The main function of the trim element is to add some prefixes before the content it contains, or add some suffixes after it. The corresponding attributes are prefix and suffix; you can overwrite, ignore, or overwrite, some content at the end of the content. The corresponding attribute is prefixOverrides and suffixOverrides. trim has such a function, therefore, we can simply use trim to replace the where element function. The sample code is as follows:

Xml Code

<select id="dynamicTrimTest" parameterType="Blog" resultType="Blog">  select * from t_blog  <trim prefix="where" prefixOverrides="and |or">   <if test="title != null">    title = #{title}   </if>   <if test="content != null">    and content = #{content}   </if>   <if test="owner != null">    or owner = #{owner}   </if>  </trim> </select>

The set element is mainly used for update operations. Its main functions are similar to those of the where element. It is mainly used to output a set before the contained statement, if the included statement ends with a comma, the comma will be ignored. If the content contained in the set is empty, an error will occur. With the set element, we can dynamically update the modified fields. The following is an example code:

Xml Code

<update id="dynamicSetTest" parameterType="Blog">  update t_blog  <set>   <if test="title != null">    title = #{title},   </if>   <if test="content != null">    content = #{content},   </if>   <if test="owner != null">    owner = #{owner}   </if>  </set>  where id = #{id} </update>

In the above sample code, if one of the conditions in the set is not met, that is, if the content in the set is null, an error is returned.

Foreach is mainly used to construct in conditions. It can iterate a set in SQL statements. Attributes of the foreach element include item, index, collection, open, separator, and close. Item indicates the alias of each element in the set during iteration. index specifies a name to indicate the position of each iteration during the iteration. open indicates the start of the statement, separator indicates the separator between each iteration, and close indicates the end of the iteration. When using foreach, the most important and error-prone is the collection attribute, this attribute must be specified, but in different cases, the value of this attribute is different, mainly in the following three cases:

1. If the input is a single parameter and the parameter type is a List, the collection property value is list

2. If the input is a single parameter and the parameter type is an array, the property value of the collection is array.

3. if the input parameters are multiple, We Need To encapsulate them into a Map. Of course, a single parameter can also be encapsulated into a map. In fact, if you pass in parameters, in MyBatis, It is encapsulated into a Map, and the map key is the parameter name. Therefore, the collection property value is the key of the input List or array object in the encapsulated map.

Let's take a look at the sample code for the above three situations:

1. single parameter List type:

Xml Code

<select id="dynamicForeachTest" resultType="Blog">  select * from t_blog where id in  <foreach collection="list" index="index" item="item" open="(" separator="," close=")">   #{item}  </foreach> </select>

The value of the above collection is list, and the Mapper corresponding to it is like this

Java code

public List<Blog> dynamicForeachTest(List<Integer> ids); 

Test code:

Java code

@Test public void dynamicForeachTest() {  SqlSession session = Util.getSqlSessionFactory().openSession();  BlogMapper blogMapper = session.getMapper(BlogMapper.class);  List<Integer> ids = new ArrayList<Integer>();  ids.add(1);  ids.add(3);  ids.add(6);  List<Blog> blogs = blogMapper.dynamicForeachTest(ids);  for (Blog blog : blogs)   System.out.println(blog);  session.close(); }

2. Single Parameter array type:

Xml Code

<select id="dynamicForeach2Test" resultType="Blog">  select * from t_blog where id in  <foreach collection="array" index="index" item="item" open="(" separator="," close=")">   #{item}  </foreach> </select> 

 The above collection is an array, corresponding Mapper code:

Java code

public List<Blog> dynamicForeach2Test(int[] ids); 

Corresponding test code:

Java code

@Test public void dynamicForeach2Test() {  SqlSession session = Util.getSqlSessionFactory().openSession();  BlogMapper blogMapper = session.getMapper(BlogMapper.class);  int[] ids = new int[] {1,3,6,9};  List<Blog> blogs = blogMapper.dynamicForeach2Test(ids);  for (Blog blog : blogs)   System.out.println(blog);  session.close(); }

3. encapsulate parameters as Map types.

Xml Code

<select id="dynamicForeach3Test" resultType="Blog">  select * from t_blog where title like "%"#{title}"%" and id in  <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">   #{item}  </foreach> </select>

The value of the preceding collection is ids, which is the key of the input Map parameter, corresponding to the Mapper code:

Java code

public List<Blog> dynamicForeach3Test(Map<String, Object> params); 

Corresponding test code:

Java code

@ Test public void dynamicForeach3Test () {SqlSession session = Util. getSqlSessionFactory (). openSession (); BlogMapper blogMapper = session. getMapper (BlogMapper. class); final List <Integer> ids = new ArrayList <Integer> (); ids. add (1); ids. add (2); ids. add (3); ids. add (6); ids. add (7); ids. add (9); Map <String, Object> params = new HashMap <String, Object> (); params. put ("ids", ids); params. put ("title", "China"); List <Blog> blogs = blogMapper. dynamicForeach3Test (params); for (Blog: blogs) System. out. println (blog); session. close ();}

The above is a detailed description of the dynamic SQL statement of mybatis. I hope you will like it.