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<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 != &#39;&#39;">
        username = #{username}    </if>
    <if test="userEmail != null and userEmail != &#39;&#39;">
        and user_email = #{userEmail}    </if>
    <if test="userCity != null and userCity != &#39;&#39;">
        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 != &#39;&#39;">
                username = #{username}
            </if>
            <if test="userEmail != null and userEmail != &#39;&#39;">
                and user_email = #{userEmail}
            </if>
            <if test="userCity != null and userCity != &#39;&#39;">
                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

and

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

<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 != &#39;&#39;">
                username = #{username}            </if>
            <if test="userEmail != null and userEmail != &#39;&#39;">
                and user_email = #{userEmail}            </if>
            <if test="userCity != null and userCity != &#39;&#39;">
                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.

Finally

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 != &#39;&#39;">
                    username = #{username}                </when>
                <when test="userEmail != null and userEmail != &#39;&#39;">
                    and user_email = #{userEmail}                </when>
                <when test="userCity != null and userCity != &#39;&#39;">
                    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 follows

public 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 != &#39;&#39;">
               username=#{username},           </if>
           <if test="userEmail != null and userEmail != &#39;&#39;">
               user_email=#{userEmail},           </if>
           <if test="userCity != null and userCity != &#39;&#39;">
               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*

接口定义如下所示

public List<User> getUsersByIds(List<Integer> ids);

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

<!--
        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);

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

<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!