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

执键写春秋
• 阅读 1672

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);
        }
    }
点赞
收藏
评论区
推荐文章
技术小男生 技术小男生
4个月前
linux环境jdk环境变量配置
1:编辑系统配置文件vi/etc/profile2:按字母键i进入编辑模式,在最底部添加内容:JAVAHOME/opt/jdk1.8.0152CLASSPATH.:$JAVAHOME/lib/dt.jar:$JAVAHOME/lib/tools.jarPATH$JAVAHOME/bin:$PATH3:生效配置
光头强的博客 光头强的博客
4个月前
Java面向对象试题
1、请创建一个Animal动物类,要求有方法eat()方法,方法输出一条语句“吃东西”。创建一个接口A,接口里有一个抽象方法fly()。创建一个Bird类继承Animal类并实现接口A里的方法输出一条有语句“鸟儿飞翔”,重写eat()方法输出一条语句“鸟儿吃虫”。在Test类中向上转型创建b对象,调用eat方法。然后向下转型调用eat()方
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
1年前
Java爬虫之JSoup使用教程
title:Java爬虫之JSoup使用教程date:201812248:00:000800update:201812248:00:000800author:mecover:https://imgblog.csdnimg.cn/20181224144920712(https://www.oschin
Wesley13 Wesley13
1年前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序selectfromtable_nameorderiddesc;2.按照指定(多个)字段排序selectfromtable_nameorderiddesc,statusdesc;3.按照指定字段和规则排序selec
Wesley13 Wesley13
1年前
Java日期时间API系列36
  十二时辰,古代劳动人民把一昼夜划分成十二个时段,每一个时段叫一个时辰。二十四小时和十二时辰对照表:时辰时间24时制子时深夜11:00凌晨01:0023:0001:00丑时上午01:00上午03:0001:0003:00寅时上午03:00上午0
Stella981 Stella981
1年前
Docker 部署SpringBoot项目不香吗?
  公众号改版后文章乱序推荐,希望你可以点击上方“Java进阶架构师”,点击右上角,将我们设为★“星标”!这样才不会错过每日进阶架构文章呀。  !(http://dingyue.ws.126.net/2020/0920/b00fbfc7j00qgy5xy002kd200qo00hsg00it00cj.jpg)  2
Stella981 Stella981
1年前
Angular material mat
IconIconNamematiconcode_add\_comment_addcommenticon<maticonadd\_comment</maticon_attach\_file_attachfileicon<maticonattach\_file</maticon_attach\
Wesley13 Wesley13
1年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
helloworld_34035044 helloworld_34035044
7个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为