MyBatis进阶

MyBatis进阶

什么是日志

• 日志文件是用于记录系统操作事件的记录文件或文件集合
• 日志保存历史数据，是诊断问题以及理解系统活动的重要依据

SLF4j与Logback

日志的使用:

1. 引入logback依赖

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

2. 创建logback.xml，并写入如下代码：

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

动态SQL

• 动态SQL是指根据参数数据动态组织SQL的技术

代码示例：

goods.xml中加入如下sql语句

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

MyBatisTestor.java中使用dynamicSelect

@Test
public void testDynamicSQL(){
    SqlSession session = null;
    try {
        session = MyBatisUtils.openSession();
        HashMap<String, Object> param = new HashMap<>();
        param.put("categoryId", 44);
        param.put("currentPrice", 500);

        List<Goods> list = session.selectList("goods.dynamicSelect", param);
        for (Goods goods : list) {
            System.out.println(goods.getTitle());
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MyBatisUtils.closeSession(session);
    }
}

MyBatis二级缓存

• 一级缓存默认开启，缓存范围SqlSession会话
• 二级缓存手动开启，属于Mapper Namespace

缓存的范围

二级缓存运行规则

• 二级开启后默认所有查询操作均使用缓存
• 写操作commit提交时对该namespace缓存强制清空
• 配置useCache=false可以不用缓存
• 配置flushCache=true代表强制清空缓存

二级缓存的使用：

goods.xml

<?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="goods">
    <!--开启了二级缓存
        1.LRU - 最近最久未使用：移除最长时间不被使用的对象
        2.LFU - 最近最少使用：移除最近访问频率最低的对象
        3.FIFO - 先进后出：按对象进入缓存的顺序来移除他们。
        4.SOFT - 软引用：移除基于垃圾收集器状态和软引用规则的对象
        5.WEAK - 弱引用：更积极的移除基于垃圾收集器状态和弱引用规则的对象。

        flushInterval 代表间隔多长时间自动清空缓存，单位毫秒，600000毫秒 = 10分钟
        size 缓存存储上限，用于保存对象或集合（1个集合算1个对象）的数量上限
        readOnly 设置为true，代表返回只读缓存，每次从缓存取出的是缓存对象本身，这种执行效率较高
                 设置为false，代表每次取出的是缓存对象的“副本”，每一次取出的对象都是不同的，这种安全性较高
    -->
    <cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"></cache>
    <!-- useCache="false"代表不使用缓存-->
    <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods" useCache="false">
        select * from t_goods order by goods_id desc limit 10
    </select>

    <!--单参数传递，使用parameterType指定参数的数据类型即可，SQL#{value}提取参数-->
    <select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where goods_id = #{value}
    </select>
    <!--多参数传递时，使用parameterType指定java.util.Map, SQL中#{key提取参数}-->
    <select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods
        where
            current_price between #{min} and #{max}
        order by current_price
        limit 0, #{limit}
    </select>

    <!--利用LinkedHashMap保存多表关联结果
        MyBatis会将每一条记录包装为LinkedHashMap对象
        key是字段名 value是字段对应的值，字段类型根据表结构进行自动判断
        优点：易于扩展，易于使用
        缺点：太过灵活，无法进行编译时检查
    -->
    <select id="selectGoodsMap" resultType="java.util.LinkedHashMap" flushCache="true">
        select g.*,  c.category_name, "1" as test from t_goods g, t_category c
        where g.category_id = c.category_id
    </select>

    <!--结果映射-->
    <resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
        <!--设置主键字段与属性映射-->
        <id property="goods.goodsId" column="goods_id"></id>
        <!--设置非主键字段与属性映射-->
        <result property="goods.title" column="title"></result>
        <result property="goods.originalCost" column="original_cost"></result>
        <result property="goods.currentPrice" column="current_price"></result>
        <result property="goods.discount" column="discount"></result>
        <result property="goods.isFreeDelivery" column="is_free_delivery"></result>
        <result property="goods.categoryId" column="category_id"></result>
        <result property="category.categoryId" column="category_id"></result>
        <result property="category.categoryName" column="category_name"></result>
        <result property="category.categoryId" column="category_id"></result>
        <result property="category.parentId" column="parent_id"></result>
        <result property="category.categoryLevel" column="category_level"></result>
        <result property="category.categoryOrder" column="category_order"></result>

        <result property="test" column="test"></result>
    </resultMap>
    <select id="selectGoodsDTO" resultMap="rmGoods">
        select g.*,  c.*, "1" as test from t_goods g, t_category c
        where g.category_id = c.category_id
    </select>

    <!--flushCache="true"在sql执行后强制清空缓存-->
    <insert id="insert" parameterType="com.imooc.mybatis.entity.Goods"
    useGeneratedKeys="true" keyProperty="goodsId" keyColumn="goods_id" flushCache="true">
        insert into t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
        values(#{title}, #{subTitle}, #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
        <!--更新goods中的id-->
        <!--<selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
            select last_insert_id();
        </selectKey>-->
    </insert>

    <update id="update" parameterType="com.imooc.mybatis.entity.Goods">
        update t_goods
        set
            title = #{title},
            sub_title = #{subTitle},
            original_cost = #{originalCost},
            current_price = #{currentPrice},
            discount = #{discount},
            is_free_delivery = #{isFreeDelivery},
            category_id = #{categoryId}
        where goods_id = #{goodsId}
    </update>

    <delete id="delete" parameterType="Integer">
        delete from t_goods where goods_id = #{value}
    </delete>

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

商品和详情对象关联查询

​ 关联商品与商品详情信息

代码案例（一对多）：

在Goods.java中添加goodDetail集合成员

创建goods_detail.xml

<?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="goodsDetail">
    <select id="selectByGoodsId" parameterType="Integer" resultType="com.imooc.mybatis.entity.GoodsDetail">
        select * from t_goods_detail where goods_id = #{value}
    </select>
</mapper>

在mybatis-config.xml中添加goods_detail.xml的映射

<mapper resource="mappers/goods_detail.xml"></mapper>

在goods.xml编写select语句

<!--
        resultMap可用于说明一对多或者多对一的映射逻辑
        id 是resultMap属性引用的标志
        type 指向One的实体(Goods)
    -->
<resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
    <!--映射Goods对象的主键到goods_id字段-->
    <id column="goods_id" property="goodsId"></id>
    <!--
            collection的含义是，
            在select * from t_goods limit 0, 1得到结果后，对所有Goods对象遍历得到goods_id字段值，
            并带入到goodsDetail命名空间的findByGoodsId的SQL中执行查询
            将得到的“商品详情”集合赋值给goodsDetail List对象
        -->
    <collection property="goodsDetails" select="goodsDetail.selectByGoodsId" column="goods_id"/>
</resultMap>
	<select id="selectOneToMany" resultMap="rmGoods1">
    select * from t_goods limit 0,10
</select>

在MyBatisUtils中编写测试代码

    @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);
        }
    }

