This is the fifth day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021

MyBatis dynamic SQL statement

Generate different SQL statements through various judgments and multi-condition queries. In our previous learning process, we judge dynamic SPLicing SQL statements through identity SQL statements and Java, but now MyBatis framework provides two ways to help us complete the splicing of dynamic SQL statements

Label method of mapping files
Annotation mode – Defines the type of dynamic concatenation

SELECT * FROMSys_user queries all without making any judgmentsSELECT * FROM sys_user WHERE 1=1When the equation comes backtrue, returns the result, if present1Million-hop record, the identity needs to be judged1One million timesCopy the code

String sql = "SELECT * FROM sys_user WHERE 1=1 ";
if(! map.get("user_name").equals("")){
  sql += " AND user_name LIKE '"+map.get("user_name") +"% '";
}
if(! map.get("age").equals("")){
  sql += " AND age="+map.get("age"); } By ourselves to complete the judgment splicingCopy the code

1. Dynamic SQL statement labels

Dynamic SQL is one of the powerful features of MyBatis. If you’ve ever used JDBC or a similar framework, you can understand how painful it can be to concatenate SQL statements based on different conditions, such as making sure you don’t forget to add the necessary whitespace and removing the comma from the last column name of the list. With dynamic SQL, you can get rid of this pain completely.

Using dynamic SQL is not an easy task, but with the powerful dynamic SQL language that can be used in any SQL mapping statement, MyBatis significantly improves the ease of use of this feature.

If you’ve ever worked with JSTL or any xmL-like language based text processor before, dynamic SQL elements may feel familiar. In previous versions of MyBatis, it took time to understand a large number of elements. With the help of powerful OGNL based expressions, MyBatis 3 replaces most of the previous elements, greatly reducing the number of elements to be learned by less than half.

if
choose (when, otherwise)
trim (where, set)
foreach

