The dynamic SQL technology of the Mybatis framework is a function of dynamically assembling SQL statements according to specific conditions. The meaning of its existence is to
Solve the pain point problem when splicing SQL statement strings.
Example: Query with multiple query conditions. If the conditions are set, they must appear in sql.
Be careful with setting and specifying default values. null and “” (empty string)
if
EmpMapper.java
List<Emp> getEmpByCondition(Emp emp);
EmpMapper.xml
<select id="getEmpByCondition" resultType="Emp">
select*
from t_emp
where
<!-- test indicates whether the attribute of the entity class in the current label is valid. If it is valid, it will be spliced into sql. If it is invalid, it will not be spliced -->
<if test="empName != null and empName != '' ">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
Test program TestDynamicSql,java
@Test
public void testGetEmpByCondition(){
SqlSession sqlSession = SqlSessionUtil. getSqlSession();
EmpMapper empMapper = sqlSession. getMapper(EmpMapper. class);
Emp emp = new Emp(1, "Zhang San", 20, "Male",1);
List<Emp> list_emp = empMapper. getEmpByCondition(emp);
list_emp.forEach(System.out::println);
sqlSession. close();
}
where
Here, if the condition empName behind where is set to empty, then it is wrong to use the test class query.
In the same way, if the age and gender are set to be empty, then there will be an empty shell of select * from t_emp where
How to solve?
Method 1: Add the condition of constant establishment
EmpMapper.xml
<select id="getEmpByCondition" resultType="Emp">
select*
from t_emp
where 1 = 1
<!-- test indicates whether the attribute of the entity class in the current label is valid. If it is valid, it will be spliced into sql. If it is invalid, it will not be spliced -->
<if test="empName != null and empName != '' ">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
Continue to query the situation where the above fields are all empty, and see that the result does not report an error, and the query is correct
Method 2: Use where tag
EmpMapper.xml
<select id="getEmpByCondition" resultType="Emp">
select*
from t_emp
<where>
<!-- test indicates whether the attribute of the entity class in the current label is valid. If it is valid, it will be spliced into sql. If it is invalid, it will not be spliced -->
<if test="empName != null and empName != '' ">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</where>
</select>
Continue testing for null values:
Verified the conclusion:
If none of the if conditions in the where tag are met, the where tag has no function, that is, the where keyword will not be added.
assign a value to one of the
test
Verified the conclusion:
If the if condition in the where tag is met, the where tag will automatically add the where keyword, and remove the redundant and at the front of the condition.
Note: the where tag cannot remove the redundant and at the end of the condition
That is, if written as
<where>
<!-- test indicates whether the attribute of the entity class in the current label is valid. If it is valid, it will be spliced into sql. If it is invalid, it will not be spliced -->
<if test="empName != null and empName != '' ">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</where>
At this time, the test will assign a null value to gender, and you will find that the and after the age are still there
trim
trim is used to remove or add content in the label
Common properties:
prefix: Add something in front of the content in the trim tag
prefixOverrides: Remove some content in front of the content in the trim tag
suffix: Add something after the content in the trim tag
suffixOverrides: Remove some content after the content in the trim tag
EmpMapper.xml
<select id="getEmpByCondition" resultType="Emp">
select*
from t_emp
<!-- There are 4 trim tags
prefix prefixOverrides suffix suffixOverrides
prefix adds the specified content in front of the content
prefixOverrides removes the specified content in front of the content
suffix adds the specified content after the content
suffixOverrides removes the specified content after the content
-->
<trim prefix="where" suffixOverrides="and">
<!-- test indicates whether the attribute of the entity class in the current label is valid. If it is valid, it will be spliced into sql. If it is invalid, it will not be spliced -->
<if test="empName != null and empName != '' ">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="gender != null and gender != ''">
gender = #{gender} and
</if>
</trim>
</select>
First test is not empty
The and after gender is automatically removed
Then test only the value of age
Then test all empty, and found that because there is no content, so where is not added
choose, when, otherwise
choose, when, otherwise are equivalent to if…, else if…, else
EmpMapper.java
List<Emp> getEmpByChoose(Emp emp);
EmpMapper.xml
<select id="getEmpByChoose" resultType="Emp">
select*
from t_emp
<where>
<choose>
<!-- test indicates whether the attribute of the entity class in the current label is valid. If it is valid, it will be spliced into sql. If it is invalid, it will not be spliced -->
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<when test="gender != null and gender != ''">
gender = #{gender}
</when>
<otherwise>
1 = 1
</otherwise>
</choose>
</where>
</select>
test program
@Test
public void testGetEmpByChoose(){
SqlSession sqlSession = SqlSessionUtil. getSqlSession();
EmpMapper empMapper = sqlSession. getMapper(EmpMapper. class);
Emp emp = new Emp(1, "Zhang San", 20, "Male",1);
List<Emp> list_emp = empMapper. getEmpByChoose(emp);
list_emp.forEach(System.out::println);
sqlSession. close();
}
It is found that choose, when and otherwise will choose the first one that meets the conditions. As long as one meets the conditions, it will not be judged later, and will not be included in the conditions, so each when does not need to add and, only the first condition that is established will be executed
foreach
More important tags are used for batch addition and batch deletion.
Add in batches
EmpMapper.java
int insertMoreEmp(@Param("emps") List<Emp> emp);
EmpMapper.xml
<insert id="insertMoreEmp" useGeneratedKeys="true" keyProperty="empId">
insert into t_emp
values
<!--foreach-tag
collection attribute: Just put it into the variable defined in the Param annotation of the Mapper interface
item can be understood as a row of data, that is, an object
separator represents the separator, after each cycle, it is divided by, and the last time there is no
-->
<foreach collection="emps" item="emp" separator=",">
<!-- emp.empName is used here because the return value type is List, and emp is the entity object of each row of data -->
(null, #{emp.empName}, #{emp.age}, #{emp.gender}, #{emp.deptId})
</foreach>
</insert>
test program
@Test
public void testInsertMoreEmp(){
SqlSession sqlSession = SqlSessionUtil. getSqlSession();
EmpMapper empMapper = sqlSession. getMapper(EmpMapper. class);
List<Emp> list_emp = new ArrayList<>();
list_emp.add(new Emp(null,"Saturday",23,"Male",3));
list_emp.add(new Emp(null,"Zhu Ba",25,"Female",2));
list_emp.add(new Emp(null,"Li Jiu",22,"Male",1));
int i = empMapper.insertMoreEmp(list_emp);
System.out.println("insert:" + i + "line");
sqlSession. close();
}
Batch delete
Delete method 1
EmpMapper.java
int deleteMoreEmp(@Param("empIds") Integer[] empIds);
EmpMapper.xml
<delete id="deleteMoreEmp">
delete from t_emp where emp_id in (
<foreach collection="empIds" item="empId" separator=",">
#{empId}
</foreach>)
</delete>
test
@Test
public void testDeleteMoreEmp(){
SqlSession sqlSession = SqlSessionUtil. getSqlSession();
EmpMapper empMapper = sqlSession. getMapper(EmpMapper. class);
Integer[] empIdArr = {8,9,10};
int i = empMapper.deleteMoreEmp(empIdArr);
System.out.println("Delete:" + i + "line");
sqlSession. close();
}
Delete Method 2
EmpMapper.xml
<!--Method 2-->
<delete id="deleteMoreEmp">
delete
from t_emp
where emp_id in
<!-- What does open start and close end with -->
<foreach collection="empIds" item="empId" separator="," open="(" close=")">
#{empId}
</foreach>
</delete>
Test: Automatically added front and rear brackets
Delete method 3
EmpMapper.xml
<delete id="deleteMoreEmp">
delete
from t_emp
where
<foreach collection="empIds" item="empId" separator="or">
emp_id = #{empId}
</foreach>
</delete>
Test: use or to splice conditions
Summary
Attributes of the foreach tag
collection: set the array or collection to be looped
item: Use a string to represent each data in the array or collection
separator: Set the separator between the data of each cycle
open: what to start the loop with
close: what the loop’s everything ends with
SQL fragment
sql fragment, which can record a public sql fragment, and import it through the include tag where it is used
EmpMapper.java
List<Emp> getAllEmp();
EmpMapper.xml
<sql id="empColumns">
emp_id, emp_name, age, gender, dept_id
</sql>
<select id="getAllEmp" resultType="Emp">
select
<!-- When using it, use the include refid attribute to put the id in sql -->
<include refid="empColumns"></include>
from t_emp
</select>
test
@Test
public void testGetAllEmp(){
SqlSession sqlSession = SqlSessionUtil. getSqlSession();
EmpMapper empMapper = sqlSession. getMapper(EmpMapper. class);
List<Emp> list_emp = empMapper. getAllEmp();
list_emp.forEach(System.out::println);
sqlSession. close();
}