代码案例（多对一）：

在GoodsDetail.java中添加goods成员

在goods_detail.xml中添加select语句

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

在MyBatisTestor.java中添加如下测试代码：

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

分页插件PageHelper

分页查询的麻烦事

• 当前页数据查询 - select * from tab limit 0, 10
• 总记录数查询 - select count(*) from tab
• 程序计算总页数、上一页码、下一页码

PageHelper使用流程

• maven引入PageHelper与jsqlparser

  <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.10</version>
  </dependency>
  <dependency>
      <groupId>com.github.jsqlparse</groupId>
      <artifactId>jsqlparser</artifactId>
      <version>2.0</version>
  </dependency>
  

• mybatis-config.xml增加Plugin配置

  <!-- 启用PageHelper分页插件 -->
  <plugins>
      <plugin interceptor="com.github.pagehelper.PageInterceptor">
          <!-- 设置数据库类型 -->
          <property name="helperDialect" value="mysql"/>
          <!-- 分页合理化 -->
          <property name="reasonable" value="true"/>
      </plugin>
  </plugins>
  

• 代码中使用PageHelper.startPage()自动分页

  goods.xml中的查询语句

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

  MyBatisTestor.java中的查询代码

  @Test
  public void testSelectPage(){
      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);
      }
  }
  

