Mybatis进阶

Mybatis高级特性

内容 说明 重要程度
Mybatis日志管理 Mybatis日志管理配置 ??
动态SQL处理 多条件查询下的SQL的动态执行过程 ?????
Mybatis缓存机制 介绍mybatis一二级缓存作用与配置 ?????
多表级联查询 配置mybatis多表级联查询 ??
PageHelper插件 mybatis分页插件的使用办法 ?????

1. Mybatis日志管理

1.1 什么是日志

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

1.2 SLF4j与Logback

								日志门面
	Simple Logging Facede For Java			Apache Commons-Logging
			(SLF4J)
								|
							   桥接
								|
							    \/
							   
							 实现日志
			log4j   logback   java.util.logging(jul) 	...

1.3logback



    ch.qos.logback
    logback-classic
    1.2.3



<?xml version="1.0" encoding="UTF-8" ?>

    
        
            [%thread] %d{HH:mm:ss.SSS} %-5level %logger{36} - %msg%n

2.Mybatis动态SQL

2.1动态SQL

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



@Test
public void testDynamicSQL() throws  Exception{
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
        //看这里
        Map param = new HashMap();
        param.put("currentPrice",500);
        param.put("categoryId",44);
        //查询条件
        List list = session.selectList("goods.dynamicSQL", param);
        for (Goods g : list) {
            System.out.println(g.getTitle()+":"+g.getCategoryId() + ":" + g.getCurrentPrice());
        }
    } catch (Exception e) {
        throw  e;
    } finally {
        MybatisUtils.closeSession(session);
    }
}

3.MyBatis二级缓存

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

二级缓存运行规则

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

//一级缓存测试
@Test
public void testLv1Cache() {
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
        Goods goods = session.selectOne("goods.selectById", 739);
        Goods goods1 = session.selectOne("goods.selectById", 739);
        System.out.println(goods.getTitle());
        System.out.println(goods1.getTitle());
        System.out.println(goods.hashCode()+":"+goods1.hashCode());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MybatisUtils.closeSession(session);
    }
    try {
        session = MybatisUtils.openSession();
        Goods goods = session.selectOne("goods.selectById", 739);
        session.commit();//commit提交时对该namespace缓存强制清空
        Goods goods1 = session.selectOne("goods.selectById", 739);
        System.out.println(goods.getTitle());
        System.out.println(goods1.getTitle());
        System.out.println(goods.hashCode()+":"+goods1.hashCode());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MybatisUtils.closeSession(session);
    }
}
//二级缓存测试
@Test
public void testLv2Cache() {
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
        Goods goods = session.selectOne("goods.selectById", 739);
        System.out.println(goods.getTitle());

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MybatisUtils.closeSession(session);
    }
    try {
        session = MybatisUtils.openSession();
        Goods goods = session.selectOne("goods.selectById", 739);
        System.out.println(goods.getTitle());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MybatisUtils.closeSession(session);
    }
}

4.多表级联查询

4.1OneToMany对象关联查询


    
    
        
        
        
        
    
    



    


@Test
public void testOneToMany() {
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
        //看这里
       List  list = session.selectList("goods.selectOneToMany");
       for(Goods g:list){
           System.out.println(g.getTitle());
       }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MybatisUtils.closeSession(session);
    }

}

//Goods实体类
public class Goods {
    private Integer goodsId;
    private String  title;
    private String  subTitle;
    private Float originalCost;
    private Float currentPrice;
    private Float  discount;
    private Integer isFreeDelivery;
    private Integer categoryId;
    private List goodsDetails; //GoodsDetail

    public List getGoodsDetails() {
        return goodsDetails;
    }

    public void setGoodsDetails(List goodsDetails) {
        this.goodsDetails = goodsDetails;
    }

