Mybatis [13] - Use of Mybatis dynamic Sql tags

Mybatis has a powerful feature that other frameworks need to be careful about when concatenating SQL, such as where Spaces are needed and removing commas from the last column name of the list. Mybtis’ dynamic SQL can help us escape the pain of this struggle. It can also handle the situation where you are not sure whether your parameter is null or not, we don’t need to judge in the business logic, directly in SQL, the code is extremely simple. The main dynamic SQL tags are as follows:

(trim,set)
(the when, otherwise)

Note: In mapper, if greater than sign (>), less than sign (), greater than or equal to sign (), less than or equal to sign (), it is best to convert into entity symbols, this is because mapper is an XML file, XML file itself contains many Angle brackets such as <>, so parsing errors may occur.

The original symbol	<	< =	>	> =	&	‘	“
Replace the symbol	`<`	`< =`	`>`	`> =`	`&`	`'`	`"`

We often need to screen out the data we need according to the conditions behind WHERE. When multiple conditions are joined together, we generally use the tag statement. If the conditions inside if are true, then the tag statement will be used. So one way to do this is to first use where 1 = 1, and this condition is always true, and then all the subsequentsubstatements add and, and if we add judgment, then all we need to do is tag it.

<! - dynamic SQLifTag -- -- > <! -- & Can be used instead of and, notice! --> <select id="selectStudentByDynamicSQL" resultType="Student"> <! Select id,name,age,score from student where name likeThe '%' #{name} The '%'-- > <! - below is the string concatenation, can only write the value, to understand, easy to SQL injection, execution efficiency is low, is not recommended - > select id, name, age, score from student where1=1
        <if test="name ! = null and name ! = "">
            and name like The '%' #{name} The '%'
        </if>
        <if test="age > 0">
            and age > #{age}
        </if>
    </select>
Copy the code

Select * from student where 1=1 and name like ‘%’? ‘%’ and age > ? Select * from student where 1=1 and name like ‘%’? SQL statement with no query condition: Select * from student where 1=1; select * from student where 1=1; select * from student where 1=1; Therefore, we need to add the eternal truth sentence 1=1 after where, but this has a problem, when the amount of data is large, it will seriously affect the efficiency of SQL query.

.

, use the tag in the query, automatically fill the where clause, in the absence of query conditions, will not add a where clause, which would solve the problem which we are involved, the rest is the label and clause, first, fragments may not contain the and, also can contain, system will automatically remove the and, But in other fragments, you have to write and, otherwise it will be wrong. If name is null, the if tag in the second if tag is removed without error.

    <select id="selectStudentByDynamicSQLWhere" resultType="Student"> <! Select id,name,age,score from student where name likeThe '%' #{name} The '%'-- > <! Select id,name,age,score from student <where> < select name,age,score from student <where> <if test="name ! = null and name ! = "">
                and name like The '%' #{name} The '%'
            </if>
            <if test="age > 0">
                and age > #{age}
            </if>
        </where>
    </select>
Copy the code

If the where element is not canonical, then we can customize the where element with the following attributes:

Prefix: Indicates that the content is preceded by a prefix. The prefix is not 100%. It is automatically added as required
Suffix: Suffix is added at the end of the content. It is not 100%, but will be added automatically as needed
PrefixOverrides: it is possible to omit some content in the header containing the content (it cannot be added by itself)
SuffixOverrides: it is also possible to omit some content at the end of the containing content

The following is an error if the name passed is not null 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 The '%' #{name} The '%'
            </if>
            <if test="age > 0">
                age > #{age}
            </if>
        </trim>
    </select>
Copy the code

Does not increment and by itself before the second age:

The following is correct, we add and to both labels, 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>Copy the code

Below is a suffix mode, the prefix = “set” said in front of the entire statement prefixed with the set, suffixoverride = “, “said suffix behind each statement”, “can be ignored, if it is needed. Suffix =” where id = #{id}, add where id = #{id}, :

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>Copy the code

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

<update id="updateStudent"> update student <set> <! --> < test="name "--> < 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>Copy the code

.

Sometimes, we only want to match the first condition, or we only match the second condition if the first condition does not match. Instead of checking whether all the sub-statements can match, we execute the escape when we find a match

<! - selectStudentByDynamicSQLChoose is similar to the switch, after meet won't be a judge at the back of the - > <! If the name is not empty, select by name; if the name is empty, select by age; if there is no query condition, select by age. There is no query conditions -- -- > < select id = "selectStudentByDynamicSQLChoose resultType" = "Student" > <! Select id,name,age,score from student where name like '%' #{name} '%' --> select id,name,age,score from student where name like '%' #{name} '%' -- 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>Copy the code

Tags are like switch statements, each of which is like a case, followed by a break statement by default. If only one of these statements is met, the following sub-statements will not be judged. If none of the previous statements are executed, the content of the label will be executed, which is just like the default in the switch statement.

foreach

One of the more common operations IN dynamic SQL is to iterate over a collection, usually when building IN conditional statements. Points to note:

Collection indicates the type of collection to iterate over, and array indicates the array to iterate over
Open, close, and separator are SQL splices of traversal content
The foreach element is very powerful. It allows you to specify a collection and declare the collection item and index variables that can be used inside the element. It also allows you to specify beginning and ending strings and to place separators between iteration results.
You can pass any iterable (List, Set, etc.), Map, or array object to foreach as a collection parameter. When using an iterable or array, index is the number of iterations in the current iteration, and the value of item is the element fetched in the current iteration. When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.

1. For example, we need to find the information of students whose IDS are 1, 2 and 3. We do not want to search one at a time, but want to pass in the array ID at a time to find a set of students. An SQL interface can be written like this, passing in an array of objects:

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

Select * from array where the left symbol is an open parenthesis, the right parenthesis is a close parenthesis, and 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>Copy the code

Select * from list (int);

public List<Student>selectStudentByDynamicSQLForeachList(List<Integer>studentIds);
Copy the code

SQL statement colleAction = 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>Copy the code

3. When traversing a list of type objects:

public List<Student>selectStudentByDynamicSQLForeachListStudent(List<Student>students);
Copy the code

SQL statement similar to above, except that the use of attributes is not quite the same:

<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>Copy the code

It is used to define SQL fragments for reuse in other SQL tags. Sub-tags are required for reuse in other places. You can define any part of THE SQL, so labels can be placed anywhere in dynamic SQL.

<sql id="selectHead"> select id,name,age,score from student </sql> <! - with 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>Copy the code

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

[Author profile] : Qin Huai, public number [Qin Huai Grocery store] author, the road of technology is not at that time, mountain high water long, even slow, chi and not stop. The world wants everything to be fast and faster, but I hope I can take every step, write every article, and look forward to communicating with you.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Mybatis [13] — Use of Mybatis dynamic Sql tags

.

.

foreach

Mybatis [13] — Use of Mybatis dynamic Sql tags

.

.

foreach

Related Posts

Dockerfile and Compose

Surveillance trying something new? Please look for the Prometheus

Scala’s higher-kinded type