MyBatis query database four (dynamic SQL — if, trim, where, set, foreach tags)

Directory

Directory

Dynamic SQL

1. The if tag

Two, trim label

Three, where label

Four, set label

Five, foreach tag

Dynamic SQL

Dynamic SQL is one of the powerful features of MyBatis, Using dynamic SQL is not an easy task, but With the powerful dynamic SQL language that can be used in any SQL mapping statement, MyBatis Significantly improved the ease of use of this feature.

1. if tag

When registering a user, some information is required, and some information is not required, such as our CSDN information, the user nickname is required… The rest such as personal profile, date of birth, and starting work are not required How should the program be implemented?

At this time, you need to use the dynamic tag to judge. For example, gender photo is a non-required field when adding it. The specific implementation is as follows:

syntax:

 <insert id="add2">
        insert into userinfo (username,
        <if test="photo!=null">
            photo,
        </if>
        password)
        values(#{username},
        <if test="photo!=null">
            #{photo},
        </if>
        #{password})
    </insert>

Is empty the same as null?

Null and empty are not the same in MySQL

2. trim tag

If in a certain situation, all attributes are optional, then you don’t know which attribute will definitely be filled,

It is necessary to consider the combination of tags and tags, and adopt a dynamic format for multiple fields.

The function of the label: to remove a certain extra character before and after the SQL statement, and its attributes are

  • prefix: Indicates the entire statement block, prefixed with the value of prefix
  • suffix: Indicates the entire statement block, with the value of suffix as the suffix
  • prefixOverrides: Indicates the prefix to be removed from the entire statement block
  • suffixOverrides: Indicates the suffix to be removed from the entire statement block

grammar:

Adjust the insert statement of UserMapper.xml to:

<insert id="add3">
        insert into userinfo
<!-- prefix: add a prefix suffix: add a suffix suffixOverrides: remove the last ',', if there is any, go, if there is no, don't go -->
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                 username,
            </if>
            <if test="password!=null">
                password,
            </if>
            <if test="photo!=null">
                photo,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                #{username},
            </if>
            <if test="password!=null">
                #{password},
            </if>
            <if test="photo!=null">
                #{photo},
            </if>
        </trim>
    </insert>

3. where tag

where tag function: realize where replacement in query, it can realize if there is no query condition, then it can hide the where sql in the query, but if there is a query condition, then it will generate where sql Query, and use the where tag to automatically remove the first and character

<select id="getListByParam" resultType="com.example.demo.eneity.Userinfo">
        select * from userinfo
        <where>
            <if test="username != null">
                username=#{username}
            </if>
            <if test="password != null">
                and password=#{password}
            </if>
        </where>
    </select>

When we only pass one parameter

Then we Let’s see if there will be any problems when only the password value is given?

This is the characteristic of the where tag,

  1. The where tag will remove the first and keyword but not the last and keyword
  2. The where tag is usually used together with the if tag
  3. If there is no content in the where tag, the where sql keyword will not be generated

The where tag can also be replaced by ? trim prefix=”where” prefixOverrides=”and”

4. set tag

To update user data based on the incoming user object attributes, you can use the tag to specify dynamic content.

Modify the user method in the UserMapper interface: modify other attributes that are not null according to the incoming user id attribute:

Used to judge the update operation

<update id="update2">
        update userinfo
        <set>
            <if test="username!=null">
                username=#{username},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
            <if test="photo!=null">
                photo=#{photo},
            </if>
        </set>
        where id=#{id}
    </update>

Note: There is a ‘,’ after #{photo}, and what we changed this time is photo, and we can run normally

Features:

  1. The set tag is usually used together with the if tag
  2. The set tag will automatically remove the last English symbol

The above tags can also be replaced with .

5. foreach tag

This tag can be used when traversing a collection. The tag has the following attributes:

  • collection: the collection in the bound method parameter, such as List, Set, Map or array object
  • item: each object when traversing
  • open: the string at the beginning of the statement block
  • close: the string at the end of the statement block
  • separator: the string of intervals between each traversal

grammar:

New interface method in UserMapper:

 int dels(List<Integer> ids);

Added delete SQL in UserMapper.xml:

<delete id="dels">
        delete from userinfo
        where id in
        <foreach collection="ids" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </delete>

Unit test code, delete users with id 1, 2 and 3:

 @Rollback(value = false)
    @Test
    void dels() {
        List<Integer> ids = new ArrayList<>();
        ids. add(1);
        ids. add(2);
        ids. add(3);
        int result = userMapper.dels(ids);
        System.out.println("Deleted:" + result);
    }