Learn MyBatis dynamic SQL
- coldplay.xixiforward
- 2020-12-09 17:46:132711browse
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<User> findByUser(User user);
The definition of Mapper.xml corresponding to the interface is as follows
<select id="findByUser" resultType="com.example.mybatis.entity.User">
select
id, username, user_email userEmail, user_city userCity, age
from user
where <if test="username != null and username != ''">
username = #{username} </if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail} </if>
<if test="userCity != null and userCity != ''">
and user_city = #{userCity} </if></select>
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.
#where
Change the above SQL to the following
<select id="findByUser" resultType="com.example.mybatis.entity.User">
select
id, username, user_email userEmail, user_city userCity, age from user
<where>
<if test="username != null and username != ''">
username = #{username}
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>
<if test="userCity != null and userCity != ''">
and user_city = #{userCity}
</if>
</where>
</select>
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.
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
trim tag below to implement the function of the where tag
<select id="findByUser" resultType="com.example.mybatis.entity.User">
select
id, username, user_email userEmail, user_city userCity, age
from user <trim prefix="where" prefixOverrides="and">
<if test="username != null and username != ''">
username = #{username} </if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail} </if>
<if test="userCity != null and userCity != ''">
and user_city = #{userCity} </if>
</trim>
</select>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}
choose
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.<select id="findByUser" resultType="com.example.mybatis.entity.User">
select
id, username, user_email userEmail, user_city userCity, age
from user <where>
<choose>
<when test="username != null and username != ''">
username = #{username} </when>
<when test="userEmail != null and userEmail != ''">
and user_email = #{userEmail} </when>
<when test="userCity != null and userCity != ''">
and user_city = #{userCity} </when>
</choose>
</where>
</select>
set
set tag is used for Update operation and will automatically generate SQL statements based on parameter selection. The interface is defined as followspublic int updateUser(User user);The Mapper.xml definition corresponding to the interface is as follows
<update id="updateUser" parameterType="com.example.mybatis.entity.User">
update user <set>
<if test="username != null and username != ''">
username=#{username}, </if>
<if test="userEmail != null and userEmail != ''">
user_email=#{userEmail}, </if>
<if test="userCity != null and userCity != ''">
user_city=#{userCity}, </if>
<if test="age != null">
age=#{age} </if>
</set>
where id=#{id} </update>
##foreachforeach tag can be iterated Generate a series of values
*
for use in SQL in statements*接口定义如下所示 接口对应的 Mapper.xml 定义如下所示 用于批量插入 接口定义如下所示 接口对应的 Mapper.xml 定义如下所示public List<User> getUsersByIds(List<Integer> ids);
<!--
collection: 指定要遍历的集合
默认情况下
如果为Collection类型的，key为collection；
如果为List类型的，key为list
如果是数组类型，key为array
可以通过@Param("ids")来指定key
item: 将当前遍历的元素赋值给指定的变量
open: 给遍历的结果添加一个开始字符
close: 给遍历的结果添加一个结束字符
separator: 每个元素之间的分隔符
--><select id="getUsersByIds"
resultType="com.example.mybatis.entity.User">
select * from user
where id in <foreach collection="list" item="id" open="(" close=")" separator=",">
#{id} </foreach></select>
public int addUserList(List<User> users);
<insert id="addUserList"
parameterType="com.example.mybatis.entity.User">
insert into user
(username, user_email, user_city, age)
values <foreach item="user" collection="list" separator=",">
(#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age}) </foreach></insert><!--返回自增主键--><insert id="addUserList"
parameterType="com.example.mybatis.entity.User"
useGeneratedKeys="true"
keyProperty="id">
insert into user
(username, user_email, user_city, age)
values <foreach item="user" collection="list" separator=",">
(#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age}) </foreach></insert><!--还可以这样写--><!--
这种方式需要数据库连接属性设置allowMultiQueries=true
这种分号分隔多个SQL还可以用于其他的批量操作，如修改、删除
--><insert id="addUserList"
parameterType="com.example.mybatis.entity.User">
<foreach item="user" collection="list" separator=";">
insert into user
(username, user_email, user_city, age)
values
(#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age}) </foreach></insert><!--如果是Oracle数据库，则需要这样写--><insert id="addUserList"
parameterType="com.example.mybatis.entity.User">
<foreach item="user" open="begin" close="end;" collection="list">
insert into user
(username, user_email, user_city, age)
values
(#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age}); </foreach></insert>
The above is the detailed content of Learn MyBatis dynamic SQL. For more information, please follow other related articles on the PHP Chinese website!