Learn MyBatis dynamic SQL

sql tutorialIntroducing the powerful features of SQL MyBatis 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.

Preparation

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

CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(255) DEFAULT NULL, user_email varchar(255) DEFAULT NULL, user_city varchar(255) DEFAULT NULL, age int(11) DEFAULT NULL, PRIMARY KEY (id))

if

Define interface method

public List findByUser(User user);

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

 select id, username, user_email userEmail, user_city userCity, age from user where  username = #{username}   and user_email = #{userEmail}   and user_city = #{userCity} 

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, theandthat followswhereshould be deleted. . To solve this problem, you can use thewheretag.

#where

Change the above SQL to the following

 select id, username, user_email userEmail, user_city userCity, age from user   username = #{username}   and user_email = #{userEmail}   and user_city = #{userCity}   

ifwhere##if inside the tag If thetag meets the conditions, then thewheretag will be spliced into a where statement. If the SQL spliced with theiftag 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. Theprefix

suffixattributes in the

trimtrimtag will be used to generate The actual SQL statement will be spliced with the statement inside the label.

prefixOverridesorsuffixOverridesattributes 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: IfprefixOverridesis encountered before the spliced SQL statement, MyBatis will automatically delete them

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

trimtag below to implement the function of thewheretag

 select id, username, user_email userEmail, user_city userCity, age from user   username = #{username}   and user_email = #{userEmail}   and user_city = #{userCity}   

iflabel splicing is as follows

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

trimlabel 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 thetrimtag 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}

choose

 select id, username, user_email userEmail, user_city userCity, age from user    username = #{username}   and user_email = #{userEmail}   and user_city = #{userCity}    

set

public int updateUser(User user);

 update user   username=#{username},   user_email=#{userEmail},   user_city=#{userCity},   age=#{age}   where id=#{id} 

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

*

接口定义如下所示

public List getUsersByIds(List ids);

 select * from user where id in  #{id} 

public int addUserList(List users);

 insert into user (username, user_email, user_city, age) values  (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})  insert into user (username, user_email, user_city, age) values  (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})   insert into user (username, user_email, user_city, age) values (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})   insert into user (username, user_email, user_city, age) values (#{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!