架构设计

MyBatis进阶使用（日志管理、动态SQL、二级缓存、多表联级、Pagehelper分页、批处理）

MyBatis进阶使用

日志管理

依赖使用Logback进行日志管理：


<dependency>
       <groupId>ch.qos.logback</groupId>
       <artifactId>logback-classic</artifactId>
       <version>1.3.0-alpha5</version>
  </dependency>

需在资源文件夹中单独创建日志的配置文件logback.xml,文件名是强制的，程序运行时，logback会查找默认的配置文件logback.xml，从而打印调试信息。


<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <appender class="ch.qos.logback.core.ConsoleAppender" name="console">
        <encoder>
            <pattern>[%thread] %d{H  H:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern>
        </encoder>
    </appender>
    <!--日志输出级别(优先级高到低):
        error: 错误 - 系统的故障日志 
        warn: 警告 - 存在风险或使用不当的日志 
        info: 一般性消息 
        debug: 程序内部用于调试信息 
        trace: 程序运行的跟踪信息 -->
    <root level="debug">
        <appender-ref ref="console"/>
    </root>
</configuration>

动态SQL

用于实现动态SQL的元素主要有：

if
choose（when，otherwise）
trim
where
set
foreach

MyBatis进阶使用（日志管理、动态SQL、二级缓存、多表联级、Pagehelper分页、批处理）

单独if

避免出现语法错误，需要在此 SQL 语句中，添加where 1=1 ，是多条件拼接时的小技巧，后面的条件查询就可以都用 and 了。因为如果后面的if不为空，就会出现where and XX，这不符合语法：


<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods
       where 1=1
            <if test="categoryId != null">and category_id = #{categoryId} </if>
            <!--&lt;表示小于号-->
            <if test="currentPrice != null">and current_price &lt; #{currentPrice} </if>
    </select>

where+if结合

where语句的作用主要是简化SQL语句中where中的条件判断的：


<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods
        <where>
            <if test="categoryId != null">and category_id = #{categoryId} </if>
            <!--&lt;表示小于号-->
            <if test="currentPrice != null">and current_price &lt; #{currentPrice} </if>
        </where>
    </select>

set+if结合

set元素主要是用在更新操作的时候，它的主要功能和where元素其实是差不多的：


<update id="update" parameterType="com.imooc.mybatis.entity.Goods">
        update t_goods
        <!--set 用于配合if用于管理
      set 子句.有如下功能:
          a) 如果有条件满足, 会添加 set 关键字并执行sql语句
          b) 如果第一个条件中有逗号,但后续的条件没有满足的,会自动去尾部逗号。
          c) 如果修改条件都不满足就不生产set语句,出现错误,可以使用在set中添加id=#{id}来避免错误
        -->
        <set>
            id=#{id}
            <if test="title != null and title !=''">
                 title = #{title},
            </if>
            ......
            <if test="category_id != null and category_id !=''">
                 category_id = #{categoryId},
            </if>
        </set>
        where goods_id = #{goodsId}
    </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值，发现两次的内存地址是一样的：


@Test
    public void testLv1Cache() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 1603);
            Goods goods1 = session.selectOne("goods.selectById", 1603);
            System.out.println(goods.hashCode() + ":" + goods1.hashCode());
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }

    }


[main] 13  13:26:27.900 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
[main] 13  13:26:27.917 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:26:27.917 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:26:27.917 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:26:27.917 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:26:28.066 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection
[main] 13  13:26:29.448 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 658532887.
[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]
[main] 13  13:26:29.457 DEBUG goods.selectById - ==>  Preparing: select * from t_goods where goods_id=?
[main] 13  13:26:29.608 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 13  13:26:29.655 DEBUG goods.selectById - <==      Total: 1
1621002296:1621002296
[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]
[main] 13  13:26:29.672 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17]
[main] 13  13:26:29.672 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 658532887 to pool.

Process finished with exit code 0

当在测试方法中添加两个SqlSession，可以发现两个SqlSession分别执行了一次SQL语句，且内存地址是不一样的。这就说明了一级缓存只作用于SqlSession。


@Test
    public void testLv1Cache() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById" , 1603);
            Goods goods1 = session.selectOne("goods.selectById" , 1603);
            System.out.println(goods.hashCode() + ":" + goods1.hashCode());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }

        try{
            session = MyBatisUtils.openSession();
            Goods goods3 = session.selectOne("goods.selectById" , 1603);
            Goods goods4 = session.selectOne("goods.selectById" , 1603);
            System.out.println(goods3.hashCode() + ":" + goods4.hashCode());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }


