Shulou(Shulou.com)06/01 Report--

This article will explain in detail the examples of MyBatis dynamic SQL for you. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Dynamic SQL

One of the powerful features of MyBatis is its dynamic SQL. If you have experience with JDBC or other similar frameworks, you can feel the pain of concatenating SQL statements according to different conditions. For example, when stitching, make sure you don't forget to add the necessary spaces, and be careful to remove the comma from the last column name of the list. You can get rid of this pain completely by using the feature of dynamic SQL.

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

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

Prepare for

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 a 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 Mapper.xml definition for 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, the SQL statements in the if tag will be concatenated.

If username, userEmail, and userCity are not empty, the SQL will be spliced as follows

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, the SQL will be spliced as follows

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

However, this approach has a drawback, assuming that username is empty and userEmail and userCity are not empty at this time.

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

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

Where is followed directly by and, which is an obvious syntax error. And immediately after where should be deleted at this time. To solve this problem, you can use the where tag.

Where

Change the above SQL to look like this

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

If the if tag in the where tag meets the conditions, then the where tag will be spliced into a where statement, and if the SQL concatenated by the if tag is preceded by an and statement, then the and will be deleted. Using this method, unwanted keywords in SQL are automatically deleted, so general if tags and where tags are used together.

Trim

The prefix and suffix attributes in the trim tag are used to generate the actual SQL statement, which is concatenated with the statement inside the tag.

If the value specified in the prefixOverrides or suffixOverrides property is encountered before or after the statement, MyBatis automatically deletes them. When specifying multiple values, don't forget that each value should be followed by a space to ensure that it will not be connected to the subsequent SQL.

Prefix: add a prefix to the spliced SQL statement

Suffix: add a suffix to the spliced SQL statement

PrefixOverrides: spliced SQL statements will be deleted automatically when they encounter prefixOverrides,MyBatis before they are stitched

SuffixOverrides: spliced SQL statements will be automatically deleted when they are followed by suffixOverrides,MyBatis.

The following uses trim tags to implement the function of where tags

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

If username is empty and userEmail and userCity are not empty, the SQL statement for if tag splicing is as follows

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

Because the trim tag sets prefixOverrides= "and", and the above SQL is preceded by an and statement, you need to delete the above and statement, and because the trim tag sets prefix= "where", you need to add a where statement before the spliced SQL statement.

Finally, the SQL statement of the trim tag is spliced into the following

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

Sometimes we don't want to apply to all conditional statements, but just want to choose one of them. In this case, MyBatis provides the choose element, which is a bit like the switch statement in Java.

Select id, username, user_email userEmail, user_city userCity Age from user username = # {username} and user_email = # {userEmail} and user_city = # {userCity} set

The set tag is used for Update operations and automatically generates SQL statements based on parameter selection.

The interface is defined as follows

Public int updateUser (User user)

The Mapper.xml definition for the interface is as follows

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

The foreach tag can iterate to generate a series of values

* in statement for SQL *

The interface definition is as follows

Public List getUsersByIds (List ids)

The Mapper.xml definition for the interface is as follows

Select * from user where id in # {id}

For bulk insertion

The interface definition is as follows

Public int addUserList (List users)

The Mapper.xml definition for the interface is as follows

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}) This is the end of the example of MyBatis dynamic SQL. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.