Java如何在结果集中检索超过一百万行 [英] Java how to retrieve more than 1 million rows in a Resultset

问题描述

我正在对具有16,213,156行和10列的MYSQL表执行选择查询.但是建立连接后，代码仅执行几分钟，然后引发错误:线程"Thread-3"中的异常java.lang.OutOfMemoryError:Java堆空间

I am executing select query on a table of MYSQL which has 16,213,156 rows and 10 columns. But after connection is established code just executes for few mins and then throws error : Exception in thread "Thread-3" java.lang.OutOfMemoryError: Java heap space

我的系统配置是16 gb RAM，Java 8

My system configuration is 16 gb RAM, Java 8

我尝试将Jvm参数设置为-Xms4G& -Xmx12G.还尝试设置stmt.setFetchSize(); //到10,100,1000仍然相同的错误

I tried setting Jvm Parameters as -Xms4G & -Xmx12G . Also tried setting stmt.setFetchSize(); // to 10,100,1000 still same error

我可以使用JDBC API提取这么多的记录吗?任何帮助将不胜感激.

Can we fetch such large number of records using JDBC API am I missing anything ? Any help would be really appreciated.

package com;
import java.io.BufferedWriter;
import java.io.FileWriter;
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.Date;

import com.opencsv.CSVWriter;

public class Test1 {
    private static Connection conn = null;

    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {

        connection();
        retrieve(conn);

    }



    public static void connection()
    {

        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "<jdbc connection url>";
            conn = DriverManager.getConnection(url, "<username>", "<password>");
            System.out.println("Connection established");       
        }

        catch(Exception e)
        {
            e.printStackTrace();
        }

    }


    public static void retrieve(Connection conn)
    {
        ResultSet rs = null;
        Statement stmt = null;
        try
        {

            stmt = conn.createStatement();
            //  stmt.setFetchSize(100); // 1000, 10
            System.out.println(stmt.getFetchSize());  // By default prints 0
            rs = stmt.executeQuery("SELECT * FROM tablename");
            CSVWriter writer = new CSVWriter(new BufferedWriter(new FileWriter("C:\\finaldata\\test.csv")));
            System.out.println("**** Started writing Data to CSV ****");
            int lines = writer.writeAll(rs, true, false, false);        
            writer.flush();
            writer.close();
            System.out.println("** OpenCSV -Completed writing the resultSet at " +  new Date() + " Number of lines written to the file " + lines);  

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

推荐答案

@MickMnemonic感谢您的帮助，已解决了该问题.

@MickMnemonic Thanks for your help this solved the issue.

仅设置 fetchSize 可能不足以使MySQL驱动程序开始从数据库流式传输数据，而不是立即加载所有内容.您可以尝试

Setting fetchSize alone might not be enough for the MySQL driver to start streaming the data from the DB instead of loading everything at once. You could try with

stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE);

这篇关于Java如何在结果集中检索超过一百万行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！