Mybatis [13] – the use of mybatis dynamic SQL tag

Time:2021-2-26

Mybatis has a powerful feature. Other frameworks should be very careful when splicing SQL. For example, where spaces are needed, we should also pay attention to removing the comma of the last column name in the list. The dynamic SQL of mybtis can help us escape such a painful struggle, that is, theDynamic SQLIt can also deal with a situation, when you are not sure whether your parameter is empty or not, we don’t need to judge in the business logic, we can deal with it directly in SQL, the code is very simple. The main dynamic SQL tags are as follows:

  • <if></if>
  • <where></where>(trim,set)
  • <choose></choose>(when, otherwise)
  • <foreach></foreach>

matters needing attention:
In mapper, if the greater than sign (>), less than sign (), greater than or equal to sign (), less than or equal to sign (), it is better to convert them into entity symbols. This is because mapper is an XML file, and the XML file itself contains many angle brackets such as < > so parsing errors may occur.

Original symbol < <= > >= &
Substitution symbol &lt; &lt;= &gt; &gt;= &amp; &apos; &quot;

<if>

We often need to filter out the required data according to the conditions after where. When multiple conditions are spliced, we usually use < if > < / if >. If the conditions in if are true, then the tag statement will be used. However, we can know that the first tag of where sentence does not have and, while the following conditions all need and, so one way is to use where 1 first =1. This condition is always true, and is added to all subsequent sub statements. If judgment is added, then we only need to add the < if > tag.

<! -- dynamic SQL if tag -- >
    <! -- & and can be used instead, attention! =Need to be linked together to write -- >
    <select id="selectStudentByDynamicSQL" resultType="Student">
        <! -- select id, name, age, score from student where name like '%' # {name} '%' -- >
        <! -- the following is string splicing. You can only write value and understand it. It is easy to inject SQL and has low execution efficiency. It is not recommended to use -- >
        select id,name,age,score
        from student
        where 1=1
        <if test="name != null and name != ''">
            and name like '%' #{name} '%'
        </if>
        <if test="age > 0">
            and age > #{age}
        </if>
    </select>

When there are two query conditions, the SQL statement is: select * from student where 1 = 1 and name like ‘%’? ‘%’ and age >?

When there is a query condition: the SQL statement becomes: select * from student where 1 = 1 and name like ‘%’? ‘%’

When there is no query condition, the SQL statement is:
select * from student where 1=1

The < if > < / if > tag needs to manually add a 1 = 1 statement after where. This is because if the conditions after < if > are all false, if there is no 1 = 1 statement after where, there will be an empty where left in SQL, and SQL will report an error. So we need to add the sentence 1 = 1 after where, but there is a problem. When the amount of data is large, it will seriously affect the query efficiency of SQL.

<where></where>,<trim></trim>,<set></set>

Using the < where > < / where > tag, when there is a query statement, the where clause will be added automatically. When there is no query condition, the where clause will not be added. This solves the problem we mentioned above. The rest is the and clause of the < if > tag. First, the < if > fragment can not contain and, or it can contain. The system will automatically remove and, but the others< If > the and in the fragment must be written, otherwise there will be an error. In the following writing, if the name is null, the if in the second if tag will also be removed, and no error will be reported.

<select id="selectStudentByDynamicSQLWhere" resultType="Student">
        <! -- select id, name, age, score from student where name like '%' # {name} '%' -- >
        <! -- the following is string splicing. You can only write value and understand it. It is easy to inject SQL and has low execution efficiency. It is not recommended to use -- >
        select id,name,age,score
        from student
        <where>
            <if test="name != null and name != ''">
                and name like '%' #{name} '%'
            </if>
            <if test="age > 0">
                and age > #{age}
            </if>
        </where>
    </select>

If where is not standardized, we can define the function of where element through < trim > < / trim >. The < trim > tag mainly has the following attributes:

  • Prefix: add a prefix before the content. It will not be added 100%. It will be added automatically as needed
  • Suffix: add a suffix after the content. It will not be added 100% but will be added automatically as needed
  • Prefixoverrides: you can ignore the first part of the content (you can’t add it yourself). It doesn’t have to be ignored. It will be automatically ignored as needed
  • Suffixoverlaps: you can also ignore some content at the end of the content (you can’t add it yourself), as above

Here’s what’s going onFALSE, when the passed in name is not empty and age is greater than 0

    <select id="selectStudentByDynamicSQLWhere" resultType="Student">
        select id,name,age,score
        from student
        <trim prefix="where" prefixOverrides="and">
            <if test="name != null and name != ''">
                name like '%' #{name} '%'
            </if>
            <if test="age > 0">
                age > #{age}
            </if>
        </trim>
    </select>

Don’t add and before the second age:

Mybatis [13] - the use of mybatis dynamic SQL tag

The following is correct. We add and in front of both < if > tags, and the second and will be removed automatically:

    <select id="selectStudentByDynamicSQLWhere" resultType="Student">
        select id,name,age,score
        from student
        <trim prefix="where" prefixOverrides="and">
            <if test="name != null and name != ''">
                and name like '%' #{name} '%'
            </if>
            <if test="age > 0">
                and age > #{age}
            </if>
        </trim>
    </select>

Here is the suffix pattern, prefix="set"It means to prefix the whole statement with set, suffixoverride=","Indicates that the suffix “,” at the end of each statement can be ignored,If necessarysuffix=" where id = #{id}It means to add where id = # {ID} after the whole statement:

