当前位置: 首页 > news >正文

(1-10-2)MyBatis 进阶篇 - 教程

目录

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 实体类

(2)goods新增 List结果集

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

http://www.hskmm.com/?act=detail&tid=13685

相关文章:

  • 联想拯救者无法登录当前账户
  • WPF二合一平板电脑上屏幕旋转时获取屏幕宽高问题
  • 代码中的善意:构建人性化的软件开发文化
  • 超级恶心的题面 [USACO21OPEN] Portals G
  • 如何隐藏一个元素
  • 昆仑通态触摸屏保存参数到内部存储器并读取的方法成都控制器开发提供
  • helloword
  • 使用reCAPTCHA提升WordPress网站安全性 - 指南
  • 软工9.22
  • 在控制台执行可列出所有placeholder样式
  • 今日总结
  • 9/22
  • 对于一门古老东欧玄学的初步研究的简要报告
  • Codeforces 2127 D(图论,组合数学,DFS,分类讨论)
  • Java学习笔记:从三个实验看编程思维的锤炼
  • 题解:AT_arc068_d [ARC068F] Solitaire
  • Codeforces Round 1051 (Div. 2) D1D2题解
  • JSP
  • 每日博客
  • 探展打卡 Serverless,2025 云栖大会来了
  • 从 0 到 1,AI 走进服装店:记住每位顾客的喜好,比你还靠谱
  • STM32HAL 飞快入门(十九):UART 编程(二)—— 中断方式实现收发及局限分析
  • 贪心算法应用:多重背包启发式疑问详解
  • 划重点|云栖大会「AI 原生应用架构论坛」看点梳理
  • 君子如水,心中有火:vivo本心而为30周年
  • Margin 塌陷问题如何解决?触发BFC。BFC的概念和触发条件
  • 9.22
  • 数字统计
  • 火速收藏!2025 云栖大会 AI 中间件议程看点全公开(附免费报名通道)
  • 第二次软工作业——个人项目 - LXJ