不同数据库分页的实现原理

MySQL分页

select * from table limit 10, 20;

Oracle分页（商业数据库NO.1）

select t3.from(
	select t2.* rownum as rownum from(
    	select * from table order by id asc
    )t2 where rownum <=20
)t3
where 52.row_num>11

SQL Server 2000

select top 3 * from table
where 
	id not in
	(select top 15 id from table)

SQL Server 2012+

​

select * from table order by id
	offset 4 rows fetch next 5 row only

MyBatis整合C3P0连接池

1. 引入C3P0的依赖

   <dependency>
       <groupId>com.mchange</groupId>
       <artifactId>c3p0</artifactId>
       <version>0.9.5.4</version>
   </dependency>
   

2. 编写C3P0DataSourceFactory继承自UnpooledDataSourceFactory

   package com.imooc.mybatis.datasource;
   
   import com.mchange.v2.c3p0.ComboPooledDataSource;
   import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;
   
   /**
    * C3P0与MyBatis兼容使用的数据源工厂类
    * @author CubeMonkey
    * @create 2020-10-29 20:04
    */
   public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {
   
       public C3P0DataSourceFactory(){
           this.dataSource = new ComboPooledDataSource();
       }
   }
   
   

3. 在mybatis-config.xml中配置c3p0数据库连接池

   <dataSource type="com.imooc.mybatis.datasource.C3P0DataSourceFactory">
       <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
       <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=GMT&amp;useSSL=false&amp;allowPublicKeyRetrieval=true"/>
       <property name="user" value="root"/>
       <property name="password" value="root"/>
       <property name="initialPoolSize" value="5"/>
       <property name="maxPoolSize" value="20"/>
       <property name="minPoolSize" value="5"/>
   </dataSource>
   

MyBatis批处理

批量插入数据的局限：

1. 无法获得插入数据的id
2. 批量生成的SQL太长，可能会被服务器拒绝（可采用数据分段的方式）

批量插入：

goods.xml中的insert语句

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

MyBatisTestor.java中testInsertBatch()