[main] 13  13:30:35.994 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
[main] 13  13:30:36.010 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:30:36.011 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:30:36.011 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:30:36.011 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:30:36.175 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection
[main] 13  13:30:37.534 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 658532887.
[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]
[main] 13  13:30:37.542 DEBUG goods.selectById - ==>  Preparing: select * from t_goods where goods_id=?
[main] 13  13:30:37.614 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 13  13:30:37.663 DEBUG goods.selectById - <==      Total: 1
1621002296:1621002296
[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]
[main] 13  13:30:37.666 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17]
[main] 13  13:30:37.666 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 658532887 to pool.
[main] 13  13:30:37.667 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection
[main] 13  13:30:37.667 DEBUG o.a.i.d.pooled.PooledDataSource - Checked out connection 658532887 from pool.
[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]
[main] 13  13:30:37.667 DEBUG goods.selectById - ==>  Preparing: select * from t_goods where goods_id=?
[main] 13  13:30:37.667 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 13  13:30:37.669 DEBUG goods.selectById - <==      Total: 1
1138697171:1138697171
[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]
[main] 13  13:30:37.670 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17]
[main] 13  13:30:37.671 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 658532887 to pool.

使用session.commit();commit提交时对该namespace缓存强制清空。


@Test
    public void testLv1Cache() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods3 = session.selectOne("goods.selectById" , 1603);
            session.commit();//commit提交时对该namespace缓存强制清空
            Goods goods4 = session.selectOne("goods.selectById" , 1603);
            System.out.println(goods3.hashCode() + ":" + goods4.hashCode());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }


[main] 13  13:35:21.063 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
[main] 13  13:35:21.083 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:35:21.083 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:35:21.083 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:35:21.083 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:35:21.225 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection
[main] 13  13:35:22.517 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 658532887.
[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]
[main] 13  13:35:22.523 DEBUG goods.selectById - ==>  Preparing: select * from t_goods where goods_id=?
[main] 13  13:35:22.615 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 13  13:35:22.682 DEBUG goods.selectById - <==      Total: 1
[main] 13  13:35:22.688 DEBUG goods.selectById - ==>  Preparing: select * from t_goods where goods_id=?
[main] 13  13:35:22.688 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 13  13:35:22.691 DEBUG goods.selectById - <==      Total: 1
899543194:1138697171
[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]
[main] 13  13:35:22.693 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27406a17]
[main] 13  13:35:22.693 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 658532887 to pool.

Mybatis的二级缓存是指mapper映射文件。二级缓存的作用域是同一个namespace下的mapper映射文件内容，多个SqlSession共享。Mybatis需要手动设置启动二级缓存。生命周期和应用同步。


<!--开启了二级缓存 
eviction是缓存的清除策略,当缓存对象数量达到上限后,自动触发对应算法对缓存对象清除 。
flushInterval：代表间隔多长时间自动清空缓存，60000毫秒=10分钟。
size：代表缓存上限，用于保存对象的数量上限。
readOnly：true表示返回只读缓存，每次取出的都是缓存对象本身，执行效率高；false表示返回缓存对象的副本，可写。
     1.LRU – 最近最久未使用:移除最长时间不被使用的对象。O1 O2 O3 O4 .. O51214 99 83 1 893
     2.FIFO – 先进先出:按对象进入缓存的顺序来移除它们。 
     3.SOFT – 软引用:移除基于垃圾收集器状态和软引用规则的对象。 
     4.WEAK – 弱引用:更积极的移除基于垃圾收集器状态和弱引用规则的对象。 -->
    <cache readOnly="true" size="512" flushInterval="600000" eviction="LRU"/>

下面来测试一下，发现两次会话的内存地址相同，且程序只执行了一次SQL语句：


@Test
    public void testLv2Cache() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById" , 1603);
            System.out.println(goods.hashCode());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }

        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById" , 1603);
            System.out.println(goods.hashCode());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }


[main] 13  13:39:00.372 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
[main] 13  13:39:00.387 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:39:00.388 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:39:00.388 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:39:00.388 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 13  13:39:00.543 DEBUG goods - Cache Hit Ratio [goods]: 0.0
[main] 13  13:39:00.549 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection
[main] 13  13:39:01.918 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 1961002599.
[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]
[main] 13  13:39:01.923 DEBUG goods.selectById - ==>  Preparing: select * from t_goods where goods_id=?
[main] 13  13:39:01.969 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 13  13:39:02.010 DEBUG goods.selectById - <==      Total: 1
2144665602
[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]
[main] 13  13:39:02.015 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@74e28667]
[main] 13  13:39:02.015 DEBUG o.a.i.d.pooled.PooledDataSource - Returned connection 1961002599 to pool.
[main] 13  13:39:02.015 DEBUG goods - Cache Hit Ratio [goods]: 0.5
2144665602

部分不想要使用缓存的SQL元素，可以使用useCache="false"属性来关闭缓存。

若想执行完SQL后立马清除缓存，可以使用flushCache="true"属性。

多表级联查询

