MyBatis dynamic SQL comprehensive explanation

MyBatis dynamic SQL comprehensive explanation

Preface

Previously, MySQL was queried through static SQL, but if the business is complicated, we will encounter quotation mark problems, or an extra space, which will make the SQL code wrong. So in order to solve this problem, we have dynamic SQL.

The dynamic SQL technology of the Mybatis framework is a function that dynamically assembles SQL statements according to specific conditions. Its purpose is to solve the pain point problem when splicing SQL statement strings. This is done through tags.

Dynamic SQL

1. Take a look at the module directory structure first

Create a sql.xml file in the mapper package under resources in the class path (commonality extraction)

2. Physical modeling and logical modeling

The physical modeling step is omitted here, and the database table is required to correspond to the pojo class.

package pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private Integer empId;
    private String empName;
    private Double empSalary;

}

3. Introducing dependencies

Copy the previous log4j to the classpath resouces. In addition, the pom.xml file after we introduce the dependency is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>day03-mybatis02-dynamic</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>provided</scope>
        </dependency>

        <!-- Mybatis core -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

        <!-- junit test -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!-- MySQL Driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.3</version>
            <scope>runtime</scope>
        </dependency>

        <!-- log4j log -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

</project>

4. Global Configuration File

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--CamelCase Mapping-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--Type alias mapping-->
    <typeAliases>
        <package name="pojo"/>
    </typeAliases>
    <!--Environment Configuration-->
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="username" value="root"/>
                <property name="password" value="888888"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis-example"/>
                <property name="driver" value="com.mysql.jdbc.Driver"/>
            </dataSource>
                
        </environment>
    </environments>
    <!--Path Mapping-->
    <mappers>
        <mapper resource="mapper/sql.xml"/>
        <package name="mapper"/>
    </mappers>
</configuration>

Note: There are camelCase mappings, alias mappings, path mappings and path mappings. What is different from the previous one is that we have done commonality extraction of SQL statements here, so we have to add a SQL path mapping <mapper resource="mapper/sql.xml"/> .

5. SQL common extraction files

Create a sql.xml in the package mapper under the class path resources (because our sql is to be written in the mapping file, and it is also a mapping file, so it needs to be written under mapper). When you need to use it, add <include refid="mapper.sql.mySelectSql"></include> where you need to use this SQL statement in the mapping path file.

<?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="mapper.sql">

        <sql id="mySelectSql">
    select emp_id,emp_name,emp_salary from t_emp
</sql>


</mapper>

The common extraction file can also be left unconfigured. In this case, you can simply rewrite the statements to be executed in the mapping file.

6. Mapper interface

There are seven methods in total

package mapper;

import org.apache.ibatis.annotations.Param;
import pojo.Employee;

import java.util.List;

public interface EmployeeMapper {

     //Query all employees whose empId is greater than the employee's empId. If empId is null, query all employees List<Employee> selectEmployeeListByEmpId(Integer empId);

    /**
     * Query the set of employees whose empId is greater than the passed in empId and whose salary is greater than the passed in empSalary. If the passed in empId is null, the empId condition is not considered. If the passed in empSalary is null, the empSalary condition is not considered. */
    List<Employee> selectEmployeeListByEmpIdAndEmpSalary(@Param("empId") Integer empId, @Param("empSalary") Double empSalary);

    /**
     * Update employee information based on empId. If a value is null, this field will not be updated. */
    void updateEmployee(Employee employee);

    /**
     * Query employee information based on emp_id. If 0<emp_id<6, query all employees with an emp_id greater than 6. If emp_id is greater than 6, query all employees with an emp_id less than 6. * If it is any other case, query all employee information. */
    List<Employee> selectEmployeeList(Integer empId);

    /**
     * Add employee information */
    void insertEmployee(Employee employee);

    /**
     * Add employee collection in batches */
    void insertEmployeeList(@Param("employeeList") List<Employee> employeeList);

    /**
     * Query the employee set based on the employee ID set*/
    List<Employee> selectEmployeeListByEmpIdList(List<Integer> idList);
}

if

Objective: Query all employees whose empId is greater than the employee's empId. If empId is null, query all employees.

The methods of Dao interface are:
List<Employee> selectEmployeeListByEmpId(Integer empId);

Static sql:

<select id="selectEmployeeListByEmpId" resultType="Employee">

    <include refid="mapper.sql.mySelectSql"></include> where emp_id>#{empId}

</select>

Dynamic SQL:

<select id="selectEmployeeListByEmpId" resultType="Employee">
     <include refid="mapper.sql.mySelectSql"></include>
     <if test="empId != null">
         where emp_id>#{empId}
     </if>
 </select>

<include refid="mapper.sql.mySelectSql"></include> means referencing the extracted SQL fragment, or you can directly write the SQL statement. If it is static sql, when id is null, the query result is empty, but dynamic sql can find all. The if tag contains the test attribute name, which serves as a judgment statement.

where

