I'm trying to create a batch program. The problem is that table could have two million of rows and this could produce a problem of memory. In my job I have DB2 and in my home to test I used mysql.
I've debugged with eclipse, and I viewed that resultset cointains all rows.
I've searched in web page but I haven't found the solution. For example, my test code is:
package misCodigos;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class testLimitDB {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/testing";
// Database credentials
static final String USER = "root";
static final String PASS = "8182";
private static FileWriter fw=null;
public static void main(String[] args) {
// Clase que pretende mostrar cómo insertar filas en java
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query
// System.out.println("Creating statement...");
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
String sql = "select * from tablaTest";
PreparedStatement pstmt;
pstmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
pstmt.setFetchSize(200);
ResultSet rs = pstmt.executeQuery(sql);
System.out.println("Starting to retrieve data. Memory Used: " + getUsedMemorySize());
//System.out.println(rs.getString(rs.last()));
/* rs.last();
int numRows = rs.getRow();
System.out.println("Numero de filas: " + numRows);*/
fw = new FileWriter("test.out");
while (rs.next()) {
fw.write(rs.getInt(1) + ";" + rs.getString(2) + "\n");
}
System.out.println("Done retrieving data => Memory Used: " + getUsedMemorySize());
pstmt.close();
conn.close();
fw.flush();
fw.close();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if (fw != null){
fw.flush();
fw.close();
}
if(stmt!=null) {
stmt.close();
}
}catch(SQLException | IOException se2){
}// nothing we can do
try{
if(conn!=null) {
conn.close();
}
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Finalizado!");
}
public static long getUsedMemorySize() {
long freeSize = 0L;
long totalSize = 0L;
long usedSize = -1L;
try {
Runtime info = Runtime.getRuntime();
freeSize = info.freeMemory();
totalSize = info.totalMemory();
usedSize = totalSize - freeSize;
} catch (Exception e) {
e.printStackTrace();
}
return usedSize;
}
}
Could anyone help me?
Thanks
PD: I created the code to db2. I'm not sure if the resultset fetch only the rows that I like because I lanched with debug mode in eclipse and the resulset variable, now it is very different and it appears encrypt.
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class testLimitDB {
// JDBC driver name and database URL
//static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String JDBC_DRIVER = "com.ibm.db2.jcc.DB2Driver";
//static final String DB_URL = "jdbc:mysql://localhost/testing";
static final String DB_URL = "jdbc:db2://localhost:50000/SAMPLE";
// Database credentials MYSQL
//static final String USER = "root";
static final String USER = "db2admin";
static final String PASS = "8182";
private static FileWriter fw=null;
public static void main(String[] args) {
// Clase que pretende mostrar cómo insertar filas en java
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query
// System.out.println("Creating statement...");
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
String sql = "select * from FELIPE.TABLATEST";
PreparedStatement pstmt;
pstmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
pstmt.setFetchSize(999);
ResultSet rs = pstmt.executeQuery();
System.out.println("Starting to retrieve data. Memory Used: " + getUsedMemorySize());
//System.out.println(rs.getString(rs.last()));
/* rs.last();
int numRows = rs.getRow();
System.out.println("Numero de filas: " + numRows);*/
fw = new FileWriter("test.out");
while (rs.next()) {
fw.write(rs.getInt(1) + ";" + rs.getString(2) + "\n");
}
System.out.println("Done retrieving data => Memory Used: " + getUsedMemorySize());
pstmt.close();
conn.close();
fw.flush();
fw.close();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if (fw != null){
fw.flush();
fw.close();
}
if(stmt!=null) {
stmt.close();
}
}catch(SQLException | IOException se2){
}// nothing we can do
try{
if(conn!=null) {
conn.close();
}
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Finalizado!");
}
public static long getUsedMemorySize() {
long freeSize = 0L;
long totalSize = 0L;
long usedSize = -1L;
try {
Runtime info = Runtime.getRuntime();
freeSize = info.freeMemory();
totalSize = info.totalMemory();
usedSize = totalSize - freeSize;
} catch (Exception e) {
e.printStackTrace();
}
return usedSize;
}
}
I tried again and my conclusions are the drivers (mysql and db2) work correctly. I tested with jvisualvm and I viewed clear that the memory changed with the size of cursor. I'll write a document with differents conclusions and I'll post it.