多对一：association

如下图所示，t_goods_detail里多条记录对应一个goods_id，现在想查询t_goods_detail数据以及其关联的商品信息。 MyBatis进阶使用（日志管理、动态SQL、二级缓存、多表联级、Pagehelper分页、批处理）


<resultMap id="rmGoodsDetail" type="com.imooc.mybatis.entity.GoodsDetail">
        <id property="gdId" column="gd_id"/>
        <result property="goodsId" column="goods_id"/>
        <association property="goods" column="goods_id" select="goods.selectById"/>
    </resultMap>
    <select id="selectManyToOne" resultMap="rmGoodsDetail">
        select * from t_goods_detail limit 0,20
    </select>


package com.imooc.mybatis.entity;

/**
 * @Auther 徐士成
 * @Date 2021-06-23 14:30
 */
public class GoodsDetail {
    private Integer gdId;
    private Integer goodsId;
    private String gdPicUrl;
    private Integer gdOrder;
    private Goods goods;

    public Integer getGdId() {
        return gdId;
    }

    public void setGdId(Integer gdId) {
        this.gdId = gdId;
    }

    public Integer getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(Integer goodsId) {
        this.goodsId = goodsId;
    }

    public String getGdPicUrl() {
        return gdPicUrl;
    }

    public void setGdPicUrl(String gdPicUrl) {
        this.gdPicUrl = gdPicUrl;
    }

    public Integer getGdOrder() {
        return gdOrder;
    }

    public void setGdOrder(Integer gdOrder) {
        this.gdOrder = gdOrder;
    }

    public Goods getGoods() {
        return goods;
    }

    public void setGoods(Goods goods) {
        this.goods = goods;
    }

    @Override
    public String toString() {
        return "GoodsDetail{" +
                "gdId=" + gdId +
                ", goodsId=" + goodsId +
                ", gdPicUrl='" + gdPicUrl + '\'' +
                ", gdOrder=" + gdOrder +
                ", goods=" + goods +
                '}';
    }
}