@Test
public void testInsertBatch(){
    SqlSession session = null;
    try {
        session = MyBatisUtils.openSession();
        long st = new Date().getTime();
        List<Goods> list = new ArrayList<>();
        for(int i = 0; i < 1000; 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.insertBatch", list);
        session.commit();
        long et = new Date().getTime();
        System.out.println("执行时间：" + (et-st) + "毫秒");//1179
    } catch (Exception e) {
        if (session != null){
            session.rollback();
        }
    } finally {
        MyBatisUtils.closeSession(session);
    }
}

批量删除：

goods.xml中delet语句：

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

MyBatisTestor.java中testDeleteBatch()

@Test
public void testDeleteBatch(){
    SqlSession session = null;
    try {
        session = MyBatisUtils.openSession();
        long st = new Date().getTime();
        List<Integer> list = new ArrayList<>();
        list.add(1920);
        list.add(1921);
        list.add(1922);
        session.insert("goods.deleteBatch", list);
        session.commit();
        long et = new Date().getTime();
        System.out.println("执行时间：" + (et-st) + "毫秒");//725毫秒
    } catch (Exception e) {
        if (session != null){
            session.rollback();
        }
    } finally {
        MyBatisUtils.closeSession(session);
    }
}

MyBatis注解

注解和xml配置的优点：

• xml有更好的维护性，更适合大型的需要团队协作的项目
• 注解有更好的程序编码体验，更适合小型敏捷开发的工程

MyBatis常用注解

注解	对应XML	说明
@Insert		新增SQL
@Update		更新SQL
@Delect		删除SQL
@Select		查询SQL
@Param	–	参数映射
@Results		结果映射
@Result		字段映射

准备工作（不再使用之前用的工程）；

1. 引入mybatis、logback日志、数据库驱动的依赖：

   <dependency>
       <groupId>org.mybatis</groupId>
       <artifactId>mybatis</artifactId>
       <version>3.5.1</version>
   </dependency>
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.47</version>
   </dependency>
   <dependency>
       <groupId>ch.qos.logback</groupId>
       <artifactId>logback-classic</artifactId>
       <version>1.2.3</version>
   </dependency>
   

2. 配置mybatis-config.xml

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE configuration
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-config.dtd">
   <configuration>
       <settings>
           <!-- goods_id ==> goodsId 驼峰命名转换 -->
           <setting name="mapUnderscoreToCamelCase" value="true"/>
       </settings>
   
       <!--设置默认指向的数据库-->
       <environments default="dev">
           <!--配置环境，不同的环境不同的id名字-->
           <environment id="dev">
               <!-- 采用JDBC方式对数据库事务进行commit/rollback -->
               <transactionManager type="JDBC"></transactionManager>
               <!--采用连接池方式管理数据库连接-->
               <dataSource type="POOLED">
                   <property name="driver" value="com.mysql.jdbc.Driver"/>
                   <property name="url" value="jdbc:mysql://localhost:3306/babytun?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;allowPublicKeyRetrieval=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                   <property name="username" value="root"/>
                   <property name="password" value="root"/>
               </dataSource>
           </environment>
       </environments>
      
   </configuration>
   

3. 配置好logback.xml

   <?xml version="1.0" encoding="UTF-8"?>
   <configuration>
      <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
          <encoder>
              <pattern>[%thread] %d{HH: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>
   

4. 在java目录下创建一个dao包，在dao下创建GoodsDAO

5. 在mybatis-config.xml中配置mapper映射

   <mappers>
       <!--当有多个dao接口时，mapper不便于维护-->
       <!--        <mapper class="com.imooc.mybatis.dao.GoodsDAO"></mapper>-->
       <package name="com.imooc.mybatis.dao"/>
   </mappers>
   

按价格范围查询商品的代码：

在GoodsDAO下写入如下方法：

@Select("select * from t_goods where current_price between #{min} and #{max} order by current_price limit 0, #{limt}")
public List<Goods> selectByPriceRange(@Param("min") Float min, @Param("max") Float max, @Param("limt") Integer limt);

在MyBatisTestor中使用这个方法:

@Test
public void testSelectByPrice(){
    SqlSession session = null;
    try {
        session = MyBatisUtils.openSession();
        GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
        List<Goods> list = goodsDAO.selectByPriceRange(100f, 500f, 20);
        System.out.println(list.size());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MyBatisUtils.closeSession(session);
    }
}

插入商品的代码：

在GoodsDAO下写入如下方法：

@Insert("INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id) values(#{title}, #{subTitle}, #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})")
@SelectKey(statement = "select last_insert_id()", before = false, keyProperty = "goodsId", resultType = Integer.class)
public int insert(Goods goods);

在MyBatisTestor中使用这个方法:

@Test
public void testInsert() throws Exception {
    SqlSession session = null;
    try {
        session = MyBatisUtils.openSession();
        Goods goods = new Goods();
        goods.setTitle("测试商品");
        goods.setSubTitle("测试子标题");
        goods.setOriginalCost(200f);
        goods.setCurrentPrice(100f);
        goods.setDiscount(0.5f);
        goods.setIsFreeDelivery(1);
        goods.setCategoryId(43);
        GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
        int num = goodsDAO.insert(goods);
        session.commit();
        System.out.println(goods.getGoodsId());
    } catch (Exception e) {
        if (session != null){
            session.rollback();
        }
        throw e;
    } finally {
        MyBatisUtils.closeSession(session);
    }
}

结果映射的代码：

在GoodsDAO下写入如下方法：

@Select("select * from t_goods")
//<resultMap>
@Results({
    @Result(column = "goods_id", property = "goodsId", id = true),
    @Result(column = "title", property = "title"),
    @Result(column = "current_price", property = "currentPrice")
})
public List<GoodsDTO> selectAll();

在MyBatisTestor中使用这个方法:

@Test
public void selectAll() throws Exception {
    SqlSession session = null;
    try {
        session = MyBatisUtils.openSession();
        GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
        List<GoodsDTO> list = goodsDAO.selectAll();
        System.out.println(list.size());
    } catch (Exception e) {
        session.rollback();
        throw e;
    } finally {
        MyBatisUtils.closeSession(session);
    }
}