Target:

  • Query the set of employees whose empId is greater than the passed empId and whose salary is greater than the passed empSalary
  • If the passed empId is null, the empId condition is not considered
  • If the passed empSalary is null, the condition of empSalary will not be considered.

Dao interface method:

List<Employee> selectEmployeeListByEmpIdAndEmpSalary(@Param("empId") Integer empId, @Param("empSalary") Double empSalary);

Dynamic SQL using if tag:

<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
    <include refid="mapper.sql.mySelectSql"></include> where
   <if test="empId != null">
            emp_id>#{empId}
        </if>
        <if test="empSalary != null">
           and emp_salary>#{empSalary}
        </if>

Here we can see that if empSalary is empty, the SQL statement is select * from t_emp where emp_id >#{empId}, but if empId is empty, the SQL statement is select * from t_emp where and emp_salary>#{empSalary}. Obviously this is wrong, and the if tag is not applicable here. So we use the where tag, or the trim tag.

Dynamic SQL for where and if:

<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
    <include refid="mapper.sql.mySelectSql"></include>
   
    <where>
        <if test="empId != null">
            emp_id>#{empId}
        </if>
        <if test="empSalary != null">
           and emp_salary>#{empSalary}
        </if>
    </where>
</select>

The role of the where tag:

  • Automatically add the WHERE keyword before the first condition
  • Automatically remove the connector before the first condition (AND, OR, etc.)

trim

Trim means to build, which actually means to remove the head and tail. Here we still follow the method above.

Dynamic SQL of trim

<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
    <include refid="mapper.sql.mySelectSql"></include>
    <trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="empId != null">
            emp_id>#{empId}
        </if>

        <if test="empSalary != null">
            AND emp_salary>#{empSalary}
        </if>
    </trim>
</select>

trim tag:

  • prefix: specifies the prefix to be added dynamically
  • suffix attribute: specifies the suffix to be added dynamically
  • prefixOverrides: specifies the prefix to be removed dynamically, using "|" to separate multiple possible values
  • suffixOverrides attribute: specifies the suffix to be removed dynamically, using "|" to separate multiple possible values

set

Objective: Update employee information based on empId. If a value is null, do not update this field.

Dao interface method:
void updateEmployee(Employee employee);
Let's use the trim tag above to solve this problem.

Dynamic SQL for trim:

<update id="updateEmployee" >
    <trim prefix="set" prefixOverrides=",">
        <if test="empName!=null">
            emp_name=#{empName}
        </if>
        <if test="empSalary!=null">
            , emp_salary=#{empSalary}
        </if>
    </trim>
    where emp_id=#{empId}
</update>

Dynamic SQL of set

<update id="updateEmployee" >
    update t_emp
     <set>
         <if test="empName!=null">
             emp_name=#{empName}
         </if>
         <if test="empSalary!=null">
            , emp_salary=#{empSalary}
         </if>
     </set>

It can be seen

The function of the set tag:

  • Automatically adds the SET keyword before the first field to be modified
  • Remove the connector (,) before the first field to be modified.

choose, when, otherwise

Target:

  • Query employee information based on emp_id. If 0<emp_id<6, then query all employees with an emp_id greater than that.
  • If emp_id is greater than 6, then query all employees with less than emp_id
  • If it is other cases, query all employee information

Dao interface method:
List<Employee> selectEmployeeList(Integer empId);

Dynamic SQL

<select id="selectEmployeeList" resultType="employee">
  
    <include refid="mapper.sql.mySelectSql"></include> where
    <choose>
    <!--&lt; is the escape character for < -->
        <when ​​test="empId>0 and empId&lt;6">
            emp_id>#{empId}
        </when>
        <when ​​test="empId>6">
            emp_id&lt;#{empId}
        </when>
        <otherwise>
            1==1
        </otherwise>
    </choose>

</select>

choose, when, otherwise
Equivalent to if ... else if... else if ... else

  • If the condition of a when statement is met, the subsequent when statements will not be judged.
  • If all when are not true, the content of otherwise tag will be concatenated.

foreach

Goal 1: Add employee information in batches

Dao interface method:

void insertEmployeeList(@Param("employeeList") List employeeList);

1. Dynamic SQL