@Test
    public void testManyToOne() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            List<GoodsDetail> list = session.selectList("goods.selectManyToOne");
            for(GoodsDetail gd:list) {
                System.out.println(gd.getGdPicUrl() + ":" + gd.getGoods().getTitle());
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

一对多: collection

根据上面的多对一，反推一个商品就对应多条t_goods_detail表中的数据，那么如何将数据映射到goods中？这里需要在goods类中新增一个List goodsDetails属性，用于存放多条detail记录。


<select resultType="com.imooc.mybatis.entity.GoodsDetail" parameterType="Integer" id="selectByGoodsId">
        select * from t_goods_detail where goods_id = #{value}
    </select>
    <resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
    <!-- 映射goods对象的主键到goods_id字段 -->
        <id column="goods_id" property="goodsId"/>
    <!--collection的含义是,在 select * from t_goods limit 0,1 得到结果后,对所有Goods对象遍历得到goods_id字段值, 并代入到goodsDetail命名空间的findByGoodsId的SQL中执行查询, 将得到的"商品详情"集合赋值给goodsDetails List对象. -->
        <collection column="goods_id" property="goodsDetails" select="goods.selectByGoodsId"/>
    </resultMap>
    <select id="selectOneToMany" resultMap="rmGoods1">select * from t_goods limit 0,10 </select>


@Test
    public void testOneToMany() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            List<Goods> list = session.selectList("goods.selectOneToMany");
            for(Goods goods:list) {
                System.out.println(goods.getTitle() + ":" + goods.getGoodsDetails().size());
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

PageHelper分页

在pom.xml配置文件中添加PageHelper相关依赖：


<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.1</version>
        </dependency>

        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>4.0</version>
        </dependency>

在mybatis-config配置拦截器插件:


<plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库，helperdialect：配置使用哪种数据库语言，不配置的话pageHelper也会自动检测。-->
            <property name="helperDialect" value="mysql"/>
            <!--分页合理化，reasonable：在启用合理化时，如果 pageNum<1，则会查询第一页；如果 pageNum>pages，则会查询最后一页-->
            <property name="reasonable" value="true"/>
        </plugin>
    </plugins>


<select resultType="com.imooc.mybatis.entity.Goods" id="selectPage">
        select * from t_goods where current_price &lt; 1000
    </select>


@Test
  public void testSelectPage() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            /*startPage方法会自动将下一次查询进行分页*/
            PageHelper.startPage(2,10);
            Page<Goods> page = (Page) session.selectList("goods.selectPage");
            System.out.println("总页数:" + page.getPages());
            System.out.println("总记录数:" + page.getTotal());
            System.out.println("开始行号:" + page.getStartRow());
            System.out.println("结束行号:" + page.getEndRow());
            System.out.println("当前页码:" + page.getPageNum());
            List<Goods> data = page.getResult();//当前页数据
            for (Goods g : data) {
                System.out.println(g.getTitle());
            }
            System.out.println("");
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }


[main] 11  11:32:51.477 DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
[main] 11  11:32:51.542 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 11  11:32:51.542 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 11  11:32:51.543 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 11  11:32:51.543 DEBUG o.a.i.d.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
[main] 11  11:32:51.841 DEBUG SQL_CACHE - Cache Hit Ratio [SQL_CACHE]: 0.0
[main] 11  11:32:51.946 DEBUG goods - Cache Hit Ratio [goods]: 0.0
[main] 11  11:32:51.956 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection
[main] 11  11:32:53.159 DEBUG o.a.i.d.pooled.PooledDataSource - Created connection 2053996178.
[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]
[main] 11  11:32:53.164 DEBUG goods.selectPage_COUNT - ==>  Preparing: SELECT count(0) FROM t_goods WHERE current_price < 1000
[main] 11  11:32:53.220 DEBUG goods.selectPage_COUNT - ==> Parameters: 
[main] 11  11:32:53.383 DEBUG goods.selectPage_COUNT - <==      Total: 1
[main] 11  11:32:53.387 DEBUG goods - Cache Hit Ratio [goods]: 0.0
[main] 11  11:32:53.387 DEBUG goods.selectPage - ==>  Preparing: select * from t_goods where current_price < 1000 LIMIT ?, ?
[main] 11  11:32:53.390 DEBUG goods.selectPage - ==> Parameters: 10(Long), 10(Integer)
[main] 11  11:32:53.395 DEBUG goods.selectPage - <==      Total: 10
总页数:182
总记录数:1813
开始行号:10
结束行号:20
当前页码:2
康泰 家用智能胎心仪 分体探头操作方便 外放聆听 与家人分享宝宝心声
惠氏 启赋（Wyeth illuma）有机1段 900g （0-6月）婴儿配方奶粉（罐装）
惠氏 启赋（Wyeth illuma）有机2段900g（6-12月）较大婴儿配方奶粉（罐装）
惠氏启赋3段（12-36个月）幼儿配方奶粉900g *2罐
爱他美婴幼儿配方奶粉pre段800g 铂金版
【日本】尤妮佳MOONY 纸尿裤S84*3包
【日本】日本Moony XL38(男)拉拉裤*4包
【日本】Moony尤妮佳婴儿拉拉裤(男)L44片*3包
【日本】Moony尤妮佳婴儿裤型拉拉裤(女)L44*3包
【日本】Moony XL38(男)婴幼儿拉拉裤*3包

批处理

批量插入


<insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
        VALUES
        <foreach separator="," index="index" item="item" collection="list">
               (#{item.title},#{item.subTitle}, #{item.originalCost},
                #{item.currentPrice}, #{item.discount}, #{item.isFreeDelivery},
                #{item.categoryId})
        </foreach>
    </insert>


@Test
    public void testBatchInsert() throws Exception {
        SqlSession session = null;
        try {
            long st = new Date().getTime();
            session = MyBatisUtils.openSession();
            List list = new ArrayList();
            for (int i = 0; i < 10000; i++) {
                Goods goods = new Goods();
                goods.setTitle("测试商品");
                goods.setSubTitle("测试子标题");
                goods.setOriginalCost(200f);
                goods.setCurrentPrice(100f);
                goods.setDiscount(0.5f);
                goods.setIsFreeDelivery(1);
                goods.setCategoryId(43);
                list.add(goods);
            }
            session.insert("goods.batchInsert", list);
            session.commit();//提交事务数据
            long et = new Date().getTime();
            System.out.println("执行时间:" + (et - st) + "毫秒");
        } catch (Exception e) {
            if (session != null) {
                session.rollback();//回滚事务
            }
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

批量删除


<delete id="batchDelete" parameterType="java.util.List">
        DELETE FROM t_goods WHERE goods_id in
    <foreach separator="," index="index" item="item" collection="list" close=")" open="(">
        #{item}
    </foreach>


@Test
    public void testBatchDelete() throws Exception {
        SqlSession session = null;
        try {
            long st = new Date().getTime();
            session = MyBatisUtils.openSession();
            List list = new ArrayList();
            list.add(1920);
            list.add(1921);
            list.add(1922);
            session.delete("goods.batchDelete", list);
            session.commit();//提交事务数据
            long et = new Date().getTime();
            System.out.println("执行时间:" + (et - st) + "毫秒");
        } catch (Exception e) {
            if (session != null) {
                session.rollback();//回滚事务
            }
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

末

末路人

西方人并不比东方人聪明，但是他们却找对了路。

发布数

关注者

2354

累计阅读

MyBatis进阶使用（日志管理、动态SQL、二级缓存、多表联级、Pagehelper分页、批处理） 3年前