Introduction to the

I believe that before you use Mybatis, you have encountered a variety of conditions to judge the splicing SQL, the need to remove redundant commas and other pain, Mybatis dynamic SQL can be a good solution to the above situation, can be very flexible to assemble SQL statements, so as to improve the efficiency of development.

1. What are the dynamic splicing of SQL

If the label

Where the label

Choose when Otherwise tag

The set tag

Trim tabs

The bind tag

SQL and include tags foreach tags

2. If tag:

Select paramN from test where paramN = paramN from test where paramN = paramN from test where paramN = paramN

<select id="uid" resultType="UserEntity"> select * from UserEntity where 1=1 <if test="param1! =null and param1! =''"> and outno=#{param1} </if> <if test="param2! =null and param2! =''"> and inno=#{param2} </if> </select>Copy the code

Conditional fuzzy query and query data within a time period

<select id="uid" resultType="UserEntity"> select * from UserEntity where 1=1 <if test="param1! =null and param1! =''"> and outno=#{param1} </if> <if test="param2! =null and param2! =''">and inno LIKE CONCAT('%',#{param2 },'%' ) </if> <if test="effectiveTime ! =null and effectiveTime ! EffectiveTime; effectiveTime; effectiveTime; End_time corresponds to a database field. </if> </select>Copy the code

3, Where tag:

Features:

The WHERE keyword is automatically added to the Sql statement and the first and is removed.

<select id="uid" resultType="UserEntity"> select * from UserEntity <where> <if test="param1! =null and param1! =''"> and outno=#{param1} </if> <if test="param2! =null and param2! =''"> and inno=#{param2} </if> </where> </select>Copy the code

4, Choose when otherwise tag

Features:

As long as one of the conditions is true, the others will not be judged.

If there are no valid conditions, the content in Otherwise is executed by default

<select id="uid" resultType="UserEntity"> select * from UserEntity <where> <choose> <when test="param1! =null and param1! =''"> and outno=#{param1} </when> <when test="param2! =null and param2! =''"> and inno=#{param2} </when> <otherwise> and 1=1 </otherwise> </choose> </where> </select>Copy the code

5, Set tag:

Generates a set keyword that automatically removes the last comma.

Note:

In the final judgment condition there is always a condition. Avoid SQL errors.

<update id="uid"> update accountTable <set> <if test="aname! =null and aname! =''"> aname=#{aname}, </if> <if test="money ! =null and money ! =''"> money=#{money}, </if> <if test="ano ! =null and ano ! =''"> ano=#{ano}, </if> </set> where ano=#{ano} </update>Copy the code

6. Trim label:

Prefix: Adds the specified content to the trim content

PrefixOverrides Removes the specified content from the trim content

Suffix: Add the specified content after the trim content

SuffixOverrides: Removes the specified content after trim

Note:

Remove before you add

Adding content adds a space by default.

<update id="upT" parameterType="account"> update account <trim prefix="$" prefixOverrides="" suffix="" suffixOverrides=""> <if test="ano ! =null and ano ! =''"> ano=#{ano}, </if> <if test="aname! =null and aname! =''"> aname=#{aname}, </if> <if test="money ! =null and money ! =''"> money=#{money}, </if> </trim> where ano=#{ano} </update>Copy the code

7. Bind

Name: parameter name

Value: indicates an expression. Note that string concatenation is performed as a variable concatenation

Such as:

 <bind name="money" value="'$'+money"/>
Copy the code

Reassign parameters

<update id="upB" parameterType="account"> <bind name="money" value="money+100"/> update account <trim prefix="set" suffixOverrides=","> <if test="ano ! =null and ano ! =''"> ano=#{ano}, </if> <if test="aname! =null and aname! =''"> aname=#{aname}, </if> <if test="money ! =null and money ! =''"> money=#{money}, </if> </trim> where ano=#{ano} </update>Copy the code

SQL and include tags

SQL tags: Declare common SQL statements externally

id ​

Include tags: Common SQL statements that introduce declarations

