MyBatis笔记二

最新推荐文章于 2023-03-30 21:04:35 发布

顾青菜

最新推荐文章于 2023-03-30 21:04:35 发布

阅读量368

点赞数

文章标签： mybatis java 开发语言

本文链接：https://blog.csdn.net/qingbaicai/article/details/129585062

版权

日志文件是用于记录系统操作时间的记录文件或文件集合
日志保存历史记录，是诊断问题及理解系统活动的重要依据

日志门面SLF4J/Apache Commons-Logging===桥接==>日志实现log4j/logbacl/java.util.logging(jul)

一、日志管理

1.1 pom.xml添加依赖

<dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> <scope>test</scope> </dependency>

控制台输出

1.2 自定义日志

resource下新建文件，命名强制为logback.xml

level日志级别

error 错误系统的故障日志
warn 警告存在风险或使用不当的日志
info 一般性信息
debug 程序内部用于调试信息
trace 程序运行的跟踪信息

<?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>

二、动态SQL

动态sql是根据参数数据动态组织sql的技术

mapper文件编写查询语句

<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.gu.entity.Goods"> select * from t_goods where 1=1 <if test="categoryId!=null" > and category_id=#{categoryId} </if> <if test="currentPrice!=null" > and current_price < #{currentPrice} </if> </select>

或者

<where> <if test="categoryId!=null" > and category_id=#{categoryId} </if> <if test="currentPrice!=null" > and current_price < #{currentPrice} </if> </where>

编写测试文件

@Test public void dynamic() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); Map params = new HashMap(); params.put("categoryId", 44); params.put("currentPrice", 500); List<Goods> list = sqlSession.selectList("goods.dynamicSQL", params); sqlSession.commit(); // 提交事务数据 for(Goods goods:list ){ System.out.println(goods.getTitle()); } } catch (Exception e) { System.out.println(e); if(sqlSession!=null) { sqlSession.rollback(); } } finally { MybatisUtils.closeSession(sqlSession); } }

三、MyBatis二级缓存

一级缓存默认开启，缓存范围SqlSession会话
二级缓存手动开启，属于范围Mapper Namespace

3.1 二级缓存运行规则

二级开启后默认所有查询操作均使用缓存
写操作commit提交时对该namespace缓存强制清空
配置useCache=false可以不用缓存
配置flushCache=true代表强制清空缓存

一级缓存

public void testLv1Cache() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); Goods goods = sqlSession.selectOne("goods.selectId", 1603); Goods good1 = sqlSession.selectOne("goods.selectId", 1603); System.out.println(goods.hashCode()); System.out.println(good1.hashCode()); } finally { MybatisUtils.closeSession(sqlSession); } }

打印出hascode相同（一级缓存生命周期SqlSession会话）

添加commit后缓存消失

public void testLv1Cache() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); Goods goods = sqlSession.selectOne("goods.selectId", 1603); sqlSession.commit(); Goods good1 = sqlSession.selectOne("goods.selectId", 1603); System.out.println(goods.hashCode()); System.out.println(good1.hashCode()); } finally { MybatisUtils.closeSession(sqlSession); } }

在映射文件src/java/resource/mapper/goods.xml中开启二级缓存

<cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"></cache>

public void testLv2Cache() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); Goods goods = sqlSession.selectOne("goods.selectId", 1603); sqlSession.commit(); Goods good1 = sqlSession.selectOne("goods.selectId", 1603); System.out.println(goods.hashCode()); System.out.println(good1.hashCode()); } finally { MybatisUtils.closeSession(sqlSession); } try { sqlSession = MybatisUtils.openSession(); Goods goods = sqlSession.selectOne("goods.selectId", 1603); sqlSession.commit(); Goods good1 = sqlSession.selectOne("goods.selectId", 1603); System.out.println(goods.hashCode()); System.out.println(good1.hashCode()); } finally { MybatisUtils.closeSession(sqlSession); } }

只查询了一次，之后使用缓存

3.2 cache标签属性

<cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"></cache>

eviction是缓存的清除策略，当缓存对象数量达到上限后，自动触发对应算法对缓存对象清楚

LRU 最久未使用：移除最长时间未使用
FIFO 先进先出：按对象进入混存的顺序来移除它们
SOFT 软引用：移除基于垃圾收集器和软引用规则的对象
WEAK 弱饮用：更积极的移除基于垃圾收集器状态和弱勇规则的对象

flushInterval 缓存间隔，间隔时间自动清除

size 最大缓存对象，实体类、list都是一个缓存对象

readOnly 每次缓存取出的是缓存对象本身，效率较高。设置为false，取出的是缓存对象的“副本”，每次取出对象都不同，安全性较高

useCache 是否使用缓存 (list对象少用缓存)

<select useCache="false" id="selectAll" resultType="com.gu.entity.Goods"> -- 内容为sql语句 select * from t_goods order by goods_id desc limit 10 </select>

flushCache 执行完语句后立即情况，多用在写操作

<select flushCache="true" id="selectId" parameterType="Integer" resultType="com.gu.entity.Goods" > select * from t_goods where goods_id = #{goods_id} </select>

四、MyBatis多表级联查询

4.1 一对多查询

创建src/main/java/entity/GoodsDetail.java实体类

package com.gu.entity; 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; } }

由于与Goods是1:n关系，添加details

private List<GoodsDetail> goodsDetails; public List<GoodsDetail> getGoodsDetails() { return goodsDetails; } public void setGoodsDetails(List<GoodsDetail> goodsDetails) { this.goodsDetails = goodsDetails; }

创建映射文件resources/mappers/goods_detail.xml

<?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.gu.entity.GoodsDetail"> select * from t_goods_detail where goods_id = #{value} </select> </mapper>

