MyBatis流式查詢的項目實踐
1.應用場景說明 MyBatis
preview: JDBC三種讀取方式：
1.一次全部（默認）：一次獲取全部。
2.流式：多次獲取，一次一行。
3.遊標：多次獲取，一次多行。
在開發中我們經常需要會遇到統計數據，將數據導出到excel表格中。由於生成報表邏輯要從數據庫讀取大量數據並在內存中加工處理後再生成Excel返回給客戶端。如果數據量過大，采用默認的讀取方式（一次性獲取全部）會導致內存飆升，甚至是內存溢出。而導出數據又需要查詢大量的數據，因此采用流式查詢就比較合適瞭。
2.模擬excel導出場景
1.創建海量數據的sql腳本
CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;
#創建表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 $$
#創建一個函數，名字 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 $$
#創建一個存儲過程， 可以添加雇員
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.創建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.創建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.創建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讀取監聽器中對接口進行賦值
//在監聽器中doAfterAllAnalysed，在所有數據解析完之後回調用這個方法，我們在方法中對導出的數據集進行賦值
EasyExcleUtil.importExcel(file, EmpVo.class, new ExcleFinshCallBack(){
@Override
public void doAfterAllAnalysed(List<Object> result) {
empService.exportEmp();
}
});
}
}
4.創建src\main\java\com\llp\llpmybatis\service\EmpService.java
public interface EmpService {
List<EmpVo> exportEmp();
}
5.創建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.創建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導入導出工具類
/**
* excel讀取監聽器
*/
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);
}
}
2.Excel數據讀取監聽器
/**
* excel讀取監聽器
*/
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);
}
}
4.Excel讀取數據完成回調接口
/**
* excel讀取數據完成回調接口
*/
public interface ExcleFinshCallBack {
void doAfterAllAnalysed(List<Object> result);
}
5.拆分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();去關閉連接時，發現連接已經被終止瞭導致的，對數據導出的完整性並沒有影響
到此這篇關於MyBatis流式查詢的項目實踐的文章就介紹到這瞭,更多相關MyBatis流式查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- java操作excel表格詳解
- SpringBoot整合EasyExcel進行大數據處理的方法詳解
- 使用SpringBoot+EasyExcel+Vue實現excel表格的導入和導出詳解
- SpringBoot集成EasyExcel實現Excel導入的方法
- Java 中EasyExcel的使用方式