基于jdbc的大数据导出到excel

 直接上代码：

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

pubic class ExportData{
  public static void export(String sql) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connect = DriverManager.getConnection(
                "jdbc:mysql://172.31.50.39:3308/test_sce_doraemon?zeroDateTimeBehavior=convertToNull&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useCursorFetch=true&defaultFetchSize=1000" ,
                "root" ,
                "123456"
        );
    
        Statement statement = connect.createStatement();

        ResultSet resultSet = statement
                .executeQuery(sql);

        ResultSetMetaData metaData = resultSet.getMetaData();
        try {
            long start = System.currentTimeMillis();

            SXSSFWorkbook wb = new SXSSFWorkbook(100);
            Sheet sh = wb.createSheet();
            Row row = null;
            int i = 0;
            while ((resultSet.next())) {
                row = sh.createRow(i++);
                for (int column = 1; column <= metaData.getColumnCount(); column++) {
                    Cell cell = row.createCell(column);
                    if (i == 1) {
                        cell.setCellValue(metaData.getColumnLabel(column));
                    } else {
                        cell.setCellValue(resultSet.getString(metaData.getColumnLabel(column)));
                    }
                }
            }
            FileOutputStream out = new FileOutputStream("/work/test.xlsx");

            wb.write(out);
            out.close();
            wb.dispose();

            long end = System.currentTimeMillis();
            System.out.println("cost time: " + (end - start) / 1000 + "  s");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            statement.close();
            resultSet.close();
            connect.close();
        }

    }
}

 以上代码就是主要实现代码，主要是通过jdbc连接，一边读一边通过POI写excel，针对大数据导出性能非常好，亲测1000万的mysql测试数据导出70w只要30秒。

poi需要3.9以上，利用poi针对大数据优化的依赖，注意两个依赖的版本最好一致，免得出现莫名错误。

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.9</version>
</dependency>
<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
</dependency>

 

问题：如果是通过Mybatis的实现，是通过动态生成，并且带参数的脚本，拿不到jdbc和具体sql怎么办？

解决：我们一般通过mybatis是通过 SqlSessionTemplate的selectList等方法来获取结果，这时候可以调用

public void select(String statement, Object parameter, ResultHandler handler) {
        this.sqlSessionProxy.select(statement, parameter, handler);
 }

 重写ResultHandler的实现，里面可以会获取每条记录，我的例子：

 final SXSSFWorkbook wb = new SXSSFWorkbook(100);
 final Sheet sh = wb.createSheet();
serviceSqlSession.select(statement , map, new ResultHandler() {
		 @Override
		 public void handleResult(ResultContext resultContext) {
			 Object obj = resultContext.getResultObject();
			 Map map = (HashMap) obj;
			 Iterator<Map.Entry<String, String>> entries = map.entrySet().iterator();
			 Row row = sh.createRow(INIT_ROW_NUM++);
			 int j=0;
			 while (entries.hasNext()) {
				Map.Entry entry = entries.next();
				 Cell cell = row.createCell(j++);
				 if (INIT_ROW_NUM == 1) {
					 cell.setCellValue(entry.getKey().toString());
				 } else {
					 cell.setCellValue((String)entry.getValue().toString());
				 }
			 }
		 }
	 });
FileOutputStream out = new FileOutputStream(filePath);
 wb.write(out);
 out.close();
  wb.dispose();
INIT_ROW_NUM =0;