序
最近可能會遇到大量資料匯出Excel的場景，今天趁現在需求告一段落來做下技術預研，然后這里就順便分享給大家，
一、資料量預判
因為我們是做物聯網的，這里要匯出的資料就是設備的上報資料，客戶說要這些資料匯出成excel進行分析，又或是其他什么原因，咱不管，咱就分析下資料量，目前設備數量1500，2小時上報一次資料（最小可設定為半小時），要求可以匯出3年的資料，
資料量初步估算：1500 * 12 * 30 * 12 * 3 = 19,440,000 ，設備而且據說還要上，所以估計資料量就是千萬級吧，
現在Excel2007以后單個sheet能放的資料是1,048,576 ，列肯定是夠用的，sheet數量以前是255，現在肯定也是夠用的，但是唯一一點就是這個excel檔案能不能打開還跟電腦的記憶體有關，這個肯定要跟客戶說清楚，了解這些后就可以開始先跟客戶掰扯下，告知風險，看能不能干掉這樣的需求，又或是提供一些方案，
二、方案設計
之前小資料量是介面回傳資料給前端，前端生成excel檔案，現在這么大的資料量，估計要后端實作了，后端也不能一次性把資料查詢出來，記憶體也吃不消，搞不好就報oom錯誤了，所以批量查詢時肯定的，方案設計如下：
1、批量查詢寫入資料到excel，控制單個sheet的資料量，即將超過就新建sheet繼續寫入
2、在1的基礎上批量查詢改為流式查詢（應該流式查詢性能會高，資料庫的連接次數、查詢用的快取應該是下降的）
3、在2的基礎上分多個excel檔案（理論跟實際還是有差異，理論上資料量沒有問題，實際上怕客戶電腦記憶體不夠打開不了）
4、在3的基礎上引入自動任務，定期生成歷史資料excel檔案
5、在3、4的基礎上，合并多個excel檔案為zip包，給客戶下載
6、特殊方案，前端與后端建立ws連接，后端介面流式查詢將結果發布到ws，前端消費ws里的資料，分sheet寫入，分excel寫入，
三、流式查詢
單表是基于tk.mybatis，上關鍵代碼跟測驗示例吧：
extend就是基于tk.mybatis的實作的單表流式查詢，
StreamExampleProvider
import org.apache.ibatis.mapping.MappedStatement;
import tk.mybatis.mapper.mapperhelper.MapperHelper;
import tk.mybatis.mapper.provider.ExampleProvider;
/**
* @author zhengwen
**/
public class StreamExampleProvider extends ExampleProvider {
/**
*
* @param mapperClass
* @param mapperHelper
*/
public StreamExampleProvider(Class<?> mapperClass, MapperHelper mapperHelper) {
super(mapperClass, mapperHelper);
}
/**
* 根據Example流式查詢
*
* @param ms
* @return
*/
public String selectStreamByExampleMapper(MappedStatement ms) {
return this.selectByExample(ms);
}
/**
* 根據Example和RowBounds流式查詢
*
* @param ms
* @return
*/
public String selectStreamByExampleRowBoundsMapper(MappedStatement ms) {
return this.selectByExample(ms);
}
}
SelectStreamByExampleMapper
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;
import tk.mybatis.mapper.annotation.RegisterMapper;
/**
* @author zhengwen
**/
@RegisterMapper
public interface SelectStreamByExampleMapper<T> {
/**
* 根據example條件和RowBounds進行流式查詢
*
* @param example
* @param resultHandler
*/
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
@SelectProvider(type = StreamExampleProvider.class, method = "dynamicSQL")
void selectStreamByExampleMapper(Object example, ResultHandler resultHandler);
}
SelectStreamByExampleRowBoundsMapper
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import tk.mybatis.mapper.annotation.RegisterMapper;
/**
* @author zhengwen
**/
@RegisterMapper
public interface SelectStreamByExampleRowBoundsMapper<T> {
/**
* 根據example條件和RowBounds進行流式查詢
*
* @param example
* @param rowBounds
* @param resultHandler
*/
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
@SelectProvider(type = StreamExampleProvider.class, method = "dynamicSQL")
void selectStreamByExampleRowBoundsMapper(Object example, RowBounds rowBounds, ResultHandler resultHandler);
}
StreamMapper
import tk.mybatis.mapper.annotation.RegisterMapper;
/**
* @author zhengwen
**/
@RegisterMapper
public interface StreamMapper<T> extends
SelectStreamByExampleMapper<T>,
SelectStreamByExampleRowBoundsMapper<T> {
}
我們的mapper實作 StreamMapper
import com.easylinkin.bm.core.extend.StreamMapper;
import tk.mybatis.mapper.common.BaseMapper;
import tk.mybatis.mapper.common.ConditionMapper;
import tk.mybatis.mapper.common.IdsMapper;
import tk.mybatis.mapper.common.MySqlMapper;
import tk.mybatis.mapper.common.special.InsertListMapper;
/**
* 定制版MyBatis Mapper插件介面，如需其他介面參考官方檔案自行添加，
*
* @author zhengwen
*/
public interface Mapper<T>
extends
BaseMapper<T>,
ConditionMapper<T>,
IdsMapper<T>,
InsertListMapper<T>,
MySqlMapper<T>,
StreamMapper<T> {
}
這樣，我們的業務mapper:
/**
* @author unknown
*/
public interface DeviceSensirionRecordMapper extends Mapper<DeviceSensirionRecord> {
/**
* 分組排序list資料
*
* @param deviceSensirionRecordPageVo 頁面入參vo
* @return 上報資料分組排序
*/
List<DeviceSensirionRecordDto> listGroupAndSort(DeviceSensirionRecordPageVo deviceSensirionRecordPageVo);
/**
* 分組排序list資料流式查詢
*
* @param deviceSensirionRecordPageVo 頁面入參vo
* @return 上報資料分組排序資料游標cursor
*/
Cursor<DeviceSensirionRecordDto> listGroupAndSortStream(DeviceSensirionRecordPageVo deviceSensirionRecordPageVo);
}
四、流式查詢使用
我的junit測驗類演示了3種使用方式：
@Test
public void mapperStreamQueryTest() {
log.info("---流式查詢測驗--");
//組織模擬查詢條件物件
DeviceSensirionRecordPageVo deviceSensirionRecordPageVo = new DeviceSensirionRecordPageVo();
log.info("---{}", JSONObject.toJSONString(deviceSensirionRecordPageVo));
DeviceSensirionRecord exp = deviceSensirionRecordPageVo.getDeviceSensirionRecord();
Condition condition = new Condition(DeviceSensirionRecord.class);
Example.Criteria cri = condition.createCriteria();
cri.andEqualTo("deviceNo", exp.getDeviceNo());
//基于我們剛剛擴展tk.mybatis單表流式查詢
deviceSensirionRecordMapper.selectStreamByExampleMapper(condition, resultHandler -> {
DeviceSensirionRecord rs = (DeviceSensirionRecord) resultHandler.getResultObject();
log.info("----{}", JSONObject.toJSONString(rs));
});
//基于游標
Cursor<DeviceSensirionRecordDto> cs = deviceSensirionRecordMapper.listGroupAndSortStream(deviceSensirionRecordPageVo);
cs.forEach(c->{
log.info("----Cursor-------{}", JSONObject.toJSONString(c));
});
//基于sqlSessionTemplate
sqlSessionTemplate.select("com.xx.xx.dao.DeviceSensirionRecordMapper.listGroupAndSort",deviceSensirionRecordPageVo,new RowBounds(1,1000) , rs->{
DeviceSensirionRecordDto ds = (DeviceSensirionRecordDto) rs.getResultObject();
log.info("----{}", JSONObject.toJSONString(ds));
});
}
里面有3種方式：
1、基于我們剛剛擴展tk.mybatis單表流式查詢
2、基于游標，mybatis支持的
3、基于sqlSessionTemplate
本來還準備擴展下tk.mybatis的流式查詢支持復雜查詢的，但是試了2、3后決定放棄了，直接用也很香很簡單，沒有意義在擴展，
五、流式查詢配合多sheet生成excel
方案應該就上面那些，今天先研究到的是流式查詢配合多sheet生成，我這里流式查詢研究到了單表的、自定義的，這里直接上復雜的流式查詢配合多sheet生成excel，看我的junit測驗類：
@Test
public void streamQueryExcel() {
log.info("---流式查詢生成Excel--");
String name = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");
String fileName = baseTempPath + File.separator + name + ".xlsx";
File exFile = new File(fileName);
ExcelWriter excelWriter = ExcelUtil.getBigWriter(exFile, "上報資料-1");
List<Map<String, String>> ls = new ArrayList<>();
//sheet的index、資料序號初始
AtomicInteger sheetIndx = new AtomicInteger(0);
AtomicInteger index = new AtomicInteger(0);
DeviceSensirionRecordPageVo deviceSensirionRecordPageVo = new DeviceSensirionRecordPageVo();
//mybatis流式查詢
SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession();
Cursor<DeviceSensirionRecordDto> cs = sqlSession.getMapper(DeviceSensirionRecordMapper.class).listGroupAndSortStream(deviceSensirionRecordPageVo);
cs.forEach(c -> {
log.info("----Cursor-------{}", JSONObject.toJSONString(c));
//資料轉換
converToExcelData(index, c, ls);
//達到量就批量寫入
if (index.get() == 10000) {
writeExcelData(sheetIndx, excelWriter, ls);
//序號、資料物件清空
ls.clear();
index.set(0);
}
});
//最后一批資料寫入excel
if (CollectionUtil.isNotEmpty(ls)) {
writeExcelData(sheetIndx, excelWriter, ls);
}
//最后重繪檔案
excelWriter.flush(exFile);
excelWriter.close();
log.info("---------end------------");
}
/**
* 資料轉換
*
* @param index
* @param dto
* @param ls
*/
private void converToExcelData(AtomicInteger index, DeviceSensirionRecordDto dto, List<Map<String, String>> ls) {
index.set(index.get() + 1);
Map<String, String> mp = new HashMap<>();
mp.put("index", String.valueOf(index.get()));
mp.put("deviceNo", dto.getDeviceNo());
mp.put("temperature", String.valueOf(dto.getTemperature()));
mp.put("humidity", String.valueOf(dto.getHumidity()));
mp.put("power", String.valueOf(dto.getPower()));
mp.put("recordTime", DateUtil.format(dto.getRecordTime(), "yyyy-MM-dd HH:mm:ss"));
ls.add(mp);
}
/**
* 批量寫資料到excel
*
* @param sheetIndx
* @param excelWriter
* @param ls
*/
private void writeExcelData(AtomicInteger sheetIndx, ExcelWriter excelWriter, List<Map<String, String>> ls) {
sheetIndx.set(sheetIndx.get() + 1);
if (sheetIndx.get() != 0) {
//設定sheet名稱
excelWriter.setSheet("上報資料-" + sheetIndx);
}
//列頭
writeExcelHead(excelWriter);
//資料寫入
excelWriter.write(ls, true);
excelWriter.autoSizeColumnAll();
}
/**
* 列頭設定
* @param excelWriter
*/
private void writeExcelHead(ExcelWriter excelWriter) {
excelWriter.addHeaderAlias("index", "序號");
excelWriter.addHeaderAlias("deviceNo", "設備編碼");
excelWriter.addHeaderAlias("temperature", "溫度");
excelWriter.addHeaderAlias("humidity", "濕度");
excelWriter.addHeaderAlias("power", "電量");
excelWriter.addHeaderAlias("recordTime", "上報時間");
}
我這里是使用的HuTool的工具包，不得不說，真香啊，以前也用easyExcel、myExcel,或者直接用poi，但是都對代碼有一定的侵入性，我拋棄了，
六、流式查詢配合多sheet生成excel效果
最后說一點，流式查詢應該是長時間需要占資料庫連接的，所以也需要謹慎使用，
就先分享到這里，希望可以啟發大家，特殊方案要前端配合，沒有下載環節，但是寫資料到sheet、生成多個excel這些邏輯都是前端寫了，估計一般的前端都不會愿意，他們的理論是他們是使用資料，哈哈，其實吧我覺得這也是使用資料啊，不糾結，我們后端啥都可以干，沒必要別人不愿意還非要，那就有點QJ了，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/300314.html
標籤：其他