    public Integer getGoodsId() {
        return goodsId;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getSubTitle() {
        return subTitle;
    }

    public void setSubTitle(String subTitle) {
        this.subTitle = subTitle;
    }

    public Float getOriginalCost() {
        return originalCost;
    }

    public void setOriginalCost(Float originalCost) {
        this.originalCost = originalCost;
    }

    public Float getCurrentPrice() {
        return currentPrice;
    }

    public void setCurrentPrice(Float currentPrice) {
        this.currentPrice = currentPrice;
    }

    public Float getDiscount() {
        return discount;
    }

    public void setDiscount(Float discount) {
        this.discount = discount;
    }

    public Integer getIsFreeDelivery() {
        return isFreeDelivery;
    }

    public void setIsFreeDelivery(Integer isFreeDelivery) {
        this.isFreeDelivery = isFreeDelivery;
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    
}

//GoodsDetail实体类
public class GoodsDetail {
    private Integer gdId;
    private Integer goodsId;
    private String gdPicUrl;
    private Integer gdOrder;

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

4.2ManyToOne对象关联查询



    
    
    






@Test
public void testManyToOne() {
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
        //看这里
        List  list = session.selectList("goodsDetail.selectManyToOne");
        for(GoodsDetail gd:list){
            System.out.println(gd.getGdPicUrl()+":"+gd.getGoods().getTitle());
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MybatisUtils.closeSession(session);
    }

}

//GoodsDetail实体类
public class GoodsDetail {
    private Integer gdId;
    private Integer goodsId;
    private String gdPicUrl;
    private Integer gdOrder;
    private Goods goods; //Goods实体

    public Goods getGoods() {
        return goods;
    }

    public void setGoods(Goods goods) {
        this.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;
    }
}

//Goods实体类
public class Goods {
    private Integer goodsId;
    private String  title;
    private String  subTitle;
    private Float originalCost;
    private Float currentPrice;
    private Float  discount;
    private Integer isFreeDelivery;
    private Integer categoryId;
    private List goodsDetails;

    public List getGoodsDetails() {
        return goodsDetails;
    }

    public void setGoodsDetails(List goodsDetails) {
        this.goodsDetails = goodsDetails;
    }

    public Integer getGoodsId() {
        return goodsId;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getSubTitle() {
        return subTitle;
    }

    public void setSubTitle(String subTitle) {
        this.subTitle = subTitle;
    }

    public Float getOriginalCost() {
        return originalCost;
    }

    public void setOriginalCost(Float originalCost) {
        this.originalCost = originalCost;
    }

    public Float getCurrentPrice() {
        return currentPrice;
    }

    public void setCurrentPrice(Float currentPrice) {
        this.currentPrice = currentPrice;
    }

    public Float getDiscount() {
        return discount;
    }

    public void setDiscount(Float discount) {
        this.discount = discount;
    }

    public Integer getIsFreeDelivery() {
        return isFreeDelivery;
    }

    public void setIsFreeDelivery(Integer isFreeDelivery) {
        this.isFreeDelivery = isFreeDelivery;
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }


}

5. PageHelper分页插件

5.1 分页查询的麻烦事

1. 当前页数据查询 - select * from tab limit 0 , 10
2. 总记录数查询 - select count（*） from tab
3. 程序计算总页数、上一页页码、下一页页码

5.2 PageHelper使用流程

1.maven引入PageHelper与jsqlparser
2.mybatis-config.xml增加Plugin配置
3.代码中使用PageHelper.startPage()自动分页




    com.github.pagehelper
    pagehelper
    5.1.10



    com.github.jsqlparser
    jsqlparser
    2.0





    
        
        
        
        
    


/**
 * PageHelper分页查询
 */
@Test
public void testSelectPage() {
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
        //startPage方法会自动将下一次查询进行分页
        PageHelper.startPage(2,10);
        Page 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 data=page.getResult();//当前页数据
        for(Goods g:data){
            System.out.println(g.getTitle());
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        MybatisUtils.closeSession(session);
    }
}

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

5.3.1 MySQL分页

select * from table limit 10,20;

5.3.2 Oracle分页

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

5.3.3 SQL Server 2000 分页

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

5.3.4 SQL Server 2012+ 分页

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

6. MyBatis整合C3P0连接池




    com.mchange
    c3p0
    0.9.5.4


import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;

/**
 * C3P0与Mybatis兼容使用的数据源工厂类
 */
public class C3P0DataSource extends UnpooledDataSourceFactory {
    public C3P0DataSource(){
        this.dataSource=new ComboPooledDataSource();
    }

}

7. MyBatis批处理

7.1 批量插入数据

 
 

    INSERT INTO babytun.t_goods( title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
    VALUES
    
        (#{item.title},#{item.subTitle},#{item.originalCost},#{item.currentPrice},#{item.discount},#{item.isFreeDelivery},#{item.categoryId})
    


  /**
   * 批量插入数据
   * @throws Exception
   */
@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<1000;i++){
            Goods goods=new Goods();
            goods.setTitle("批量插入测试数据");
            goods.setSubTitle("批量插入测试子标题");
            goods.setOriginalCost(100f);
            goods.setCurrentPrice(100f);
            goods.setDiscount(1f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(43);
            //insert()方法返回值代表本次成功插入的记录总数
            list.add(goods);
        }
        int num = 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);
    }
}

7.2 批量删除数据


    delete from t_goods where goods_id in
    
        #{item}
    


/**
 * 批量删除数据
 * @throws Exception
 */
@Test
public void testBatchDelete() throws Exception {
    SqlSession session = null;
    try {
        long st=new Date().getTime();//开始时间
        session = MybatisUtils.openSession();
        List list=new ArrayList();
        for(int i=12684;i<13683;i++){
            list.add(i);
        }
        int num = session.insert("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);
    }
}

8.MyBatis注解开发

8.1 Mybatis常用注解

注解 对应XML 说明
@Insert < insert > 新增SQL
@Update < update> 更新SQL
@Delete < delete> 删除SQL
@Select < select > 查询SQL
@Param - - 参数映射
@Results < resultMap > 结果映射
@Result < id >< result > 字段映射

8.2 查询 @Select

   

        
        

    

//goodsDAO.class 这个名千万不要写成goodsDao  一定要写成xxxDAO
import com.xiaofeng.mybatis.entity.Goods;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

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

@Test
public void testSelectByPriceRange() throws Exception {
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
        //看这里
        GoodsDAO goodsDAO= session.getMapper(GoodsDAO.class);
        List list=goodsDAO.selectByPriceRange(100f,500f,20);
        System.out.println(list.size());

    } catch (Exception e) {
        throw  e;
    } finally {
        MybatisUtils.closeSession(session);
    }
}

8.3 新增 @Insert

import com.xiaofeng.mybatis.entity.Goods;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;

import java.util.List;

public interface 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);
}

@Test
public void testInsert() throws Exception {
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
        //看这里
        Goods goods = new Goods();
        goods.setTitle("测试数据");
        goods.setSubTitle("测试子标题");
        goods.setOriginalCost(300f);
        goods.setCurrentPrice(200f);
        goods.setDiscount(0.7f);
        goods.setIsFreeDelivery(0);
        goods.setCategoryId(44);
        GoodsDAO goodsDAO= session.getMapper(GoodsDAO.class);
         //insert()方法返回值代表本次成功插入的记录总数
        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);
    }
}

