MyBatis series (9) - MyBatis dynamic SQL | August more text challenge

MyBatis series summary: MyBatis series

preface

Dynamic SQL is one of the powerful features of MyBatis. If you’ve used JDBC or a similar framework, you can understand how painful it can be to concatenate SQL statements according to different conditions, such as making sure you don’t forget to add necessary whitespace, and taking care to remove the comma from the last column name in a list. With dynamic SQL, you can get rid of this pain entirely.
That’s what MaBatis says! This article is important! Essential in work!
First, let’s build a library of tests. The following examples are built on this table for testing!

Entity class

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private Integer id;
    private String title;
    private String autor;
    private Date creat_time;
    private Integer reads;
}
Copy the code

Using dynamic tags, you can write some basic logic in XML, that is, SQL. Very convenient to use!
Make point data, convenient test.

1. If and WHERE tags

xml

    <select id="getBlogInfo" resultType="Blog" parameterType="map">
        select
           *
        from
           myblog
        where
           1 = 1
        <if test="title! ='' and title! =null">
            and title like concat(concat(The '%',#{title}), '%') < /if>
    </select>
Copy the code

mapper
- ```
List<Blog> getBlogInfo(Map<String,Object> map);
Copy the code
```
- In real work development, the values we pass in usually use maps. This makes it easier and easier to expand.
- And return result, we generally use the entity class to achieve, because Swgger, before and after the end of the unbeatable convenience!! Highly recommended!
- If you want to know more about Swgger, please leave a comment. The blogger will decide whether to open a separate article to explain Swgger according to the degree of need!!

Junit Test

    @Test
    public void getMyBlog(a){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("title"."Mybatis");
        List<Blog> myBlogMappers = mapper.getBlogInfo(map);
        for (Blog myBlogMapper : myBlogMappers) {
            System.out.println(myBlogMapper);
        }
        session.close();
    }
Copy the code

Execution result:
- Perfect search.
One might ask, why do we add this label?
- First of all, the if tag makes our SQL more flexible. The title value, if passed, represents a conditional query. If not, it is invalid code.
- Then, it’s possible to do this in a service, but it’s tedious. So in practice, the if tag is the most used!
- Finally, the students noticed in the SQL above1 = 1In the actual development, we are generally soft delete (logical delete), so generally there will be a delete flag to determine whether this data exists! This was added purely to simulate real development code!

So when is the WHERE tag used?

xml

    <select id="getBlogInfo1" resultType="Blog" parameterType="map">
        select
        *
        from
        myblog
        where
        <if test="title! ='' and title! =null">
            title like concat(concat(The '%',#{title}), '%') < /if>
        <if test="id! ='' and id! =null">
            and id = #{id}
        </if>
    </select>

Copy the code

If our statement looks like this, then if title is empty, is the SQL statement equivalent to where followed by and?

Presentation:

    @Test
    public void getMyBlog1(a){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("id"."2");
        List<Blog> myBlogMappers = mapper.getBlogInfo1(map);
        for (Blog myBlogMapper : myBlogMappers) {
            System.out.println(myBlogMapper);
        }
        session.close();
    }

Copy the code

The following error message is displayed

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and id = '2' ' at line 8
### The error may exist in com/dy/dynamic/mapper/MyBlogMapper.xml
### The error may involve com.dy.dynamic.mapper.MyBlogMapper.getBlogInfo1-Inline
### The error occurred while setting parameters
### SQL: select         *         from         myblog         where                                 and id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and id = '2' ' at line 8

Copy the code

Use the WHERE tag

    <select id="getBlogInfo1" resultType="Blog" parameterType="map">
        select
        *
        from
        myblog
        <where>
        <if test="title! ='' and title! =null">
            title like concat(concat(The '%',#{title}), '%') < /if>
        <if test="id! ='' and id! =null">
            and id = #{id}
        </if>
        </where>
    </select>
Copy the code

Execute and see the result
Perfect solution!

What happens if there’s no matching condition? The SQL will eventually look like this:
- ```
SELECT * FROM BLOG
WHERE
Copy the code
```
This will cause the query to fail. What if only the second one matches? The SQL would look like this:
- ```
SELECT * FROM BLOG
WHERE AND id = 2
Copy the code
```
- This query will also fail. The problem cannot be solved simply with conditional elements.
It’s easy to see how the WHERE tag works!
- When a condition has and, it can determine whether it is the first condition, and if it is, it will automatically remove it.
- whereThe element inserts a “WHERE” clause only if the child element returns anything. AND if a clause begins with “AND” OR “OR”,whereThe element will also remove them.
If the WHERE element is not what you expect, you can also customize the functionality of the WHERE element by customizing trim. For example, the custom trim element equivalent to the WHERE element is:
- ```
<trim prefix="WHERE" prefixOverrides="AND |OR ">... </trim>Copy the code
```
- I’ll talk about trim later.

Choose, when, otherwise

Sometimes, we don’t want to use all of the conditions, but just want to use one from multiple conditions. For this case, MyBatis provides a Choose element, which is a bit like a Switch statement in Java.
When we’re done with if, remember there’s another way of judging conditions?
- That’s right, switch Case.
- Choose in dynamic SQL
- Let’s play with this

xml

    <select id="getBlogInfoWhoose" resultType="Blog" parameterType="map">
        select
            *
        from
            myblog
       <where>
          <choose>
              <when test="title! ='' and title! =null">
                  title like concat(concat(The '%',#{title}),'%')
              </when>
              <when test="id! ='' and id! =null">
                  and id = #{id}
              </when>
              <otherwise>
                  AND `reads` > 10000
              </otherwise>
          </choose>
       </where>
    </select>
Copy the code

mapper

List<Blog> getBlogInfoWhoose(Map<String,Object> map);
Copy the code

Junit Test

    @Test
    public void getMyBlog1(a){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("title"."Spring");
        List<Blog> myBlogMappers = mapper.getBlogInfoWhoose(map);
        for (Blog myBlogMapper : myBlogMappers) {
            System.out.println(myBlogMapper);
        }
        session.close();
    }
Copy the code

The execution result

When the conditions in choose are not met

Junit Test

@Test
 public void getMyBlog1(a){
     SqlSession session = MybatisUtils.getSession();
     MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
     Map<String,Object> map = new HashMap<>();
// map.put("title","Spring");
        List<Blog> myBlogMappers = mapper.getBlogInfoWhoose(map);
        for (Blog myBlogMapper : myBlogMappers) {
            System.out.println(myBlogMapper);
        }
        session.close();
    }

Copy the code

The execution result
- This will only look up data with readIs greater than 10,000

So to summarize these tags
- Keywords like reads need to be added with the ‘ ‘
- When all the conditions are not met, output the contents in Otherwise.
- The when element outputs the contents of the when condition when it is met, much like the Switch in JAVA, in the order of the condition.

3. Trim and SET tags

So with all that talking about select, let’s talk about the update tagset
I’m not going to repeat the basic update statement here, but just focus on the dynamic statement. In the actual work development generally with this in the majority, after all, it is more flexible!

① Set tag

xml

    <update id="updateBlogName" parameterType="map">
        update myblog
        <set>
            <if test="title! =null and title! = "">
                title = #{title},
            </if>
            <if test="autor! =null and autor! = "">
                autor = #{autor},
            </if>
            <if test="reads! =null and reads! = "">
                `reads` = #{reads}
            </if>
        </set>
        where
            id = #{id}
    </update>
Copy the code

mapper

Integer updateBlogName(Map<String,Object> map);
Copy the code

Junit Test

    @Test
    public  void updateBlogName(a){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("id"."3");
        map.put("title"."Rich woman asks me to go shopping with her.");
        map.put("autor"."Big big Big big");
        map.put("reads"."100000");
        Integer num = mapper.updateBlogName(map);
        System.out.println("All updated:"+num+"Piece of data");
        session.commit();// Don't forget to commit transactions
        session.close();
    }
Copy the code

The execution result
Did you notice that in SQL we kill the comma after the set statement?
- So, what if we just pass the title and autor?
- Student: In theory does the statement look like this?
  - ```
  update myblog set
      title = #{title},
  	autor = #{autor},
  where id = #{id}
  Copy the code
```
- Let’s try it out
- The set tag is the same as the WHERE tag.
- The set tag automatically identifies the comma at the end of the statement and handles it!
- Will we be able to write more flexible SQL statements? Very convenient to use!

② trim label

As we know from the above example, the where and set tags can remove comma, and, or connectives.
Trim tags are also possible!

xml

    <insert id="insertBlogName" parameterType="map">
        insert into myblog
        (<if test="title! =null and title! = "">
            title,
        </if>
        <if test="autor! =null and autor! = "">
            autor,
        </if>
        <if test="reads! =null and reads! = "">
            `reads`,
        </if>
        <if test="creat_time! =null">
            `creat_time`
        </if>)
        values(<if test="title! =null and title! = "">
            #{title},
        </if>
        <if test="autor! =null and autor! = "">
            #{autor},
        </if>
        <if test="reads! =null and reads! = "">
            #{reads},
        </if>
        <if test="creat_time! =null">
            #{creat_time}
        </if>)
    </insert>
Copy the code

mapper

Integer insertBlogName(Map<String,Object> map);
Copy the code

Junit Test

    @Test
    public void insetBlogInfo(a){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("title"."How to list rich women?");
        map.put("autor"."Big fish");
        map.put("reads"."1000");
        map.put("creat_time".new Date());
        Integer num = mapper.insertBlogName(map);
        System.out.println("A total of:"+num+"Piece of data");
        session.commit();
        session.close();
    }
Copy the code

The execution result
Remember, session.com MIT (); Commit the transaction
If we do not pass all SQL, will the above problem also occur? Is it multiple commas that cause errors in SQL execution?

We can use the trim tag to accomplish the automatic removal of hyphens such as commas

XML transformation

    <insert id="insertBlogName1" parameterType="map">
        insert into myblog
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="title! =null and title! = "">
                title,
            </if>
            <if test="autor! =null and autor! = "">
                autor,
            </if>
            <if test="reads! =null and reads! = "">
                `reads`,
            </if>
            <if test="creat_time! =null">
                `creat_time`
            </if>
        </trim>
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="title! =null and title! = "">
                #{title},
            </if>
            <if test="autor! =null and autor! = "">
                #{autor},
            </if>
            <if test="reads! =null and reads! = "">
                #{reads},
            </if>
            <if test="creat_time! =null">
                #{creat_time}
            </if>
        </trim>
    </insert>
Copy the code

The execution result
Perfect problem solving!
The strict
- Prefix: to be added at the beginning
- Suffix: what needs to be added at the end
- SuffixOverrides: what needs to be added at the end of each line
- PrefixOverrides: what needs to be added at the beginning of each line
Pay attention to the point
- When datetime is set in the database, we do not want the if tag not to be null
- Just make sure it’s not null

4. Foreach tag

What’s it like to write a for loop in SQL?
Let’s play with a request: query the blog data with ids 1, 2, 4, and 5.

So that’s it. Don’t write it this way.

select * from myblog where id = 1 or id = 2 or id = 4 or id = 5
Copy the code

1, foreach simple usage

xml

   <select id="getBlogInfos" parameterType="map" resultType="Blog">
        select * from myblog
        <where>
            <foreach collection="ids" item="id" open="and ("
                     close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>
Copy the code

mapper

List<Blog> getBlogInfos(Map<String,Object> map);
Copy the code

Junit Test

    @Test
    public void getBlogInfos(a){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        List<String> ids = new ArrayList<>();
        ids.add("1");
        ids.add("2");
        ids.add("4");
        ids.add("5");
        map.put("ids",ids);
        List<Blog> list = mapper.getBlogInfos(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
        session.close();
    }

Copy the code

The execution result
- The foreach tag
  - Collection: This is the collection we need to iterate over within the tag, namely the key we put in the map
  - Item: The key we assign to the iterated value
  - Open: the parameter to be added at the start
  - Close: indicates the end of the parameter
  - Separator: The parameter added between each value
② where in
- xml
  - ```
     <select id="getBlogInfos1" parameterType="map" resultType="Blog">
          select * from myblog
          <where>
              id in
              <foreach collection="ids" item="id" open="("
                       close=")" separator=",">
                  #{id}
              </foreach>
          </where>
      </select>
  Copy the code
```
- Nothing else needs to change
- So the end result of this is
  - ```
  select * from myblog WHERE id in (? ,? ,? ,?) 
  Copy the code
```
- These are just two examples. How to use them depends on the specific requirements.
- The length of the SQL is limited, and too many values in the IDS will not be queried.
- The default maximum length of SQL concatenation is 2000 parameters.

5. SQL and include tags

So many tags above play down, you will find a problem, is the same business, there may be a lot of repeated content, such as each query has title, Autor and other same content.
So do we have methods that are similar to utility classes in Java?
This is where we need to use our SQL, include tag ~

xml

    <sql id="if-key-info">
        <if test="title! =null and title! = "">
            title = #{title}
        </if>
        <if test="autor! =null and autor! = "">
            and autor = #{autor}
        </if>
        <if test="reads! =null and reads! = "">
            and `reads` = #{reads}
        </if>
    </sql>

    <select id="getBlogInfo" resultType="Blog" parameterType="map">
        select
           *
        from
           myblog
        <where>
            <include refid="if-key-info"></include>
        </where>
    </select>

Copy the code

The execution result
With this, isn’t it much more convenient that we can put the same ones together?
SQL id= “” : this name is arbitrary, as long as the mapper.xml is unique at this time
Include: Include: include!

I see no ending, but I will search high and low

If you think I blogger writes good! Writing is not easy, please like, follow, comment to encourage the blogger ~hahah

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 series (9) – MyBatis dynamic SQL | August more text challenge

Related articles

preface

1. If and WHERE tags

Choose, when, otherwise

3. Trim and SET tags

① Set tag

② trim label

4. Foreach tag

1, foreach simple usage

② where in

5. SQL and include tags

MyBatis series (9) – MyBatis dynamic SQL | August more text challenge

Related articles

preface

1. If and WHERE tags

Choose, when, otherwise

3. Trim and SET tags

① Set tag

② trim label

4. Foreach tag

1, foreach simple usage

② where in

5. SQL and include tags

Related Posts

Vivo Global Mall: Architecture design and practice of order center

Notes on the Python Collections module

Mybatis source code analysis