update user
<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">
  <if test="name != null and name.length()>0"> name=#{name} , </if>
  <if test="age != null "> age=#{age} ,  </if>
</trim>

Of course, we have a dynamic solution to the above statement, which is < set > tag:

<update id="updateStudent">
        update student
        <set>
            <! -- the first if tag must have a comma, and the last tag may not have -- >
            <if test="name != null"> name=#{name},</if>
            <if test="age != null">age=#{age},</if>
            <if test="score != null"> score=#{score},</if>
        </set>
         where id=#{id}
    </update>

<choose>, <when>, <otherwise>

Sometimes, we only want to match the first condition, or when the first condition does not match, we will match the second condition. Unlike the < if > < / if > in the < where > < / where > tag, we will judge whether all the sub statements can match, but when we meet a match, we will execute and jump out of < choose > < / choose >

<
    <! -- if the name is not empty, query by name. If the name is empty, query by age. If there is no query condition, there is no query condition -- >
    <select id="selectStudentByDynamicSQLChoose" resultType="Student">
        <! -- select id, name, age, score from student where name like '%' # {name} '%' -- >
        select id,name,age,score
        from student
        <where>
            <choose>
                <when test="name != null and name != ''">
                    and name like '%' #{name} '%'
                </when>
                <when test="age > 0">
                    and age > #{age}
                </when>
                <otherwise>
                    and 1 != 1
                </otherwise>
            </choose>
        </where>
    </select>

< choose > tags are like switch statements, and every < when > is like a case,Break statement is followed by defaultWhen all the previous < when > < / when > are not executed, the content of < otherwise > < / otherwise > tag will be executed, which is just like the default in the switch statement.

foreach

One of the most common operations in dynamic SQL is to traverse a collection, usually when constructing in conditional statements. Points to be noted:

  • Collection represents the collection type to traverse, and array represents the array to traverse
  • Open, close and separator are SQL splicing of traversal contents
  • The foreach element is very powerful. It allows you to specify a set and declare the set item and index variables that can be used in the element body. It also allows you to specify the beginning and end of the string and place a separator between the iteration results.
  • You can pass any iteratable object (such as list, set, etc.), map object, or array object to foreach as a set parameter. When using an iteratable object or array, index is the number of current iterations, and the value of item is the element obtained in this iteration. When using map objects (or Map.Entry Object), index is the key and item is the value.

1. For example, we need to find the information of students whose ID is 1, 2 and 3. We don’t want to check one at a time separately, but we want to pass in the array ID at a time and find out a collection of students.

The SQL interface can be written like this, passing in an array of objects:

public List<Student>selectStudentByDynamicSQLForeachArray(Object[]studentIds);

The SQL statement is as follows: when traversing the array, the left symbol is the left bracket, the right is the right bracket, and the elements are separated by commas

    <!-- select * from student where id in (1,3) -->
    <select id="selectStudentByDynamicSQLForeachArray" resultType="Student">
        select id,name,age,score
        from student
        <if test="array !=null and array.length > 0 ">
            where id in
            <foreach collection="array" open="(" close=")" item="myid" separator=",">
                #{myid}
            </foreach>
        </if>
    </select>

2. When traversing a list of type int:

public List<Student>selectStudentByDynamicSQLForeachList(List<Integer>studentIds);

The SQL statement is as follows, coleaction is specified as list:

    <select id="selectStudentByDynamicSQLForeachList" resultType="Student">
        select id,name,age,score
        from student
        <if test="list !=null and list.size > 0 ">
            where id in
            <foreach collection="list" open="(" close=")" item="myid" separator=",">
                #{myid}
            </foreach>
        </if>
    </select>

3. When traversing a list of object type:

public List<Student>selectStudentByDynamicSQLForeachListStudent(List<Student>students);

The SQL statement is similar to the above, but it is different when using attributes

<select id="selectStudentByDynamicSQLForeachListStudent" resultType="Student">
        select id,name,age,score
        from student
        <if test="list !=null and list.size > 0 ">
            where id in
            <foreach collection="list" open="(" close=")" item="stu" separator=",">
                #{stu.id}
            </foreach>
        </if>
    </select>

<sql></sql>

It is used to define SQL fragments, which is convenient to reuse in other SQL tags. When reusing in other places, you need to use < include > < / include > sub tags, < SQL > can define any part of SQL, so < include > tags can be placed anywhere in dynamic SQL.

<sql id="selectHead">
        select id,name,age,score
         from student
    </sql>
    <! -- poor readability -- >
    <select id="selectStudentByDynamicSQLfragment" resultType="Student">
        <include refid="selectHead"></include>
        <if test="list !=null and list.size > 0 ">
            where id in
            <foreach collection="list" open="(" close=")" item="stu" separator=",">
                #{stu.id}
            </foreach>
        </if>
    </select>

Dynamic SQL makes SQL more concise and reduces a lot of repetitive code. Dynamic SQL can be spliced with each other as long as it conforms to the SQL statement specification.

[about the author]
Qin Huai, official account.Qinhuai grocery store】The author points out that the road of technology is not for a moment, even though it is slow and continuous. The world hopes that everything will be fast and faster, but I hope I can take every step and write every article well. I look forward to communicating with you.

Recommended Today

Third party calls wechat payment interface

Step one: preparation 1. Wechat payment interface can only be called if the developer qualification has been authenticated on wechat open platform, so the first thing is to authenticate. It’s very simple, but wechat will charge 300 yuan for audit 2. Set payment directory Login wechat payment merchant platform( pay.weixin.qq . com) — > Product […]