项目结构
以下为总的项目结构,仅供更好的认识项目的框架,建议根据下面的内容一步步构造项目结构
MyBatis日志管理
使用日志步骤
< dependency>
< groupId> ch.qos.logback</ groupId>
< artifactId> logback-classic</ artifactId>
< version> 1.2.3</ version>
</ dependency>
在执行sql语句后会在控制台打印出sql执行的log日志
[main] 18:43:20.402 DEBUG goods.selectByTitle - ==> Preparing: select * from t_goods where title = ? order by title desc
[main] 18:43:20.445 DEBUG goods.selectByTitle - ==> Parameters: '' or 1=1 or title='【德国】爱他美婴幼儿配方奶粉1段800g*2罐 铂金版'(String)
[main] 18:43:20.574 DEBUG goods.selectByTitle - <== Total: 0
logback允许对日志进行自定义。具体做法是在resources目录下新增logback.xml文件,提供logback配置项
<?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>
< root level = " debug" >
< appender-ref ref = " console" />
</ root>
</ configuration>
MyBatis动态SQL
写代码
在goods.xml中加入新的<select>标签
< mapper>
< select id = " dynamicSQL" 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 < #{currentPrice}
</ if>
</ where>
</ select>
</ mapper>
@Test
public void testDynamicSQL ( ) throws Exception {
SqlSession session = null;
try {
session = MyBatisUtils. openSession ( ) ;
Map param = new HashMap ( ) ;
param. put ( "categoryId" , 44 ) ;
param. put ( "currentPrice" , 500 ) ;
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) ;
}
}
MyBatis二级缓存
缓存范围 二级缓存运行规则
写代码
在MybatisTestor中添加测试用例,测试selectById这个SQL语句
@Test
public void testLv1Cache ( ) throws Exception {
SqlSession session = null;
try {
session = MyBatisUtils. openSession ( ) ;
Goods goods = session. selectOne ( "goods.selectById" , 1603 ) ;
Goods goods1 = session. selectOne ( "goods.selectById" , 1603 ) ;
System. out. println ( goods. hashCode ( ) + ":" + goods1. hashCode ( ) ) ;
} catch ( Exception e) {
throw e;
} finally {
MyBatisUtils. closeSession ( session) ;
}
[main] 19:27:30.743 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id = ?
[main] 19:27:30.782 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 19:27:30.802 DEBUG goods.selectById - <== Total: 1
[main] 19:27:30.807 DEBUG goods - Cache Hit Ratio [goods]: 0.0
1427646530:1427646530
在此基础上再添加一个SqlSession,测试结果
@Test
public void testLv1Cache ( ) throws Exception {
SqlSession session = null;
try {
session = MyBatisUtils. openSession ( ) ;
Goods goods = session. selectOne ( "goods.selectById" , 1603 ) ;
Goods goods1 = session. selectOne ( "goods.selectById" , 1603 ) ;
System. out. println ( goods. hashCode ( ) + ":" + goods1. hashCode ( ) ) ;
} catch ( Exception e) {
throw e;
} finally {
MyBatisUtils. closeSession ( session) ;
}
try {
session = MyBatisUtils. openSession ( ) ;
Goods goods = session. selectOne ( "goods.selectById" , 1603 ) ;
Goods goods1 = session. selectOne ( "goods.selectById" , 1603 ) ;
System. out. println ( goods. hashCode ( ) + ":" + goods1. hashCode ( ) ) ;
} catch ( Exception e) {
throw e;
} finally {
MyBatisUtils. closeSession ( session) ;
}
}
可以看到,两个SqlSession产生了两个缓存地址,互不相同,但在各自的SqlSession中的缓存地址一致
[main] 23:53:31.335 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id = ?
[main] 23:53:31.380 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 23:53:31.406 DEBUG goods.selectById - <== Total: 1
1764696127:1764696127
[main] 23:53:31.411 DEBUG o.a.i.t.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@478190fc [wrapping: com.mysql.jdbc.JDBC4Connection@79e2c065]]
[main] 23:53:31.412 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@478190fc [wrapping: com.mysql.jdbc.JDBC4Connection@79e2c065]]
[main] 23:53:31.413 DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection
[main] 23:53:31.413 DEBUG o.a.i.t.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@1aafa419 [wrapping: com.mysql.jdbc.JDBC4Connection@79e2c065]]
[main] 23:53:31.414 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id = ?
[main] 23:53:31.414 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 23:53:31.415 DEBUG goods.selectById - <== Total: 1
1108924067:1108924067
< mapper>
< cache eviction = " LRU" flushInterval = " 600000" size = " 512" readOnly = " true" />
< select id = " selectAll" resultType = " com.imooc.mybatis.entity.Goods" useCache = " false" >
select * from t_goods order by goods_id desc limit 10
</ select>
< select id = " selectById" parameterType = " Integer" resultType = " com.imooc.mybatis.entity.Goods" >
select * from t_goods where goods_id = #{value}
</ select>
< 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>
</ mapper>
在MybatisTestor中添加测试用例,测试二级缓存,通过测试两个不同的SqlSession,一样的SQL语句获得数据的方式来判断
@Test
public void testLv2Cache ( ) throws Exception {
SqlSession session = null;
try {
session = MyBatisUtils. openSession ( ) ;
Goods goods = session. selectOne ( "goods.selectById" , 1603 ) ;
System. out. println ( goods. hashCode ( ) ) ;
} catch ( Exception e) {
throw e;
} finally {
MyBatisUtils. closeSession ( session) ;
}
try {
session = MyBatisUtils. openSession ( ) ;
Goods goods = session. selectOne ( "goods.selectById" , 1603 ) ;
System. out. println ( goods. hashCode ( ) ) ;
} catch ( Exception e) {
throw e;
} finally {
MyBatisUtils. closeSession ( session) ;
}
}
通过控制台显示的结果可以看到,SQL语句只执行了一次,两个缓存的地址一模一样
[main] 00:00:57.271 DEBUG goods.selectById - ==> Preparing: select * from t_goods where goods_id = ?
[main] 00:00:57.317 DEBUG goods.selectById - ==> Parameters: 1603(Integer)
[main] 00:00:57.340 DEBUG goods.selectById - <== Total: 1
1448525331
[main] 00:00:57.347 DEBUG o.a.i.t.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@1d2adfbe [wrapping: com.mysql.jdbc.JDBC4Connection@36902638]]
[main] 00:00:57.348 DEBUG o.a.i.t.jdbc.JdbcTransaction - Closing JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@1d2adfbe [wrapping: com.mysql.jdbc.JDBC4Connection@36902638]]
[main] 00:00:57.349 DEBUG goods - Cache Hit Ratio [goods]: 0.5
1448525331
OneToMany对象关联查询
学生和班级的关系是一对多,在设计表时,学生表需要持有班级表的外键。 学生和学籍档案的关系是一对一,在设计表时,学生表和学籍档案表通过主键进行关联。 学生和课程的关系是多对多,在设计表时,需要单独抽象出一张中间表,在这张表中持有学生和课程的编号,通过学生和课程分别与中间表关联。
写代码
创建GoodsDetail类(java→com.imooc.mybatis.entity)
package com. imooc. mybatis. entity;
public class GoodsDetail {
private Integer gdId;
private Integer goodsId;
private String gdPicUrl;
private Integer gdOrder;
private Goods goods;
}
添加Getter/Setter方法 创建goods_detail.xml(resources→mappers),说明实体类和数据库中的表的对应关系
<?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
< configuration>
< mappers>
< mapper resource = " mappers/goods_detail.xml" />
</ mappers>
</ configuration>
在Goods.java中添加GoodsDetail对象属性
public class Goods {
private List< GoodsDetail> goodsDetails;
}
在goods.xml中添加resultMap对goodDetails进行获取
< mapper>
< resultMap id = " rmGoods1" type = " com.imooc.mybatis.entity.Goods" >
< id column = " goods_id" property = " goodsId" > </ id>
< collection property = " goodsDetails" select = " goodsDetail.selectByGoodsId"
column = " goods_id" />
</ resultMap>
< select id = " selectOneToMany" resultMap = " rmGoods1" >
select * from t_goods limit 0,10
</ select>
</ mapper>
在MybatisTestor测试用例中添加新的方法
@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) ;
}
}
由控制台的输出可以看到,商品描述的总数量被打印出来
[main] 00:51:24.997 DEBUG goods.selectOneToMany - ==> Preparing: select * from t_goods limit 0,3
[main] 00:51:25.034 DEBUG goods.selectOneToMany - ==> Parameters:
[main] 00:51:25.055 DEBUG goodsDetail.selectByGoodsId - ====> Preparing: select * from t_goods_detail where goods_id = ?
[main] 00:51:25.056 DEBUG goodsDetail.selectByGoodsId - ====> Parameters: 739(Integer)
[main] 00:51:25.074 DEBUG goodsDetail.selectByGoodsId - <==== Total: 14
[main] 00:51:25.081 DEBUG goodsDetail.selectByGoodsId - ====> Preparing: select * from t_goods_detail where goods_id = ?
[main] 00:51:25.082 DEBUG goodsDetail.selectByGoodsId - ====> Parameters: 740(Integer)
[main] 00:51:25.094 DEBUG goodsDetail.selectByGoodsId - <==== Total: 11
[main] 00:51:25.095 DEBUG goodsDetail.selectByGoodsId - ====> Preparing: select * from t_goods_detail where goods_id = ?
[main] 00:51:25.095 DEBUG goodsDetail.selectByGoodsId - ====> Parameters: 741(Integer)
[main] 00:51:25.104 DEBUG goodsDetail.selectByGoodsId - <==== Total: 6
[main] 00:51:25.104 DEBUG goods.selectOneToMany - <== Total: 3
亲润 孕妇护肤品豆乳大米盈润保湿胶原蚕丝面膜(18片装):14
爱恩幼 孕妇护肤品润养颜睡眠面膜 100g:11
斯利安 孕妈专用 洗发水 氨基酸表面活性剂 舒缓头皮 滋养发根 让你的秀发会喝水 品质孕妈:6
通过观察返回的list可以得到goodsDetails数据
ManyToOne对象关联查询
多的一方要关联一的一方,只需要持有一的实体就可以,也就是增加一个一的一方的对象
写代码
在在GoodsDetail.java中添加Goods对象属性
package com. imooc. mybatis. entity;
public class GoodsDetail {
private Goods goods;
}
对其设置Getter/Setter方法 在goods_detail.xml中添加对象关联的描述
< mapper>
< resultMap id = " rmGoodsDetail" type = " com.imooc.mybatis.entity.GoodsDetail" >
< id column = " gd_id" property = " gdId" />
< result column = " goods_id" property = " goodsId" />
< 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>
</ mapper>
在MybatisTestor测试用例中添加新的方法,调用goodsDetail的selectManyToOne
@Test
public void testManyToOne ( ) throws Exception {
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) {
throw e;
} finally {
MyBatisUtils. closeSession ( session) ;
}
}
通过观察返回的list可以得到goodsDetails中goods数据
PageHelper分页插件
写代码
< dependency>
< groupId> com.github.pagehelper</ groupId>
< artifactId> pagehelper</ artifactId>
< version> 5.1.10</ version>
</ dependency>
< dependency>
< groupId> com.github.jsqlparser</ groupId>
< artifactId> jsqlparser</ artifactId>
< version> 2.0</ version>
</ dependency>
在mybatis_config.xml中启用PageHelper
< configuration>
< plugins>
< plugin interceptor = " com.github.pagehelper.PageInterceptor" >
< property name = " helperDialect" value = " mysql" />
< property name = " reasonable" value = " true" />
</ plugin>
</ plugins>
</ configuration>
在goods.xml中添加新的<select>查询项
< mapper>
< select id = " selectPage" resultType = " com.imooc.mybatis.entity.Goods" >
select * from t_goods where current_price < 1000
</ select>
</ mapper>
在MybatisTestor测试用例中添加新的方法,测试PageHelper中的各个方法的返回值
@Test
public void testSelectPage ( ) throws Exception {
SqlSession session = null;
try {
session = MyBatisUtils. openSession ( ) ;
PageHelper. startPage ( 2 , 5 ) ;
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) ;
}
}
总页数:362
总记录数:1809
开始行号:5
结束行号:10
当前页码:2
美康粉黛 金风玉露隔离霜孕妇护肤品化妆品隔离污染均匀肤色持久服帖 自然无妆感 温情绿
亲润 孕妇专用遮瑕保湿隔离提亮肤色气垫CC霜
柔色孕期彩妆 植物彩妆买1送1 雾面口红唇膏不脱妆多色可选
哇爱 孕妇待产专用待产包 13件套【实用型】
兰多 升级版时尚妈咪包 桔色
MyBatis整合C3P0连接池
连接池解决现状问题的原理
连接池在初始化的时候会默认存放n个连接对象并存放到一个容器(LinkedList)中。 当需要连接的时候,如果连接池中有连接就直接从连接池中获取,如果连接池中没有则新创建连接,如果连接数大于最大连接数时,如果再有请求需要获取连接则将其添加到等待队列 对于新创建的连接,一般不会直接销毁,它将被放到连接池中等待重复使用,只有当空闲超时后被释放。 对于原本就在连接池中的连接对象,用完之后直接放回连接池中。 连接池中的连接数在空闲时会逐渐趋近于最小连接数(核心连接数),连接池在满载时的连接数会接近最大连接数。
写代码
< dependency>
< groupId> com.mchange</ groupId>
< artifactId> c3p0</ artifactId>
< version> 0.9.5.4</ version>
</ dependency>
让Mybatis对连接池进行支持,需要额外扩展一个类,创建datasource包(java→com.imooc.mybatis),用于保存数据源 创建C3P0DataSourceFactory类(java→com.imooc.mybatis.datasource),并添加以下代码
public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {
public C3P0DataSourceFactory ( ) {
this . dataSource = new ComboPooledDataSource ( ) ;
}
}
在mybatis-config.xml中修改使用的连接池,注意改变了dataSource,driver→driverClass,url→jdbcUrl
< environments default = " dev" >
< environment id = " dev" >
< transactionManager type = " JDBC" > </ transactionManager>
< dataSource type = " com.imooc.mybatis.datasource.C3P0DataSourceFactory" >
< property name = " driverClass" value = " com.mysql.jdbc.Driver" />
< property name = " jdbcUrl" value = " jdbc:mysql://localhost:3306/babytun?useUnicode=true& characterEncoding=UTF-8" />
< 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>
</ environments>
在执行SQL语句是时可以看到控制台输出是由C3P0打印出来的 [main] 10:35:27.550 DEBUG c.m.v.resourcepool.BasicResourcePool - com.mchange.v2.resourcepool.BasicResourcePool@18078bef config: [start -> 5; min -> 5; max -> 20; inc -> 3; num_acq_attempts -> 30; acq_attempt_delay -> 1000; check_idle_resources_delay -> 0; max_resource_age -> 0; max_idle_time -> 0; excess_max_idle_time -> 0; destroy_unreturned_resc_time -> 0; expiration_enforcement_delay -> 0; break_on_acquisition_failure -> false; debug_store_checkout_exceptions -> false; force_synchronous_checkins -> false]
MyBatis批处理
写代码
在goods.xml中添加新的查询项进行批处理操作
< mapper>
< insert id = " batchInsert" 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" item = " item" index = " index" separator = " ," >
(#{item.title},#{item.subTitle}, #{item.originalCost}, #{item.currentPrice}, #{item.discount}, #{item.isFreeDelivery}, #{item.categoryId})
</ foreach>
</ insert>
</ mapper>
在MybatisTestor测试用例中添加批量插入操作
@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++ ) {
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.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) ;
}
}
批量删除操作方式和批量插入类似 在goods.xml中添加新的删除项进行批处理操作
< mapper>
< delete id = " batchDelete" parameterType = " java.util.List" >
DELETE FROM t_goods WHERE goods_id in
< foreach collection = " list" item = " item" index = " index" open = " (" close = " )" separator = " ," >
#{item}
</ foreach>
</ delete>
</ mapper>
MybatisTestor测试用例中添加批量删除操作
@Test
public void testBatchDelete ( ) throws Exception {
SqlSession session = null;
try {
long st = new Date ( ) . getTime ( ) ;
session = MyBatisUtils. openSession ( ) ;
List list = new ArrayList ( ) ;
list. add ( 1920 ) ;
list. add ( 1921 ) ;
list. add ( 1922 ) ;
session. delete ( "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) ;
}
}