One of the powerful features of MyBatis is its dynamic SQL, the previous splicetime need to pay attention to the space, the last comma of the list, now can not be manually processed, MyBatis uses powerful expression based on OGNL to achieve, the following is mainly introduced.

If tag

If is the most commonly used statement for simple conditional selection. The basic usage example is as follows:

    <select id="queryAllUsersByName" resultType="com.example.springboot.mybatisxml.entity.User">
        select * from user where1 = 1"if test="name ! = null and name ! = "">
            and name = #{name}
        </if>
        <if test="age ! = null ">
            and age = #{age}
        </if>
    </select>
Copy the code

2. Where tag

In the example above, “1=1” is used to avoid errors when the following conditions are not met. Is there any way to avoid this writing method? Insert a ‘where’ tag into the SQL if the tag contains a return value. If the tag returns a value starting with and or OR, the value will be removed.

    <select id="queryAllUsersByName" resultType="com.example.springboot.mybatisxml.entity.User">
        select * from user 
        <where>
            <if test="name ! = null and name ! = "">
                and name = #{name}
            </if>
            <if test="age ! = null ">
                and age = #{age}
            </if>
        </where>
    </select>
Copy the code

3. Trim Label

The prefix attribute indicates the statement prefix, the prefixOverrides attribute indicates which special strings need to be removed, and the prefixOverrides attribute ignores text sequences delimited by pipes (note that Spaces are also necessary in this example). Suffixes are treated the same as prefixes.

The trim tag has the following attributes

  • Prefix: The prefix overrides and adds to its content.
  • Suffix: Suffixes overlay and add to their content.
  • PrefixOverrides: indicates the condition to determine the prefix.
  • SuffixOverrides: suffixOverrides

Here are two examples.

  1. Use prefix properties
    <select id="queryAllUsersByName" resultType="com.example.springboot.mybatisxml.entity.User">
        select * from user
        <trim prefix="WHERE" prefixOverrides="AND |OR " >
            <if test="name ! = null and name ! = "">
                and name = #{name}
            </if>
            <if test="sex ! = null ">
                or sex = #{sex}
            </if>
            <if test="age ! = null ">
                and age = #{age}
            </if>
        </trim>
    </select>
Copy the code
  1. Use suffix attributes
<update id="update" parameterType="Object">
        UPDATE user
        <trim suffix=" SET " suffixOverrides=",">
            <if test="id ! = null ">id=#{id},</if>
            <if test="name ! = null ">name=#{name},</if>
            <if test="age ! = null ">age=#{age},</if>
        </trim>
        WHERE ID=#{id}
    </update>
Copy the code

Four, labels,

Function is traversal collection, it can well support array and List, Set interface Set traversal, often and SQL in combination more.

The main attributes of the foreach tag are as follows

  • Item: Represents the current element in the loop.
  • Index: Indicates the index of the current element in the set.
  • Collection: Configures list attribute names, etc.
  • Open and close: What symbols are configured to wrap these collection elements.
  • Separator: Configures the separator for each element.

Here’s an example:

<select id="queryAllUsersByName" resultType="com.example.springboot.mybatisxml.entity.User">
        select * from user where id in
        <foreach item="id" index="index" collection="userList"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
Copy the code

To this MyBatis dynamic SQL common functions have been introduced, there are questions welcome message communication oh!

Recommended reading

1. Spring Security in a minute! 2. Mybatis – Generator in one minute 3. SpringBoot series – Mybatis (XML configuration) 4.SpringBoot series – Mybatis (XML configuration)

Java, a insist on the original public account, to provide you with a series of system architecture, micro services, Java, SpringBoot, SpringCloud and other high quality technical articles. If you think the article is good, I hope you can forward it or “I am reading” oh, thank you very much! Follow the public account below and reply to “1024”, there are surprises!

This article is published by OpenWrite!