七、动态SQL
简介
- 动态 SQL 是 MyBatis 的强大特性之一
- 动态 SQL 用来拼接 SQL 语句
新实体类
Book
@Data @AllArgsConstructor @NoArgsConstructor public class Book { private String id; private String name; private double money; private Date buyDate; }
IF
mapper接口
public interface BookMapper { /** * if 标签测试 * * 模拟查询书名和比某价格便宜的书 * 两个条件都看 */ List<Book> findBookByNameAndMoney(@Param("name") String name, @Param("money") Double money); }
XML具体实现
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="zwz.mapper.BookMapper"> <select id="findBookByNameAndMoney" resultType="book"> select * from book where 1 = 1 <if test="name != ''"> and name like #{name} </if> <if test="money != 0.0"> and money < #{money} </if> </select> </mapper>
测试
/** * 模拟查询书名和比某价格便宜的书 * 如果name为空 忽略 * 如果价格为0.0 忽略 */ @Test public void testDynamicChoose(){ SqlSession session = MybatisUtils.getSqlSession(); BookMapper mapper = session.getMapper(BookMapper.class); List<Book> books = mapper.findBookByNameOrMoney("%设计%", 80.0); for (Book book : books) { System.out.println(book); } }
Choose
public interface BookMapper { /** * choose 标签测试 * * 模拟查询书名和比某价格便宜的书 * 如果姓名不为空，只看姓名 * 如果姓名为空，就看价格 * * @param name * @param money * @return */ List<Book> findBookByNameOrMoney(@Param("name") String name, @Param("money") Double money); }
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="zwz.mapper.BookMapper"> <select id="findBookByNameOrMoney" resultType="book"> select * from book where 1 = 1 <choose> <when test="name != ''"> and name like #{name} </when> <when test="money != 0.0"> and money < #{money} </when> <otherwise> and 1 = 1 </otherwise> </choose> </select> </mapper>
/** * 模拟查询书名和比某价格便宜的书 * 如果name为空 忽略 * 如果价格为0.0 忽略 */ @Test public void testDynamicChoose(){ SqlSession session = MybatisUtils.getSqlSession(); BookMapper mapper = session.getMapper(BookMapper.class); List<Book> books = mapper.findBookByNameOrMoney("%设计%", 80.0); for (Book book : books) { System.out.println(book); } }
Foreach
public interface BookMapper { /** * foreach 标签测试 * * 查询指定书名的书列表 * * @param books * @return */ List<Book> findInName(@Param("books") List<String> books); }
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="zwz.mapper.BookMapper"> <select id="findInName" resultType="book" parameterType="list"> select * from book where 1 = 1 and name in <foreach collection="books" index="index" item="item" open="(" close=")" separator=","> #{item} </foreach> </select> </mapper>
/** * 查询指定书名的书列表 */ @Test public void testDynamicForeach(){ SqlSession session = MybatisUtils.getSqlSession(); BookMapper mapper = session.getMapper(BookMapper.class); List<String> bookNames = new ArrayList<>(); bookNames.add("JavaScript程序设计"); bookNames.add("JAVA程序设计"); bookNames.add("C++程序设计"); List<Book> books = mapper.findInName(bookNames); for (Book book : books) { System.out.println(book); } }
八、缓存
8.1 一级缓存
MyBatis 内置了一个强大的事务性查询缓存机制，它可以非常方便地配置和定制
默认情况下，只启用了本地的会话缓存（一级缓存），它仅仅对一个会话中的数据进行缓存
- 映射语句文件中的所有 select 语句的结果将会被缓存。
- 映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存。
- 缓存会使用最近最少使用算法（LRU, Least Recently Used）算法来清除不需要的缓存。
- 缓存不会定时进行刷新（也就是说，没有刷新间隔）。
- 缓存会保存列表或对象（无论查询方法返回哪种）的 1024 个引用。
- 缓存会被视为读/写缓存，这意味着获取到的对象并不是共享的，可以安全地被调用者修改，而不干扰其他调用者或线程所做的潜在修改。
比如两次查询同样的用户，可以发现系统只执行了一次SQL指令
@Test public void testCache1(){ SqlSession session = MybatisUtils.getSqlSession(); UserMapper mapper = session.getMapper(UserMapper.class); // 第一次查询 User user = mapper.findUserById("7a0f791f1e7b416ea01b860470fa9aeb"); System.out.println(user); System.out.println("+++++++++++++++++++++++++++++++++++++++"); // 第二次查询 User user2 = mapper.findUserById("7a0f791f1e7b416ea01b860470fa9aeb"); System.out.println(user2); System.out.println(user == user2); session.close(); }
输出日志如下
Created connection 508512860. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e4f4a5c] ==> Preparing: select * from user where id = ?; ==> Parameters: 9e3eed6af6da4214b520535cac13f13a(String) <== Columns: id, name, age <== Row: 9e3eed6af6da4214b520535cac13f13a, ZWZ1改, 18 <== Total: 1 User(id=9e3eed6af6da4214b520535cac13f13a, name=ZWZ1改, age=18) +++++++++++++++++++++++++++++++++++++++ User(id=9e3eed6af6da4214b520535cac13f13a, name=ZWZ1改, age=18) true Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e4f4a5c] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e4f4a5c] Returned connection 508512860 to pool. Process finished with exit code 0
8.2 二级缓存
关闭二级缓存
如果没有开启二级缓存，在两个不同的SqlSession中发起查询，系统会执行两次SQL指令
@Test public void testCache2(){ SqlSession session1 = MybatisUtils.getSqlSession(); UserMapper2 mapper1 = session1.getMapper(UserMapper2.class); User2 user1 = mapper1.findUserById("9e3eed6af6da4214b520535cac13f13a"); session1.close(); SqlSession session2 = MybatisUtils.getSqlSession(); UserMapper2 mapper2 = session2.getMapper(UserMapper2.class); User2 user2 = mapper2.findUserById("9e3eed6af6da4214b520535cac13f13a"); session2.close(); }
输出日志如下
Created connection 2075952726. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7bbc8656] ==> Preparing: select * from user where id = ?; ==> Parameters: 9e3eed6af6da4214b520535cac13f13a(String) <== Columns: id, name, age <== Row: 9e3eed6af6da4214b520535cac13f13a, ZWZ1改, 18 <== Total: 1 Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7bbc8656] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7bbc8656] Returned connection 2075952726 to pool. Opening JDBC Connection Checked out connection 2075952726 from pool. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7bbc8656] ==> Preparing: select * from user where id = ?; ==> Parameters: 9e3eed6af6da4214b520535cac13f13a(String) <== Columns: id, name, age <== Row: 9e3eed6af6da4214b520535cac13f13a, ZWZ1改, 18 <== Total: 1 Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7bbc8656] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7bbc8656] Returned connection 2075952726 to pool. Process finished with exit code 0
开启二级缓存
在mybatis-config.xml中配置全局缓存
<settings> <!--开启日志--> <setting name="logImpl" value="STDOUT_LOGGING"/> <!--驼峰转换--> <setting name="mapUnderscoreToCamelCase" value="true"/> <!-- 开启全局缓存--> <setting name="cacheEnabled" value="true"/> </settings>
在mapper层xml中，配置cache
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
测试
@Test public void testCache2(){ SqlSession session1 = MybatisUtils.getSqlSession(); UserMapper2 mapper1 = session1.getMapper(UserMapper2.class); User2 user1 = mapper1.findUserById("9e3eed6af6da4214b520535cac13f13a"); session1.close(); SqlSession session2 = MybatisUtils.getSqlSession(); UserMapper2 mapper2 = session2.getMapper(UserMapper2.class); User2 user2 = mapper2.findUserById("9e3eed6af6da4214b520535cac13f13a"); session2.close(); }
从日志输出中可以发现，系统只执行了一次SQL指令
Created connection 307829448. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@12591ac8] ==> Preparing: select * from user where id = ?; ==> Parameters: 9e3eed6af6da4214b520535cac13f13a(String) <== Columns: id, name, age <== Row: 9e3eed6af6da4214b520535cac13f13a, ZWZ1改, 18 <== Total: 1 Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@12591ac8] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@12591ac8] Returned connection 307829448 to pool. Cache Hit Ratio [cache2.mapper.UserMapper2]: 0.5 Process finished with exit code 0
