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
<!--logback maven依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<!--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>
2.Mybatis动态SQL
2.1动态SQL
动态SQL是指根据参数数据动态组织SQL的技术
<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.xiaofeng.mybatis.entity.Goods">
select * from t_goods
<where>
<if test="categoryId!=null">
and category_id=#{categoryId}
</if>
<if test="currentPrice!=null">
and current_price<#{currentPrice}
</if>
</where>
</select>
@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<Goods> 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);
}
}
<!-- 开启了二级缓存-->
<!--
eviction是缓存的清除策略，当缓存对象数量达到上限后，自动触发对应算法对缓存对象清除
1.LRU - 最近最久未使用：移除最长时间不被使用的对象
o1 o2 o3 o4 ... o512
14 99 83 1 ... 893
2.FIFO - 先进先出：按对象进入缓存的顺序来移除它们
3.SOFT - 软引用： 移除基于垃圾收集器状态和软引用规则的对象(jvm)
4.WEAK - 弱引用：更积极的移除基于垃圾收集器状态和弱引用规则的对象（jvm）
5.LFU - 最近最少使用：移除最近访问频率最低的对象
flushInterval 代表间隔多长时间自动清空缓存，单位毫秒，600000毫秒=10分钟
size 缓存存储上限，用于保存对象或集合（1个集合算1个对象）的数量上限
readOnly 设置为true，代表返回只读缓存，每次从缓存取出放入是缓存对象本身，这种执行效率较高
设置为false，代表每次取出的是缓存对象的“副本”，每一次取出的对象都是不同的，这种安全性较高
-->
<cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"/>
4.多表级联查询
4.1OneToMany对象关联查询
<!--goods.xml-->
<!--
resultMap可用于说明一对多或者多对一的映射逻辑
id是resultMap属性引用的标志
type是指向One的实体（Goods）
-->
<resultMap id="rmGoods1" type="com.xiaofeng.mybatis.entity.Goods">
<!--映射goods对象的主键到goods_id字段-->
<id column="goods_id" property="goodsId"></id>
<!--
collection的含义是，
在select * from t_goods limit 0,10 得到结果后，对所有Goods对象遍历得到goods_id字段值，并代入到goodsDetail命名空间的selectByGoodsId的SQL中进行查询，将得到的“商品详情”集合赋值给goodsDetail List 对象
-->
<collection property="goodsDetails" select="goodsDetail.selectByGoodsId" column="goods_id"/>
</resultMap>
<select id="selectOneToMany" resultMap="rmGoods1">
select * from t_goods limit 0 ,1
</select>
<!--goods_detail.xml-->
<mapper namespace="goodsDetail">
<select id="selectByGoodsId" resultType="com.xiaofeng.mybatis.entity.GoodsDetail" parameterType="Integer">
select * from t_goods_detail where goods_id=#{value}
</select>
</mapper>
@Test
public void testOneToMany() {
SqlSession session = null;
try {
session = MybatisUtils.openSession();
//看这里
List<Goods> 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<GoodsDetail> goodsDetails; //GoodsDetail
public List<GoodsDetail> getGoodsDetails() {
return goodsDetails;
}
public void setGoodsDetails(List<GoodsDetail> 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对象关联查询
<!--goods_detail.xml-->
<resultMap id="rmGoodsDetail" type="com.xiaofeng.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,20
</select>
<!--goods.xml-->
<select id="selectById" parameterType="Integer" resultType="com.xiaofeng.mybatis.entity.Goods">
select * from t_goods where goods_id=#{value}
</select>
@Test
public void testManyToOne() {
SqlSession session = null;
try {
session = MybatisUtils.openSession();
//看这里
List<GoodsDetail> 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<GoodsDetail> goodsDetails;
public List<GoodsDetail> getGoodsDetails() {
return goodsDetails;
}
public void setGoodsDetails(List<GoodsDetail> 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()自动分页
<!--maven pom.xml-->
<!--pageHelper分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
<!--sql 解析工具-->
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>2.0</version>
</dependency>
<!--mybatis-config.xml-->
<!--启用PageHelper分页插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!--设置数据库类型-->
<property name="helperDialect" value="mysql"/>
<!--分页合理化-->
<property name="reasonable" value="true"/>
</plugin>
</plugins>
/**
* PageHelper分页查询
*/
@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());
}
} 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连接池
<!--maven pox.xml-->
<!--c3p0连接池-->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.4</version>
</dependency>
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();
}
}
<!-- mybatis-config.xml -->
<environment id="dev">
<!--采用JDBC方式对数据库事务进行commit/rollback-->
<transactionManager type="JDBC"></transactionManager>
<!--采用C3P0连接池方式管理数据库连接-->
<dataSource type="com.xiaofeng.mybatis.datasource.C3P0DataSource">
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
<property name="jdbcUrl"
value="jdbc:mysql://localhost:3306/babytun?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&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>
</environment>
7. MyBatis批处理
7.1 批量插入数据
<!--
批量插入数据的局限：
1.无法获得插入数据的id
2.批量生成的SQL太长，可能会被服务器拒绝
-->
<!--
insert into table
value("a","a1","a2"),("b","b1","b2")
-->
<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO babytun.t_goods( title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.title},#{item.subTitle},#{item.originalCost},#{item.currentPrice},#{item.discount},#{item.isFreeDelivery},#{item.categoryId})
</foreach>
</insert>
/**
* 批量插入数据
* @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 id="batchDelete" parameterType="java.util.List">
delete from t_goods where goods_id in
<foreach collection="list" index="index" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
/**
* 批量删除数据
* @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
<!--mybatis-config.xml-->
<mappers>
<!--两者二选一即可-->
<mapper class="com.xiaofeng.mybatis.dao.GoodsDAO"/>
<!-- <package name="com.xiaofeng.mybatis.dao"/>-->
</mappers>
//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<Goods> 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<Goods> 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")
//<resultMap>
@Results({
//<id>
@Result(column = "goods_id", property = "goodsId", id = true),
//<result>
@Result(column = "title", property = "title"),
@Result(column = "current_price", property = "currentPrice")
})
public List<GoodsDTO> selectAll();
}
@Test
public void testSelectAll() 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) {
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;
}
}