Shulou(Shulou.com)06/03 Report--

This article focuses on "how to understand MyBatis dynamic SQL", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand MyBatis dynamic SQL.

Catalogue

Preface

Dynamic sql

1. Take a look at the module directory structure first.

two。 Physical modeling and logical modeling

3. Introduce dependency

4. Global profile

5.sql commonness extraction file

6.mapper interface

If

Static sql:

Dynamic sql:

Where

Dynamic sql with if tags:

Dynamic sql for where and if:

Trim

Dynamic sql of trim

Trim tag:

Set

Dynamic sql of trim:

Dynamic sql of set

The purpose of the set tag is:

Choose 、 when 、 otherwise

Dynamic sql

Foreach

1. Dynamic sql

two。 Dynamic sql

Batch queries: foreach tags

Test program

Preface

The previous mysql is queried through static sql, but if the business is complex, we will encounter the problem of quotation marks, or an extra space, which makes the sql code wrong, so to solve this problem, we have dynamic sql.

The dynamic SQL technology of Mybatis framework is a function of dynamically assembling SQL statements according to specific conditions, and its significance is to solve the pain point problem when concatenating SQL statement strings. Specifically, it is realized through tags.

Dynamic sql1. Take a look at the module directory structure first.

Create a sql.xml file under the mapper package under resources of the classpath (common extraction)

two。 Physical modeling and logical modeling

The physical modeling steps are omitted here, requiring the tables of the database to correspond to the pojo class.

Package pojo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructorpublic class Employee {private Integer empId; private String empName; private Double empSalary;} 3. Introduce dependency

Copy the previous log4j to the classpath resouces, and the pom.xml after introducing dependency is as follows:

4.0.0 org.example day03-mybatis02-dynamic 1.0-SNAPSHOT jar org.projectlombok lombok 1.18.8 provided org.mybatis mybatis 3.5.7 junit junit 4.12 test mysql mysql-connector-java 5.1.3 runtime log4j log4j 1.2.17 4. Global profile

Note: there are hump mapping, alias mapping, path mapping and path mapping. What is different from the previous one is that we have done the common extraction of sql statements here, so we have to add a path mapping of sql.

5.sql commonness extraction file

Create a sql.xml under the package mapper under the classpath resources (because our sql is to be written in the mapping file, it is also a mapping file, so it needs to be written under mapper). When you want to use it, add it in the mapping path file where you need to use this sql statement.

Select emp_id,emp_name,emp_salary from t_emp

The common extraction file can also be unconfigured, so just rewrite the statement to be executed in the mapping file.

6.mapper interface

There are seven ways.

Package mapper;import org.apache.ibatis.annotations.Param;import pojo.Employee;import java.util.List;public interface EmployeeMapper {/ / query all employees larger than the empId based on their empId. If empId is null, query all employees List selectEmployeeListByEmpId (Integer empId) / * query the collection of employees whose salary is larger than the incoming empId and salary is larger than the incoming empSalary. If the passed empId is null, the empId condition will not be considered. * if the incoming empSalary is null, the empSalary condition will not be considered * / List selectEmployeeListByEmpIdAndEmpSalary (@ Param ("empId") Integer empId, @ Param ("empSalary") Double empSalary) / * update employee information according to empId, if a value is null, do not update this field * / void updateEmployee (Employee employee); / * query employee information according to emp_id, if "{empId}"

Indicates that the extracted sql fragment is referenced, or the sql statement can be written directly. If it is a static sql, when the id is null, the query is empty, while the dynamic sql can find all of it. The if tag contains the test attribute name, which is used as a judgment statement.

Where

Goal:

Query a collection of employees that are larger than the incoming empId and whose salary is greater than the incoming empSalary

If the empId passed in is null, the empId condition is not considered

If the empSalary passed in is null, the condition of empSalary is not considered.

Dao API method:

List selectEmployeeListByEmpIdAndEmpSalary (@ Param ("empId") Integer empId, @ Param ("empSalary") Double empSalary)

Dynamic sql with if tags: where emp_id > # {empId} and emp_salary > # {empSalary}

You can see here that if empSalary is empty, the sql statement is select * from t_emp where emp_id > # {empId}, but if empId is empty, then the sql statement is select * from t_emp where and emp_salary > # {empSalary}, obviously this is wrong, and the if tag does not apply here. So we use the where tag, or the trim tag.

Dynamic sql of where and if: emp_id > # {empId} and emp_salary > # {empSalary}

The purpose of the where tag is:

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, in fact, to go from the beginning to the end, here or according to the above method

Trim's dynamic sql emp_id > # {empId} AND emp_salary > # {empSalary} trim tag:

Prefix: specify the prefix to add dynamically

Suffix attribute: specifies the suffix to add dynamically

PrefixOverrides: specify prefixes to be removed dynamically, using "|" to separate possible values

SuffixOverrides attribute: specifies the suffix to be removed dynamically, using "|" to separate possible multiple values

Set

Goal: update employee information according to empId. If a value is null, this field will not be updated.

Dao API method:

Void updateEmployee (Employee employee)

Let's use the trim tag above to solve this problem.

Dynamic sql of trim: emp_name=# {empName}, emp_salary=# {empSalary} where emp_id=# {empId} set dynamic sql update t_emp emp_name=# {empName}, emp_salary=# {empSalary}

You can see

The purpose of the set tag is:

Automatically add the SET keyword before the first field to be modified

Remove the connector (,) before the first field to be modified.

Choose 、 when 、 otherwise

Goal:

Query employee information according to emp_id, if 0 # {empId} emp_id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.