Refid:

Advantages: Easy to modify SQL as a whole

Cons: Difficult to read

<select id="selA" resultType="account"> select <include refid="mysql"></include> from account </select> <sql id="mysql">  ano,aname,apwd,money </sql>Copy the code

9. Foreach tags:

The foreach element can be used to iterate through a collection when constructing an IN conditional statement. Foreach will remove the extra “,”. If the collection is empty, the operation in the foreach element will not be performed, but the “in” keyword will be added, and an error will be reported.

Item: The element in the collection

Index: The index of the collection in which the element resides. When iterating through a map, index is the key

Collection: the type of the collection. Optional values include @param (“name”), key in Map, open,

Close: concatenated at the beginning and end

Separator: The separator between each element

Note: Foreach supports traversal of List, Set, Map, Array, etc

When iterating through an array or a List or Set, index is the number of iterations and item is the element acquired during the iteration.

When iterating through a Map (or a collection of Map.Entry objects), index is the key and item is the value

Collection Properties

Collection =” List “when passing a single argument that is a List

Collection =” Array “when passing a single argument and an Array

When passing a single parameter that is Set, use the @param annotation, as below

If @param (“name”) is passed as a single parameter, collection=”name”, that is, the value of the @param attribute is the same

Arrays.put (“ids”, arrays.asList (1, 2))) where collection=” IDS”

When multiple arguments are passed and encapsulated as classes, such as List roleIds in the User class, collection=”roleIds”; If the User class has member variable Role Role and the Role class has member variable prilIds, collection=” role-. prilIds”

 <select id="selF" parameterType="list" resultType="account">
    select * from account where ano in
    <foreach collection="list" item="item" open="(" separator="," close=")">
    #{item}
    </foreach>
 </select>
   <insert id="inF">
    insert into log values 
    <foreach collection="list"  item="log" separator=",">
    (#{log.outno},#{log.inno},#{log.money})
    </foreach>  
   </insert>
Copy the code

10 and the sample

Dynamic SQL for batch modification

The controller layer

@putMapping ("/aaa") @apiOperation (value = "") public R (@requestBody Asa Asa) {PutMapping("/aaa") @apiOperation (value =" ") public R (@requestBody Asa Asa) { this.goodTestService.updateState(asa.getIds(),asa.getMsg()); return R.data("cg"); }Copy the code

service

package com.troy.testa.service; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.IService; import com.troy.testa.dto.GoodTestDTO; import com.troy.testa.entity.GoodTest; import org.springframework.data.domain.Pageable; import java.util.List; * @author Zhangh * @date 2021-03-21 15:31:34 */ Public Interface GoodTestService extends IService<GoodTest> { void updateState(String[] ids,String msg); }Copy the code

serviceImp

 @Override
    public void updateState(String[] ids,String msg) {

        baseMapper.updateState(ids,msg);
    }
Copy the code

dao

package com.troy.testa.dao; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.troy.testa.dto.GoodTestDTO; import com.troy.testa.entity.GoodTest; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import com.baomidou.mybatisplus.core.metadata.IPage; import java.util.List; @author Zhangh * @date 2021-03-21 15:31:34 */ @mapper Public Interface GoodTestDao extends BaseMapper<GoodTest> { void updateState(@Param("ids") String[] ids,@Param("msg") String msg); }Copy the code

xml

<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < mapper namespace="com.troy.testa.dao.GoodTestDao"> <update id="updateState"> <foreach collection="ids" item="item" index="index" separator=";" > UPDATE good_test SET good_state = 1,good_name=#{msg} WHERE id = #{item} </foreach> </update>Copy the code

Postman test

Tables have been modified in batches

Success!

Although it can be implemented in a program loop, dynamic SQL is used to save a lot of work.

Conclusion:

You often need to dynamically concatenate SQL based on different conditions, AND also ensure Spaces, commas at the end of column names, redundant AND, OR conditions, AND so on. It is easier to handle this situation in MyBatis.