MyBatis流式查询
1.应用场景说明
- MyBatis preview: JDBC三种读取方式： 1.一次全部（默认）：一次获取全部。 2.流式：多次获取，一次一行。 3.游标：多次获取，一次多行。
在开发中我们经常需要会遇到统计数据，将数据导出到excel表格中。由于生成报表逻辑要从数据库读取大量数据并在内存中加工处理后再生成Excel返回给客户端。如果数据量过大，采用默认的读取方式（一次性获取全部）会导致内存飙升，甚至是内存溢出。而导出数据又需要查询大量的数据，因此采用流式查询就比较合适了。
2.模拟excel导出场景
1.创建海量数据的sql脚本
CREATETABLE dept(/*部门表*/
deptno MEDIUMINTUNSIGNEDNOTNULLDEFAULT0,
dname VARCHAR(20)NOTNULLDEFAULT"",
loc VARCHAR(13)NOTNULLDEFAULT"");#创建表EMP雇员CREATETABLE emp
(empno MEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*编号*/
ename VARCHAR(20)NOTNULLDEFAULT"",/*名字*/
job VARCHAR(9)NOTNULLDEFAULT"",/*工作*/
mgr MEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*上级编号*/
hiredate DATENOTNULL,/*入职时间*/
sal DECIMAL(7,2)NOTNULL,/*薪水*/
comm DECIMAL(7,2)NOTNULL,/*红利*/
deptno MEDIUMINTUNSIGNEDNOTNULLDEFAULT0/*部门编号*/);#工资级别表CREATETABLE salgrade
(
grade MEDIUMINTUNSIGNEDNOTNULLDEFAULT0,
losal DECIMAL(17,2)NOTNULL,
hisal DECIMAL(17,2)NOTNULL);#测试数据INSERTINTO salgrade VALUES(1,700,1200);INSERTINTO salgrade VALUES(2,1201,1400);INSERTINTO salgrade VALUES(3,1401,2000);INSERTINTO salgrade VALUES(4,2001,3000);INSERTINTO salgrade VALUES(5,3001,9999);delimiter $$
#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串createfunction rand_string(n INT)returnsvarchar(255)#该函数会返回一个字符串begin#定义了一个变量 chars_str， 类型 varchar(100)#默认给 chars_str 初始值 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'declare chars_str varchar(100)default'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255)default'';declare i intdefault0;while i < n do# concat 函数 : 连接函数mysql函数set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));set i = i +1;endwhile;return return_str;end $$
#这里我们又自定了一个函数,返回一个随机的部门号createfunction rand_num()returnsint(5)begindeclare i intdefault0;set i = floor(10+rand()*500);return i;end $$
#创建一个存储过程， 可以添加雇员createprocedure insert_emp(instartint(10),in max_num int(10))begindeclare i intdefault0;#set autocommit =0 把autocommit设置成0#autocommit = 0 含义: 不要自动提交set autocommit =0;#默认不提交sql语句repeatset i = i +1;#通过前面写的函数随机产生字符串和部门编号，然后加入到emp表insertinto emp values((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
endrepeat;#commit整体提交所有sql语句，提高效率commit;end $$
#添加8000000数据call insert_emp(100001,8000000)$$
#命令结束符，再重新设置为;delimiter;
2.MyBatis流式查询
1.创建src\main\java\com\llp\llpmybatis\entity\Emp.java
@DatapublicclassEmp{privateInteger empno;privateString ename;privateString job;privateInteger mgr;privateDate hiredate;privateBigDecimal sal;privateBigDecimal comm;privateInteger deptno;}
2.创建src\main\java\com\llp\llpmybatis\vo\EmpVo.java
@DatapublicclassEmpVo{@ExcelProperty("员工编号")privateInteger empno;@ExcelProperty("员工姓名")privateString ename;@ExcelProperty("员工工种")privateString job;@ExcelProperty("主管编号")privateInteger mgr;@ExcelProperty("入职日期")privateDate hiredate;@ExcelProperty("工资")privateBigDecimal sal;@ExcelProperty("通讯")privateBigDecimal comm;@ExcelProperty("部门编号")privateInteger deptno;}
3.创建src\main\java\com\llp\llpmybatis\controller\EmpController.java
@RestControllerpublicclassEmpController{@AutowiredprivateEmpService empService;/**
* 导出员工数据到excel
*/@RequestMapping("/export")publicvoidexportEmp(){StopWatch watch =newStopWatch();
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")publicvoidimportEmp(@RequestParam(name ="file")MultipartFile file){//这里我们在导入时传入回调接口的匿名内部类实现，在ExcleDataListener easyExcel读取监听器中对接口进行赋值//在监听器中doAfterAllAnalysed，在所有数据解析完之后回调用这个方法，我们在方法中对导出的数据集进行赋值EasyExcleUtil.importExcel(file,EmpVo.class,newExcleFinshCallBack(){@OverridepublicvoiddoAfterAllAnalysed(List<Object> result){
empService.exportEmp();}});}}
4.创建src\main\java\com\llp\llpmybatis\service\EmpService.java
publicinterfaceEmpService{List<EmpVo>exportEmp();}
5.创建src\main\java\com\llp\llpmybatis\service\impl\EmpServiceImpl.java（重点）
@ServicepublicclassEmpServiceImplimplementsEmpService{@ResourceprivateEmpDao empdao;/**
* mybatis流式查询导出员工数据
* @return
*/@OverridepublicList<EmpVo>exportEmp(){StopWatch stopWatch =newStopWatch();
stopWatch.start();List<EmpVo> empList =newArrayList<>();
empdao.getAll(newResultHandler<EmpVo>(){/**
* mybatis流失查询会回调处理逻辑
* @param resultContext
*/@OverridepublicvoidhandleResult(ResultContext<?extendsEmpVo> resultContext){
empList.add(resultContext.getResultObject());}});
stopWatch.stop();System.out.println("查询共计耗费"+stopWatch.getTotalTimeMillis()+"毫秒");return empList;}}
6.创建src\main\java\com\llp\llpmybatis\dao\EmpDao.java（重点）
@RepositorypublicinterfaceEmpDao{voidgetAll(ResultHandler<EmpVo> handler);}
这里dao层没有返回值，但是在还是需要指定resultMap，因为查询的数据要映射到回调函数的resultContext中，此外还需要设置：resultSetType=“FORWARD_ONLY” 、fetchSize=“-2147483648”
EmpDao.xml
<mappernamespace="com.llp.llpmybatis.dao.EmpDao"><resultMapid="empResultMap"type="com.llp.llpmybatis.vo.EmpVo"><resultcolumn="empno"property="empno"/><resultcolumn="ename"property="ename"/><resultcolumn="job"property="job"/><resultcolumn="mgr"property="mgr"/><resultcolumn="hiredate"property="hiredate"/><resultcolumn="sal"property="sal"/><resultcolumn="comm"property="comm"/><resultcolumn="deptno"property="deptno"/></resultMap><selectid="getAll"resultMap="empResultMap"resultSetType="FORWARD_ONLY"fetchSize="-2147483648">
select *
from emp;
</select></mapper>
至此mybatis流式查询就完成了
3.Excel通用导出工具类
1.Excel导入导出工具类
publicclassEasyExcleUtil{privatestaticfinalint MAXROWS =500000;/**
* excel读取
*
* @param file excel文件
* @param head 列名
* @param callBack 回调接口的实现类
*/publicstaticvoidimportExcel(MultipartFile file,Class head,ExcleFinshCallBack callBack){try{EasyExcel.read(file.getInputStream(), head,newExcleDataListener(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所能容纳的最大行数，导致无法继续创建新的行
* 1048575
* 1000000
*/publicstaticvoidexcelExport(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
*/privatestaticHorizontalCellStyleStrategygetDefaultHorizontalCellStyleStrategy(){/** 表头样式 **/WriteCellStyle headWriteCellStyle =newWriteCellStyle();// 背景色（浅灰色）// 可以参考：https://www.cnblogs.com/vofill/p/11230387.html
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 字体大小WriteFont headWriteFont =newWriteFont();
headWriteFont.setFontHeightInPoints((short)10);
headWriteCellStyle.setWriteFont(headWriteFont);//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);/** 内容样式 **/WriteCellStyle contentWriteCellStyle =newWriteCellStyle();// 内容字体样式（名称、大小）WriteFont contentWriteFont =newWriteFont();
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);// 头样式与内容样式合并returnnewHorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}/**
* 将数据分sheet进行导出
* @param data 查询结果
* @param fileName 导出文件名称
* @param clazz 映射实体class类
* @param <T> 查询结果类型
* @throws Exception
*/publicstatic<T>voidexcelExportDivisionBySheet(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 =newExcelWriterSheetBuilder();
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();}}}}privatestaticOutputStreamgetOutputStream(String fileName,HttpServletResponse response)throwsIOException{
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数据读取监听器
/**
* excel读取监听器
*/publicclassExcleDataListenerextendsAnalysisEventListener{//定义一个保存Excel所有记录的集合privateList<Object> list =newLinkedList<>();//回调接口privateExcleFinshCallBack callBack;/**
* 构造注入ExcleFinshCallBack
* @param callBack
*/publicExcleDataListener(ExcleFinshCallBack callBack){this.callBack = callBack;}/**
* 这个每一条数据解析都会来调用
* 我们将每一条数据都保存到list集合中
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/@Overridepublicvoidinvoke(Object data,AnalysisContext context){
list.add(data);}/**
* 所有数据解析完成了 都会来调用这个方法
* 在
* @param context
*/@OverridepublicvoiddoAfterAllAnalysed(AnalysisContext context){this.callBack.doAfterAllAnalysed(this.list);}}
4.Excel读取数据完成回调接口
/**
* excel读取数据完成回调接口
*/publicinterfaceExcleFinshCallBack{voiddoAfterAllAnalysed(List<Object> result);}
5.拆分List集合工具类
/**
* 拆分List集合
*/publicclassSplitListUtil{/**
*
* @param list 待切割集合
* @param len 集合按照多大size来切割
* @param <T>
* @return
*/publicstatic<T>List<List<T>>splitList(List<T> list,int len){if(list ==null|| list.size()==0|| len <1){returnnull;}List<List<T>> result =newArrayList<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 集合平均分组
*/publicstatic<T>List<List<T>>groupList(List<T> source,int n){if(source ==null|| source.size()==0|| n <1){returnnull;}if(source.size()< n){returnArrays.asList(source);}List<List<T>> result =newArrayList<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();
去关闭连接时，发现连接已经被终止了导致的，对数据导出的完整性并没有影响
本文转载自: https://blog.csdn.net/qq_44981526/article/details/126355254
版权归原作者 llp1110 所有， 如有侵权，请联系我们删除。
版权归原作者 llp1110 所有， 如有侵权，请联系我们删除。