1 Mybaits--动态SQL
- 动态SQL是Mybatis强大特性之一。极大的简化我们拼装SQL的操作。
- 动态SQL元素和使用JSTL或其他类似基于XML的文本处理器相似。
- Mybatis采用功能强大的基于OGNL的表达式来简化操作。
- if
- choose(when ,otherwise)
- trim(where,set)
- foreach
1.1 动态SQL之if
package cn.demo2; /** * 描述:POJO */ public class Employee { private Integer id; private String lastName; private String gender; private String email; private Department department; public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } @Override public String toString() { return "Employee{" + "email='" + email + '\'' + ", gender='" + gender + '\'' + ", lastName='" + lastName + '\'' + ", id=" + id + '}'; } }
package cn.demo2; import java.util.List; public interface IEmployeeDAO { public List<Employee> getConditionIf(Employee employee); }
<?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="cn.demo2.IEmployeeDAO"> <select id="getConditionIf" resultType="cn.demo2.Employee" > SELECT id,last_name lastName,email,gender from employee WHERE <if test="id != null"> id = #{id} </if> <if test="lastName != null and lastName != ''"> and last_name like #{lastName} </if> <if test="email != null and email.trim() != ''"> and email = #{email} </if> <if test="gender == '男' or gender == '女'"> and gender = #{gender} </if> </select> </mapper>
package cn.test; import cn.demo2.Employee; import cn.demo2.IEmployeeDAO; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MybatisTest2 { /** * 测试查询 * @throws IOException */ @Test public void demo1() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession session = sqlSessionFactory.openSession(); IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class); Employee employee = new Employee(); employee.setId(5); List<Employee> employees = dao.getConditionIf(employee); System.out.print(employees); session.close(); } }
1.2 动态SQL之where
- 如果上面的程序中的id没有传入,那么拼接的SQL会多出and。
- 解决方案一:在where之后加上1=1。
- 解决方案二:Mybatis推荐使用where标签来将所有的查询条件包括在内,Mybatis就会将where标签中多出的and或or删除自动删除。并且where标签只会去掉第一个多出的and或or等。
package cn.demo2; /** * 描述:POJO */ public class Employee { private Integer id; private String lastName; private String gender; private String email; private Department department; public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } @Override public String toString() { return "Employee{" + "email='" + email + '\'' + ", gender='" + gender + '\'' + ", lastName='" + lastName + '\'' + ", id=" + id + '}'; } }
<?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="cn.demo2.IEmployeeDAO"> <select id="getConditionIf" resultType="cn.demo2.Employee" > SELECT id,last_name lastName,email,gender from employee <where> <if test="id != null"> id = #{id} </if> <if test="lastName != null and lastName != ''"> and last_name like #{lastName} </if> <if test="email != null and email.trim() != ''"> and email = #{email} </if> <if test="gender == '男' or gender == '女'"> and gender = #{gender} </if> </where> </select> </mapper>
package cn.test; import cn.demo2.Employee; import cn.demo2.IEmployeeDAO; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MybatisTest2 { /** * 测试查询 * @throws IOException */ @Test public void demo1() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession session = sqlSessionFactory.openSession(); IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class); Employee employee = new Employee(); employee.setLastName("%呵%"); List<Employee> employees = dao.getConditionIf(employee); System.out.print(employees); session.close(); } }
1.2 动态SQL之trim
package cn.demo2; /** * 描述:POJO */ public class Employee { private Integer id; private String lastName; private String gender; private String email; private Department department; public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } @Override public String toString() { return "Employee{" + "email='" + email + '\'' + ", gender='" + gender + '\'' + ", lastName='" + lastName + '\'' + ", id=" + id + '}'; } }
package cn.demo2; import java.util.List; public interface IEmployeeDAO { public List<Employee> getConditionIf(Employee employee); }
<?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="cn.demo2.IEmployeeDAO"> <select id="getConditionIf" resultType="cn.demo2.Employee" > SELECT id,last_name lastName,email,gender from employee <!-- where标签职能消除前面多余的and或者or之类的语句,而如果and或or在之后,那么where就无能为力了 trim可以来解决这样的问题 prefix表示在拼接的SQL之前加前缀 prefixOverrides是将前面多余的and或or等消除 suffix和prefix相反 prefixOverrides和prefixOverrides相反 --> <trim prefix="where" suffixOverrides="and"> <if test="id != null"> id = #{id} and </if> <if test="lastName != null and lastName != ''"> last_name like #{lastName} and </if> <if test="email != null and email.trim() != ''"> email = #{email} and </if> <if test="gender == '男' or gender == '女'"> and gender = #{gender} </if> </trim> </select> </mapper>
package cn.test; import cn.demo2.Employee; import cn.demo2.IEmployeeDAO; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MybatisTest2 { /** * 测试查询 * @throws IOException */ @Test public void demo1() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession session = sqlSessionFactory.openSession(); IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class); Employee employee = new Employee(); employee.setLastName("%呵%"); List<Employee> employees = dao.getConditionIf(employee); System.out.print(employees); session.close(); } }
1.3 动态SQL之分支查询(和Java中的switch--case相似)
package cn.demo2; /** * 描述:POJO */ public class Employee { private Integer id; private String lastName; private String gender; private String email; private Department department; public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } @Override public String toString() { return "Employee{" + "email='" + email + '\'' + ", gender='" + gender + '\'' + ", lastName='" + lastName + '\'' + ", id=" + id + '}'; } }
package cn.demo2; import java.util.List; public interface IEmployeeDAO { public List<Employee> getConditionIf(Employee employee); }
<?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="cn.demo2.IEmployeeDAO"> <select id="getConditionIf" resultType="cn.demo2.Employee" > SELECT id,last_name lastName,email,gender from employee <where> <choose> <when test="id != null"> id = #{id} </when> <when test="lastName != null"> last_name like #{lastName} </when> <when test="email != null"> email = #{email} </when> <otherwise> gender = #{gender} </otherwise> </choose> </where> </select> </mapper>
package cn.test; import cn.demo2.Employee; import cn.demo2.IEmployeeDAO; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MybatisTest2 { /** * 测试查询 * @throws IOException */ @Test public void demo1() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession session = sqlSessionFactory.openSession(); IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class); Employee employee = new Employee(); employee.setLastName("%呵%"); List<Employee> employees = dao.getConditionIf(employee); System.out.print(employees); session.close(); } }
1.4 动态SQL之更新
public void updateEmployee(Employee employee);
<update id="updateEmployee" > update employee <set> <if test="email != null"> email = #{email}, </if> <if test="lastName != null"> last_name = #{lastName} </if> <if test="gender != null"> gender = #{gender} </if> </set> where id = #{id} </update>
@Test public void demo1() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession session = sqlSessionFactory.openSession(); IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class); Employee employee = new Employee(); employee.setId(5); employee.setLastName("哈哈"); dao.updateEmployee(employee); session.commit(); session.close(); }
1.5 动态SQL--forEach
public List<Employee> getEmpsByConditionForEach(List<Integer> ids);
<select id="getEmpsByConditionForEach" resultType="cn.demo2.Employee" > select * from employee where id IN <foreach collection="list" item="item_id" separator="," open="(" close=")" > #{item_id} </foreach> </select>
/** * 测试查询 * @throws IOException */ @Test public void demo1() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession session = sqlSessionFactory.openSession(); IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class); dao.getEmpsByConditionForEach(Arrays.asList(5,6,7)); session.close(); }
1.6 动态SQL--MySQL批量插入
- MySQL支持insert into 表名 values(),(),()。
package cn.demo2; /** * 描述:POJO */ public class Employee { private Integer id; private String lastName; private String gender; private String email; private Department department; public Employee(Integer id, Department department, String lastName, String gender, String email) { this.id = id; this.department = department; this.lastName = lastName; this.gender = gender; this.email = email; } public Employee() { } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } @Override public String toString() { return "Employee{" + "email='" + email + '\'' + ", gender='" + gender + '\'' + ", lastName='" + lastName + '\'' + ", id=" + id + '}'; } }
package cn.demo2; import cn.demo1.Employee; import java.util.List; public class Department { private Integer id; private String departmentName; private List<cn.demo1.Employee> emps; public Department(Integer id) { this.id = id; } public Department() { } public List<cn.demo1.Employee> getEmps() { return emps; } public void setEmps(List<Employee> emps) { this.emps = emps; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getDepartmentName() { return departmentName; } public void setDepartmentName(String departmentName) { this.departmentName = departmentName; } @Override public String toString() { return "Department{" + "id=" + id + ", departmentName='" + departmentName + '\'' + '}'; } }
package cn.demo2; import org.apache.ibatis.annotations.Param; import java.util.List; public interface IEmployeeDAO { public void insertBatch(@Param("emps")List<Employee> emps); }
<?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="cn.demo2.IEmployeeDAO"> <insert id="insertBatch"> insert into employee (last_name,gender,email,dept_id) values <foreach collection="emps" item="emp" separator="," > (#{emp.lastName},#{emp.gender},#{emp.email},#{emp.department.id}) </foreach> </insert> </mapper>
package cn.test; import cn.demo2.Department; import cn.demo2.Employee; import cn.demo2.IEmployeeDAO; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.List; public class MybatisTest2 { /** * 测试查询 * @throws IOException */ @Test public void demo1() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession session = sqlSessionFactory.openSession(); IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class); List<Employee> emps = new ArrayList<>(); emps.add(new Employee(null,new Department(1),"好人","女","123@qq.com")); emps.add(new Employee(null,new Department(2),"坏人","男","456@qq.com")); dao.insertBatch(emps); session.commit(); session.close(); } }
1.7 动态SQL插入--Oracle下的批量插入
- Oracle不支持insert into 表名 values(),(),()。
- Oracle支持的方式:多个insert语句放在begin和end之间。
<insert id="insertBatch"> <foreach collection="emps" item="emp" open="begin" close="end/"> insert into employee(id,last_name,email) values(employee_seq.nextval,#{emp.lastName},#{emp.email}) </foreach> </insert>
2 Mybatis的缓存机制
- Mybatis包含一个非常强大的查询缓存特性,它可以非常方便的配置和定制。缓存可以极大的提升查询效率。
- Mybatis系统中默认定义了两级缓存。
- 一级缓存和二级缓存。
- 默认情况下,只有一级缓存开启。
- 二级缓存需要手动开启和配置,它是基于namespace基本的缓存。
- 为了提高扩展性。Mybatis定义了缓存接口Cache。我们可以通过实现Cache接口来自定义二级缓存。
2.1 一级缓存
- 一级缓存,即本地缓存,作用域默认为sqlSession。当session flush或close后,该session中的所有Cache将被清空。
- 本地缓存不能被关闭,但可以调用clearCache()清空本地缓存。
2.2 一级缓存失效情况(没有用到当前一级缓存的情况,效果就是,还需要向数据库发出SQL语句)
- 不同的sqlSession对应不同的一级缓存。
- 同一个sqlSession但是查询条件不同。
- 同一个sqlSession两次查询期间执行了任何一次增删改操作。
- 同一个sqlSession两次查询期间手动清空了缓存。
2.3 二级缓存(基于namespace级别的缓存,一个namespace对应一个二级缓存)
- 二级缓存,全局作用域缓存。
- 二级缓存默认不开启,需要手动配置。
- Mybatis提供二级缓存的接口以及实现,缓存实现要求POJO实现Serializable接口。
- 二级缓存在一级缓存关闭或提交之后才会生效。
2.4 二级缓存使用步骤
- 全局配置文件中开启二级缓存
<settings> <setting name="cacheEnabled" value="true"/> </settings>
- 去映射文件中配置使用二级缓存
<cache/>
- POJO需要实现Serializable接口
2.5 二级缓存相关属性
<!-- eviction=“ FIFO” :缓存回收策略: • LRU – 最近最少使用的:移除最长时间不被使用的对象。 • FIFO – 先进先出:按对象进入缓存的顺序来移除它们。 • SOFT – 软引用:移除基于垃圾回收器状态和软引用规则的对象。 • WEAK – 弱引用:更积极地移除基于垃圾收集器状态和弱引用规则的对象。 • 默认的是 LRU。 flushInterval :刷新间隔,单位毫秒 默认情况是不设置,也就是没有刷新间隔,缓存仅仅调用语句时刷新 size :引用数目,正整数 代表缓存最多可以存储多少个对象,太大容易导致内存溢出 readOnly :只读,true/false true:只读缓存;会给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这提供了很重要的性能优势。 false:读写缓存;会返回缓存对象的拷贝(通过序列化)。这会慢一些,但是安全,因此默认是 false。 -->
2.6 和缓存有关的设置/属性
- CacheEnalbed
- true;开启二级缓存。
- false:关闭二级缓存。
- 每个select标签默认有UseCache属性,而且属性值默认为true。
- true;表示这个select使用二级缓存。
- false:表示这个select不使用二级缓存。
- sql标签的flushCache属性:
- 增删改标签默认flushCache=true。SQL执行以后,会同时清空一级缓存和二级缓存。查询默认flushCache=false。
- sqlSession.clearCache():
- 只是用来清除一级缓存。
- 当在某个作用域(一级缓存和二级缓存)进行了增删改操作,默认该作用域下所有select中的缓存将被clear。
2.7 缓存的原理
2.8 第三方缓存整合
- EhCache是一个纯Java的进程内的缓存框架,具有快速、精干等特点,是Hibernate中默认的CacheProvider。
- Mybatis定义了Cache接口方便我们进行自定义扩展。
- 步骤:
- 导入ehcache包,以及整合包。日志包。
- ehcache-core-2.6.8.jar
- mybatis-ehcache-1.0.3.jar
- slf4j-api-1.6.1.jar
- slf4j-log4j12-1.6.2.jar
- 编写ehcache.xml配置文件
- 导入ehcache包,以及整合包。日志包。
<?xml version="1.0" encoding="UTF-8"?> <ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../config/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> <!-- 属性说明: l diskStore:指定数据在磁盘中的存储位置。 l defaultCache:当借助CacheManager.add("demoCache")创建Cache时,EhCache便会采用<defalutCache/>指定的的管理策略 以下属性是必须的: l maxElementsInMemory - 在内存中缓存的element的最大数目 l maxElementsOnDisk - 在磁盘上缓存的element的最大数目,若是0表示无穷大 l eternal - 设定缓存的elements是否永远不过期。如果为true,则缓存的数据始终有效,如果为false那么还要根据timeToIdleSeconds,timeToLiveSeconds判断 l overflowToDisk - 设定当内存缓存溢出的时候是否将过期的element缓存到磁盘上 以下属性是可选的: l timeToIdleSeconds - 当缓存在EhCache中的数据前后两次访问的时间超过timeToIdleSeconds的属性取值时,这些数据便会删除,默认值是0,也就是可闲置时间无穷大 l timeToLiveSeconds - 缓存element的有效生命期,默认是0.,也就是element存活时间无穷大 diskSpoolBufferSizeMB 这个参数设置DiskStore(磁盘缓存)的缓存区大小.默认是30MB.每个Cache都应该有自己的一个缓冲区. l diskPersistent - 在VM重启的时候是否启用磁盘保存EhCache中的数据,默认是false。 l diskExpiryThreadIntervalSeconds - 磁盘缓存的清理线程运行间隔,默认是120秒。每个120s,相应的线程会进行一次EhCache中数据的清理工作 l memoryStoreEvictionPolicy - 当内存缓存达到最大,有新的element加入的时候, 移除缓存中element的策略。默认是LRU(最近最少使用),可选的有LFU(最不常使用)和FIFO(先进先出) -->
- 配置cache标签
<cache type= "org.mybatis.caches.ehcache.EhcacheCache"></cache>
- 参照缓存:如果想在命名空间*享相同的缓存配置和实力,可以使用cache-ref元素来引用另外一个缓存。