在执行Java代码以将DB表数据导出到Excel表(.xlsx)时，请从内存中退出

Question

我正在尝试将表数据从数据库导出到Excel表。由于数据很大，因此我使用XSSFWorkBook，以便将数据导出为XLSX文件。我使用了setFetchSize((，以便更快地从db获取数据。我在这里有一个问题，尽管它很快就消耗了我的系统内存和CPU使用情况。我尝试了所有可能的答案谷歌搜索。如果有人建议我进一步继续。

，将会有更大的帮助。

实现了一个建立数据库连接的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％。期望一些快速的导出方法建议，而不会影响记忆。

Answer 1

您可以将最大堆大小设置为运行Java。使用命令行选项-Xmx。

JAVA_ARGS="-Xmx1024m"

查看有关此