目录
1. MyBatis 日志管理
1.1 日志概述
1.2 (logback)日志环境配置
01. pom.xml
02. 运行测试方法
03. 配置logback.xml文件
2. 动态SQL的使用
2.1 概述
2.2 goods.xml
2.3 testDynamicSQL()
2.4 实现效果
3. MyBatis 二级缓存
3.1 缓存范围 与 运行规则
3.2 测试一级缓存
3.3 sqlSession.commit() 强制清空 已有的缓存
3.4 开启二级缓存
(1)查询标签设置useCache="true"开启缓存:
(2)插入标签设置flushCache="true"清空缓存
4. 一对多 & 多对一 对象关联查询
4.1 oneToMany 对象关联查询
(1) 创建 GoodsDetail 实体类
(3)编写 goods_detail.xml 的 selectGoodsDetailByGoodsId 查询标签
(4) mybatis-config.xml 中添加配置
(4) 编写 goods.xml 的 rmGoodsOneToMany结果集
(5)编写 selectOneTo(Many 查询标签
(6) 编写 testOneToMany() 测试方法
(7)debug 效果
4.2 ManyToOne 对象关联查询
(1) 编写 goods_detail.xml 的结果集 rmGoodsDetailManyToOne
(2)编写多对一 查询selectManyToOne 标签
(3)编写测试类 testManyToOne()
(4)debug 效果
5. 分页查询插件PageHelper
5.0 base
(1) 分页查询的麻烦事
5.1 PageHelper
5.2 pageHelper 使用流程
(1) 引入依赖
(2)mybatis-config.xml 中增 Pagehelper 配置
(3)goods.xml 中添加分页查询标签
(4) 编写测试方法 testSelectPage()
(5) 封装实现
(6) 实现效果
6.Mybatis 配置 C3P0 连接池
6.1 引入 c3p0 依赖
6.2 创建C3P0 与 Mybatis 兼容使用的数据源工厂类
6.3 mybatis-config.xml 中配置C3P0 的相关连接信息
7. Mybatis 批处理 SQL
7.1 批量插入
(1)编写SQL标签
(2) 编写测试类
7.2 批量删除
(1) 编写SQL标签
(2)测试方法 testBatchDelete
8. MyBatis 注解开发
8.1 MyBatis 的常用注解
8.2 @Select
(1) 注释掉 mapper 标签
(2) 编写 GoodsDTO1
(3) 编写 GoodsDAO 接口
(4)调整 mybatis-config.xml
(5) 测试 @Select 注解
8.3 @Insert
(1)编写接口
(2)编写测试类
(3)测试testSelectAll()
1. MyBatis 日志管理
1.1 日志概述
1.2 (logback)日志环境配置
01. pom.xml
ch.qos.logback
logback-classic
1.2.11
02. 运行测试方法
@Test
public void testSelectByTitle02() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
Map param = new HashMap();
param.put("title","'爱恩幼 孕妇护肤品润养颜睡眠面膜 100g'");
List list = sqlSession.selectList("goods.selectByTitleAndOrder", param);
for(Goods goods: list){
System.out.println(goods.getSubTitle() + ":" + goods.getDiscount());
}
}catch (Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
03. 配置logback.xml文件
[%thread] %d{HH:mm:ss:SSS} %-5level %logger{36} - %msg%n
2. 动态SQL的使用
2.1 概述
2.2 goods.xml
select * from t_goods
where
1=1
and categroy_id = #{categoryId}
and current_price < #{currentPrice}
-->
select * from t_goods
and category_id = #{categoryId}
and current_price < #{currentPrice}
2.3 testDynamicSQL()
@Test
public void testDynamicSQL() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
Map param = new HashMap();
param.put("categoryId", 44);
param.put("currentPrice", 500);
// 查询条件
List list = sqlSession.selectList("goods.dynamicSQL02", param);
for(Goods goods: list){
System.out.println(goods.getTitle() + ":" +goods.getGoodsId() +goods.getCategoryId() + ":" + goods.getCurrentPrice());
}
}catch (Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
2.4 实现效果
3. MyBatis 二级缓存
3.1 缓存范围 与 运行规则
3.2 测试一级缓存
@Test
public void testLvCache() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
Goods goods1 = sqlSession.selectOne("goods.selectById", 831);
Goods goods2 = sqlSession.selectOne("goods.selectById", 831);
System.out.println(goods1.hashCode() + "-"+ goods2.hashCode());
}catch (Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
在同一个会话中,下一次重复的查询会使用重复记录
3.3 sqlSession.commit() 强制清空 已有的缓存
3.4 开启二级缓存
@Test
public void testLvCache02() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
Goods goods1 = sqlSession.selectOne("goods.selectById", 831);
System.out.println(goods1.hashCode());
}catch (Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
try{
sqlSession = MyBatisUtils.openSession();
Goods goods3 = sqlSession.selectOne("goods.selectById", 831);
System.out.println(goods3.hashCode());
}catch (Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
(1)查询标签设置useCache="true"开启缓存:
select * from t_goods where goods_id = #{value};
(2)插入标签设置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})
4. 一对多 & 多对一 对象关联查询
4.1 oneToMany 对象关联查询
(1) 创建 GoodsDetail 实体类
package com.phdvb.mybatis.entity;
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;
}
}
(2)goods新增 List<GoodsDetail> 结果集
public class Goods {
private Integer goodsId; // 商品编号
private String title; // 商品标题
private String subTitle; // 子标题
private Float originalCost; // 原始价格
private Float currentPrice; // 当前价格
private Float discount; // 折扣率
private Integer isFreeDelivery; // 是否包邮(1-包邮, 0- 不包邮)
private Integer categoryId; // 分类编号
private List goodsDetailList;
(3)编写 goods_detail.xml 的 selectGoodsDetailByGoodsId 查询标签
select * from t_goods_detail where goods_id = #{value}
(4) mybatis-config.xml 中添加配置
(4) 编写 goods.xml 的 rmGoodsOneToMany结果集
(5)编写 selectOneTo(Many 查询标签
select * from t_goods limit 0, 2
(6) 编写 testOneToMany() 测试方法
/**
* 一对多对象 关联查询
*/
@Test
public void testOneToMany() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
List list = sqlSession.selectList("goods.selectOneToMany");
for(Goods goods: list){
System.out.println(goods.getTitle() + ":" + goods.getGoodsDetailList().size());
}
}catch(Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
(7)debug 效果
4.2 ManyToOne 对象关联查询
(1) 编写 goods_detail.xml 的结果集 rmGoodsDetailManyToOne
-->
(2)编写多对一 查询selectManyToOne 标签
select * from t_goods_detail limit 0, 20
(3)编写测试类 testManyToOne()
/**
* 多对一 关联查询
*/
@Test
public void testManyToOne() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
List list = sqlSession.selectList("goodsDetail.selectManyToOne");
for(GoodsDetail goodsDetail: list){
System.out.println(goodsDetail.getGdId() + ":" +goodsDetail.getGoodsId()+ ":" + goodsDetail.getGdPicUrl() + goodsDetail.getGoods().getSubTitle());
}
}catch(Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
(4)debug 效果
5. 分页查询插件PageHelper
5.0 base
(1) 分页查询的麻烦事
5.1 PageHelper
官方链接:
MyBatis 分页插件 PageHelper
如何使用分页插件doc:
如何使用分页插件
5.2 pageHelper 使用流程
(1) 引入依赖
com.github.pagehelper
pagehelper
5.1.8
com.github.jsqlparser
jsqlparser
2.0
(2)mybatis-config.xml 中增 Pagehelper 配置
(3)goods.xml 中添加分页查询标签
select * from t_goods where current_price < 800
(4) 编写测试方法 testSelectPage()
/**
* PageHelper 分页查询
*/
@Test
public void testSelectPage() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
/*
startPage 方法会自动将 下一次查询进行分页
*/
PageHelper.startPage(3,15);
Page page = (Page) sqlSession.selectList("goods.selectPage");
System.out.println("总页数:" + page.getPages()); //总页数:119
System.out.println("总记录数:" + page.getTotal()); //总记录数:1774
System.out.println("开始行号:" + page.getStartRow()); //开始行号:30
System.out.println("结束行号:" + page.getEndRow()); //结束行号:45
System.out.println("当前页码:" + page.getPageNum()); //当前页码:3
List list = page.getResult();
for (Goods good : list) {
System.out.println(good.getGoodsId() +"-"+ good.getSubTitle());
}
System.out.println();
}catch (Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
(5) 封装实现
(6) 实现效果
6.Mybatis 配置 C3P0 连接池
6.1 引入 c3p0 依赖
com.mchange
c3p0
0.9.5.4
6.2 创建C3P0 与 Mybatis 兼容使用的数据源工厂类
package com.phdvb.mybatis.datasource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;
/**
* C3P0 与 Mybatis 兼容使用的数据源工厂类
*/
public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {
public C3P0DataSourceFactory() {
this.dataSource = new ComboPooledDataSource();
}
}
6.3 mybatis-config.xml 中配置C3P0 的相关连接信息
-->
-->
-->
-->
-->
-->
7. Mybatis 批处理 SQL
Q1:
batchInsert 无法获取新增数据的 Id
Q2:
批量生成的SQL太长,可能会被服务器拒绝
7.1 批量插入
(1)编写SQL标签
insert into 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},
)
(2) 编写测试类
@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++ ){ //执行时间:3160毫秒
// for (int i = 0; i < 2; i++ ){ //执行时间:1970毫秒
Goods goods = new Goods();
goods.setTitle("测试商品"+ "i");
goods.setSubTitle("测试子标题"+ "i");
goods.setOriginalCost(300f + i);
goods.setCurrentPrice(500f + i);
goods.setDiscount(i * 0.0001f);
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){
throw e;
}finally{
MyBatisUtils.closeSession(session);
}
}
7.2 批量删除
(1) 编写SQL标签
delete from t_goods where goods_id in
#{item}
(2)测试方法 testBatchDelete
@Test
public void testBatchDelete() throws Exception{
SqlSession session = null;
try{
long st = new Date().getTime();
session = MyBatisUtils.openSession();
List list = new ArrayList();
list.add(12685);
list.add(12684);
list.add(12683);
list.add(12682);
list.add(12681);
session.delete("goods.batchDelete", list);
session.commit();
long et = new Date().getTime();
System.out.println("执行时间:" + (et - st) + "毫秒") ;
}catch(Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(session);
}
}
8. MyBatis 注解开发
将xml代码放入程序中,开发更加方便
8.1 MyBatis 的常用注解
8.2 @Select
(1) 注释掉 mapper 标签
(2) 编写 GoodsDTO1
package com.phdvb.mybatis.dto;
public class GoodsDTO1 {
private Integer goodsId;
private String title;
private Float currentPrice;
public GoodsDTO1() {
}
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;
}
}
(3) 编写 GoodsDAO 接口
package com.phdvb.mybatis.dao;
import com.phdvb.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} limit 0, #{limt}")
public List selectByPriceRange(@Param("min") Float min, @Param("max") Float max, @Param("limt") Integer limt);
}
(4)调整 mybatis-config.xml
-->-->
(5) 测试 @Select 注解
// 测试 @Select 注解
@Test
public void testAnnotationSelectRange() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
GoodsDAO goodsDAO = sqlSession.getMapper(GoodsDAO.class);
List list = goodsDAO.selectByPriceRange(100f, 500f, 20);
System.out.println(list.size());
}catch (Exception e){
throw e;
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
8.3 @Insert
(1)编写接口
@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})")
// 之前使用 获取id
@SelectKey(statement = "select last_insert_id()", before = false, keyProperty = "goodsId", resultType = Integer.class)
public int insert(Goods goods);
(2)编写测试类
// 测试 @Insert 注解
@Test
public void testInsert() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
Goods goods = new Goods();
goods.setTitle("测试Insert goods");
goods.setSubTitle("测试Insert Sub goods");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.55f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
GoodsDAO goodsDAO = sqlSession.getMapper(GoodsDAO.class);
int num = goodsDAO.insert(goods);
sqlSession.commit();
System.out.println(goods.getGoodsId());
}catch (Exception e){
if(sqlSession != null){
sqlSession.rollback();
}
}finally{
MyBatisUtils.closeSession(sqlSession);
}
}
(3)测试testSelectAll()
@Test
public void testSelectAll() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
GoodsDAO goodsDAO = sqlSession.getMapper(GoodsDAO.class);
List list = goodsDAO.selectAll();
System.out.println(list.size()); //11936
}catch (Exception e){
throw e;
}finally {
MyBatisUtils.closeSession(sqlSession);
}
}