Too practical 😋, MyBatis dynamic tags

This is the 10th day of my participation in the August More Text Challenge

Dynamic SQL

The so-called dynamic SQL, is based on the different parameters we passed to dynamically construct different SQL statements, and MyBatis provides us with 9 dynamic tags, to achieve dynamic SQL.

SQL, inclue tags

SQL tags can be used to extract duplicate SQL statements. They can be reused in other SQL statement blocks. Use include tags to reference common SQL statement blocks

<! < SQL id="field" > /* If there are other concatenated statements, Is drawn behind the statement will not be able to add a semicolon * / username, birthday, sex, address < / SQL >, for example: <! <select id="findAll" resultType="user"> select <include refid="field">Copy the code

If the label

The if tag enables query using different SQL statements depending on the value of the entity class. For example, if the id is not empty, the query can be based on the id. If the username is not empty, the username must be added as the query condition. This situation is often encountered in multi-condition combinatorial queries.

If tag attribute test: used to judge conditions. OGNL expression is used. If the passed parameter meets the OGNL expression in test, the corresponding content of the IF tag is dynamically concatenated to the SQL statement.

<! <select id="findByCondition" resultType="user"> select *from user where 1=1 =null"> <! And username=#{username} </if> <if test="address! =null"> <! And address=#{address} </if> </select>Copy the code

If userame=”12″ and age=22 were passed in, the SQL statement would be as follows:select * from user where 1=1 and username="12" and age=22

Where the label

The “1=1” tag is added to the end of the “where” tag. The “1=1” tag is added to the end of the “WHERE” tag. The “1=1” tag is added to the end of the “WHERE” tag. The WHERE tag strips the concatenation and or or prefix of any query condition that is not the first if tag, so there is no need to write “1=1” again.

<! <select id="findByCondition" resultType="user"> select *from user where> <if test="username! =null"> <! </if> <if test="address!" =null"> <! And address=#{address} </if> </where> </select>Copy the code

In the above example, if only age=22 is passed, the SQL statement after concatenation is as follows:select * from user where age=22

The set tag

The set tag is similar to the WHERE tag. Previously, we saw that the WHERE tag removes the concatenation and/or prefix of the query condition that is not the first if tag. When we use the if tag in the update statement, it looks like this:

<update id="updateUser"> update user set <if test="username! =null"> username=#{username}, </if> <if test="sex! =null"> sex=#{sex}, </if> <if test="address! =null"> address=#{address} </if> where id=1 </update>Copy the code

Mysql > update user set username=? Update user set username=? ,sex= ? Where id=1 and sex=? The set tag automatically removes the prefix comma from a block of code that is not the first or the suffix comma from a block of code that is not the last.

Use the set tag

<update id="updateUser"> update user <set> <if test="username! =null"> username=#{username}, </if> <if test="sex! =null"> sex=#{sex}, </if> <if test="address! =null"> address=#{address} </if> </set> where id=1 </update>Copy the code

If address is null, then with the help of the set tag we can generate the following SQL statement:update user set username=? ,sex= ? where id=1If we pass sex, address, username null, then we will generate SQL statement:update user set sex=? , address=? where id=1

Trim tabs

The trim tag is a formatted tag that is used to add or omit a specified prefix or suffix to a concatenated SQL condition statement. It can be used as a SET or A WHERE tag.

The TRIM tag has the following attributes

Prefix: Prefix the SQL statement within the trim tag
Suffix: Add the suffix to the SQL statement in the trim tag
PrefixOverrides: remove excess prefix specified content, such as prefixOverrides = “and | or”, getting rid of the excess prefix trim TAB within the SQL statement “and” or “or”.
SuffixOverrides to remove the specified suffix. Such as suffixOverrides = “and | or”, getting rid of the excess suffix trim TAB within the SQL statement “and” or “or”.

example

As in the update statement, the trim tag is applied to remove the specified suffix:

<update ID ="trimTest" parameterType="Object"> Update user set <trim suffixOverrides=","> <if test="username" ! = null"> username=#{username}, </if> <if test="sex ! = null"> sex11zhidin=#{sex}, </if> </trim> where id=#{id} </update>Copy the code

For example, the trim application in select removes the specified prefix

< select id = "trimTest" > select * from the user where / / will remove more than the and prefix < trim prefixOverrides = "and | or" > < if test = "username! = null"> and username=#{username} </if> <if test="sex ! = null"> and sex11zhidin=#{sex} </if> </trim> </select>Copy the code

For example, insert statements add the specified prefix, suffix, delete the specified suffix

<insert id="insert" parameterType="Object"> insert into user <trim prefix="(" suffix=")" suffixOverrides=","> <if test="username ! = null"> username, </if> <if test="sex ! = null "> sex, </if> </trim> <trim prefix="values(" suffix=")" suffixOverrides=","> <if test="username ! = null"> #{username}, </if> <if test="sex ! = null"> #{sex}, </if> </trim> </insert>Copy the code

The foreach tag

The foreach tag is used to traverse collections or arrays.

Collection: Represents a collection to iterate over. It can be an Array, a List, a Map, etc. If you pass in an array array, the value is array; When is a List collection, the value is List; When a Map collection is passed in, the property value of the collection is the key of the Map.
Open: represents the beginning of a statement
Close: indicates the end part
Item: represents each element of the traversal set, that is, the element obtained in this traversal. When traversal is a Map set, index is the key and item is the corresponding value of the key.
Separator: Indicates the separator
Index: Indicates the attribute name of the index. It is the subscript of the current element in the set. If the object traversed is a Map set, this value is the key of the Map

Its application scenarios mainly include the following three types:

Select * from user where id in (? Select * from user where id in (? ,? ,? ,?) Therefore, we need to traverse the collection or array, using each element of the collection to construct the contents of the in condition statement.

Java code:

Int [] ids = {42,43,44}; // Array array userdao.getUserByids (ids);Copy the code

Mapper mapping file:

<select id="findByInids" resultType="user"  parameterType="com.demo.domain.QueryVo">
    select * from user where
     <foreach collection="array" open="id in (" close=")" item="id" separator=",">
        #{id}
     </foreach>
</select>
Copy the code

Select * from user where id in (? ,? ,? ,?)

To implement batch inserts, such as inserting multiple rows into a database table at a time, without using SQL dynamic tags foreach, we may need to use round-propagation to insert one data at a time, which is very troublesome. With foreach, we can easily implement multiple records at a time.

Java code

ArrayList<User> users = new ArrayList<>(); Users.add (new User("1",new Date()," male "," China ")); Users.add (new User("2",new Date()," male "," China ")); Users.add (new User("3",new Date()," male "," China ")); userDao.insert(users);Copy the code

Mapper Mapping file

<insert id="insert" keyProperty="id">
    insert into user(username,birthday,sex,address)
    values
    <foreach collection="list" separator="," item="User">
        (#{User.username},#{User.birthday},#{User.sex},#{User.address}
    </foreach>

</insert>

Copy the code

After mosaics of the above example SQL statement is: insert into user (username, birthday, sex, address) values (?,?,?,?,? ,? ,? ,?) . (? ,? ,? ,?) . (? ,? ,? ,?)

Update a field according to the key in the Map. The Map value is the new value of the specified field after the update.

Java code

HashMap<String, String> map = new HashMap<>(); Map. Put ("username"," Programmer without milk tea "); userDao.updateByMap(map, 67);Copy the code

Mapper Mapping file

<update id="updateByMap" >
    update user
    set
    <foreach collection="_parameter" index="key" item="val" separator=",">
        ${key}=#{val}
    </foreach>
    where
    id =#{id}
</update>
Copy the code

SQL statement: update user set username=? where id =?

🚨 Note: note: Select * from user where id in (); select * from user where id in (); So we can use the if tag or a combination of the WHERE tag and the if tag to determine whether the incoming parameter is null.

Choose the tag

Sometimes we don’t want to apply all the criteria, but just choose one of several options. MyBatis provides the choose tag to judge whether the condition in WHEN is true or not in order. If one of the conditions is true, the code block in the corresponding WHEN will be executed. The following when will not be judged and choose will be ended. When all when conditions in Choose are not satisfied, the code block in Otherwise is executed. Similar to the Java switch statement, choose is switch, when is case, and otherwise is default.

<select id="chooseTest" parameterType="user" resultMap="BaseResultMap"> SELECT * from user <where> <choose> <when test="username! =null"> AND username = #{username} </when> <when test="sex! =null"> AND sex = #{sex} </when> <otherwise> AND address = #{address} </otherwise> </choose> </where> </select>Copy the code

The bind tag

The bind tag can be used to declare a variable and bind it in the context of an SQL statement for use elsewhere in the SQL statement. It is often used for fuzzy queries (${} can be used for fuzzy queries, or the concat() function, which only the MySQL database can use when SQL injection cannot be prevented).

In MySQL, concat() is used to concatenate fuzzy query condition statements. This method can only be used to manipulate data in MySQL.

<select id="bindTest">
    select * from user where username like comcat('%',#{username},'%')
</select>
Copy the code

The BIND tag can be implemented regardless of the database type.

<select id="bindTest">
    <bind name="bindValue" value=" '%' + username + '%' "></bind>
    select * from user where uername like #{bindValue}
</select>
Copy the code

The above example concatenates the following SQL statement:select * from user where username like '%username%';

🏁 the above is the detailed explanation of MyBatis dynamic tag, if there is any error, also please leave a comment correction, if you think this article is helpful to you then click a like 👍 bar 😋😻 👍