MyBatis流式查詢的使用詳解
更新時間：2022年08月17日 10:36:52 作者：llp1110
流式查詢指的是查詢成功后不是返回一個集合而是返回一個迭代器，應用每次從迭代器取一條查詢結果。流式查詢的好處是能夠降低內存使用
1.應用場景說明
MyBatis
preview: JDBC三種讀取方式：
1.一次全部（默認）：一次獲取全部。
2.流式：多次獲取，一次一行。
3.游標：多次獲取，一次多行。
在開發(fā)中我們經常需要會遇到統計數據，將數據導出到excel表格中。由于生成報表邏輯要從數據庫讀取大量數據并在內存中加工處理后再生成Excel返回給客戶端。如果數據量過大，采用默認的讀取方式（一次性獲取全部）會導致內存飆升，甚至是內存溢出。而導出數據又需要查詢大量的數據，因此采用流式查詢就比較合適了。
2.模擬excel導出場景
1.創(chuàng)建海量數據的sql腳本
CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;
#創(chuàng)建表EMP雇員
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級編號*/
hiredate DATE NOT NULL,/*入職時間*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*紅利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/
) ;
#工資級別表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
);
#測試數據
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
delimiter $$
#創(chuàng)建一個函數，名字 rand_string，可以隨機返回我指定的個數字符串
create function rand_string(n INT)
returns varchar(255) #該函數會返回一個字符串
begin
#定義了一個變量 chars_str， 類型 varchar(100)
#默認給 chars_str 初始值 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
# concat 函數 : 連接函數mysql函數
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
#這里我們又自定了一個函數,返回一個隨機的部門號
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
#創(chuàng)建一個存儲過程， 可以添加雇員
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit設置成0
#autocommit = 0 含義: 不要自動提交
set autocommit = 0; #默認不提交sql語句
repeat
set i = i + 1;
#通過前面寫的函數隨機產生字符串和部門編號，然后加入到emp表
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
#commit整體提交所有sql語句，提高效率
commit;
end $$
#添加8000000數據
call insert_emp(100001,8000000)$$
#命令結束符，再重新設置為;
delimiter ;
2.MyBatis流式查詢
1.創(chuàng)建src\main\java\com\llp\llpmybatis\entity\Emp.java
@Data
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private BigDecimal sal;
private BigDecimal comm;
private Integer deptno;
}
2.創(chuàng)建src\main\java\com\llp\llpmybatis\vo\EmpVo.java
@Data
public class EmpVo {
@ExcelProperty("員工編號")
private Integer empno;
@ExcelProperty("員工姓名")
private String ename;
@ExcelProperty("員工工種")
private String job;
@ExcelProperty("主管編號")
private Integer mgr;
@ExcelProperty("入職日期")
private Date hiredate;
@ExcelProperty("工資")
private BigDecimal sal;
@ExcelProperty("通訊")
private BigDecimal comm;
@ExcelProperty("部門編號")
private Integer deptno;
}3.創(chuàng)建src\main\java\com\llp\llpmybatis\controller\EmpController.java
@RestController
public class EmpController {
@Autowired
private EmpService empService;
/**
* 導出員工數據到excel
*/
@RequestMapping("/export")
public void exportEmp(){
StopWatch watch = new StopWatch();
watch.start();
List<EmpVo> empList = empService.exportEmp();
//將數據分sheet進行導出
EasyExcleUtil.excelExportDivisionBySheet(EmpVo.class, "員工信息_"+System.currentTimeMillis(), empList);
watch.stop();
long totalTimeMillis = watch.getTotalTimeMillis();
System.out.println("共計耗時："+totalTimeMillis+"毫秒");
}
/**
* 導入excel數據到員工表
* @param file
*/
@RequestMapping("/import")
public void importEmp(@RequestParam(name = "file") MultipartFile file){
//這里我們在導入時傳入回調接口的匿名內部類實現，在ExcleDataListener easyExcel讀取監(jiān)聽器中對接口進行賦值
//在監(jiān)聽器中doAfterAllAnalysed，在所有數據解析完之后回調用這個方法，我們在方法中對導出的數據集進行賦值
EasyExcleUtil.importExcel(file, EmpVo.class, new ExcleFinshCallBack(){
@Override
public void doAfterAllAnalysed(List<Object> result) {
empService.exportEmp();
}
});
}
}4.創(chuàng)建src\main\java\com\llp\llpmybatis\service\EmpService.java
public interface EmpService {
List<EmpVo> exportEmp();
}5.創(chuàng)建src\main\java\com\llp\llpmybatis\service\impl\EmpServiceImpl.java（重點）
@Service
public class EmpServiceImpl implements EmpService {
@Resource
private EmpDao empdao;
/**
* mybatis流式查詢導出員工數據
* @return
*/
@Override
public List<EmpVo> exportEmp() {
StopWatch stopWatch = new StopWatch();
stopWatch.start();
List<EmpVo> empList = new ArrayList<>();
empdao.getAll(new ResultHandler<EmpVo>() {
/**
* mybatis流失查詢會回調處理邏輯
* @param resultContext
*/
@Override
public void handleResult(ResultContext<? extends EmpVo> resultContext) {
empList.add(resultContext.getResultObject());
}
});
stopWatch.stop();
System.out.println("查詢共計耗費"+stopWatch.getTotalTimeMillis()+"毫秒");
return empList;
}
}6.創(chuàng)建src\main\java\com\llp\llpmybatis\dao\EmpDao.java（重點）
@Repository
public interface EmpDao {
void getAll(ResultHandler<EmpVo> handler);
}
這里dao層沒有返回值，但是在還是需要指定resultMap，因為查詢的數據要映射到回調函數的resultContext中，此外還需要設置：resultSetType=“FORWARD_ONLY” 、fetchSize=“-2147483648”
EmpDao.xml
<mapper namespace="com.llp.llpmybatis.dao.EmpDao">
<resultMap id="empResultMap" type="com.llp.llpmybatis.vo.EmpVo">
<result column="empno" property="empno"/>
<result column="ename" property="ename"/>
<result column="job" property="job"/>
<result column="mgr" property="mgr"/>
<result column="hiredate" property="hiredate"/>
<result column="sal" property="sal"/>
<result column="comm" property="comm"/>
<result column="deptno" property="deptno"/>
</resultMap>
<select id="getAll" resultMap="empResultMap" resultSetType="FORWARD_ONLY" fetchSize="-2147483648">
select *
from emp;
</select>
</mapper>至此mybatis流式查詢就完成了
3.Excel通用導出工具類
1.Excel導入導出工具類
public class EasyExcleUtil {
private static final int MAXROWS = 500000;
/**
* excel讀取
*
* @param file excel文件
* @param head 列名
* @param callBack 回調接口的實現類
*/
public static void importExcel(MultipartFile file, Class head, ExcleFinshCallBack callBack) {
try {
EasyExcel.read(file.getInputStream(), head, new ExcleDataListener(callBack)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 導出數據
*
* @param head 類名
* @param excelname excel名字
* @param data 數據
* java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowa
* 這是由于Excel的一張sheet允許的最大行數是1048575，由于導出的數據比較大，超出了一張sheet所能容納的最大行數，導致無法繼續(xù)創(chuàng)建新的行
* 1048575
* 1000000
*/
public static void excelExport(Class head, String excelname, List data) {
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = requestAttributes.getResponse();
// 這里注意 有同學反應使用swagger 會導致各種問題，請直接用瀏覽器或者用postman
//response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
try {
// 這里URLEncoder.encode可以防止中文亂碼 當然和easyexcel沒有關系
String fileName = URLEncoder.encode(excelname, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), head).sheet("Sheet1").doWrite(data);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 獲取默認表頭內容的樣式
*
* @return
*/
private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy() {
/** 表頭樣式 **/
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景色（淺灰色）
// 可以參考：https://www.cnblogs.com/vofill/p/11230387.html
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 字體大小
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
//設置表頭居中對齊
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
/** 內容樣式 **/
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 內容字體樣式（名稱、大?。?
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("宋體");
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//設置內容垂直居中對齊
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//設置內容水平居中對齊
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//設置邊框樣式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 頭樣式與內容樣式合并
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* 將數據分sheet進行導出
* @param data 查詢結果
* @param fileName 導出文件名稱
* @param clazz 映射實體class類
* @param <T> 查詢結果類型
* @throws Exception
*/
public static <T> void excelExportDivisionBySheet(Class clazz, String fileName, List<T> data) {
OutputStream out = null;
ExcelWriter excelWriter = null;
try {
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = requestAttributes.getResponse();
// 分割的集合
List<List<T>> lists = SplitList.splitList(data, MAXROWS);
out = getOutputStream(fileName, response);
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
excelWriter = excelWriterBuilder.build();
ExcelWriterSheetBuilder excelWriterSheetBuilder;
WriteSheet writeSheet;
for (int i = 1; i <= lists.size(); i++) {
excelWriterSheetBuilder = new ExcelWriterSheetBuilder();
excelWriterSheetBuilder.sheetNo(i);
excelWriterSheetBuilder.sheetName("sheet" + i);
writeSheet = excelWriterSheetBuilder.build();
excelWriter.write(lists.get(i - 1), writeSheet);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws IOException {
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
// response.setContentType("application/vnd.ms-excel"); // .xls
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // .xlsx
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
return response.getOutputStream();
}
}2.Excel數據讀取監(jiān)聽器
/**
* excel讀取監(jiān)聽器
*/
public class ExcleDataListener extends AnalysisEventListener {
//定義一個保存Excel所有記錄的集合
private List<Object> list = new LinkedList<>();
//回調接口
private ExcleFinshCallBack callBack;
/**
* 構造注入ExcleFinshCallBack
* @param callBack
*/
public ExcleDataListener(ExcleFinshCallBack callBack) {
this.callBack = callBack;
}
/**
* 這個每一條數據解析都會來調用
* 我們將每一條數據都保存到list集合中
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(Object data, AnalysisContext context) {
list.add(data);
}
/**
* 所有數據解析完成了 都會來調用這個方法
* 在
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
this.callBack.doAfterAllAnalysed(this.list);
}
}3.Excel讀取數據完成回調接口
/**
* excel讀取數據完成回調接口
*/
public interface ExcleFinshCallBack {
void doAfterAllAnalysed(List<Object> result);
}
4.拆分List集合工具類
/**
* 拆分List集合
*/
public class SplitListUtil {
/**
*
* @param list 待切割集合
* @param len 集合按照多大size來切割
* @param <T>
* @return
*/
public static <T> List<List<T>> splitList(List<T> list, int len) {
if (list == null || list.size() == 0 || len < 1) {
return null;
}
List<List<T>> result = new ArrayList<List<T>>();
int size = list.size();
int count = (size + len - 1) / len;
for (int i = 0; i < count; i++) {
List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
result.add(subList);
}
return result;
}
/**
* @param source 源集合
* @param n 分成n個集合
* @param <T> 集合類型
* @return
* @description 集合平均分組
*/
public static <T> List<List<T>> groupList(List<T> source, int n) {
if (source == null || source.size() == 0 || n < 1) {
return null;
}
if (source.size() < n) {
return Arrays.asList(source);
}
List<List<T>> result = new ArrayList<List<T>>();
int number = source.size() / n;
int remaider = source.size() % n;
// 偏移量，每有一個余數分配，就要往右偏移一位
int offset = 0;
for (int i = 0; i < n; i++) {
List<T> list1 = null;
if (remaider > 0) {
list1 = source.subList(i * number + offset, (i + 1) * number + offset + 1);
remaider--;
offset++;
} else {
list1 = source.subList(i * number + offset, (i + 1) * number + offset);
}
result.add(list1);
}
return result;
}
}4.測試結果
sheet1
sheet2
sheet3
5.遺留問題待處理
這個問題時由于excelWriter.finish();去關閉連接時，發(fā)現連接已經被終止了導致的，對數據導出的完整性并沒有影響
到此這篇關于MyBatis流式查詢的使用詳解的文章就介紹到這了,更多相關MyBatis流式查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了mybatis-plus如何禁用一級緩存的方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-03-03
這篇文章主要介紹了Java設計模式之觀察者模式解析,觀察者模式,又被稱為發(fā)布/訂閱模式,它定義了一種一對多的依賴關系,讓多個觀察者對象同時監(jiān)聽某一個主題對象,這個主題對象在狀態(tài)變化時,會通知所有的觀察者對象,使他們能夠自動更新自己,需要的朋友可以參考下2023-09-09
調試工具（debug工具）是一種用于幫助程序員識別和修復程序中的錯誤的工具,它們提供了一系列的功能,幫助程序員在代碼執(zhí)行的過程中跟蹤和檢測問題,本文將給大家介紹使用debug工具去運行java程序的實現步驟,需要的朋友可以參考下2024-04-04
今天小編就為大家分享一篇關于Java事件監(jiān)聽機制講解，小編覺得內容挺不錯的，現在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-02-02
這篇文章主要介紹了SpringBoot（微服務）注冊分布式Consul,Spring Boot應用可以通過向Consul注冊自身來實現服務發(fā)現和治理，使得其他服務可以在Consul中發(fā)現并調用它，需要的朋友可以參考下2023-04-04
這篇文章主要介紹了Springboot配置文件相關說明解析,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-06-06
一般來講SpringBoot項目是不需要指定版本，而SSM項目是需要指定版本，SpringBoot的核心依賴就是spring-boot-starter-parent和spring-boot-starter-web兩個依賴，關于這兩個依賴的相關介紹具體今天小編給大家介紹下2022-07-07
只學書上的理論是遠遠不夠的，只有在實戰(zhàn)中才能獲得能力的提升，本篇文章手把手帶你用java+Springboot+Maven+mybatis+Vue+Mysql實現一個花店商城系統,大家可以在過程中查缺補漏，提升水平2022-01-01
這篇文章主要介紹了使用EasyPoi輕松導入導出Excel文檔的方法示例，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-12-12
Spring?Boot使用Schedule實現定時任務的方法
這篇文章主要介紹了Spring?Boot使用Schedule實現定時任務,本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-03-03