在mybatis-config.xml配置文件中添加映射

在goods改动映射文件

<resultMap id="rmGoods1" type="com.gu.entity.Goods">  <id column="goods_id" property="goodsId"></id> <result column="goods_id" property="goodsId"></result>  <collection property="goodsDetails" select="goodsDetail.selectByGoodsId" column="goods_id"/> </resultMap> <select id="selectOneToMany" resultMap="rmGoods1"> select * from t_goods limit 0 , 1 </select>

测试查询

@Test public void testOneToMany() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); List<Goods> list = sqlSession.selectList("goods.selectOneToMany", 1603); for (Goods goods: list) { System.out.println(goods.getTitle()); for (GoodsDetail goodsDetail: goods.getGoodsDetails()) { System.out.println(goodsDetail.getGdPicUrl()); } } } finally { MybatisUtils.closeSession(sqlSession); } }

4.2 多对一查询

多的一方，添加实体，在goodsDetaile中添加Goods实体

private Goods goods; public Goods getGoods() { return goods; } public void setGoods(Goods goods) { this.goods = goods; }

添加映射的sql方法

<resultMap id="rmGoodsDetails" type="com.gu.entity.GoodsDetail" > <id column="gd_id" property="gdId"></id>  <association property="goods" select="goods.selectId" column="goods_id"> </association> </resultMap> <select id="selectManyToOne" resultMap="rmGoodsDetails"> select * from t_goods_detail limit 0, 10 </select>

测试方法

public void testSelectManyToOne() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); List<GoodsDetail> list = sqlSession.selectList("goodsDetail.selectManyToOne"); for (GoodsDetail gd: list) { System.out.println(gd.getGdPicUrl() + gd.getGoods().getTitle()); } } finally { MybatisUtils.closeSession(sqlSession); } }

五、分页插件（PageHelper）

需要完成的工作:

当前页数据查询
总计数查询
程序计算总页数、上一页页码、下一页页码

5.1 根据pageHelper官网文档配置

pom.xml配置依赖

<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配置插件选项

mappers/goods.xml中配置sql语句

<select id="selectPage" resultType="com.gu.entity.Goods"> select * from t_goods </select>

测试代码

public void testSelectPage() { 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.getTitle()); } } finally { MybatisUtils.closeSession(sqlSession); } }

六、MyBatis整合C3P0连接池

C3P0连接池功能较MyBatis更为强大

引入依赖

<dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.4</version> </dependency>

新建文件存储数据源 java/com/gu/datasource/C3P0DataSourceFactory.java

package com.gu.datasource; import com.mchange.v2.c3p0.ComboPooledDataSource; import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory; //c3p0与MyBatis兼容使用的数据源工厂 public class C3P0DataSourceFactory extends UnpooledDataSourceFactory { public C3P0DataSourceFactory() { // 由c3p0创建数据源 this.dataSource = new ComboPooledDataSource(); } }

mybatis-config.xml配置p3c0

七、批处理

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 -- list指代集合，不能随意更改 separator分隔符 <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映射文件：删除

<delete id="batchDelete" parameterType="java.util.list"> delete from t_goods where goods_id in <foreach collection="list" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach> </delete>

测试代码

sqlSession = MybatisUtils.openSession(); List<Goods> list = new ArrayList<>(); long st = new Date().getTime(); for(int i = 0; i < 1000; i++){ Goods goods = new Goods(); goods.setTitle("测试商品01"); goods.setSubTitle("测试子标题"); goods.setOriginalCost(200f); goods.setCurrentPrice(100f); goods.setIsFreeDelivery(1); goods.setDiscount(0.5f); goods.setCategoryId(43); list.add(goods); } int num = sqlSession.insert("goods.batchInsert", list); sqlSession.commit(); // 提交事物数据 long et = new Date().getTime(); System.out.println("num" + num); System.out.println( "执行时间 " + (et - st) + "毫秒");

批量插入数据的局限

无法获得插入数据的id
批量生成的SQL太长，可能会被服务器拒绝

八、MyBatis注解开发

注解可以替换原有的xml标签进行开发

注解	对应xml	说明
@Insert		新增sql
@Update		更新sql
@Delete		删除sql
@Select		查询sql
@Param	--	参数映射
@Results		结果映射
@Result		字段映射

新建DAO层 com/gu/dao/GoodsDao.java文件

package com.imooc.mybatis.dao; import com.imooc.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, #{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})") // before = false在insert等语句之后执行 keyProperty主键 @SelectKey(statement = "select last_insert_id()", before = false, keyProperty = "goodsId", resultType = Integer.class) public int insert(Goods 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(); }

mybatis-config.xml文件创建相关映射

测试代码

@Test public void testSelectByPriceRange() throws Exception { SqlSession session = null; try{ session = MyBatisUtils.openSession(); // session可以根据配置动态生成goodsDAO类 GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class); List<Goods> list = goodsDAO.selectByPriceRange(100f, 500f, 20); for(Goods goods: list) { System.out.println(goods.getTitle()); } }catch (Exception e) { throw e; } finally { MyBatisUtils.closeSession(session); } } // /** // * 新增数据 // * @throws Exception // */ @Test public void testInsert() throws Exception { 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); //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); } } @Test public void testSelectAll() throws Exception { SqlSession session = null; try { session = MyBatisUtils.openSession(); GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class); List list = goodsDAO.selectAll(); System.out.println(list.size()); } catch (Exception e) { throw e; } finally { MyBatisUtils.closeSession(session); } }

顾青菜

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis笔记二

- 不同连接方式关键词不同--> <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/> <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/babytun"/> <property name="user" value="root"/> <property name="password" value="gu19950402"/> <!
复制链接

扫一扫