In the previous four articles, we have learned a lot of things in MyBatis, and there are not many simple things left to learn, MyBatis learning is gradually coming to an end.

Below is the address of the previous article

(MyBatis learning summary (4), notes & many-to-one, one-to-many)

(MyBatis learning summary (3), ResultMap & Log, paging)

(MyBatis learning summary (II), MyBatis implementation of CURD & MyBatis common configuration involved)

MyBatis learning summary (a), a preliminary understanding of MyBatis & simple implementation

The point of dynamic SQL, which we wrote as the last part of MyBatis learning, is the penultimate article.

Dynamic SQL is also not a few words can explain clearly, need a certain length, so this article only write dynamic SQL.

Dynamic SQL

Dynamic SQL is one of the powerful features of MyBatis. If you've ever used JDBC or a similar framework, you can understand how painful it can be to concatenate SQL statements based on different conditions, such as making sure you don't forget to add the necessary whitespace and removing the comma from the last column name of the list. With dynamic SQL, you can get rid of this pain completely.

If you’ve ever worked with JSTL or any xmL-like language based text processor before, dynamic SQL elements may feel familiar. 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, greatly reducing the number of elements to be learned by less than half.

if

choose (when, otherwise)

trim (where, set)

foreach

Let's set up the environment before we get into dynamic SQL

Set up dynamic SQL environment

Create a table

create table `blog` (
	`id` varchar(50) not null comment 'blog id',
    `title` varchar(100) not null comment 'Blog title',
    `author` varchar(30) not null comment 'the writer',
    `createTime` datetime not null comment 'Creation time',
    `views` int(30) not null comment 'Views' 
) engine=InnoDB default charset=utf8;
Copy the code

After creating the table, instead of using SQLYog to insert the data directly, we use Java code to insert the data

Writing entity classes

public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}
// Omit getters, setters, toString methods, and constructors
Copy the code

Write a Mapper interface

public interface BlogMapper {
// Write the corresponding adding data method in mapper interface
    int addBlog (Blog blog);
}
Copy the code

