java - 执行 java 代码以将 DB 表数据导出到 Excel 工作表 (.xlsx) 时内存不足

问题描述

我正在尝试将表数据从数据库导出到 Excel 工作表。由于数据很大，我使用了 XSSFWorkbook，以便将数据导出为 xlsx 文件。我使用 setFetchsize() 以便更快地从数据库中获取数据。我在这里有一个问题，虽然它很快会消耗我的系统内存和 CPU 使用率。我尝试了所有可能的答案谷歌搜索。如果有人建议我继续进行，将会有更大的帮助。

实现了一个建立 DB 连接的 Java 类，一旦建立连接，就将表中的数据提取到结果集，以提高我使用 setFetchSize(1000) 方法的提取速度。恰好在 80000 条记录之后内存不足。也尝试使用 setFetchSize(10000) 。

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XSSF_Excel_Appender {
  private static Connection getConnection(){
    Connection con = null;
    String url = "connection string";
    try{
        Class.forName("driver class");
        con = DriverManager.getConnection(url,"username","password");
    }
    catch(ClassNotFoundException e){
        e.printStackTrace();
        System.out.println("Driver class not found.");
    }
    catch(SQLException e){
        e.printStackTrace();
        System.out.println("Exception occured while connecting DB");
    }
    return con;
  }

  public boolean getTableData(int range){
    boolean flag = true;
    ArrayList<Object[]> tableDataList = null;
    int num = 0;
    Connection con = getConnection();
    if(con != null){
        try{
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet("*******");
            XSSFRow headingRow = sheet.createRow(0);
            headingRow.createCell(0).setCellValue("*****");
            headingRow.createCell(1).setCellValue("*******");
            headingRow.createCell(2).setCellValue("*********");
            headingRow.createCell(3).setCellValue("*****");
            headingRow.createCell(4).setCellValue("******");
            headingRow.createCell(5).setCellValue("*****");
            Statement ps1=con.createStatement();
            ResultSet resultSet = ps1.executeQuery("SELECT COUNT(*) from 
            table_name");
            while(resultSet.next()) {
              num = Integer.parseInt(resultSet.getString(1));
            }
            System.out.println("Number of rows in (table_name) "+num);
            for(int i=0;i<100000;i+=range) {
                Statement ps2 = con.createStatement();
                ps2.setFetchSize(1000);
                ResultSet result = ps2.executeQuery("SELECT ROWNUM as 
                S_NO,(TABLE_NAME).* FROM Table_name offset "+i+" rows 
                fetch next "+range+" rows only");
                tableDataList = new ArrayList<Object[]>();
                while(result.next()) {
                    Object[] objArray = new Object[6];
                    objArray[0] = (result.getString(1) == null ? "Null" : 
                                   result.getString(1));
                    objArray[1] = (result.getString(2) == null ? "Null" : 
                                   result.getString(2));
                    objArray[2] = (result.getString(3) == null ? "Null" : 
                                   result.getString(3));
                    objArray[3] = (result.getString(4) == null ? "Null" : 
                                   result.getString(4));
                    objArray[4] = (result.getString(5) == null ? "Null" : 
                                   result.getString(5));
                    objArray[5] = (result.getString(6) == null ? "Null" : 
                                   result.getString(6));
                    tableDataList.add(objArray);
                }
                if(tableDataList != null && tableDataList.size() > 0){
                    int lastRow=sheet.getLastRowNum();
                    for (Object[] objects : tableDataList) {
                        XSSFRow row = sheet.createRow(++lastRow);
                        int colNum = 0;
                        for (Object field : objects) {
                            Cell cell = row.createCell(colNum++);
                            if (field instanceof String) {
                                cell.setCellValue((String) field);
                            } else if (field instanceof Integer) {
                                cell.setCellValue((Integer) field);
                            }
                        }
                    }
                }
            }
            String file = "Location to store the exported xlsx workbook";
            FileOutputStream fos = new FileOutputStream(new File(file));
            workBook.write(fos);
            fos.close();
          }catch(SQLException e){
             flag = false;
             e.printStackTrace();
             System.out.println("Unable to create PreparedStatement");
         }
        catch(FileNotFoundException e){
            e.printStackTrace();
            System.out.println("Invalid directory or file not found");
        }catch(IOException e){
            e.printStackTrace();
            System.out.println("Error while writing .xlsx to directory");
        }
    }
    return flag;
}

public static void main(String[] args) {
    Date d1 = new Date();
    try {
        System.out.println("Inside Main method time: "+d1);
        int range = 10000;
        XSSF_Excel_Appender exporter = new XSSF_Excel_Appender();
        if(exporter.getTableData(range)) {
            System.out.println("Successfully exported");
        }
        else{
            System.out.println("Some error has occurred");
        }
    }
    catch(Exception ex) {
        ex.printStackTrace();
    }
    Date d2 = new Date();
    System.out.println("End time : "+d2);
    long diff = d2.getTime() - d1.getTime();
    System.out.println("Total time taken (using Array List) : 
   "+diff/(60*1000)%60+" minutes,"+diff/1000%60+" seconds.");
  }
 }

获取 80000 条记录时，CPU 使用率已达到 100%。期待一些快速导出方法建议而不影响内存。

标签： javaexceljdbcresultset

java - 执行 java 代码以将 DB 表数据导出到 Excel 工作表 (.xlsx) 时内存不足

问题描述

解决方案

推荐阅读