JDBC Connection MySQL, Big Data set report: Java.lang.OutOfMemoryError:Java heap Space

Problem Description:

In the project you need to connect MySQL, query thousands of records, each field is quite large. As a result, the following error is reported in eclipse:

Java.lang.OutOfMemoryError:Java Heap Space

Cause Analysis:

MySQL sends all the records it queries to the Java side, and the JVM throws this exception if 98% of the time is used for GC and the available heap size is less than 2%. The JVM heap setting is the set of memory space that the JVM can provision during the run of the Java program. The JVM automatically sets the value of the heap size when it starts, and its initial space (that is,-XMS) is 1/64 of the physical memory, and the maximum space (-XMX) is 1/4 of the physical memory. You can use options such as the-XMN-XMS-XMX provided by the JVM to set it up.

The solution is logged as follows:
Programme one:
Eclipse has a boot parameter that sets the JVM size, because the Eclipse runtime requires the JVM itself, so the JVM size set in Eclipse.ini is not the size of the JVM used to run a particular program, regardless of the size of the JVM the program is running on.
So how can you set the JVM size of a program?
(Of course, the console runs without this problem, such as: java-xms256m-xmx1024m classname, so you can set the JVM size of the current program)
Because the JVM configuration of one of the default programs in Eclipse is:-xms8m-xmx128m, we need to manually adjust the amount of memory to be processed so that there is no memory overflow.
The specific setup methods are:
Select the class being run, click on the menu ' Run->run configurations ', select (x) =argument tab under the VM Arguments box to enter
-xms128m-xmx512m (depending on the size of your physical memory), save the operation is OK

Scenario Two:
The root cause of this problem is that the default heap size of the JVM virtual machine is 64M, which can be achieved by setting its maximum and minimum values. The method of setting is mainly several.
1. You can change the system environment variable plus java_opts=-xms64m-xmx512m in Windows
2. If you are using Tomcat, under Windows, you can add the following in C:\tomcat5.5.9\bin\catalina.bat:
Set java_opts=-xms64m-xmx256m
Position in: REM Guess catalina_home if not defined the line below plus fit.
3. If it is a Linux system
Linux in front of {tomcat_home}/bin/catalina.sh, plus
Set java_opts= '-xms64-xmx512 '

Scenario three (strong versatility):

Using PreparedStatement:

1, when the PreparedStatement set the following properties, the use of the stream data receive mode, each time only from the server to receive some data, until all the data processing is complete, the JVM OOM will not occur.
Setresultsettype (resultset.type_forward_only);
Setfetchsize (Integer.min_value);
2, Call statement Enablestreamingresults method, in fact Enablestreamingresults method internal encapsulation is the 1th way.
3, set the connection properties Usecursorfetch=true (version 5.0 driver start support), statement to Type_forward_only Open, and then set the fetch size parameter, the server-side cursor, each time from the server to take Fetch_ Size bar data.

Package Com.seven.dbtools.dbtools;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import java.sql.Statement; Import Java.util.arraylist;public class Jdbchandlemysqlbigresultset {public static long ImportData (String sql) {string url = "Jdbc:mysql://ipaddress:3306/test?user=username&password=password"; try {class.forname (" Com.mysql.jdbc.Driver ");} catch (ClassNotFoundException E1) {e1.printstacktrace ();} Long Allstart = System.currenttimemillis (); Long Count = 0; Connection con = null; PreparedStatement PS = null; Statement st = null; ResultSet rs = null;try {con = drivermanager.getconnection (URL);p s = (preparedstatement) con.preparestatement (SQL,  Resultset.type_forward_only, resultset.concur_read_only); Ps.setfetchsize (Integer.min_value);p s.setfetchdirection (resultset.fetch_reverse), rs = Ps.executequery (); while ( Rs.next ()) {///Here Processes Business logic count++;if (count%600000==0) {System.out.println ("writeInto the "+ (count/600000) +" Files! "); Long end = System.currenttimemillis ();}} System.out.println ("+count+" row for retrieving data volume!) ");} catch (SQLException e) {e.printstacktrace ();} finally {try {if (rs!=null) {Rs.close ()}} catch (SQLException e) { E.printstacktrace ();} try {if (ps!=null) {ps.close ();}} catch (SQLException e) {e.printstacktrace ();} try {if (con!=null) {con.close ();}} catch (SQLException e) {e.printstacktrace ()}} return count;} public static void Main (string[] args) throws interruptedexception {String sql = ' select * from test.bigtable '; importdata (SQL);}}

JDBC Connection MySQL, Big Data set report: Java.lang.OutOfMemoryError:Java heap Space