title: Java how to retrieve more than 1 million rows in a Resultset
body: <p>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 : <strong>Exception in thread "Thread-3" java.lang.OutOfMemoryError: Java heap space</strong> </p>
  
  <p>My system configuration is 16 gb RAM, Java 8</p>
  
  <p>I tried setting Jvm Parameters as -Xms4G &amp; -Xmx12G . Also tried setting stmt.setFetchSize();  // to 10,100,1000 still same error </p>
  
  <p>Can we fetch such large number of records using JDBC API am I missing anything ? Any help would be really appreciated.</p>
  
  <pre><code>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 = "&lt;jdbc connection url&gt;";
              conn = DriverManager.getConnection(url, "&lt;username&gt;", "&lt;password&gt;");
              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();
          }
      }
  }
  </code></pre>