8.4 结果映射 @Results

//GoodsDAO.java
import com.xiaofeng.mybatis.dto.GoodsDTO;
import com.xiaofeng.mybatis.entity.Goods;
import org.apache.ibatis.annotations.*;

import java.util.List;

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


@Test
public  void testSelectAll() throws Exception{
    SqlSession session = null;
    try {
        session = MybatisUtils.openSession();
		//看这里
        GoodsDAO goodsDAO= session.getMapper(GoodsDAO.class);
        List list=goodsDAO.selectAll();
        System.out.println(list.size());

    } catch (Exception e) {

        throw  e;
    } finally {
        MybatisUtils.closeSession(session);
    }
}

//GoodsDTO.java
//Data Transfer Object 数据传输对象
public class GoodsDTO {
    private Integer goodsId;
    private String title;
    private Float currentPrice;

    public Integer getGoodsId() {
        return goodsId;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Float getCurrentPrice() {
        return currentPrice;
    }

    public void setCurrentPrice(Float currentPrice) {
        this.currentPrice = currentPrice;
    }
}
mybatis

相关

mybatis的一级缓存和二级缓存

Springboot配置tk-mybatis

jdbc,jpa,springjdbc，springdatajpa,mybatis之间的区别

Mybatis： 简介

SpringBoot学习- 3、整合MyBatis

spring-boot mybatis配置

SpringBoot+Mybatis加载Mapper.xml文件的两种方式

mybatis-plus 多租户屏蔽某个特定mapper的方法@InterceptorIgnore

[Mybatis]2.增删改

MyBatis学习总结（二）——MyBatis核心配置文件与输入输出映射

MyBatis学习笔记

mybatis的Could not set property ‘id’ of ‘com.*.sys.entity’ with value ‘****

标签