<insert id="insertEmployeeList">
    insert into t_emp(emp_name,emp_salary)values
    <!--The collection tag can be written as list, collection,
    Or define the parameter name yourself @Param("employeeList") List<Employee> employeeList-->
    <foreach collection="employeeList" separator="," item="emp">
        (#{emp.empName},#{emp.empSalary})
    </foreach>
</insert>

Objective 2: Query multiple employee information based on multiple IDs

Dao Interface

List selectEmployeeListByEmpIdList(List idList);

2. Dynamic SQL

<select id="selectEmployeeListByEmpIdList" resultType="employee">
    <include refid="mapper.sql.mySelectSql"></include>
     <foreach collection="collection" item="id" separator="," open="where emp_id in (" close=")">
         #{id}
     </foreach>
</select>

Batch query: foreach tag

  1. collection attribute: Indicates the object to be traversed. If the parameter to be traversed is named using the @Param annotation, use that name. If it is not named List or collection.
  2. item attribute: represents the traversed element. We will use this element to assemble SQL statements: If the traversed element is a POJO object, we will get the data through #{traversed element.POJO attribute}; if the traversed element is a simple type of data, we will use #{traversed element} to get this simple type of data
  3. separator attribute: separator between traversed elements
  4. open attribute: add a prefix before the first element traversed
  5. close attribute: add a suffix after the last element traversed

Testing Procedure

import mapper.EmployeeMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import pojo.Employee;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class Test {
    private EmployeeMapper employeeMapper;
    private InputStream is;
    private SqlSession sqlSession;
    @Before
    public void init() throws Exception{
        //Goal: Get the proxy object of the EmployeeMapper interface, use it to call the selectEmployee(1) method, and then return the Employee object //1. Convert the global configuration file into a byte input stream is = Resources.getResourceAsStream("mybatisConfig.xml");
        //2. Create a SqlSessionFactoryBuilder object SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //3. Use the builder pattern to create a SqlSessionFactory object SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
        //4. Use factory mode to create a SqlSession object sqlSession = sqlSessionFactory.openSession();
        //5. Use dynamic proxy mode to create a proxy object of EmployeeMapper interface employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    }


    @After
    public void after() throws Exception{
        //Commit the transaction!!!
        sqlSession.commit();
        //7. Close resources is.close();
        sqlSession.close();
    }

    @org.junit.Test
    public void testSelectEmployeeListByEmpId(){
        System.out.println(employeeMapper.selectEmployeeListByEmpId(null));
    }

    @org.junit.Test
    public void testSelectEmployeeListByEmpIdAndEmpSalary(){
        System.out.println(employeeMapper.selectEmployeeListByEmpIdAndEmpSalary(2, 300d));
    }

    @org.junit.Test
    public void testUpdateEmployee(){
        Employee employee = new Employee(3,"celia", 9000d);

        employeeMapper.updateEmployee(employee);
    }

    @org.junit.Test
    public void testSelectEmployeeList(){
    System.out.println(employeeMapper.selectEmployeeList(7));
}

   @org.junit.Test
   public void testInsertEmployee(){
        employeeMapper.insertEmployee(new Employee(null,"tom",300d));
    }

    @org.junit.Test
    public void testInsertEmployeeList(){
        List<Employee> employeeList = new ArrayList<>();
        for (int i = 11; i <= 20; i++) {
            employeeList.add(new Employee(null,"aobama"+i,2000d));
        }

        employeeMapper.insertEmployeeList(employeeList);

    }

    @org.junit.Test
    public void testSelectEmployeeListByEmpIdList(){
        List<Integer> idList = new ArrayList<>();
        idList.add(23);
        idList.add(33);
        idList.add(32);
        idList.add(21);
        idList.add(22);
        System.out.println(employeeMapper.selectEmployeeListByEmpIdList(idList));
    }


}

This is the end of this article about MyBatis dynamic SQL. For more related MyBatis dynamic SQL content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MyBatis solves the problem of comma in Update dynamic SQL
  • Mybatis dynamic SQL if test writing and rules detailed explanation
  • Summary of the pitfalls of testing in Mybatis dynamic sql
  • Mybatis dynamic SQL sample code
  • Get started with Mybatis entity class names and multi-parameter dynamic SQL in one hour
  • An article to show you the dynamic SQL of mybatis

<<:  Function overloading in TypeScript

>>:  How to use the jquery editor plugin tinyMCE

Recommend

Vant+postcss-pxtorem implements browser adaptation function

Rem layout adaptation The styles in Vant use px a...

JavaScript static scope and dynamic scope explained with examples

Table of contents Preface Static scope vs. dynami...

How to remove MySQL from Ubuntu and reinstall it

First delete mysql: sudo apt-get remove mysql-* T...

JS implements simple calendar effect

This article shares the specific code of JS to ac...

The meaning and usage of linux cd

What does linux cd mean? In Linux, cd means chang...

JS implements multiple tab switching carousel

Carousel animation can improve the appearance and...

Teach you how to use Portainer to manage multiple Docker container environments

Table of contents Portainer manages multiple Dock...

How to import Chinese data into csv in Navicat for SQLite

This article shares with you the specific method ...

How to solve the problem of character set when logging in to Linux

Character set error always exists locale: Cannot ...

Will css loading cause blocking?

Maybe everyone knows that js execution will block...

Several ways to run Python programs in the Linux background

1. The first method is to use the unhup command d...

Implementing parameter jump function in Vue project

Page Description:​ Main page: name —> shisheng...

How to install openjdk in docker and run the jar package

Download image docker pull openjdk Creating a Dat...

How to use MySQL DATEDIFF function to get the time interval between two dates

describe Returns the time interval between two da...

Example code for drawing double arrows in CSS common styles

1. Multiple calls to single arrow Once a single a...