CODEWITHSUNDEEP
javamysql
Vinodh Ravi
Vinodh Ravi

Reputation: 67

Out Of Memory Error: Java Heap Space - PreparedStatement MySQL

void insert(ArrayList myList){
       conn = openDBConnection(); //Database Connection
       Iterator iterator = myList.iterator();
       while(iterator.hasNext()){
           insertIntoDB((myClass)iterator.next(),conn);
       }
       closeDBConnection();
   }

   void insertIntoDB(myClass myObject, Connection conn){
     String query = "insert into myTable values(?,?)";
     PreparedStatement myStatement = conn.prepareStatement(query);
     myStatement.setInt(1,myObject.getMyKey());
     myStatement.setInt(2,myObject.getMyValue());
     myStatement.execute();
    }

In the above code myList is an arrayList of objects with more than 1.2M entries. I am getting the following error after inserting around 1000 records:

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:437)
    at com.mysql.jdbc.Connection.clientPrepareStatement(Connection.java:2187)
    at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4829)
    at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4734)
    at com.att.research.space.SpaceDaoImpl.insertMapping(SpaceDaoImpl.java:99)
    at com.att.research.space.ElementMappingLoader.insertMappingData(ElementMappingLoader.java:68)
    at com.att.research.space.CorrelationEngine.loadMappingFiles(CorrelationEngine.java:69)
    at com.att.research.space.CorrelationEngine.main(CorrelationEngine.java:25)

I tried using System.gc() inside the iterator loop. But I don't think it is a good way to code and also it is consuming lot of CPU cycle.

The above code is a sample code format of my original code.

Upvotes: 2

Views: 7021

Answers (4)

Reece
Reece

Reputation: 691

Avoiding memory leaks with finally block should definitely be addressed first; however if its just a very large resultset; I found this useful:

http://benjchristensen.com/2008/05/27/mysql-jdbc-memory-usage-on-large-resultset/

namely adding 

stmt.setFetchSize(Integer.MIN_VALUE);

Before runing stmt.executeQuery();

Upvotes: 0

Andreas
Andreas

Reputation: 159096

You run out of memory because you did not close the statement. This is called a resource leak.

Using try-with-resources (Java 7+):

void insertIntoDB(myClass myObject, Connection conn){
    String query = "insert into myTable values(?,?)";
    try (PreparedStatement myStatement = conn.prepareStatement(query)) {
        myStatement.setInt(1,myObject.getMyKey());
        myStatement.setInt(2,myObject.getMyValue());
        myStatement.execute();
    }
}

Pre-Java 7:

void insertIntoDB(myClass myObject, Connection conn){
    String query = "insert into myTable values(?,?)";
    PreparedStatement myStatement = conn.prepareStatement(query);
    try {
        myStatement.setInt(1,myObject.getMyKey());
        myStatement.setInt(2,myObject.getMyValue());
        myStatement.execute();
    } finally {
        myStatement.close();
    }
}

As others have suggested, you should re-use your statement, but it's the missing close() that's the main issue.

Upvotes: 3

duffymo
duffymo

Reputation: 308763

The whole point of PreparedStatement is to create it once and bind variables.

Here's how I'd suggest that you write it:

private static final String INSERT_QUERY = "insert into myTable values(?,?)";

public int insert(Connection c, List<MyClass> myList) {
    int numRows = 0;
    PreparedStatement ps = null;    
    try {
        ps = c.prepareStatement(INSERT_QUERY);
        for (MyClass x : myList) {
            ps.setInt(1, x.getMyKey());
            ps.setInt(2, x.getMyValue());
            numRows += ps.executeUpdate();          
        }
    } finally {
       close(ps);
    }
    return numRows;
}

I left some details for you to figure out (e.g. that close method).

A word of advice: Lose that affection for naming everything "MyFoo". Your code is unreadable with such a naming convention. Think more carefully about how you name things.

Upvotes: 2

TTM
TTM

Reputation: 21

move the statement out of the loop and user addBatch() and executeBatch instead

Upvotes: 1

Related Questions