Before that, we learned the global configuration file of mybatis. Now we begin to learn the mapping file of mybatis. In the mapping file, you can write the following top-level element labels:
cache – Cache configuration for this namespace. cache-ref – Cache configuration that references other namespaces. resultMap – Describing how to load objects from the database result set is the most complex and powerful element. parameterMap – Old style parameter mapping. This element has been discarded and may be removed in the future! Please use inline parameter mapping. This element is not described in the document. sql – A repeatable sentence block that can be referenced by other sentences. insert – Map insert statement. update – Map update statement. delete – Map delete statement. select – Map query statements.
Many attributes can be added to each top-level element tag. Let's start to learn more about the specific configuration.
1. insert, update and delete elements
| attribute | describe |
|---|---|
| id | A unique identifier in the namespace that can be used to reference this statement. |
| parameterType | The fully qualified name or alias of the class that will be passed into the parameters of this statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through the type handler. The default value is unset. |
| parameterMap | The property used to reference external parameterMap has been discarded. Please use inline parameter mapping and parameterType attribute. |
| flushCache | When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is (for insert, update and delete statements) true. |
| timeout | This setting is the number of seconds the driver waits for the database to return the requested result before throwing an exception. The default value is unset (database driven). |
| statementType | Optional status, PREPARED or CALLABLE. This will make MyBatis use statement, PreparedStatement or CallableStatement respectively. The default value is PREPARED. |
| useGeneratedKeys | (only applicable to insert and update) this will make MyBatis use the getGeneratedKeys method of JDBC to retrieve the primary key generated inside the database (such as the auto increment field of relational database management systems such as MySQL and SQL Server). The default value is false. |
| keyProperty | (only applicable to insert and update) specify the attribute that can uniquely identify the object. MyBatis will use the return value of getGeneratedKeys or the selectKey sub element of the insert statement to set its value. The default value is unset. If there is more than one generated column, you can separate multiple attribute names with commas. |
| keyColumn | (only applicable to insert and update) set the column name of the generated key value in the table. In some databases (such as PostgreSQL), it must be set when the primary key column is not the first column in the table. If there is more than one generated column, you can separate multiple attribute names with commas. |
| databaseId | If the database vendor ID (databaseIdProvider) is configured, MyBatis will load all statements without databaseId or matching the current databaseId; If there are statements with and without, the statements without will be ignored. |
<!--If the database supports self increment, you can use this method-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into user(user_name) values(#{userName})
</insert>
<!--If the database does not support self increment, the following method can be used for assignment query-->
<insert id="insertUser2" >
<selectKey order="BEFORE" keyProperty="id" resultType="integer">
select max(id)+1 from user
</selectKey>
insert into user(id,user_name) values(#{id},#{userName})
</insert>
2. select element
1. Parameter passing of select
<!--
When the query statement contains multiple parameters, if#{property name} can't get the specific value, so how should it be used?
Here is mybatis Parameter transfer method
1,If it is a single parameter,
Basic type: use#{write freely}
Reference types: Using#{property name of class}
2,Multiple parameters:
When multiple parameters are passed in during query, it cannot be simply passed#{parameter name} to get the value,
Only through arg0,arg1...perhaps param1,param2 And so on
The reason is, mybatis When multiple parameters are passed in, these parameters will be encapsulated into one map At this time map Medium key namely
arg0,arg1,param1,param2 These values, but obviously, the value transfer method is not very friendly, and there is no way to transfer values according to the name of the parameter
Get the specific value, so you can specify the value of the parameter in the following way key What is it?
Emp selectEmpByNoAndName(@Param("empno") Integer empno, @Param("ename") String ename);
That is, through@Param To specify deposit map Medium key What is the value
3,use map To pass parameters:
Still direct use#{key} to get the specific attribute value
-->
<select id="selectEmpByNoAndName" resultType="com.mashibing.bean.Emp">
select * from emp where empno=#{empno} and ename=#{ename}
</select>
<select id="selectEmpByNoAndName2" resultType="com.mashibing.bean.Emp">
select * from emp where empno=#{empno} and ename=#{ename}
</select>
2. Parameter value method
There are two ways to get values when writing sql statements in xml files, namely #{} and ${}. Let's take a look at the differences between them:
<!--
When used#When {} is used to obtain the value, you will find that the printed sql statement is as follows:
select * from emp where empno=? and ename=?
When used ${}When you get the value, you will find that it is printed sql The statement is as follows:
select * from emp where empno=7369 and ename='SMITH'
Through the case just now, we have found the existing problems,
use#{} method: the parameter precompiling method is adopted, and the position of the parameter is "0"? Instead, there will be no problem of sql injection
use ${}Value selection method: directly follow sql The way statements are spliced
Here you need to note that if our sql Some values in the statement do not support parameter precompiling, so they must be used ${}To get the value
-->
<select id="selectEmpByNoAndName" resultType="com.mashibing.bean.Emp">
select * from #{t} where empno=${empno} and ename=${ename}
</select>
3. Processing collection return results
EmpDao.xml
<!--When the result of the return value is a collection, the type of the return value is still the specific type in the collection-->
<select id="selectAllEmp" resultType="com.mashibing.bean.Emp">
select * from emp
</select>
<!--When querying, you can set the type of return value as map，When mybatis After the query is completed, the name of the column will be used as the key
The value of the column as value，Convert to map in
-->
<select id="selectEmpByEmpReturnMap" resultType="map">
select * from emp where empno = #{empno}
</select>
<!--Note that when the returned result is a collection object, the type of the returned value must be written into the collection value Type of
At the same time dao Method to add@MapKey Annotation to set key What is the result
@MapKey("empno")
Map<Integer,Emp> getAllEmpReturnMap();-->
<select id="getAllEmpReturnMap" resultType="com.mashibing.bean.Emp">
select * from emp
</select>
UserDao.java
package com.mashibing.dao;
import com.mashibing.bean.Emp;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpDao {
public Emp findEmpByEmpno(Integer empno);
public int updateEmp(Emp emp);
public int deleteEmp(Integer empno);
public int insertEmp(Emp emp);
Emp selectEmpByNoAndName(@Param("empno") Integer empno, @Param("ename") String ename,@Param("t") String tablename);
Emp selectEmpByNoAndName2(Map<String,Object> map);
List<Emp> selectAllEmp();
Map<String,Object> selectEmpByEmpReturnMap(Integer empno);
@MapKey("empno")
Map<Integer,Emp> getAllEmpReturnMap();
}
4. Custom result set -- resultMap
Dog.java
package com.mashibing.bean;
public class Dog {
private Integer id;
private String name;
private Integer age;
private String gender;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Dog{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
'}';
}
}
dog.sql
/*
Navicat MySQL Data Transfer
Source Server : node01
Source Server Version : 50729
Source Host : 192.168.85.111:3306
Source Database : demo
Target Server Type : MYSQL
Target Server Version : 50729
File Encoding : 65001
Date: 2020-03-24 23:54:22
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `dog`
-- ----------------------------
DROP TABLE IF EXISTS `dog`;
CREATE TABLE `dog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dname` varchar(255) DEFAULT NULL,
`dage` int(11) DEFAULT NULL,
`dgender` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of dog
-- ----------------------------
INSERT INTO dog VALUES ('1', 'chinese rhubarb', '1', 'male');
INSERT INTO dog VALUES ('2', 'Erhuang', '2', 'female');
INSERT INTO dog VALUES ('3', 'Sanhuang', '3', 'male');
DogDao.java
package com.mashibing.dao;
import com.mashibing.bean.Dog;
public interface DogDao {
public Dog selectDogById(Integer id);
}
DogDao.xml
<?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="com.mashibing.dao.DogDao">
<!--
in use mybatis When making a query, mybatis By default, it will help us encapsulate the results, but the column names and attribute names are required to correspond one by one
In the actual use process, we will find that sometimes the column names in the database do not correspond to the attribute names in our class one by one. At this time, we need to use aliases
There are two ways to implement aliasing:
1,In writing sql Add an alias when the statement
2,Custom encapsulated result set
-->
<!--Encapsulate the results according to the query data resultMap Property that represents the use of custom rules-->
<select id="selectDogById" resultMap="myDog">
select * from dog where id = #{id}
</select>
<!--Customize the result set and follow the data of each column javaBean Corresponding to the object properties of
type:Which one javaBean Object
id:Unique identification for reference by other attribute tags
-->
<resultMap id="myDog" type="com.mashibing.bean.Dog">
<!--
Specify the corresponding rule of the primary key column:
column: Represents a primary key column in a table
property:appoint javaBean Properties of
-->
<id column="id" property="id"></id>
<!--Set the correspondence of other columns-->
<result column="dname" property="name"></result>
<result column="dage" property="age"></result>
<result column="dgender" property="gender"></result>
</resultMap>
<!--Can be in sql Write alias in statement-->
<!-- <select id="selectDogById" resultType="com.mashibing.bean.Dog">
select id id,dname name,dage age,dgender gender from dog where id = #{id}
</select>-->
<!--In this way, no result can be found, because the attribute name does not correspond to the column name one by one-->
<!-- <select id="selectDogById" resultType="com.mashibing.bean.Dog">
select * from dog where id = #{id}
</select>-->
</mapper>
5. Joint query
emp.java
package com.mashibing.bean;
import java.util.Date;
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double common;
private Dept dept;
public Emp() {
}
public Emp(Integer empno, String ename) {
this.empno = empno;
this.ename = ename;
}
public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Double common, Dept dept) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.common = common;
this.dept = dept;
}
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Integer getMgr() {
return mgr;
}
public void setMgr(Integer mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public Double getCommon() {
return common;
}
public void setCommon(Double common) {
this.common = common;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", common=" + common +
", dept=" + dept +
'}';
}
}
Dept.java
package com.mashibing.bean;
public class Dept {
private Integer deptno;
private String dname;
private String loc;
public Dept() {
}
public Dept(Integer deptno, String dname, String loc) {
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
@Override
public String toString() {
return "Dept{" +
"deptno=" + deptno +
", dname='" + dname + '\'' +
", loc='" + loc + '\'' +
'}';
}
}
EmpDao.xml
<?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">
<!--namespace:Writing the full class name of the interface is to tell the specific implementation of which interface the configuration file is to be implemented-->
<mapper namespace="com.mashibing.dao.EmpDao">
<!--When querying again, sometimes you need to associate other objects, so you need to use association query
You can customize the result set as follows
-->
<select id="selectEmpAndDept" resultMap="empDept">
select * from emp left join dept on emp.deptno = dept.deptno where empno = #{empno};
</select>
<resultMap id="empDept" type="com.mashibing.bean.Emp">
<id column="empno" property="empno"></id>
<result column="ename" property="ename"></result>
<result column="job" property="job"></result>
<result column="mgr" property="mgr"></result>
<result column="hiredate" property="hiredate"></result>
<result column="sal" property="sal"></result>
<result column="comm" property="common"></result>
<result column="deptno" property="dept.deptno"></result>
<result column="dname" property="dept.dname"></result>
<result column="loc" property="dept.loc"></result>
</resultMap>
<!--stay mybatis It also provides a simple form to use association The label can be done
-->
<resultMap id="empDept" type="com.mashibing.bean.Emp">
<id column="empno" property="empno"></id>
<result column="ename" property="ename"></result>
<result column="job" property="job"></result>
<result column="mgr" property="mgr"></result>
<result column="hiredate" property="hiredate"></result>
<result column="sal" property="sal"></result>
<result column="comm" property="common"></result>
<association property="dept" javaType="com.mashibing.bean.Dept">
<id column="deptno" property="deptno"></id>
<result column="dname" property="dname"></result>
<result column="loc" property="loc"></result>
</association>
</resultMap>
</mapper>
Test
@Test
public void test08() {
//Get database session
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp emp = mapper.selectEmpAndDept(7369);
System.out.println(emp);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
6. Get collection elements
Dept.java
package com.mashibing.bean;
import java.util.List;
public class Dept {
private Integer deptno;
private String dname;
private String loc;
private List<Emp> emps;
public Dept() {
}
public Dept(Integer deptno, String dname, String loc) {
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public List<Emp> getEmps() {
return emps;
}
public void setEmps(List<Emp> emps) {
this.emps = emps;
}
@Override
public String toString() {
return "Dept{" +
"deptno=" + deptno +
", dname='" + dname + '\'' +
", loc='" + loc + '\'' +
", emps=" + emps +
'}';
}
}
DeptDao.java
package com.mashibing.dao;
import com.mashibing.bean.Dept;
import com.mashibing.bean.Emp;
import java.util.List;
public interface DeptDao {
public Dept getDeptAndEmps(Integer deptno);
}
DeptDao.xml
<?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="com.mashibing.dao.DeptDao">
<!--Define query collection elements-->
<select id="getDeptAndEmps" resultMap="deptEmp">
select * from dept left join emp on dept.deptno = emp.deptno where dept.deptno=#{deptno}
</select>
<resultMap id="deptEmp" type="com.mashibing.bean.Dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
<!--Encapsulates the elements of a collection class
property: Specifies the properties of the collection
ofType:Specifies the type of element in the collection
-->
<collection property="emps" ofType="com.mashibing.bean.Emp">
<id property="empno" column="empno"></id>
<result column="ename" property="ename"></result>
<result column="job" property="job"></result>
<result column="mgr" property="mgr"></result>
<result column="hiredate" property="hiredate"></result>
<result column="sal" property="sal"></result>
<result column="comm" property="common"></result>
</collection>
</resultMap>
</mapper>
Test
@Test
public void test09() {
//Get database session
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
DeptDao mapper = sqlSession.getMapper(DeptDao.class);
Dept deptAndEmps = mapper.getDeptAndEmps(10);
System.out.println(deptAndEmps);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
7. Step by step query
In the above logical query, we complete the association query of sql statements by ourselves. Can we ask mybatis to help us realize automatic association query?
Step by step of association query
DeptDao.java
package com.mashibing.dao;
import com.mashibing.bean.Dept;
import com.mashibing.bean.Emp;
import java.util.List;
public interface DeptDao {
public Dept getDeptAndEmps(Integer deptno);
public Dept getDeptAndEmpsBySimple(Integer deptno);
}
EmpDao.java
package com.mashibing.dao;
import com.mashibing.bean.Emp;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpDao {
Emp selectEmpAndDept(Integer empno);
Emp selectEmpAndDeptBySimple(Integer empno);
}
DeptDao.xml
<?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="com.mashibing.dao.DeptDao">
<select id="getDeptAndEmpsBySimple" resultType="com.mashibing.bean.Dept">
select * from dept where deptno = #{deptno}
</select>
</mapper>
EmpDao.xml
<?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="com.mashibing.dao.EmpDao">
<select id="selectEmpAndDeptBySimple" resultMap="simpleEmpAndDept">
select * from emp where empno = #{empno}
</select>
<resultMap id="simpleEmpAndDept" type="com.mashibing.bean.Emp">
<id column="empno" property="empno"></id>
<result column="ename" property="ename"></result>
<result column="job" property="job"></result>
<result column="mgr" property="mgr"></result>
<result column="hiredate" property="hiredate"></result>
<result column="sal" property="sal"></result>
<result column="comm" property="common"></result>
<association property="dept" select="com.mashibing.dao.DeptDao.getDeptAndEmpsBySimple" column="deptno">
</association>
</resultMap>
</mapper>
Test
@Test
public void test08() {
//Get database session
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
// Emp emp = mapper.selectEmpAndDept(7369);
Emp emp = mapper.selectEmpAndDeptBySimple(7369);
System.out.println(emp);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
Step by step query of collection
EmpDao.java
package com.mashibing.dao;
import com.mashibing.bean.Emp;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpDao {
Emp selectEmpAndDeptBySimple(Integer empno);
Emp selectEmpByStep(Integer empno);
}
DeptDao.java
package com.mashibing.dao;
import com.mashibing.bean.Dept;
import com.mashibing.bean.Emp;
import java.util.List;
public interface DeptDao {
public Dept getDeptAndEmps(Integer deptno);
public Dept getDeptAndEmpsBySimple(Integer deptno);
public Dept getDeptAndEmpsByStep(Integer deptno);
}
EmpDao.xml
<?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="com.mashibing.dao.EmpDao">
<select id="selectEmpByStep" resultType="com.mashibing.bean.Emp">
select * from emp where deptno = #{deptno}
</select>
</mapper>
DeptDao.xml
<?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="com.mashibing.dao.DeptDao">
<select id="getDeptAndEmpsByStep" resultMap="deptEmpByStep">
select * from dept where deptno = #{deptno}
</select>
<resultMap id="deptEmpByStep" type="com.mashibing.bean.Dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
<!--Encapsulates the elements of a collection class
property: Specifies the properties of the collection
ofType:Specifies the type of element in the collection
-->
<collection property="emps" ofType="com.mashibing.bean.Emp" select="com.mashibing.dao.EmpDao.selectEmpByStep" column="deptno">
</collection>
</resultMap>
</mapper>
Test
@Test
public void test09() {
//Get database session
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
DeptDao mapper = sqlSession.getMapper(DeptDao.class);
// Dept deptAndEmps = mapper.getDeptAndEmps(10);
Dept deptAndEmpsByStep = mapper.getDeptAndEmpsByStep(10);
System.out.println(deptAndEmpsByStep);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
8. Delayed query
When we are associating tables, we may not need the attribute value of the associated object when querying the results. At this time, the function can be realized by delaying loading. Add the following attributes to the global configuration file
mybatis-config.xml
<settings> <!--Turn on delay loading--> <setting name="lazyLoadingEnabled" value="true"/> </settings>
If global loading is set, but you want to not apply the delay policy when querying an sql statement, you can add the following attributes:
<association property="dept" select="com.mashibing.dao.DeptDao.getDeptAndEmpsBySimple" column="deptno" fetchType="eager"/>
3. Dynamic sql
Dynamic SQL is one of the powerful features of MyBatis. If you have used JDBC or other similar frameworks, you should be able to understand how painful it is to splice SQL statements according to different conditions. For example, when splicing, make sure you can't forget to add the necessary spaces and remove the comma of the last column name in the list. Using dynamic SQL, you can completely get rid of this pain.
Using dynamic SQL is not easy, 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 have used JSTL or any text processor based on XML like language before, you may feel deja vu about dynamic SQL elements. In previous versions of MyBatis, it took time to understand a large number of elements. With the powerful expression based on OGNL, MyBatis 3 replaces most of the previous elements and greatly simplifies the types of elements. Now there are fewer types of elements to learn than half of the original.
-
if
-
choose (when, otherwise)
-
trim (where, set)
-
foreach
1,if
EmpDao.xml
<select id="getEmpByCondition" resultType="com.mashibing.bean.Emp">
select * from emp where
<if test="empno!=null">
empno > #{empno} and
</if>
<if test="ename!=null">
ename like #{ename} and
</if>
<if test="sal!=null">
sal > #{sal}
</if>
</select>
EmpDao.java
public List<Emp> getEmpByCondition(Emp emp);
Test.java
@Test
public void test10() {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp emp = new Emp();
emp.setEmpno(6500);
emp.setEname("%E%");
emp.setSal(500.0);
List<Emp> empByCondition = mapper.getEmpByCondition(emp);
for (Emp emp1 : empByCondition) {
System.out.println(emp1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
It seems that the test is normal, but you should pay attention to what happens if the parameter values we pass in are missing? At this time, the spliced sql statement will become problematic. For example, if the parameter is not passed or the last parameter is lost, there will be one more keyword of where or and in the statement. Therefore, a specific solution is also given in mybatis:
The where element inserts the "where" clause only if the child element returns anything. Moreover, if the clause starts with "AND" OR ", the where element will also remove them.
<select id="getEmpByCondition" resultType="com.mashibing.bean.Emp">
select * from emp
<where>
<if test="empno!=null">
empno > #{empno}
</if>
<if test="ename!=null">
and ename like #{ename}
</if>
<if test="sal!=null">
and sal > #{sal}
</if>
</where>
</select>
There seems to be no problem now, but our conditions are added before and after the splicing of sql statements, so how should we deal with it?
<!--
trim Intercept string:
prefix: Prefix, as sql Add a prefix as a whole
prefixOverrides:Remove redundant characters in front of the whole string
suffixOverrides:Remove the following redundant string
-->
<select id="getEmpByCondition" resultType="com.mashibing.bean.Emp">
select * from emp
<trim prefix="where" prefixOverrides="and" suffixOverrides="and">
<if test="empno!=null">
empno > #{empno} and
</if>
<if test="ename!=null">
ename like #{ename} and
</if>
<if test="sal!=null">
sal > #{sal} and
</if>
</trim>
</select>
2,foreach
Another common use scenario for dynamic SQL is traversal of collections (especially when building IN conditional statements).
<!--foreach Is to traverse the collection
collection="deptnos" Specifies the collection to traverse
close="" What does it end with
index="" Given an index value
item="" The value of each element traversed
open="" What does it start with
separator="" Separator representing multiple elements
-->
<select id="getEmpByDeptnos" resultType="Emp">
select * from emp where deptno in
<foreach collection="deptnos" close=")" index="idx" item="deptno" open="(" separator=",">
#{deptno}
</foreach>
</select>
3,choose
Sometimes, we don't want to use all the conditions, but just want to choose one from multiple conditions. In this case, MyBatis provides the choose element, which is a bit like the switch statement in Java.
<select id="getEmpByConditionChoose" resultType="com.mashibing.bean.Emp">
select * from emp
<where>
<choose>
<when test="empno!=null">
empno > #{empno}
</when>
<when test="ename!=null">
ename like #{ename}
</when>
<when test="sal!=null">
sal > #{sal}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
4,set
A similar solution for dynamically updating statements is called set. The set element can be used to dynamically include columns that need to be updated and ignore other columns that do not need to be updated.
<update id="updateEmpByEmpno">
update emp
<set>
<if test="empno!=null">
empno=#{empno},
</if>
<if test="ename!=null">
ename = #{ename},
</if>
<if test="sal!=null">
sal = #{sal}
</if>
</set>
<where>
empno = #{empno}
</where>
</update>
4. Cache
MyBatis has built-in a powerful transactional query caching mechanism, which can be easily configured and customized. In order to make it more powerful and easy to configure, we have made many improvements to the cache implementation in MyBatis 3.
By default, only local session caching is enabled, which only caches the data in one session. To enable global L2 caching, just add a line to your SQL mapping file:
<cache/>
When this label is added, it will have the following effects:
-
The results of all select statements in the mapping statement file will be cached.
-
All insert, update, and delete statements in the mapping statement file flush the cache.
-
The cache will use the least recently used (LRU) algorithm to clear the unnecessary cache.
-
The cache is not refreshed regularly (that is, there is no refresh interval).
-
The cache holds 1024 references to a list or object, regardless of what the query method returns.
-
The cache is treated as a read / write cache, which means that the acquired object is not shared and can be safely modified by the caller without interfering with potential modifications made by other callers or threads.
When configuring, it is also divided into L1 cache and L2 cache:
L1 cache: thread level cache, which is local cache and sqlSession level cache
L2 cache: global cache, not limited to the current session
1. Use of L1 cache
The first level cache is the sqlsession level cache, which exists by default. In the following case, we found that I sent two identical requests, but the sql statement was executed only once, which means that the results have been cached at the first query.
@Test
public void test01() {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
List<Emp> list = mapper.selectAllEmp();
for (Emp emp : list) {
System.out.println(emp);
}
System.out.println("--------------------------------");
List<Emp> list2 = mapper.selectAllEmp();
for (Emp emp : list2) {
System.out.println(emp);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
In most cases, L1 cache is OK, but there are several special cases that will cause L1 cache invalidation:
1. The first level cache is the sqlsession level cache. If only multiple sqlsessions are opened in the application, the cache will become invalid
@Test
public void test02(){
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
List<Emp> list = mapper.selectAllEmp();
for (Emp emp : list) {
System.out.println(emp);
}
System.out.println("================================");
SqlSession sqlSession2 = sqlSessionFactory.openSession();
EmpDao mapper2 = sqlSession2.getMapper(EmpDao.class);
List<Emp> list2 = mapper2.selectAllEmp();
for (Emp emp : list2) {
System.out.println(emp);
}
sqlSession.close();
sqlSession2.close();
}
2. When writing sql statements for queries, you must pay attention to the parameters passed. If the parameters are inconsistent, the results will not be cached
3. If the data is modified during sending, the results will not be cached
@Test
public void test03(){
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp empByEmpno = mapper.findEmpByEmpno(1111);
System.out.println(empByEmpno);
System.out.println("================================");
empByEmpno.setEname("zhangsan");
int i = mapper.updateEmp(empByEmpno);
System.out.println(i);
System.out.println("================================");
Emp empByEmpno1 = mapper.findEmpByEmpno(1111);
System.out.println(empByEmpno1);
sqlSession.close();
}
4. During two queries, manually emptying the cache will also invalidate the cache
@Test
public void test03(){
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp empByEmpno = mapper.findEmpByEmpno(1111);
System.out.println(empByEmpno);
System.out.println("================================");
System.out.println("Manually empty the cache");
sqlSession.clearCache();
System.out.println("================================");
Emp empByEmpno1 = mapper.findEmpByEmpno(1111);
System.out.println(empByEmpno1);
sqlSession.close();
}
2. L2 cache
L2 cache is a global scope cache. It is not enabled by default and needs to be configured manually.
Mybatis provides the interface and implementation of the secondary cache. When the cache is implemented, the entity class is required to implement the Serializable interface. The secondary cache will not take effect until the sqlSession is closed or submitted.
1. Use of cache
Steps:
1. Add the following configuration to the global configuration file:
<setting name="cacheEnabled" value="true"/>
2. Labels need to be used in mapping files that use L2 cache
3. Entity classes must implement the Serializable interface
@Test
public void test04(){
SqlSession sqlSession = sqlSessionFactory.openSession();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
EmpDao mapper2 = sqlSession2.getMapper(EmpDao.class);
Emp empByEmpno = mapper.findEmpByEmpno(1111);
System.out.println(empByEmpno);
sqlSession.close();
Emp empByEmpno1 = mapper2.findEmpByEmpno(1111);
System.out.println(empByEmpno1);
sqlSession2.close();
}
2. Cached properties
Occurrence: indicates the cache recycling policy. The default is LRU
LRU: the least recently used object and the object that has not been used for the longest time is removed
FIFO: first in first out. Objects are removed in the order they enter the cache
SOFT: SOFT reference, which removes objects based on garbage collector status and SOFT reference rules
WEAK: WEAK references, which more actively remove objects based on garbage collector status and WEAK reference rules
Flush internal: refresh interval, in milliseconds
The default is not set, that is, there is no refresh interval. The cache is only refreshed when the statement is called
size: number of references, positive integer
Represents the maximum number of objects that can be stored in the cache. Too large can easily lead to memory overflow
readonly: read only, true/false
true: read only cache, which will return the same instance of cache objects to all calls, so these objects cannot be modified.
false: reading and writing cache will return a copy of the cache object (serialization Implementation). This method is relatively safe. The default value is
//You can see that you will look for data in the L2 cache, and there will be no data in the L2 cache and the L1 cache at the same time, because the data in the L2 cache will not take effect until sqlsession is closed
@Test
public void test05(){
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp empByEmpno = mapper.findEmpByEmpno(1111);
System.out.println(empByEmpno);
sqlSession.close();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
EmpDao mapper2 = sqlSession2.getMapper(EmpDao.class);
Emp empByEmpno2 = mapper2.findEmpByEmpno(1111);
System.out.println(empByEmpno2);
Emp empByEmpno3 = mapper2.findEmpByEmpno(1111);
System.out.println(empByEmpno3);
sqlSession2.close();
}
//The order of cache query is to query the L2 cache first and then the L1 cache
@Test
public void test05(){
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp empByEmpno = mapper.findEmpByEmpno(1111);
System.out.println(empByEmpno);
sqlSession.close();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
EmpDao mapper2 = sqlSession2.getMapper(EmpDao.class);
Emp empByEmpno2 = mapper2.findEmpByEmpno(1111);
System.out.println(empByEmpno2);
Emp empByEmpno3 = mapper2.findEmpByEmpno(1111);
System.out.println(empByEmpno3);
Emp empByEmpno4 = mapper2.findEmpByEmpno(7369);
System.out.println(empByEmpno4);
Emp empByEmpno5 = mapper2.findEmpByEmpno(7369);
System.out.println(empByEmpno5);
sqlSession2.close();
}
3. Scope of L2 cache:
If the global L2 cache configuration is set, it should be noted that in each individual select statement, you can set to turn off the query cache to complete special settings
1. Setting in setting is to configure the L2 cache to be enabled. The L1 cache is always enabled by default
<setting name="cacheEnabled" value="true"/>
2. useCache attribute of select tag:
In each select ed query, you can set whether to use L2 cache for the current query, which is only valid for L2 cache
3. flushCache attribute of sql tag
After the query is emptied, the default value is true and false, and the default value is to delete the first level cache
4,sqlSession.clearCache()
It's just used to clear the L1 cache
3. Consolidate third-party cache
In some cases, we can also customize the implementation cache or create adapters for other third-party caching schemes to completely override the caching behavior.
1. Import the corresponding maven dependency
<!-- https://mvnrepository.com/artifact/org.ehcache/ehcache --> <dependency> <groupId>org.ehcache</groupId> <artifactId>ehcache</artifactId> <version>3.8.1</version> </dependency> <!-- https://mvnrepository.com/artifact/org.mybatis.caches/mybatis-ehcache --> <dependency> <groupId>org.mybatis.caches</groupId> <artifactId>mybatis-ehcache</artifactId> <version>1.2.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>2.0.0-alpha1</version> </dependency> <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-log4j12 --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>2.0.0-alpha1</version> <scope>test</scope> </dependency>
2. Import ehcache configuration file
<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd">
<!-- Disk save path -->
<diskStore path="D:\ehcache" />
<defaultCache
maxElementsInMemory="1"
maxElementsOnDisk="10000000"
eternal="false"
overflowToDisk="true"
timeToIdleSeconds="120"
timeToLiveSeconds="120"
diskExpiryThreadIntervalSeconds="120"
memoryStoreEvictionPolicy="LRU">
</defaultCache>
</ehcache>
<!--
Attribute description:
l diskStore: Specifies where data is stored on disk.
l defaultCache: When with the help of CacheManager.add("demoCache")establish Cache When, EhCache Will adopt<defalutCache/>Specified management policy
The following attributes are required:
l maxElementsInMemory - Cached in memory element Maximum number of
l maxElementsOnDisk - Cached on disk element The maximum number of. If 0, it means infinity
l eternal - Set cached elements Whether it will never expire. If yes true，The cached data is always valid if false Then according to timeToIdleSeconds，timeToLiveSeconds judge
l overflowToDisk - Sets whether the memory cache will expire when it overflows element Cache to disk
The following properties are optional:
l timeToIdleSeconds - When buffer exists EhCache The data in has been accessed for more than two times timeToIdleSeconds These data will be deleted when the value of the attribute is taken. The default value is 0,That is, the idle time is infinite
l timeToLiveSeconds - cache element The default is 0.,that is element Infinite survival time
diskSpoolBufferSizeMB This parameter setting DiskStore(Disk cache)Cache size for.The default is 30 MB.each Cache Each should have its own buffer.
l diskPersistent - stay VM Whether to enable disk saving when restarting EhCache The default value is false.
l diskExpiryThreadIntervalSeconds - The running interval of the disk cache cleanup thread is 120 seconds by default. Each 120 s，The corresponding thread will run once EhCache Data cleaning in
l memoryStoreEvictionPolicy - When the memory cache reaches the maximum, there is a new one element When I joined, Remove from cache element Strategy for. Default is LRU(Least recently used), optional LFU(Least frequently used) and FIFO(First in first out)
-->
3. Add custom cache in mapper file
<cache type="org.mybatis.caches.ehcache.EhcacheCache"></cache>
Small partners who think the article is good can pay attention to the blogger and continuously update the technical good article in the follow-up!