Write the corresponding mapper.xml file


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.molu.mapper.BlogMapper">
<insert id="addBlog" parameterType="Blog">
    insert into mybatis.blog (id,title,author,createTime,views)
    values (#{id},#{title},#{author},#{createTime},#{views});
</insert>
</mapper>
Copy the code

Let’s write another utility class that uses UUID to generate a random blogId

Utility class

public class BlogGetId {
    public static String getId(a){
        return UUID.randomUUID().toString().replaceAll("-".""); }}Copy the code

Insert data into the test class

public class MapperTest {
    @Test
    public void addBlogTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        // Insert data
        mapper.addBlog(new Blog(BlogGetId.getId(),"Introduction to Java"."moluu".new Date(),9999));
        mapper.addBlog(new Blog(BlogGetId.getId(),"Introduction to MySQl"."lin".new Date(),1000));
        mapper.addBlog(new Blog(BlogGetId.getId(),"Introduction to Mybatis"."moluu".new Date(),9999));
        mapper.addBlog(new Blog(BlogGetId.getId(),"Introduction to Spring"."lin".new Date(),9999));
        // Commit transaction for add, delete, and change operations. Otherwise you can't insert itsqlSession.commit(); sqlSession.close(); }}Copy the code

Insert the success

If the insertion fails, check to see if the configuration file binds it, if the SQL in mapper.xml is written incorrectly, and if the submission is omitted........

At this point the environment is ready

Simple to use

For some complex queries, we may specify multiple query conditions, but these conditions may or may not exist, and without the persistence layer framework we may need to concatenate our own SQL statements, which is very painful. However, MyBatis provides dynamic SQL to solve this problem.

IF

Let’s start with the simplest if

<mapper namespace="com.molu.mapper.BlogMapper">
<insert id="addBlog" parameterType="Blog">
    insert into mybatis.blog (id,title,author,createTime,views)
    values (#{id},#{title},#{author},#{createTime},#{views});
    <! -- If is usually defined in the CRUD tag body -->
    <if test="">
        
    </if>
</insert>
</mapper>
Copy the code

Now we want to implement a feature that returns all the blogs if we do not restrict the query criteria, and the blogs that correspond to the criteria if we restrict the criteria.

Use if to do this

Write corresponding methods in the Mapper interface

public interface BlogMapper {
// Return the query result based on the condition
    List<Blog> queryBlogIF(Map map);
}
Copy the code

Modify the mapper. XML file

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog where 1=1

    <if test="title ! = null">
        and title = #{title};
    </if>
    <if test="author ! = null">
        and author = #{author};
    </if>
</select>
Copy the code

We used the if tag to do a simple conditional append to the query SQL.

Writing test classes

public class MapperTest {
    @Test
    public void queryBlogIFTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        List<Blog> blogs = mapper.queryBlogIF(map);
        for(Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }}Copy the code

We pass an empty map to the query code, that is, no criteria are appended.

Since we wrote a condition in the WHERE clause where 1=1, this condition must be satisfied and will return all blogs.

Try to put values into the map (append conditions)

public class MapperTest {
    @Test
    public void queryBlogIFTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        // Now the title has a value
        map.put("title"."Introduction to Java");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for(Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }}Copy the code

As you can see, the data for the condition is returned after the condition is appended.

Using the if tag, we append a condition to the WHERE clause if the test condition is satisfied. This is the simplest use of dynamic SQL

<if test="title ! = null">
    <! Select * from test where title = 'test';
	and title = #{title};
    <! SQL > add condition to where clause
</if>
Copy the code

Let’s play if again

Now let’s query the blog whose author is’ Lin ‘. It’s easy to put the author value into the map.

/ / a map. The put (" title ", "introduction to Java");
map.put("author"."lin");
Copy the code

Query result:

Very practical and very simple a label is not it?

Where

The WHERE tag allows us to avoid errors in our SQL concatenation

In normal development, we don’t write 1=1 at the end of the WHERE clause, so I’m just being lazy to show you. Okay

1=1;

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog where
    <if test="title ! = null">
        and title = #{title};
    </if>
    <if test="author ! = null">
        and author = #{author};
    </if>
</select>
Copy the code

In this case, if tag concatenation is a problem, let’s configure the logging implementation to see the CONCatenation SQL

<settings>
    <! For convenience, we don't need log4j.
	<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
Copy the code

Now let’s run the test class again

Concatenate an AND after where, directly causing SQL statement error.

It’s not really easy to solve this problem completely, because you don’t always use one condition. And nature is indispensable.

MyBatis has a simple solution that works for most scenarios. In other scenarios, you can customize it to fit your needs. And here, all it takes is one simple change.

The WHERE element inserts the “where” clause only if the child element returns anything. Also, if the clause begins with “AND” OR “OR,” the WHERE element removes those as well.

That is, by wrapping the IF tag with a WHERE tag, we can avoid the problem of concatenating and after where, and most importantly, we don’t need to make any changes to our concatenating SQL.

<mapper namespace="com.molu.mapper.BlogMapper">
<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where >
        <if test="title ! = null">
            and title = #{title};
        </if>
        <if test="author ! = null">
            and author = #{author};
        </if>
    </where>
</select>
</mapper>
Copy the code

Run the test class again

As you can see, the AND at the beginning of the CONCatenation SQL is removed directly after running, fundamentally avoiding the above problem.

If we do not append the condition where is automatically removed, which implements the function of returning all blogs without append the condition

You can also customize the functionality of the WHERE element by customizing the trim element if the where element is different from what you expected. For example, the custom trim element equivalent to the WHERE element is:

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

If the WHERE tag doesn’t solve your problem, you can also use the trim tag to change the rules of the WHERE tag and do some simple customization. I’m not going to do that anymore, so if you’re interested you can try it out.

Choose, when, or otherwise

Sometimes, we don’t want to use all of the conditions, but just choose one of several conditions to use. For this, MyBatis provides the Choose element, which is a bit like the Switch statement in Java.

It is the same as the above example, but the policy changes to: if “title” is passed in, search by “title”; if “author” is passed in, search by “author”. If neither is passed in, the Blog with 9999 page views is returned (perhaps because the administrator decided that rather than return a large number of meaningless random blogs, it would be better to return a few blogs selected by the administrator).

Let’s implement this by first modifying mapper.xml

<mapper namespace="com.molu.mapper.BlogMapper">
    <select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title ! = null">
                    and title = #{title}
                </when>
                <when test="author ! = null">
                    and author = #{author}
                </when>
                <otherwise>
                    views = 9999
                </otherwise>
            </choose>
        </where>
    </select>
</mapper>
Copy the code

When written like this, there is no need to return all blogs without conditions. It will satisfy at least one condition, and even if we do not add any conditions, it will concatenate the views = 9999 in the Otherwise tag, so that rather than returning a large number of meaningless random blogs, it will return a few blogs selected by the administrator.

Try not to append conditions

Try to append all conditions

It only executes the condition that was initially satisfied, similar to the Switch statement in Java. So there’s no mutual exclusion, it only satisfies one condition, which is the first condition.

set

Set is similar to WHERE, which is also used to avoid errors when we concatenate SQL

The set element inserts the set keyword dynamically at the top of the line and removes the extra comma. Because when we do SQL concatenation, it’s hard to avoid the “, “problem.

Let’s write a modification operation

First write the corresponding method in the Mapper interface

public interface BlogMapper {
    // Modify the blog according to the ID
    int updateBlog(Map map);
}
Copy the code

Modify the mapper. XML file

<mapper namespace="com.molu.mapper.BlogMapper">
    <update id="updateBlog" parameterType="map">
        update mybatis.blog
        <set>
            <if test="title ! = null">
                title = #{title},
            </if>
            <if test="author ! = null">
                author = #{author},
            </if>
        </set>
        where id = #{id};
    </update>
</mapper>
Copy the code

When we look at a concatenated SQL like this, we can obviously sense that something is going wrong. The comma is obviously one more comma, and it ends up concatenated to:

update mybatis.bolg set title = #{title},author = #{author}, where id = #{id};
Copy the code

Such SQL is logically impossible to execute, but if it’s in a set tag, it doesn’t matter.

The test class

    @Test
    public void updateBlogTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title"."Web portal");
        map.put("author"."lin");
        map.put("id"."7280ac58a49040818f97cf3d59e598dd");
        mapper.updateBlog(map);
        sqlSession.commit();
        sqlSession.close();
    }
Copy the code

Test results:

Set, like WHERE, you can use the trim tag to change the rules of the set tag and do some simple customization.

SQL fragment

When we demonstrate the above tags, some SQL statements are frequently used, and we can use SQL tags to extract them. Implement reuse of SQL.

For example, these lines of SQL are basically used all the time.

<if test="title ! = null">
 	title = #{title}
</if>
<if test="author ! = null">
	author = #{author}
</if>
Copy the code

We use SQL tags to extract it and get a better id.

<sql id="TA_sql">
	<if test="title ! = null">
		title = #{title}
	</if>
	<if test="author ! = null">author ! = #{author}</if>
</sql>
Copy the code

We can use it later when we need to use this SQLincludeThe tag refers to it.

    <select id="queryBlog" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <include refid="TA_sql"></include>
        </where>
    </select>
Copy the code

The test class

    @Test
    public void queryBlogTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title"."Web portal");
        List<Blog> blogs = mapper.queryBlog(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }
Copy the code

Test results:

As you can see, by referring to SQL fragments this way, we can also achieve the desired results with less effort.

foreach

The foreach element is very powerful. It allows you to specify a collection and declare the collection item and index variables that can be used inside the element. It also allows you to specify beginning and ending strings and separators between iterations of collection items. This element also doesn’t add extra delimiters by mistake, look how smart it is! Hint that you can pass any iterable (List, Set, etc.), Map, or array object to Foreach as a collection parameter. When using an iterable or array, index is the sequence number of the current iteration and the value of item is the element fetched in the current iteration. When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.

Look at these relatively official words still can not see the reason, we still through the case to understand.

Since we're going to iterate over the set, our UUID is no longer valid, so let's change it to 1, 2, 3, 4

Write our corresponding method in the Mapper interface

public interface BlogMapper {
    List<Blog> queryBlogForeach(Map map);
}
Copy the code

Write the mapper.xml file

<mapper namespace="com.molu.mapper.BlogMapper">
    <select id="queryBlogForeach" resultType="blog" parameterType="map">
        select * from mybatis.blog
        <where>
            <! Foreach -->
            <foreach collection="Fids" open="(" separator="or" close=")" item="Fid">
                id = #{Fid}
            </foreach>
        </where>
    </select>
</mapper>
Copy the code

The test class

    @Test
    public void queryBlogTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        ArrayList Fids = new ArrayList();
        Fids.add(1);
        Fids.add(2);
        map.put("Fids",Fids);
        mapper.queryBlogForeach(map);
        sqlSession.close();
    }
Copy the code

Test results:

So by this point you should be able to guess what the foreach tag does, right

All the foreach tag does is simply concatenate the SQL, traversing the collection before concatenating.

 <! Foreach -->
            <foreach collection="Fids" item="Fid" open="(" separator="or" close=")" >
                id = #{Fid}
            </foreach>
Copy the code

If we wanted to implement the function of querying the specified blog by the passed ID, the SQL statement would look like this:

select * from mybatis.blog where 1=1 and(id = #{id} or id = #{id} or...).Copy the code

Writing like this, how to look how uncomfortable. One is to write dead, there are limitations, two to write SQL so that people feel a little low…..

We can dynamically pass in the specified ID with the foreach tag.

Start with the properties of foreach

collectionRepresents the name of the collection to iterate over, which is the list we will create in our test class
item, represents the name of each element iterated, which is the parameter we will write in the following condition (Fid)
openRepresents the start of the concatenated SQL
separatorRepresents the delimiter used when concatenating SQL
closeIndicates the end of the concatenated SQL

You should have some idea of the foreach tag.

That is, the collection of lists we pass in through the map will be traversed by the foreach tag

Each iterated element is passed as a condition to our WHERE clause.

Because we used the WHERE tag, the SQL executed successfully even if there was nothing in our collection.

If there is an element in the collection, it will be collated by the foreach tag.

Separator close, we define open, separator close, so that the SQL will not be a problem.

(id = #{Fid} or id = #{iFd} or…..) The stitching.

Dynamic SQL is essentially SQL statements, but we can execute some logical code at the SQL level

Dynamic SQL is in the splicing OF SQL statements, we just need to ensure the correctness of SQL, according to the FORMAT of SQL to find ways to arrange and combine it

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

【MyBatis learning summary (5), dynamic SQL】

Dynamic SQL

Set up dynamic SQL environment

Simple to use

IF

Where

Choose, when, or otherwise

set

SQL fragment

foreach

Relax your eyes

The original address

Painters home page

【MyBatis learning summary (5), dynamic SQL】

Dynamic SQL

Set up dynamic SQL environment

Simple to use

IF

Where

Choose, when, or otherwise

set

SQL fragment

foreach

Relax your eyes

The original address

Painters home page

Related Posts

The threading world in JAVA

Note on the refactoring of card games using microservices (viii) : Game gateway servers

Learn about Zookeeper