1.日志
用于記錄系統(tǒng)操作記錄触幼，保存歷史數(shù)據(jù)拳亿，診斷問題
1.1.SLF4j與Logback
日志門面：統(tǒng)一的對(duì)外接口
1-1.jpg
pom中插入依賴
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
resources下面添加logback.xml日志配置文件
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<!-- %thread對(duì)應(yīng)的線程 %-5表示按5個(gè)字符左對(duì)齊
%logger表示哪個(gè)類產(chǎn)生的日志 %msg日志具體輸出行楞，%n換行-->
<pattern>[%thread] %d{HH:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<!--
日志輸出級(jí)別（高-低）
error：錯(cuò)誤 - 系統(tǒng)的故障日志
warn：警告 - 存在風(fēng)險(xiǎn)或者使用不當(dāng)?shù)娜罩? info： 一般性信息
debug： 程序內(nèi)部用于調(diào)試的信息
trace：程序運(yùn)行的跟蹤信息
-->
<root level="debug">
<appender-ref ref="console"/>
</root>
</configuration>
2.動(dòng)態(tài)SQL
根據(jù)參數(shù)數(shù)據(jù)動(dòng)態(tài)組織SQL
goods.xml:
<!-- 動(dòng)態(tài)SQL-->
<select id="dynamicSQL" parameterType="java.util.Map" resultType="org.example.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>
調(diào)用：
@Test
public void testDynamicSQL() throws Exception {
SqlSession session = null;
session = MyBatisUtils.openSession();
Map param = new HashMap();
param.put("category", 44);
param.put("currentPrice", 500);
//可以不加namespace爬迟，確保id是唯一即可
List<Goods> list = session.selectList("goods.dynamicSQL",param);
for (int i = 0; i < list.size(); i++) {
Goods goods = list.get(i);
System.out.println(goods.getTitle());
}
MyBatisUtils.closeSession(session);
}
3.緩存
主要是MyBatis二級(jí)緩存
- 一級(jí)緩存默認(rèn)開啟剩瓶，緩存范圍SqlSession會(huì)話
- 二級(jí)緩存手動(dòng)開啟番刊，緩存范圍是Mapper Namespace
1-2.jpg
3.1.二級(jí)緩存運(yùn)行規(guī)則
- 二級(jí)開啟后默認(rèn)所有查詢操作均使用緩存
- 寫操作commit提交時(shí)對(duì)該namespace緩存強(qiáng)制清空
- 配置useCache=false可以不使用緩存
- 配置flushCache=true代表強(qiáng)制清空緩存
一級(jí)緩存的測(cè)試代碼：
@Test
public void testLv1Cache() {
SqlSession session = null;
session = MyBatisUtils.openSession();
Goods goods = session.selectOne("goods.selectById",1603);
Goods goods1 = session.selectOne("goods.selectById",1603);
System.out.println("goods hashCode:" + goods.hashCode() + "-goods1 hashCode:" + goods1.hashCode());
MyBatisUtils.closeSession(session);
SqlSession session1 = null;
session1 = MyBatisUtils.openSession();
Goods goods2 = session1.selectOne("goods.selectById",1603);
Goods goods3 = session1.selectOne("goods.selectById",1603);
System.out.println("goods2 hashCode:" + goods2.hashCode() + "-goods3 hashCode:" + goods3.hashCode());
MyBatisUtils.closeSession(session1);
}
運(yùn)行結(jié)果：
goods hashCode:574268151-goods1 hashCode:574268151
goods2 hashCode:423583818-goods3 hashCode:423583818
可以看到同一個(gè)session的hashCode相同含鳞，說明是同一個(gè)對(duì)象，并且debug日志也只會(huì)執(zhí)行一次sql查詢芹务，如果在goods和goods1之間插入session.commit()方法蝉绷，則goods和goods1則會(huì)是兩個(gè)不同的對(duì)象。
二級(jí)緩存測(cè)試代碼：
goods.xml中添加
<!-- 開啟二級(jí)緩存-->
<cache eviction="LRU" flushInterval="600000" readOnly="true"/>
測(cè)試代碼與一級(jí)緩存一樣不變
運(yùn)行結(jié)果：
goods hashCode:270095066-goods1 hashCode:270095066
goods2 hashCode:270095066-goods3 hashCode:270095066
說明開啟了二級(jí)緩存后枣抱，不同session也會(huì)公用一個(gè)緩存數(shù)據(jù)熔吗。
3.2.二級(jí)緩存參數(shù)說明
二級(jí)緩存相關(guān)：
<mapper namespace="goods">
<!--
開啟二級(jí)緩存
eviction是緩存的清除策略，當(dāng)緩存對(duì)象數(shù)量達(dá)到上限后佳晶，自動(dòng)出發(fā)對(duì)應(yīng)算法對(duì)緩存對(duì)象清除
1.LRU-最近最久未使用：移除最長(zhǎng)時(shí)間不被使用的對(duì)象
O1 O2 ... O512
14 99 893
則會(huì)移除O512的對(duì)象桅狠，因?yàn)樗e例上次使用時(shí)間最長(zhǎng)
2.LFU-最近最少使用：移除最近訪問頻率最低的對(duì)象
3.FIFO-先進(jìn)先出：按對(duì)象進(jìn)入緩存的順序來移除
4.SOFT-軟引用：移除基于垃圾收集器狀態(tài)和軟引用規(guī)則的對(duì)象
5.WEAK-弱引用：更積極的移除基于垃圾收集器狀態(tài)和弱引用規(guī)則的對(duì)象
3,4,5很少使用。
flushInterval 代表間隔多長(zhǎng)時(shí)間自動(dòng)清空緩存轿秧，單位毫秒中跌，600000毫秒=10分鐘
size 緩存的存儲(chǔ)上限，用于保存對(duì)象或集合（1個(gè)集合算1個(gè)對(duì)象）的數(shù)量上限
readOnly 設(shè)置為true淤刃，代表返回只讀緩存晒他，每次從緩存取出的是緩存對(duì)象本身，執(zhí)行效率高
設(shè)置為false逸贾，代表每次取出的是緩存對(duì)象的副本陨仅，每次取出對(duì)象是不同的，安全性高铝侵。
-->
<cache eviction="LRU" flushInterval="600000" readOnly="true"/>
...
單條相關(guān)：
<!-- useCache="false"代表不使用緩存-->
<select id="selectAll" resultType="org.example.entity.Goods" useCache="false">
select * from t_goods order by goods_id desc limit 10
</select>
----------
<!-- flushCache="true"在sql執(zhí)行后強(qiáng)制清空緩存灼伤，同時(shí)本條sql執(zhí)行結(jié)果也不會(huì)緩存-->
<insert id="insert"
parameterType="org.example.entity.Goods"
useGeneratedKeys="true"
keyProperty="goodsId"
keyColumn="goods_id"
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})
</insert>
4.多表級(jí)聯(lián)查詢
1-3.jpg
4.1.一對(duì)多查詢
商品與商品圖片詳情就是1對(duì)多的關(guān)系
開發(fā)步驟：
4.1.1.resources下創(chuàng)建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="org.example.entity.GoodsDetail">
select * from t_goods_detail where goods_id = #{value}
</select>
</mapper>
4.1.2.goods.xml中配置一對(duì)多
<!--
resultMap可用于說明一對(duì)多或者多對(duì)一的映射邏輯
id是resultMap屬性引用的標(biāo)志
type指向One的實(shí)體（Goods）
-->
<resultMap id="rmGoods1" type="org.example.entity.Goods">
<!--
映射goods對(duì)象的主鍵到goods_id字段
由于其他字段都能與數(shù)據(jù)庫(kù)對(duì)應(yīng)上，所以不用再次列舉
-->
<id column="goods_id" property="goodsId"></id>
<collection property="goodsDetails" select="goodsDetail.selectByGoodsId"
column="goods_id"/>
</resultMap>
<!-- 一對(duì)多查詢-->
<select id="selectOneToMany" resultMap="rmGoods1">
select * from t_goods limit 0,1
</select>
4.1.3.mybatis-config.xml中加入應(yīng)用
加入對(duì)goods_detail.xml的引用
<mapper resource="mappers/goods_detail.xml"/>
4.1.4.調(diào)用測(cè)試
@Test
public void testOneToMany(){
SqlSession session = null;
session = MyBatisUtils.openSession();
List<Goods> list = session.selectList("goods.selectOneToMany");
for (int i = 0; i < list.size(); i++) {
Goods goods = list.get(i);
System.out.println(goods.getTitle() + " : " +goods.getGoodsDetails().size());
}
MyBatisUtils.closeSession(session);
}
4.2.多對(duì)一查詢
商品圖片詳情與商品是多對(duì)一
開發(fā)步驟：
4.2.1.mapper.xml配置
goods_detail.xml中添加多對(duì)一查詢語句和resultMap:
<!-- 多表對(duì)應(yīng)查詢實(shí)體的resultMap-->
<resultMap id="rmGoodsDetail" type="org.example.entity.GoodsDetail">
<!-- 多表的主鍵-->
<id column="gd_id" property="gdId"></id>
<!-- 多表的外鍵咪鲜，一表的主鍵-->
<result column="goods_id" property="goodsId"/>
<!-- 對(duì)表對(duì)應(yīng)的selectById查詢語句-->
<association property="goods" select="goods.selectById" column="goods_id"></association>
</resultMap>
<!-- 多表的查詢語句-->
<select id="selectManyToOne" resultMap="rmGoodsDetail">
select * from t_goods_detail limit 0,10
</select>
4.2.2.調(diào)用
@Test
public void testManyToOne(){
SqlSession session = null;
session = MyBatisUtils.openSession();
List<GoodsDetail> list = session.selectList("goodsDetail.selectManyToOne");
for (int i = 0; i < list.size(); i++) {
GoodsDetail goodsDetail = list.get(i);
if (goodsDetail.getGoods() == null)
continue;
System.out.println(goodsDetail.getGdPicUrl() + " : " + goodsDetail.getGoods().getTitle());
}
MyBatisUtils.closeSession(session);
}
5.分頁P(yáng)ageHelper
原理：
- 當(dāng)前數(shù)據(jù)查詢使用語句
select * from tab limit 0,10
- 總記錄數(shù)查詢
select count(*) from tab
- 程序計(jì)算總頁數(shù)狐赡、當(dāng)前頁、上一頁下一頁碼
5.1.PageHelper使用流程
-
maven引入PageHelper與jsqlparser
<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增加Plugin配置
<!-- 啟用PageHelper分頁插件--> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"> <!-- 設(shè)置數(shù)據(jù)庫(kù)類型--> <property name="helperDialect" value="mysql"/> <!-- 分頁合理化疟丙，頁數(shù)小于查詢第一頁颖侄，大于總頁數(shù)則顯示最后一頁--> <property name="reasonable" value="true"/> </plugin> </plugins> mapper.xml中添加查詢sql
<!-- 查詢價(jià)格小于1000的數(shù)據(jù)-->
<select id="selectPage" resultType="org.example.entity.Goods">
select * from t_goods where current_price < 1000
</select>
- 代碼中使用PageHelper.startPage()自動(dòng)分頁
@Test
public void testPageHelper(){
SqlSession session = null;
session = MyBatisUtils.openSession();
PageHelper.startPage(2, 10);
Page<Goods> page = (Page)session.selectList("goods.selectPage");
System.out.println("總頁數(shù)：" + page.getPages());
System.out.println("總記錄數(shù)：" + page.getTotal());
System.out.println("開始行號(hào)：" + page.getStartRow());
System.out.println("當(dāng)前頁碼：" + page.getEndRow());
List<Goods> data = page.getResult();//當(dāng)前頁數(shù)據(jù)
for (int i = 0; i < data.size(); i++) {
Goods goods = data.get(i);
System.out.println(goods.getTitle());
}
MyBatisUtils.closeSession(session);
}
--結(jié)果
總頁數(shù)：181
總記錄數(shù)：1810
開始行號(hào)：10
當(dāng)前頁碼：20
康泰 家用智能胎心儀 分體探頭操作方便 外放聆聽 與家人分享寶寶心聲
6.C3P0連接池
配置流程：
-
maven引入c3p0的引用
<dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.4</version> </dependency> -
創(chuàng)建C3P0DataSourceFactory
//C3P0與MyBatis兼容使用的數(shù)據(jù)源工廠類 public class C3P0DataSourceFactory extends UnpooledDataSourceFactory { public C3P0DataSourceFactory(){ this.dataSource = new ComboPooledDataSource(); } } mybatis.xml中引入datasource配置
<!-- C3P0連接池-->
<dataSource type="org.example.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="111111"/>
<property name="initialPoolSize" value="5"/>
<property name="maxPoolSize" value="20"/>
<property name="minPoolSize" value="5"/>
</dataSource>
剩下的不便
7.MyBatis批處理
在mapper.xml中使用foreach標(biāo)簽
<!-- insert into table-->
<!-- values("a","a1","a2"),("a","a1","a2"),(....)-->
<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>
調(diào)用：
@Test
//分別插入
public void testBatchInsert1(){
SqlSession session = null;
session = MyBatisUtils.openSession();
long st = new Date().getTime();
for (int i = 0; i < 1000; i++) {
Goods goods = new Goods();
goods.setTitle("測(cè)試批量插入商品");
goods.setSubTitle("子標(biāo)題");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.5f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
session.insert("goods.insert", goods);
}
session.commit();
long et = new Date().getTime();
System.out.println("執(zhí)行時(shí)間:" + (et - st) + "毫秒");
MyBatisUtils.closeSession(session);
}
@Test
//批量插入
public void testBatchInsert2(){
SqlSession session = null;
session = MyBatisUtils.openSession();
long st = new Date().getTime();
List list = new ArrayList();
for (int i = 0; i < 1000; i++) {
Goods goods = new Goods();
goods.setTitle("測(cè)試批量插入商品");
goods.setSubTitle("子標(biāo)題");
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("執(zhí)行時(shí)間:" + (et - st) + "毫秒");
MyBatisUtils.closeSession(session);
}
分別插入的時(shí)間：
執(zhí)行時(shí)間:976毫秒
批處理插入的時(shí)間：
執(zhí)行時(shí)間:679毫秒
由此可見批處理的效率很高
批量刪除：
<!-- 批量刪除 （1, 2)-->
<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>
--調(diào)用
@Test
//批量刪除
public void testBatchDelete(){
SqlSession session = null;
session = MyBatisUtils.openSession();
long st = new Date().getTime();
List list = new ArrayList();
for (int i = 4670; i <= 4677; i++) {
list.add(i);
}
session.insert("goods.batchDelete",list);
session.commit();
long et = new Date().getTime();
System.out.println("執(zhí)行時(shí)間:" + (et - st) + "毫秒");
MyBatisUtils.closeSession(session);
}
8.注解
mapper可以通過注解方式配置
新建GoodsDAO：
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)
int insert(Goods goods);
@Select("select * from t_goods")
//配置返回值map
@Results({
@Result(column = "goods_id", property = "goodsId", id = true),
@Result(column = "current_price", property = "currentPrice")
})
List<GoodsDTO> selectAll();
}
mybatis-config.xml中配置dao
<mappers>
<!-- 對(duì)應(yīng)的sql mapper映射類或者包-->
<!-- <mapper class="org.example.dao.GoodsDAO"/>-->
<package name="org.example.dao"/>
</mappers>
調(diào)用：
@Test
public void testSelect(){
SqlSession session = null;
session = MyBatisUtils.openSession();
GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
List<Goods> list = goodsDAO.selectByPriceRange(100f, 500f, 20);
System.out.println(list.size());
MyBatisUtils.closeSession(session);
}
@Test
public void testInsert(){
SqlSession session = null;
session = MyBatisUtils.openSession();
GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
Goods goods = new Goods();
goods.setTitle("測(cè)試商品");
goods.setSubTitle("測(cè)試子標(biāo)題");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.5f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
goodsDAO.insert(goods);
session.commit();
MyBatisUtils.closeSession(session);
}
@Test
public void testSelectAll(){
SqlSession session = null;
session = MyBatisUtils.openSession();
GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
List<GoodsDTO> goodsDTOS = goodsDAO.selectAll();
System.out.println(goodsDTOS.size());
MyBatisUtils.closeSession(session);
}