本期内容目录:
动态SQL
[!note]
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
动态SQL的标签包含:
if
choose (when, otherwise)
trim (where, set)
foreach
if
<select id="selectEmpByCondition" resultType="Emp"> select * from emp where <if test="empno!=null"> empno = #{empno} and if> <if test="ename!=null"> ename = #{ename} if> select>
以上动态sql当有一个字段没传入时就会报错,此时需要使用where标签,该标签在没有传入字段值时会将未传入字段前面的and或者or去掉:
<select id="selectEmpByCondition1" resultType="Emp"> select * from emp <where> <if test="empno!=null"> empno = #{empno} if> <if test="ename!=null"> and ename = #{ename} if> where> select>
DAO层接口:
public Emp selectEmpByCondition1(Emp emp);
测试方法:
@Test public void test_selectEmpByCondition1(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); Emp emp = new Emp();// emp.setEmpno(2222); emp.setEname("lisi"); Emp emp2 = mapper.selectEmpByCondition1(emp); System.out.println(emp2); sqlSession.close(); }
测试结果:
DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.0DEBUG [main] - ==> Preparing: select * from emp WHERE ename = ?DEBUG [main] - ==> Parameters: lisi(String)DEBUG [main] - <== Total: 1Emp{empno=5555, ename='lisi', job='null', mgr=null, hiredate=null, sal=null, comm=null, dept=null}
choose
只能选择其中一个满足条件的when子句执行
<select id="selectEmpByCondition2" resultType="Emp"> select * from emp <where> <choose> <when test="empno!=null"> empno = #{empno} when> <when test="ename!=null"> ename = #{ename} when> <otherwise> 1=1 otherwise> choose> where> select>
DAO层接口:
public Emp selectEmpByCondition2(Emp emp);
测试方法:
@Test public void test_selectEmpByCondition2(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); Emp emp = new Emp(); emp.setEmpno(2222); emp.setEname("lisi"); Emp emp2 = mapper.selectEmpByCondition2(emp); System.out.println(emp2); sqlSession.close(); }
测试结果:可以看出以上两个查询条件只会选择第一个去进行查询
DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.0DEBUG [main] - ==> Preparing: select * from emp WHERE empno = ?DEBUG [main] - ==> Parameters: 2222(Integer)DEBUG [main] - <== Total: 1Emp{empno=2222, ename='hhh', job='null', mgr=null, hiredate=null, sal=1500.0, comm=null, dept=null}
trim
trim截取字符串,用来自定义where元素的格式,和where元素的功能等价。 prefix:为sql语句整体添加一个前缀 prefixOverrides:去除整体sql语句前面多余的字符串 suffixOverrides:去除整体sql语句后面多余的字符串
<select id="selectEmpByCondition3" resultType="Emp"> select * from emp <trim prefix="where" prefixOverrides="and" suffixOverrides="and | or"> <if test="empno!=null"> empno = #{empno} and if> <if test="ename!=null"> ename = #{ename} or if> trim> select>
DAO层接口:
public Emp selectEmpByCondition3(Emp emp);
测试方法:
@Test public void test_selectEmpByCondition3(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); Emp emp = new Emp(); emp.setEmpno(2222); emp.setEname("lisi"); Emp emp2 = mapper.selectEmpByCondition3(emp); System.out.println(emp2); sqlSession.close(); }
测试结果:
DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.0DEBUG [main] - ==> Preparing: select * from emp where empno = ? and ename = ?DEBUG [main] - ==> Parameters: 2222(Integer), lisi(String)DEBUG [main] - <== Total: 0null
foreach
foreach:遍历集合中的元素 collection:指定要遍历的集合 separator:分隔符 open:以什么开始 close:以什么结束 item:遍历过程中每一个元素值 index:索引
<select id="selectEmpByDeptnos" resultType="Emp"> select * from emp where deptno in <foreach collection="deptnos" separator="," open="(" item="deptno" index="idx" close=")"> #{deptno} foreach> select>
DAO层接口:
public List<Emp> selectEmpByDeptnos(@Param("deptnos") List<Integer> deptnos);
测试方法:
@Test public void test_selectEmpByDeptnos(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); List<Emp> emps = mapper.selectEmpByDeptnos(Arrays.asList(1, 2)); for(Emp emp : emps){ System.out.println(emp); } sqlSession.close(); }
测试结果:
DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.0DEBUG [main] - ==> Preparing: select * from emp where deptno in ( ? , ? )DEBUG [main] - ==> Parameters: 1(Integer), 2(Integer)DEBUG [main] - <== Total: 5Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=null}Emp{empno=2222, ename='hhh', job='null', mgr=null, hiredate=null, sal=1500.0, comm=null, dept=null}Emp{empno=3333, ename='wangwu', job='null', mgr=null, hiredate=null, sal=4000.0, comm=null, dept=null}Emp{empno=4444, ename='maliu', job='null', mgr=null, hiredate=null, sal=8000.0, comm=null, dept=null}Emp{empno=5555, ename='lisi', job='null', mgr=null, hiredate=null, sal=null, comm=null, dept=null}
缓存
MyBatis缓存机制: 如果没有缓存,那么每次查询的时候都需要从数据库中加载数据,这会造成io的性能问题。所以,在很多情况下连续执行两条相同的sql语句,可以直接从缓存中获取,如果获取不到,那么再去查询数据库,这意味着查询完成的结果需要放到缓存中。
一级缓存
表示SqlSession级别的缓存,默认开启。每次查询的时候会开启一个会话,此会话相当于一次连接,关闭之后自动失效。
测试方法:
@Test public void test_selectEmpByEmpno_3(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); Emp emp = mapper.selectEmpByEmpno(1111); System.out.println(emp); Emp emp1 = mapper.selectEmpByEmpno(1111); System.out.println(emp1); }
测试结果:可以看到第二次查询相同记录时并没有执行sql去数据库查询,而是去SqlSession中取出记录
DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}
!> 缓存失效的情况:
a.在同一个方法中可能开启多个SqlSession会话,此时缓存将失效。会话跟方法没有关系,缓存的数据是保存在各自的SqlSession中的。
测试方法:
@Test public void test_selectEmpByEmpno_4(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); Emp emp = mapper.selectEmpByEmpno(1111); System.out.println(emp); SqlSession sqlSession1 = sqlSessionFactory.openSession(); EmpDao mapper1 = sqlSession1.getMapper(EmpDao.class); Emp emp1 = mapper1.selectEmpByEmpno(1111); System.out.println(emp1); }
测试结果:可以看到开启了两个SqlSession后再去查询相同的记录时,第二层仍会执行SQL去数据库查询,代表缓存已失效。
DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', empsnull}}
b.当传递对象时,如果对象中的属性值不同,缓存将失效
测试方法:
Emp emp = new Emp(); @Test public void test_selectEmpByEmpno_5(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); emp.setEmpno(1111); Emp emp1 = mapper.selectEmpByCondition1(emp); System.out.println(emp1); emp.setEmpno(2222); Emp emp2 = mapper.selectEmpByCondition1(emp); System.out.println(emp2); sqlSession.close(); }
测试结果:
DEBUG [main] - ==> Preparing: select * from emp WHERE empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=null}DEBUG [main] - ==> Preparing: select * from emp WHERE empno = ?DEBUG [main] - ==> Parameters: 2222(Integer)DEBUG [main] - <== Total: 1Emp{empno=2222, ename='hhh', job='null', mgr=null, hiredate=null, sal=1500.0, comm=null, dept=null}
c.当在一个会话中如果修改了数据,那么缓存将失效
测试方法:
@Test public void test_selectEmpByEmpno_6(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); Emp emp = mapper.selectEmpByEmpno(1111); System.out.println(emp); System.out.println("======================="); emp.setEname("hhhh"); Integer update = mapper.update(emp); System.out.println(update); System.out.println("========================"); emp = mapper.selectEmpByEmpno(1111); System.out.println(emp); sqlSession.close(); }
测试结果:
DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}=======================DEBUG [main] - ==> Preparing: update emp set ename = ? where empno = ?DEBUG [main] - ==> Parameters: hhhh(String), 1111(Integer)DEBUG [main] - <== Updates: 11========================DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='hhhh', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}
d.如果在一个会话过程中,手动清空了缓存,那么缓存将失效
测试方法:
@Test public void test_selectEmpByEmpno_7(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); Emp emp = mapper.selectEmpByEmpno(1111); System.out.println(emp); //清空缓存 sqlSession.clearCache(); System.out.println("========================"); emp = mapper.selectEmpByEmpno(1111); System.out.println(emp); sqlSession.close(); }
测试结果:
DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}========================DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}
二级缓存
二级缓存是全局范围的缓存,SqlSession关闭之后才会生效。默认是不开启的,如果需要开启则进行如下配置:a.全局配置文件mybatis-config.xml中:
<settings> <setting name="cacheEnabled" value="true"/> settings>
b.指定在哪个映射文件中使用缓存,那么就在哪个映射文件中添加cache标签
如映射文件EmpDao.xml中的根标签mapper中:
<cache>cache>
c.对应的java实体类必须要实现序列化的接口Serializable。
如在实体类Emp.java中:
package com.fan.bean;import java.io.Serializable;import java.util.Date;public class Emp implements Serializable { private Integer empno; private String ename; 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; }}
[!tip]
使用二级缓存时可包含多个属性值(即在cache标签中的属性)
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
eviction:缓存淘汰机制,包括:
LRU:最近最少使用
FIFO:先进先出,按照添加缓存的顺序来移除缓存
SOFT:软引用:基于垃圾回收器状态和软引用规则移除对象。
WEAK: 弱引用:更积极地基于垃圾收集器状态和弱引用规则移除对象。
flushInterval:设置多长时间进行缓存刷新,单位为毫秒
size:引用的条数,是一个正整数,缓存中可以存储多少个对象,一般不设置,设置太大的话可能导致内存溢出
readonly:只读属性,值为true或false
true:只读缓存,会给所有的调用的方法返回该对象的实例,不安全
false:读写缓存,只是返回缓存对象的拷贝,比较安全
[!attention|label:问题一]一级缓存和二级缓存有没有可能同时存在数据?
不会同时存在,因为二级缓存生效的时候,是在一级缓存sqlSession关闭的时候
[!attention|label:问题二]当查询数据的时候,是先查询以及缓存还是先查询二级缓存?
在开启了二级缓存的情况下,先查询二级缓存,再查询一级缓存
测试方法:在二级缓存开启了的情况下
@Test public void test_selectEmpByEmpno_8(){ SqlSession sqlSession = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); Emp emp = mapper.selectEmpByEmpno(1111); System.out.println(emp); sqlSession.close(); SqlSession sqlSession1 = sqlSessionFactory.openSession(); EmpDao mapper1 = sqlSession1.getMapper(EmpDao.class); Emp emp1 = mapper1.selectEmpByEmpno(1111); System.out.println(emp1); Emp emp2 = mapper1.selectEmpByEmpno(1111); System.out.println(emp2); Emp emp3 = mapper1.selectEmpByEmpno(2222); System.out.println(emp3); Emp emp4 = mapper1.selectEmpByEmpno(2222); System.out.println(emp4); sqlSession1.close(); }
测试结果:可以看出二级缓存的输出结果多了一个命中率的参数Cache Hit Ratio [com.fan.dao.EmpDao]: 0.5,代表两次查询中命中率为0.5,即两次相同的记录查询中第二次使用了二级缓存
DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.0DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}} WARN [main] - As you are using functionality that deserializes object streams, it is recommended to define the JEP-290 serial filter. Please refer to https://docs.oracle.com/pls/topic/lookup?ctx=javase15&id=GUID-8296D8E8-2B93-4B9A-856E-0A65AF9B8C66DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.5Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.6666666666666666Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.5DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 2222(Integer)DEBUG [main] - <== Total: 1Emp{empno=2222, ename='hhh', job='null', mgr=null, hiredate=null, sal=1500.0, comm=null, dept=Dept{deptno=2, dname='信息', loc='null', emps=null}}DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.4Emp{empno=2222, ename='hhh', job='null', mgr=null, hiredate=null, sal=1500.0, comm=null, dept=Dept{deptno=2, dname='信息', loc='null', emps=null}}
第三方缓存
第三方缓存:继承第三方的组件,来充当缓存的作用,如ehcache等。
[!tip]
MyBatis的github官方仓库中介绍了很多关于MyBatis的插件,如缓存插件ehcache-cache、反向生成插件generator等,每一个插件的介绍都提供了相关的网站。
ehcache-cache插件
使用方式:
首先,引入相应的依赖jar包
<dependency> <groupId>org.mybatis.cachesgroupId> <artifactId>mybatis-ehcacheartifactId> <version>1.2.1version> dependency>
为了打印该插件的输出日志,还需要引入
<dependency> <groupId>org.slf4jgroupId> <artifactId>slf4j-apiartifactId> <version>2.0.0-alpha1version> dependency> <dependency> <groupId>org.slf4jgroupId> <artifactId>slf4j-log4j12artifactId> <version>2.0.0-alpha1version> <scope>testscope> dependency>
其次,需要在resource目录下配置一个名为 ehcache.xml的配置文件
<?xml version="1.0" encoding="UTF-8"?><ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"> <diskStore path="D:\ehcache" /> <defaultCache maxElementsInMemory="1" maxElementsOnDisk="10000000" eternal="false" overflowToDisk="true" timeToIdleSeconds="120" timeToLiveSeconds="120" diskExpiryThreadIntervalSeconds="120" memoryStoreEvictionPolicy="LRU"> defaultCache>ehcache>
最后,在想要使用该插件的映射文件中进行配置
<cache type="org.mybatis.caches.ehcache.EhcacheCache">cache>
之后便可以使用了,输出日志和二级缓存一样没任何差别。
测试方法:
@Test public void test_sqlSessionFactory(){ SqlSession sqlSession = sqlSessionFactory.openSession(); SqlSession sqlSession1 = sqlSessionFactory.openSession(); EmpDao mapper = sqlSession.getMapper(EmpDao.class); EmpDao mapper1 = sqlSession1.getMapper(EmpDao.class); Emp emp = mapper.selectEmpByEmpno(1111); System.out.println(emp); sqlSession.close();//关闭会话意味着一级缓存也将关闭 System.out.println("============"); Emp emp1 = mapper1.selectEmpByEmpno(1111); System.out.println(emp1); sqlSession1.close(); }
测试结果:
DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.0DEBUG [main] - ==> Preparing: select * from emp left join dept on emp.deptno = dept.deptno where empno = ?DEBUG [main] - ==> Parameters: 1111(Integer)DEBUG [main] - <== Total: 1Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}============DEBUG [main] - Cache Hit Ratio [com.fan.dao.EmpDao]: 0.5Emp{empno=1111, ename='zhangsan', job='null', mgr=null, hiredate=null, sal=6666.67, comm=null, dept=Dept{deptno=1, dname='软件', loc='null', emps=null}}
以上内容已同步到个人博客: https://bfanfanfan.gitee.io/fanfanfan
Ⓜ️ 我是一只小白猿,下期间!