一、日志
截屏2022-07-22 下午10.21.46.png
截屏2022-07-22 下午10.22.08.png
截屏2022-07-22 下午10.27.01.png
logback.xml
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<!--
日志输出级别(优先级高到低)：
error:错误 - 系统的故障日志
warning:警告 - 存在风险或使用不当的日志
info:一般性消息
debug:程序内部用于调试信息
trace:程序运行的跟踪信息
-->
<root level="debug">
<appender-ref ref="console"/>
</root>
</configuration>
二、动态SQL
截屏2022-07-28 15.57.26.png
<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>
@Test
public void testDynamicSQL() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
HashMap param = new HashMap();
param.put("categoryId",44);
param.put("currentPrice",500);
List<Goods> list = sqlSession.selectList("goods.dynamicSQL",param);
for (Goods goods : list) {
System.out.println(goods);
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
三、二级缓存
截屏2022-07-28 16.41.50.png
截屏2022-07-28 16.41.25.png
截屏2022-07-28 16.43.26.png
截屏2022-07-28 17.06.56.png
四、对象关联查询
1、一对多
<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>
<!-- resultMap可用于说明一对多或者多对一的映射逻辑
id是resultMap属性引用的标志
type指向One的实体(Goods)
-->
<resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
<!-- 映射goods对象的主键到goods_id字段-->
<id property="goodsId" column="goods_id"/>
<!-- collection的含义是，在select * from t_goods limit 0,1得到结果后，对所有Goods对象遍历得到goods_id字段值，
并带入到goodsDetail命名空间的selectByGoodsId的SQL中执行查询，
将得到的"商品详情"集合赋值给goodsDetails集合对象
-->
<collection property="goodsDetails" column="goods_id" select="goodsDetail.selectByGoodsId"/>
</resultMap>
<select id="selectOneToMany" resultMap="rmGoods1">
select * from t_goods limit 0,1
</select>
@Test
public void testSelectOneToMany() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<Goods> list = sqlSession.selectList("goods.selectOneToMany");
for (Goods goods : list) {
System.out.println(goods);
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
2、多对一
<mapper namespace="goodsDetail">
<resultMap id="rmGoodsDetail" type="com.imooc.mybatis.entity.GoodsDetail">
<id property="gdId" column="gd_id"/>
<result property="goodsId" column="goods_id"/>
<association property="goods" select="goods.selectById" column="goods_id"/>
</resultMap>
<select id="selectManyToOne" resultMap="rmGoodsDetail">
select * from t_goods_detail limit 0,1
</select>
</mapper>
<select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods where goods_id = #{value}
</select>
@Test
public void testSelectMonyToOne() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<GoodsDetail> list = sqlSession.selectList("goodsDetail.selectManyToOne");
for (GoodsDetail goodsDetail : list) {
System.out.println(goodsDetail);
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
五、分页插件PageHelper
截屏2022-07-29 15.35.48.png
截屏2022-07-29 15.37.07.png
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
<!-- 启用PageHelper分页插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 设置数据库类型-->
<property name="helperDialect" value="mysql"/>
<!-- 分页合理化-->
<property name="reasonable" value="true"/>
</plugin>
</plugins>
<select id="selectPage" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods where current_price < 1000
</select>
@Test
public void testSelectPage() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
PageHelper.startPage(2,10);
Page<Goods> page = (Page)sqlSession.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> list = page.getResult();
for (Goods goods : list) {
System.out.println(goods);
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
六、Mybatis整合C3P0连接池
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.4</version>
</dependency>
C3P0DataSourceFactory.java类
package com.imooc.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();
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!-- goods_id == goodsId 驼峰命名转换-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 启用PageHelper分页插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 设置数据库类型-->
<property name="helperDialect" value="mysql"/>
<!-- 分页合理化-->
<property name="reasonable" value="true"/>
</plugin>
</plugins>
<!-- 设置默认指向的数据库-->
<environments default="dev">
<!-- 配置环境，不同的环境不同的id名字-->
<environment id="dev">
<!-- 采用JDBC方式对数据库事务进行commit/rollback-->
<transactionManager type="JDBC"></transactionManager>
<!-- 采用连接池方式管理数据库连接-->
<!-- <dataSource type="POOLED">-->
<dataSource type="com.imooc.mybatis.datasource.C3P0DataSourceFactory">
<!-- <property name="driver" value="com.mysql.cj.jdbc.Driver"/>-->
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
<!-- <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&characterEncoding=UTF-8"/>-->
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&characterEncoding=UTF-8"/>
<!-- <property name="username" value="root"/>-->
<property name="user" value="root"/>
<property name="password" value="htxx1234"/>
<property name="initialPoolSize" value="5"/>
<property name="maxPoolSize" value="20"/>
<property name="minPoolSize" value="5"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/goods.xml"/>
<mapper resource="mappers/goods_detail.xml"/>
</mappers>
</configuration>
七、Mybatis整合Druid连接池
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.14</version>
</dependency>
DruidDataSourceFactory.java类
public class DruidDataSourceFactory extends UnpooledDataSourceFactory {
public DruidDataSourceFactory() {
this.dataSource = new DruidDataSource();
}
@Override
public DataSource getDataSource() {
try {
((DruidDataSource)this.dataSource).init();//初始化Druid数据源
}catch (SQLException e) {
throw new RuntimeException(e);
}
return this.dataSource;
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!--开启驼峰命名转换form_id -> formId-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="dev">
<!--开发环境配置-->
<environment id="dev">
<!--事务管理器采用JDBC方式-->
<transactionManager type="JDBC"></transactionManager>
<!--利用Mybatis自带连接池管理连接-->
<!--<dataSource type="POOLED">-->
<!--Mybatis与Druid的整合-->
<dataSource type="com.imooc.oa.datasource.DruidDataSourceFactory">
<!--JDBC连接属性-->
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/imooc-oa?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/test.xml"/>
</mappers>
</configuration>
八、Mybatis的批处理
批量插入
<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>
@Test
public void testBatchInsert() throws Exception {
SqlSession sqlSession = null;
try {
long st = new Date().getTime();
sqlSession = 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);
}
sqlSession.insert("goods.batchInsert", list);
sqlSession.commit();
long et = new Date().getTime();
System.out.println("执行时间：" + (et -st) + "毫秒");
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
批量删除
<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>
@Test
public void testBatchDelete() {
SqlSession session = null;
try {
long st = new Date().getTime();
session = MybatisUtils.openSession();
System.out.println("session" + session);
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) {
e.printStackTrace();
if (session != null) {
session.rollback();
}
} finally {
MybatisUtils.closeSession(session);
}
}
九、Mybatis注解开发
截屏2022-07-30 下午3.15.59.png
mybatis-config.xml配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/babytun"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--<mapper class="com.imooc.mybatis.dao.GoodsDao"/>-->
<package name="com.imooc.mybatis.dao"/>
</mappers>
</configuration>
GoodsDao.java接口
public interface GoodsDao {
@Select("select * from t_goods where current_price between #{min} and #{max} order by current_price limit 0,#{limit}")
public List<Goods> selectByPriceRange(@Param("min") Float min, @Param("max") Float max, @Param("limit") Integer limit);
@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 Integer insert(Goods goods);
@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<GoodsDTO> selectAll();
}
MybatisTestor.java测试类
public class MybatisTestor {
@Test
public void testSelectByPriceRange() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
GoodsDao goodsDao = sqlSession.getMapper(GoodsDao.class);
List<Goods> list = goodsDao.selectByPriceRange(100F, 500F, 20);
System.out.println(list.size());
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testInsert() {
SqlSession session = null;
try {
session = MybatisUtils.openSession();
Goods goods = new Goods();
goods.setTitle("测试标题");
goods.setSubTitle("测试副标题");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.5f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
GoodsDao goodsDao = session.getMapper(GoodsDao.class);
Integer num = goodsDao.insert(goods);
session.commit();
System.out.println(goods.getGoodsId());
} catch (Exception e) {
e.printStackTrace();
if (session != null) {
session.rollback();
}
} finally {
MybatisUtils.closeSession(session);
}
}
@Test
public void testSelectAll() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
GoodsDao goodsDao = sqlSession.getMapper(GoodsDao.class);
List<GoodsDTO> list = goodsDao.selectAll();
System.out.println(list.size());
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
}
