MyBatis进阶使用
日志管理
依赖使用Logback进行日志管理：
1 <dependency> 2 <groupId>ch.qos.logback</groupId> 3 <artifactId>logback-classic</artifactId> 4 <version>1.3.0-alpha5</version> 5 </dependency>
需在资源文件夹中单独创建日志的配置文件logback.xml,文件名是强制的，程序运行时，logback会查找默认的配置文件logback.xml，从而打印调试信息。
1<?xml version="1.0" encoding="UTF-8"?> 2<configuration> 3 <appender class="ch.qos.logback.core.ConsoleAppender" name="console"> 4 <encoder> 5 <pattern>[%thread] %d{H H:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern> 6 </encoder> 7 </appender> 8 <!--日志输出级别(优先级高到低): 9 error: 错误 - 系统的故障日志 10 warn: 警告 - 存在风险或使用不当的日志 11 info: 一般性消息 12 debug: 程序内部用于调试信息 13 trace: 程序运行的跟踪信息 --> 14 <root level="debug"> 15 <appender-ref ref="console"/> 16 </root> 17</configuration>
动态SQL
用于实现动态SQL的元素主要有：
- if
- choose（when，otherwise）
- trim
- where
- set
- foreach
单独if
避免出现语法错误，需要在此 SQL 语句中， 添加where 1=1 ，是多条件拼接时的小技巧， 后面的条件查询就可以都用 and 了。因为如果后面的if不为空，就会出现where and XX，这不符合语法：
1<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods"> 2 select * from t_goods 3 where 1=1 4 <if test="categoryId != null">and category_id = #{categoryId} </if> 5 <!--<表示小于号--> 6 <if test="currentPrice != null">and current_price < #{currentPrice} </if> 7 </select>
where+if结合
where语句的作用主要是简化SQL语句中where中的条件判断的：
1<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods"> 2 select * from t_goods 3 <where> 4 <if test="categoryId != null">and category_id = #{categoryId} </if> 5 <!--<表示小于号--> 6 <if test="currentPrice != null">and current_price < #{currentPrice} </if> 7 </where> 8 </select>
set+if结合
set元素主要是用在更新操作的时候，它的主要功能和where元素其实是差不多的：
1<update id="update" parameterType="com.imooc.mybatis.entity.Goods"> 2 update t_goods 3 <!--set 用于配合if用于管理 4 set 子句.有如下功能: 5 a) 如果有条件满足, 会添加 set 关键字并执行sql语句 6 b) 如果第一个条件中有逗号,但后续的条件没有满足的,会自动去尾部逗号。 7 c) 如果修改条件都不满足就不生产set语句,出现错误,可以使用在set中添加id=#{id}来避免错误 8 --> 9 <set> 10 id=#{id} 11 <if test="title != null and title !=''"> 12 title = #{title}, 13 </if> 14 ...... 15 <if test="category_id != null and category_id !=''"> 16 category_id = #{categoryId}, 17 </if> 18 </set> 19 where goods_id = #{goodsId} 20 </update>
trim
set 和 where 其实都是 trim 标签的一种类型， 该两种功能都可以使用 trim 标签进行实现。
<trim prefix="where" prefixOverrides="AND |OR"></trim>
表示当 trim 中含有内容时， 添加 where， 且第一个为 and 或 or 时， 会将其去掉。而如果没有内容， 则不添加 where。
<trim prefix="SET" suffixOverrides=","></trim>
表示当 trim 中含有内容时， 添加 set， 且最后的内容为 , 时， 会将其去掉。而没有内容， 不添加 set
二级缓存
MyBatis自带的缓存有一级缓存和二级缓存。 Mybatis的一级缓存是指Session缓存。一级缓存的作用域默认是一个SqlSession。Mybatis默认开启一级缓存。 也就是在同一个SqlSession中，执行相同的查询SQL，第一次会去数据库进行查询，并写到缓存中； 第二次以后是直接去缓存中取。 当执行SQL查询中间发生了增删改的操作，MyBatis会把SqlSession的缓存清空。 下面通过测试来观察，测试方法中在同一个SqlSession 执行两次同样的查询方法，会发现SQL语句只执行了一次，又通过获取hashCode值，发现两次的内存地址是一样的：
1@Test 2 public void testLv1Cache() throws Exception { 3 SqlSession session = null; 4 try { 5 session = MyBatisUtils.openSession(); 6 Goods goods = session.selectOne("goods.selectById", 1603); 7 Goods goods1 = session.selectOne("goods.selectById", 1603); 8 System.out.println(goods.hashCode() + ":" + goods1.hashCode()); 9 } catch (Exception e) { 10 throw e; 11 } finally { 12 MyBatisUtils.closeSession(session); 13 } 14 15 }
1[main] 13 13:26:27.900 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2[main] 13 13:26:27.917 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 3[main] 13 13:26:27.917 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 4[main] 13 13:26:27.917 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 5[main] 13 13:26:27.917 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 6[main] 13 13:26:28.066 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection 7[main] 13 13:26:29.448 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 658532887. 8[main] 13 13:26:29.448 DEBUG o.a.i.t.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 9[main] 13 13:26:29.457 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id=? 10[main] 13 13:26:29.608 DEBUG goods.selectById - ==> Parameters: 1603(Integer) 11[main] 13 13:26:29.655 DEBUG goods.selectById - <== Total: 1 121621002296:1621002296 13[main] 13 13:26:29.663 DEBUG o.a.i.t.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 14[main] 13 13:26:29.672 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 15[main] 13 13:26:29.672 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 658532887 to pool. 16 17Process finished with exit code 0 18
当在测试方法中添加两个SqlSession，可以发现两个SqlSession分别执行了一次SQL语句，且内存地址是不一样的。这就说明了一级缓存只作用于SqlSession。
1@Test 2 public void testLv1Cache() throws Exception { 3 SqlSession session = null; 4 try{ 5 session = MyBatisUtils.openSession(); 6 Goods goods = session.selectOne("goods.selectById" , 1603); 7 Goods goods1 = session.selectOne("goods.selectById" , 1603); 8 System.out.println(goods.hashCode() + ":" + goods1.hashCode()); 9 }catch (Exception e){ 10 throw e; 11 }finally { 12 MyBatisUtils.closeSession(session); 13 } 14 15 try{ 16 session = MyBatisUtils.openSession(); 17 Goods goods3 = session.selectOne("goods.selectById" , 1603); 18 Goods goods4 = session.selectOne("goods.selectById" , 1603); 19 System.out.println(goods3.hashCode() + ":" + goods4.hashCode()); 20 }catch (Exception e){ 21 throw e; 22 }finally { 23 MyBatisUtils.closeSession(session); 24 } 25 }
1[main] 13 13:30:35.994 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2[main] 13 13:30:36.010 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 3[main] 13 13:30:36.011 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 4[main] 13 13:30:36.011 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 5[main] 13 13:30:36.011 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 6[main] 13 13:30:36.175 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection 7[main] 13 13:30:37.534 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 658532887. 8[main] 13 13:30:37.535 DEBUG o.a.i.t.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 9[main] 13 13:30:37.542 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id=? 10[main] 13 13:30:37.614 DEBUG goods.selectById - ==> Parameters: 1603(Integer) 11[main] 13 13:30:37.663 DEBUG goods.selectById - <== Total: 1 121621002296:1621002296 13[main] 13 13:30:37.666 DEBUG o.a.i.t.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 14[main] 13 13:30:37.666 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 15[main] 13 13:30:37.666 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 658532887 to pool. 16[main] 13 13:30:37.667 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection 17[main] 13 13:30:37.667 DEBUG o.a.i.d.pooled.PooledDataSource - Checked out connection 658532887 from pool. 18[main] 13 13:30:37.667 DEBUG o.a.i.t.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 19[main] 13 13:30:37.667 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id=? 20[main] 13 13:30:37.667 DEBUG goods.selectById - ==> Parameters: 1603(Integer) 21[main] 13 13:30:37.669 DEBUG goods.selectById - <== Total: 1 221138697171:1138697171 23[main] 13 13:30:37.669 DEBUG o.a.i.t.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 24[main] 13 13:30:37.670 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 25[main] 13 13:30:37.671 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 658532887 to pool. 26
使用session.commit();commit提交时对该namespace缓存强制清空。
1@Test 2 public void testLv1Cache() throws Exception { 3 SqlSession session = null; 4 try{ 5 session = MyBatisUtils.openSession(); 6 Goods goods3 = session.selectOne("goods.selectById" , 1603); 7 session.commit();//commit提交时对该namespace缓存强制清空 8 Goods goods4 = session.selectOne("goods.selectById" , 1603); 9 System.out.println(goods3.hashCode() + ":" + goods4.hashCode()); 10 }catch (Exception e){ 11 throw e; 12 }finally { 13 MyBatisUtils.closeSession(session); 14 } 15 }
1[main] 13 13:35:21.063 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2[main] 13 13:35:21.083 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 3[main] 13 13:35:21.083 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 4[main] 13 13:35:21.083 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 5[main] 13 13:35:21.083 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 6[main] 13 13:35:21.225 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection 7[main] 13 13:35:22.517 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 658532887. 8[main] 13 13:35:22.518 DEBUG o.a.i.t.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 9[main] 13 13:35:22.523 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id=? 10[main] 13 13:35:22.615 DEBUG goods.selectById - ==> Parameters: 1603(Integer) 11[main] 13 13:35:22.682 DEBUG goods.selectById - <== Total: 1 12[main] 13 13:35:22.688 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id=? 13[main] 13 13:35:22.688 DEBUG goods.selectById - ==> Parameters: 1603(Integer) 14[main] 13 13:35:22.691 DEBUG goods.selectById - <== Total: 1 15899543194:1138697171 16[main] 13 13:35:22.692 DEBUG o.a.i.t.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 17[main] 13 13:35:22.693 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17] 18[main] 13 13:35:22.693 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 658532887 to pool. 19
Mybatis的二级缓存是指mapper映射文件。二级缓存的作用域是同一个namespace下的mapper映射文件内容，多个SqlSession共享。Mybatis需要手动设置启动二级缓存。生命周期和应用同步。
1<!--开启了二级缓存 2eviction是缓存的清除策略,当缓存对象数量达到上限后,自动触发对应算法对缓存对象清除 。 3flushInterval：代表间隔多长时间自动清空缓存，60000毫秒=10分钟。 4size：代表缓存上限，用于保存对象的数量上限。 5readOnly：true表示返回只读缓存，每次取出的都是缓存对象本身，执行效率高；false表示返回缓存对象的副本，可写。 6 1.LRU – 最近最久未使用:移除最长时间不被使用的对象。O1 O2 O3 O4 .. O51214 99 83 1 893 7 2.FIFO – 先进先出:按对象进入缓存的顺序来移除它们。 8 3.SOFT – 软引用:移除基于垃圾收集器状态和软引用规则的对象。 9 4.WEAK – 弱引用:更积极的移除基于垃圾收集器状态和弱引用规则的对象。 --> 10 <cache readOnly="true" size="512" flushInterval="600000" eviction="LRU"/>
下面来测试一下，发现两次会话的内存地址相同，且程序只执行了一次SQL语句：
1@Test 2 public void testLv2Cache() throws Exception { 3 SqlSession session = null; 4 try{ 5 session = MyBatisUtils.openSession(); 6 Goods goods = session.selectOne("goods.selectById" , 1603); 7 System.out.println(goods.hashCode()); 8 }catch (Exception e){ 9 throw e; 10 }finally { 11 MyBatisUtils.closeSession(session); 12 } 13 14 try{ 15 session = MyBatisUtils.openSession(); 16 Goods goods = session.selectOne("goods.selectById" , 1603); 17 System.out.println(goods.hashCode()); 18 }catch (Exception e){ 19 throw e; 20 }finally { 21 MyBatisUtils.closeSession(session); 22 } 23 }
1[main] 13 13:39:00.372 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2[main] 13 13:39:00.387 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 3[main] 13 13:39:00.388 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 4[main] 13 13:39:00.388 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 5[main] 13 13:39:00.388 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 6[main] 13 13:39:00.543 DEBUG goods - Cache Hit Ratio [goods]: 0.0 7[main] 13 13:39:00.549 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection 8[main] 13 13:39:01.918 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 1961002599. 9[main] 13 13:39:01.919 DEBUG o.a.i.t.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@74e28667] 10[main] 13 13:39:01.923 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id=? 11[main] 13 13:39:01.969 DEBUG goods.selectById - ==> Parameters: 1603(Integer) 12[main] 13 13:39:02.010 DEBUG goods.selectById - <== Total: 1 132144665602 14[main] 13 13:39:02.014 DEBUG o.a.i.t.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@74e28667] 15[main] 13 13:39:02.015 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@74e28667] 16[main] 13 13:39:02.015 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 1961002599 to pool. 17[main] 13 13:39:02.015 DEBUG goods - Cache Hit Ratio [goods]: 0.5 182144665602
部分不想要使用缓存的SQL元素，可以使用useCache="false"属性来关闭缓存。
若想执行完SQL后立马清除缓存，可以使用flushCache="true"属性。
多表级联查询
多对一：association
如下图所示，t_goods_detail里多条记录对应一个goods_id，现在想查询t_goods_detail数据以及其关联的商品信息。
1 <resultMap id="rmGoodsDetail" type="com.imooc.mybatis.entity.GoodsDetail"> 2 <id property="gdId" column="gd_id"/> 3 <result property="goodsId" column="goods_id"/> 4 <association property="goods" column="goods_id" select="goods.selectById"/> 5 </resultMap> 6 <select id="selectManyToOne" resultMap="rmGoodsDetail"> 7 select * from t_goods_detail limit 0,20 8 </select>
1package com.imooc.mybatis.entity; 2 3/** 4 * @Auther 徐士成 5 * @Date 2021-06-23 14:30 6 */ 7public class GoodsDetail { 8 private Integer gdId; 9 private Integer goodsId; 10 private String gdPicUrl; 11 private Integer gdOrder; 12 private Goods goods; 13 14 public Integer getGdId() { 15 return gdId; 16 } 17 18 public void setGdId(Integer gdId) { 19 this.gdId = gdId; 20 } 21 22 public Integer getGoodsId() { 23 return goodsId; 24 } 25 26 public void setGoodsId(Integer goodsId) { 27 this.goodsId = goodsId; 28 } 29 30 public String getGdPicUrl() { 31 return gdPicUrl; 32 } 33 34 public void setGdPicUrl(String gdPicUrl) { 35 this.gdPicUrl = gdPicUrl; 36 } 37 38 public Integer getGdOrder() { 39 return gdOrder; 40 } 41 42 public void setGdOrder(Integer gdOrder) { 43 this.gdOrder = gdOrder; 44 } 45 46 public Goods getGoods() { 47 return goods; 48 } 49 50 public void setGoods(Goods goods) { 51 this.goods = goods; 52 } 53 54 @Override 55 public String toString() { 56 return "GoodsDetail{" + 57 "gdId=" + gdId + 58 ", goodsId=" + goodsId + 59 ", gdPicUrl='" + gdPicUrl + '\'' + 60 ", gdOrder=" + gdOrder + 61 ", goods=" + goods + 62 '}'; 63 } 64} 65
1@Test 2 public void testManyToOne() throws Exception { 3 SqlSession session = null; 4 try { 5 session = MyBatisUtils.openSession(); 6 List<GoodsDetail> list = session.selectList("goods.selectManyToOne"); 7 for(GoodsDetail gd:list) { 8 System.out.println(gd.getGdPicUrl() + ":" + gd.getGoods().getTitle()); 9 } 10 } catch (Exception e) { 11 throw e; 12 } finally { 13 MyBatisUtils.closeSession(session); 14 } 15 }
一对多: collection
根据上面的多对一，反推一个商品就对应多条t_goods_detail表中的数据，那么如何将数据映射到goods中？这里需要在goods类中新增一个List<GoodsDetail> goodsDetails属性，用于存放多条detail记录。
1<select resultType="com.imooc.mybatis.entity.GoodsDetail" parameterType="Integer" id="selectByGoodsId"> 2 select * from t_goods_detail where goods_id = #{value} 3 </select> 4 <resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods"> 5 <!-- 映射goods对象的主键到goods_id字段 --> 6 <id column="goods_id" property="goodsId"/> 7 <!--collection的含义是,在 select * from t_goods limit 0,1 得到结果后,对所有Goods对象遍历得到goods_id字段值, 并代入到goodsDetail命名空间的findByGoodsId的SQL中执行查询, 将得到的"商品详情"集合赋值给goodsDetails List对象. --> 8 <collection column="goods_id" property="goodsDetails" select="goods.selectByGoodsId"/> 9 </resultMap> 10 <select id="selectOneToMany" resultMap="rmGoods1">select * from t_goods limit 0,10 </select>
1@Test 2 public void testOneToMany() throws Exception { 3 SqlSession session = null; 4 try { 5 session = MyBatisUtils.openSession(); 6 List<Goods> list = session.selectList("goods.selectOneToMany"); 7 for(Goods goods:list) { 8 System.out.println(goods.getTitle() + ":" + goods.getGoodsDetails().size()); 9 } 10 } catch (Exception e) { 11 throw e; 12 } finally { 13 MyBatisUtils.closeSession(session); 14 } 15 }
PageHelper分页
在pom.xml配置文件中添加PageHelper相关依赖：
1<dependency> 2 <groupId>com.github.pagehelper</groupId> 3 <artifactId>pagehelper</artifactId> 4 <version>5.2.1</version> 5 </dependency> 6 7 <dependency> 8 <groupId>com.github.jsqlparser</groupId> 9 <artifactId>jsqlparser</artifactId> 10 <version>4.0</version> 11 </dependency>
在mybatis-config配置拦截器插件:
1<plugins> 2 <!-- com.github.pagehelper为PageHelper类所在包名 --> 3 <plugin interceptor="com.github.pagehelper.PageInterceptor"> 4 <!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库，helperdialect：配置使用哪种数据库语言，不配置的话pageHelper也会自动检测。--> 5 <property name="helperDialect" value="mysql"/> 6 <!--分页合理化，reasonable：在启用合理化时，如果 pageNum<1，则会查询第一页；如果 pageNum>pages，则会查询最后一页--> 7 <property name="reasonable" value="true"/> 8 </plugin> 9 </plugins>
1<select resultType="com.imooc.mybatis.entity.Goods" id="selectPage"> 2 select * from t_goods where current_price < 1000 3 </select>
1@Test 2 public void testSelectPage() throws Exception { 3 SqlSession session = null; 4 try { 5 session = MyBatisUtils.openSession(); 6 /*startPage方法会自动将下一次查询进行分页*/ 7 PageHelper.startPage(2,10); 8 Page<Goods> page = (Page) session.selectList("goods.selectPage"); 9 System.out.println("总页数:" + page.getPages()); 10 System.out.println("总记录数:" + page.getTotal()); 11 System.out.println("开始行号:" + page.getStartRow()); 12 System.out.println("结束行号:" + page.getEndRow()); 13 System.out.println("当前页码:" + page.getPageNum()); 14 List<Goods> data = page.getResult();//当前页数据 15 for (Goods g : data) { 16 System.out.println(g.getTitle()); 17 } 18 System.out.println(""); 19 } catch (Exception e) { 20 throw e; 21 } finally { 22 MyBatisUtils.closeSession(session); 23 } 24 }
1[main] 11 11:32:51.477 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2[main] 11 11:32:51.542 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 3[main] 11 11:32:51.542 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 4[main] 11 11:32:51.543 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 5[main] 11 11:32:51.543 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 6[main] 11 11:32:51.841 DEBUG SQL_CACHE - Cache Hit Ratio [SQL_CACHE]: 0.0 7[main] 11 11:32:51.946 DEBUG goods - Cache Hit Ratio [goods]: 0.0 8[main] 11 11:32:51.956 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection 9[main] 11 11:32:53.159 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 2053996178. 10[main] 11 11:32:53.159 DEBUG o.a.i.t.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7a6d7e92] 11[main] 11 11:32:53.164 DEBUG goods.selectPage_COUNT - ==> Preparing: SELECT count(0) FROM t_goods WHERE current_price < 1000 12[main] 11 11:32:53.220 DEBUG goods.selectPage_COUNT - ==> Parameters: 13[main] 11 11:32:53.383 DEBUG goods.selectPage_COUNT - <== Total: 1 14[main] 11 11:32:53.387 DEBUG goods - Cache Hit Ratio [goods]: 0.0 15[main] 11 11:32:53.387 DEBUG goods.selectPage - ==> Preparing: select * from t_goods where current_price < 1000 LIMIT ?, ? 16[main] 11 11:32:53.390 DEBUG goods.selectPage - ==> Parameters: 10(Long), 10(Integer) 17[main] 11 11:32:53.395 DEBUG goods.selectPage - <== Total: 10 18总页数:182 19总记录数:1813 20开始行号:10 21结束行号:20 22当前页码:2 23康泰 家用智能胎心仪 分体探头操作方便 外放聆听 与家人分享宝宝心声 24惠氏 启赋（Wyeth illuma）有机1段 900g （0-6月）婴儿配方奶粉（罐装） 25惠氏 启赋（Wyeth illuma）有机2段900g（6-12月）较大婴儿配方奶粉（罐装） 26惠氏启赋3段（12-36个月）幼儿配方奶粉900g *2罐 27爱他美婴幼儿配方奶粉pre段800g 铂金版 28【日本】尤妮佳MOONY 纸尿裤S84*3包 29【日本】日本Moony XL38(男)拉拉裤*4包 30【日本】Moony尤妮佳婴儿拉拉裤(男)L44片*3包 31【日本】Moony尤妮佳婴儿裤型拉拉裤(女)L44*3包 32【日本】Moony XL38(男)婴幼儿拉拉裤*3包
批处理
批量插入
1<insert id="batchInsert" parameterType="java.util.List"> 2 INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id) 3 VALUES 4 <foreach separator="," index="index" item="item" collection="list"> 5 (#{item.title},#{item.subTitle}, #{item.originalCost}, 6 #{item.currentPrice}, #{item.discount}, #{item.isFreeDelivery}, 7 #{item.categoryId}) 8 </foreach> 9 </insert>
1 @Test 2 public void testBatchInsert() throws Exception { 3 SqlSession session = null; 4 try { 5 long st = new Date().getTime(); 6 session = MyBatisUtils.openSession(); 7 List list = new ArrayList(); 8 for (int i = 0; i < 10000; i++) { 9 Goods goods = new Goods(); 10 goods.setTitle("测试商品"); 11 goods.setSubTitle("测试子标题"); 12 goods.setOriginalCost(200f); 13 goods.setCurrentPrice(100f); 14 goods.setDiscount(0.5f); 15 goods.setIsFreeDelivery(1); 16 goods.setCategoryId(43); 17 list.add(goods); 18 } 19 session.insert("goods.batchInsert", list); 20 session.commit();//提交事务数据 21 long et = new Date().getTime(); 22 System.out.println("执行时间:" + (et - st) + "毫秒"); 23 } catch (Exception e) { 24 if (session != null) { 25 session.rollback();//回滚事务 26 } 27 throw e; 28 } finally { 29 MyBatisUtils.closeSession(session); 30 } 31 }
批量删除
1<delete id="batchDelete" parameterType="java.util.List"> 2 DELETE FROM t_goods WHERE goods_id in 3 <foreach separator="," index="index" item="item" collection="list" close=")" open="("> 4 #{item} 5 </foreach>
1@Test 2 public void testBatchDelete() throws Exception { 3 SqlSession session = null; 4 try { 5 long st = new Date().getTime(); 6 session = MyBatisUtils.openSession(); 7 List list = new ArrayList(); 8 list.add(1920); 9 list.add(1921); 10 list.add(1922); 11 session.delete("goods.batchDelete", list); 12 session.commit();//提交事务数据 13 long et = new Date().getTime(); 14 System.out.println("执行时间:" + (et - st) + "毫秒"); 15 } catch (Exception e) { 16 if (session != null) { 17 session.rollback();//回滚事务 18 } 19 throw e; 20 } finally { 21 MyBatisUtils.closeSession(session); 22 } 23 }