<if test=" return only Boolean type ">We can concatenate dynamic SQL statements with many equations


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hanpang.mapper.UserMapper">
  <resultMap id="BaseMapper" type="com.hanpang.model.User">
    <id column="id"  property="userId" />
    <result column="username" property="userName"/>
    <result column="password" property="pwd"/>
    <result column="nick_name" property="nickName"/>
    <result column="age" property="userAge"/>
    <result column="sex" property="userSex"/>
  </resultMap>
  <! SELECT * from 'SELECT';
  <sql id="base_columns">
    id,username,password,nick_name,age,sex
  </sql>
  <! -- 1. Identity and IF tag complete dynamic SQL statement -->
  <select id="identityIfSQL" parameterType="com.hanpang.model.User" resultMap="BaseMapper">
    SELECT <include refid="base_columns"/> FROM sys_user
    WHERE 1=1
    <! Join SQL statement with IF tag -->
    <! Ampersand is not supported in all XML files. -->
    <! ></if>-- </if>--> -- </if>-->
    <if test="userName ! = null&amp;&amp; userName != ''"> <! And -->
      AND username LIKE CONCAT(#{userName},'%')
    </if>
    <if test="userSex ! = null">
      AND sex=#{userSex}
    </if>
  </select>
</mapper>
Copy the code

< WHERE > : used in combination with the IF tag.

(1) When there is content between the tags < WHERE > content
tags, add the where keyword in the form of the content in front of the entire content

(2) Check whether the front end of where follow-up content contains AND OR OR, if it starts with the above two, it will be automatically removed

<select id="whereIfSQL" parameterType="com.hanpang.model.User" resultMap="BaseMapper">
  SELECT <include refid="base_columns"/> FROM sys_user
  <where>
    <if test="userName ! = null and userName ! = ""> <! And -->
      AND username LIKE CONCAT(#{userName},'%')
    </if>
    <if test="userSex ! = null">
      AND sex=#{userSex}
    </if>
  </where>
</select>
Copy the code

<trim>Tags can implement WHERE tags and set tags
- The trim tag is not whitespace free; the attributes in the tag can be combined in any way
- Prefix =” What content is added to the front of the content when it is found between the tags”
- PrefixOverride =” When finding content between tags, check that the first part of the content matches the given content. If the match is successful, remove it.”
- Suffix =” What to add at the end of the content when there is content between the tags”
- SuffixOverride “When finding content between tags, check that the last part of the content matches the given content. If the match is successful, remove it.”

<select id="trimIfSQL" parameterType="com.hanpang.model.User" resultMap="BaseMapper">
  SELECT <include refid="base_columns"/> FROM sys_user
  <! -- Trim tag implements where tag -->
  <trim prefix="WHERE " prefixOverrides="AND |OR ">
    <if test="userName ! = null and userName ! = "">
      AND username LIKE CONCAT(#{userName},'%')
    </if>
    <if test="userSex ! = null">
      AND sex=#{userSex}
    </if>

  </trim>
</select>
Copy the code

: used in combination with an IF tag to complete dynamic updates.

(1) When there is content between the

tags
tags, add the set keyword to the front of the entire content

(2) Check whether the last end of the set follow-up content contains a comma, and remove it

<update id="updateSet" parameterType="com.hanpang.model.User">
  UPDATE sys_user
  <set>
    <if test="userName ! = null and userName.trim().length > 0">
      username = #{userName},
    </if>
    <if test="pwd ! = null and pwd ! = "">
      password = #{pwd},
    </if>
    <if test="nickName ! = null and nickName.trim().length > 0">
      nick_name = #{nickName},
    </if>
    <if test="userAge ! = null">
      age =#{userAge},
    </if>
    <if test="userSex ! = null">
      sex =#{userSex},
    </if>
  </set>
  <where>
    <if test="userId ! = null">
      id=#{userId}
    </if>
  </where>

</update>
Copy the code

Equivalent writing

<update id="updateTrim" parameterType="com.hanpang.model.User">
  UPDATE sys_user
  <trim prefix="SEX" suffixOverrides=",">
    <if test="userName ! = null and userName.trim().length > 0">
      username = #{userName},
    </if>
    <if test="pwd ! = null and pwd ! = "">
      password = #{pwd},
    </if>
    <if test="nickName ! = null and nickName.trim().length > 0">
      nick_name = #{nickName},
    </if>
    <if test="userAge ! = null">
      age =#{userAge},
    </if>
    <if test="userSex ! = null">
      sex =#{userSex},
    </if>
  </trim>
  <where>
    <if test="userId ! = null">
      id=#{userId}
    </if>
  </where>

</update>
Copy the code

<trim>Classic use of: dynamically add statements, one – to – one order, not out of order

<insert id="add" parameterType="com.hanpang.model.User" useGeneratedKeys="true" keyProperty="userId">
  INSERT INTO sys_user
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="userName ! = null and userName.trim().length > 0">
      username ,
    </if>
    <if test="pwd ! = null and pwd ! = "">
      password,
    </if>
    <if test="nickName ! = null and nickName.trim().length > 0">
      nick_name ,
    </if>
    <if test="userAge ! = null">
      age ,
    </if>
    <if test="userSex ! = null">
      sex ,
    </if>
  </trim>
  <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
    <if test="userName ! = null and userName.trim().length > 0">
      #{userName},
    </if>
    <if test="pwd ! = null and pwd ! = "">
      #{pwd},
    </if>
    <if test="nickName ! = null and nickName.trim().length > 0">
      #{nickName},
    </if>
    <if test="userAge ! = null">
      #{userAge},
    </if>
    <if test="userSex ! = null">
      #{userSex},
    </if>
  </trim>
</insert>
Copy the code

tag: loop collection (array, List, Map)

<foreach 
         collection="The premise is a parameter (@param not set), is array, the default name is array, is list the default name is list." 
         item="Get the element, the specific value or object in the collection." open="Content added at the front of the content." 
         close="Something added at the end of the content." 
         separator="Set what separations to use between elements"
         ></foreach>
Copy the code

If data is not aliased, the default name is array

 int deleteArray(int[] tempArray); // Do not use @param
Copy the code

<! -- 1. Note that if you use array form, do not set paramterType to automatically recognize -->
<delete id="deleteArray">
  DELETE FROM sys_user
  <where>
    <if test="array ! = null and array.length>0">
      <! -- id in (10,20,30) -->
      id in
      <foreach collection="array" item="val" open="(" close=")" separator=",">
        #{val}
      </foreach>
    </if>
  </where>
</delete>
Copy the code

Data setting alias status

<delete id="deleteArrayParam">
  DELETE FROM sys_user
  <where>
    <! -- id in (10,20,30) -->
    id in
    <foreach collection="myArray" item="val" open="(" close=")" separator=",">
      #{val}
    </foreach>
  </where>
</delete>
Copy the code

Set operations

<delete id="deleteList" parameterType="list">
  DELETE FROM sys_user
  <where>
    id in
    <foreach collection="list" item="val" open="(" close=")" separator=",">
      #{val}
    </foreach>
  </where>
</delete>
Copy the code

Batch Add Operation

<insert id="addBatch" parameterType="list">
  INSERT INTO sys_user (username,nick_name,sex,age)
  VALUES
  <foreach collection="list" item="user" separator=",">
    (#{user.userName},#{user.nickName},#{user.userSex},#{user.userAge})
  </foreach>
</insert>
Copy the code

<insert id="addBatch" parameterType="list">
  INSERT INTO sys_user (username,nick_name,sex,age)
  VALUES
  <foreach collection="list" item="user" open="(" close=")" separator="),(">
    #{user.userName},#{user.nickName},#{user.userSex},#{user.userAge}
  </foreach>
</insert>
Copy the code

2. The label does not support the less than sign

<select id="listAge" parameterType="int" resultMap="BaseMapper">
  <! -- SELECT <include refid="base_columns"/> FROM sys_user WHERE age < #{age} -->
  <! -- SELECT <include refid="base_columns"/> FROM sys_user WHERE age &lt; #{age}-->
  <! -- SELECT <include refid="base_columns"/> FROM sys_user WHERE age &lt; = #{age}-->
  SELECT <include refid="base_columns"/>FROM sys_user WHERE <! [CDATA[ age<#{age} ]]><! SQL > select * from CDATA;
</select>
Copy the code

3. Execute multiple SQL statements at a time

By default, MySQL does not support the one-time execution of polymorphic SQL statements

<update id="excuteMoreSQL">
  UPDATE sys_user SET age=66 WHERE id=6;
  DELETE FROM sys_user WHERE id=5;
</update>
Copy the code

AllowMultiQueries =true can be set when connecting to MySQL

<property name="url" value="JDBC: mysql: / / 127.0.0.1:3306 / antu_mybatis? characterEncoding=utf-8&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;allowMultiQueries=true"/>
Copy the code

4. Annotate dynamic SQL statements

package com.hanpang.mapper;

import com.hanpang.model.Dept;
import com.hanpang.provider.DeptProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface DeptMapper {
    @Insert(value = "INSERT INTO SYS_DEPT (DEPT_NAME,DEPT_USER) VALUES (#{deptName},#{deptUser})")
    @Options(useGeneratedKeys=true,keyProperty="deptId")
    int add(Dept dept);

    @Select("SELECT * FROM sys_dept")
    @Results(id="BaseMapper",value = { @Result(id = true,column = "dept_id",property = "deptId"), @Result(column = "dept_name",property = "deptName"), @Result(column = "dept_user",property = "deptUser") })
    List<Dept> listAll(a);

    @Select("SELECT * FROM sys_dept WHERE dept_name LIKE CONCAT('%',#{name},'%')")
    @ResultMap(value="BaseMapper")
    List<Dept> listByName(String deptName);

    @Select("SELECT * FROM sys_dept")
    @Results(value = { @Result(id = true,column = "dept_id",property = "deptId"), @Result(column = "dept_name",property = "deptName"), @Result(column = "dept_user",property = "deptUser"), @Result(property = "userList",column = "dept_id",many = @Many(select = "com.hanpang.mapper.UserMapper.getUserByDeptId")) })
    List<Dept> one2manyList(a);
    @Select("SELECT sd.dept_id,sd.dept_name,sd.dept_user,su.* FROM sys_dept sd LEFT JOIN sys_user su ON sd.dept_id=su.dept_id")
    @Results(value = { @Result(id = true,column = "dept_id",property = "deptId"), @Result(column = "dept_name",property = "deptName"), @Result(column = "dept_user",property = "deptUser"), @Result(property = "userList",column = "dept_id",many = @Many(resultMap= "com.hanpang.mapper.UserMapper.BaseMapper")) })
    List<Dept> one2manyListJoin(a);

    @Insert("")
    void addBatch(List<Dept> deptList);

    @InsertProvider(value= DeptProvider.class,method="insertDeptBatchSQL")
    void insertBatch(List<Dept> deptList);




}

Copy the code

package com.hanpang.provider;

import com.hanpang.model.Dept;

import java.util.List;

public class DeptProvider {
    public String insertDeptBatchSQL(List<Dept> deptList){
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO sys_dept (dept_name,dept_user) VALUES");
        deptList.forEach(dept -> {
            builder.append("(");
            builder.append(dept.getDeptName());
            builder.append("', '");
            builder.append(dept.getDeptUser()) ;
            builder.append("'),");
        });
        builder.deleteCharAt(builder.length()-1);
        System.out.println("* * * * * * * * * * * * *");
        System.out.println(builder.toString());
        returnbuilder.toString(); }}Copy the code

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.

Dynamic SQL statement to pit MyBatis

MyBatis dynamic SQL statement

1. Dynamic SQL statement labels

2. The label does not support the less than sign

3. Execute multiple SQL statements at a time

4. Annotate dynamic SQL statements

Dynamic SQL statement to pit MyBatis

MyBatis dynamic SQL statement

1. Dynamic SQL statement labels

2. The label does not support the less than sign

3. Execute multiple SQL statements at a time

4. Annotate dynamic SQL statements

Related Posts

Use of the Node-inspect command line tool for debugging

Mysql8: The client connection caching-sha2-password problem

Springboot uniformly handles the return results and exceptions