Home  >  Article  >  Database  >  Learn MyBatis dynamic SQL

Learn MyBatis dynamic SQL

2020-12-09 17:46:132600browse

sql tutorialIntroducing the powerful features of SQL MyBatis SQL

Learn MyBatis dynamic SQL

Recommended (free): sql tutorial

Dynamic SQL

One of the powerful features of MyBatis is its dynamic SQL. If you have experience using JDBC or other similar frameworks, you will understand the pain of splicing SQL statements based on different conditions. For example, when splicing, make sure not to forget to add necessary spaces, and be careful to remove the comma from the last column name in the list. Take advantage of the dynamic SQL feature to get rid of this pain completely.

Although it was not easy to use dynamic SQL in the past, MyBatis improved this situation by providing a powerful dynamic SQL language that can be used in any SQL mapping statement.

Dynamic SQL elements are similar to JSTL or XML-based text processors. In previous versions of MyBatis, there were many elements that took time to understand. MyBatis 3 has greatly simplified the types of elements. Now you only need to learn half of the original elements. MyBatis uses powerful OGNL-based expressions to eliminate most other elements.


First create the User entity class

public class User {
    private Integer id;
    private String username;
    private String userEmail;
    private String userCity;
    private Integer age;}

Create the user table

  username varchar(255) DEFAULT NULL,
    user_email varchar(255) DEFAULT NULL,
    user_city varchar(255) DEFAULT NULL,
    age int(11) DEFAULT NULL,


Define interface method

public List findByUser(User user);

The definition of Mapper.xml corresponding to the interface is as follows

If the test on the if tag is true, then the SQL statement in the if tag will be Splicing.

If username, userEmail, and userCity are not empty, then the SQL will be spliced ​​as shown below

select id, username, user_email userEmail, user_city userCity, age 
from user where username = ? and user_email = ? and user_city = ?

If only username is not empty, then the SQL will be spliced ​​as shown below

select id, username, user_email userEmail, user_city userCity, age 
from user where username = ?

However, there is a disadvantage in this method. Assume that username is empty at this time, userEmail and userCity are not empty.

Let's analyze the dynamic SQL code. Now there is no value assigned to username, that is, username==null, so the "username=#{username}" code will not be added to the SQL statement, so the final spliced Dynamic SQL is like this:

select id, username, user_email userEmail, user_city userCity, age 
from user where and user_email = ? and user_city = ?

where is directly followed by and, which is an obvious syntax error. At this time, the and that follows where should be deleted. . To solve this problem, you can use the where tag.


Change the above SQL to the following


ifwhere##if inside the tag If the tag meets the conditions, then the where tag will be spliced ​​into a where statement. If the SQL spliced ​​with the if tag has an and statement at the front, then the and will be spliced ​​into a where statement. delete. Using this method, unnecessary keywords in SQL will be automatically deleted, so generally if tags and where tags are used in combination. The prefix


suffix attributes in the

trimtrim tag will be used to generate The actual SQL statement will be spliced ​​with the statement inside the label.

If the values ​​specified in the

prefixOverrides or suffixOverrides attributes are encountered before or after the statement, MyBatis will automatically delete them. When specifying multiple values, don't forget to have a space after each value to ensure that it will not be connected with subsequent SQL.

prefix: Add a prefix to the spliced ​​SQL statement

suffix: Add a suffix to the spliced ​​SQL statement

prefixOverrides: If prefixOverrides is encountered before the spliced ​​SQL statement, MyBatis will automatically delete them

suffixOverrides: If it is encountered after the spliced ​​SQL statement suffixOverrides, MyBatis will automatically delete them

Use the

trim tag below to implement the function of the where tag

if username is empty, userEmail and userCity are not empty, then the SQL statement of

if label splicing is as follows

and user_email = #{userEmail} and user_city = #{userCity}
Because the

trim label is set with prefixOverrides="and" , and the above SQL statement has an and statement in front of it, so the above and statement needs to be deleted, and because the trim tag is set with prefix="where", it is necessary to add a where statement in front of the spliced ​​SQL statement.


trimThe SQL statement of the tag is spliced ​​as follows

where user_email = #{userEmail} and user_city = #{userCity}


Sometimes we don’t want to apply to all conditional statement, but only want to select one of them. For this situation, MyBatis provides the choose element, which is a bit like the switch statement in Java.


set tag is used for Update operation and will automatically generate SQL statements based on parameter selection.

The interface is defined as follows

public int updateUser(User user);
The Mapper.xml definition corresponding to the interface is as follows

       update user       
       where id=#{id}    

##foreachforeach tag can be iterated Generate a series of values


for use in SQL in statements*


public List getUsersByIds(List ids);

接口对应的 Mapper.xml 定义如下所示



public int addUserList(List users);

接口对应的 Mapper.xml 定义如下所示

    insert into user
    (username, user_email, user_city, age)
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})    
    insert into user
    (username, user_email, user_city, age)
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})    
        insert into user
        (username, user_email, user_city, age)
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})    
        insert into user
        (username, user_email, user_city, age)
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age});    

The above is the detailed content of Learn MyBatis dynamic SQL. For more information, please follow other related articles on the PHP Chinese website!

This article is reproduced at:learnku.com. If there is any infringement, please contact admin@php.cn delete