JDBC: OutOfMemoryError when running the `insert` query on MySQL

Question

Please have a look at the below code

KeyWordTableCreator.java

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;

public class KeyWordTableCreator 
{
    public KeyWordTableCreator(String str)
    {
        File file = new File(str);

        try
        {
            readAndUpdate(file);
        }
        catch(IOException io)
        {
            io.printStackTrace();
        }
    }

    public void readAndUpdate(File file) throws IOException
    {
        DatabaseConnector db = new DatabaseHandler();
        db.makeConnection();

        BufferedReader br = new BufferedReader(new FileReader(file));
        String str = "";
        int index=0;

        label1:
        while((str=br.readLine())!=null)
        {
            label2:
            for(int i=0;i<str.length();i=i+3)
            {

                String word = str.substring(i, i+3);
                //  System.out.println(word);
                int insert = db.insert(index, word);

                if(insert<0)
                {
                    System.out.println("Operation Broke");
                    break label1;
                }

            }


            System.out.println("Index Updated: "+index);
            //System.out.println(String.valueOf("Total words in this run: "+tempCounter));
            index++;

            if((index%100)==0)
            {
                System.gc();
            }

        }

        db.closeConnection();
        System.out.println("Completed");
    }
}

DatabaseConnector.java

public interface DatabaseConnector {

    public void makeConnection();
    public void closeConnection();
    public int insert(int index, String hashWord);

}

DatabaseHandler.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;



    public class DatabaseHandler implements DatabaseConnector
    {
        private Connection con;

        @Override
        public void makeConnection() 
        {
           try
            {
                 Class.forName("com.mysql.jdbc.Driver");

                 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xxx","xxx","");

                 //System.out.println("Connection Established");
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }

        @Override
        public void closeConnection() 
        {
            try
            {
                con.close();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }

        @Override
        public int insert(int index, String hashWord)
        {
            String str="";
            int result=0;
            int returnResult=0;

            try
            {
                con.setAutoCommit(false);
                PreparedStatement ps = con.prepareStatement("insert into Key_Word (indexVal,hashed_word) values(?,?)");
                ps.setInt(1, index);
                ps.setString(2, hashWord);

                result = ps.executeUpdate();
                con.commit();

                if(result>0)
                {
                    returnResult = 1;
                }
                else
                {
                    returnResult = -1;
                    System.out.println( index+" failed to update");
                }


            }
            catch(SQLException s)
            {
                returnResult = -1;
                s.printStackTrace();
                try {
                    con.rollback();
                    System.out.println(index+" Exception Occured. Update Failed");

                } catch (SQLException ex) {
                    ex.printStackTrace();
                    str = index+" Exception Occured. Update Failed. Rollback Failed";
                }
            }

            return returnResult;
        }

    }

The database insert() method is running inside the KeyWordCreator. Basically, inside its loops, the database insert() method will be called for 150 million times. But, this is giving me OutOfMemory Exception after running for sometime.

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
        at com.mysql.jdbc.MysqlIO.unpackField(MysqlIO.java:766)
        at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:431)
        at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3245)

        at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2413)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2836)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2828)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2777)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1651)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3966)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3943)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2399)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2366)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2350)
        at xxx.DatabaseHandler.insert(DatabaseHandler.java:64)
        at xxx.KeyWordTableCreator.readAndUpdate(KeyWordTableCreator.ja
va:51)
        at xxx.KeyWordTableCreator.<init>(KeyWordTableCreator.java:25)
        at xxx.Main.main(Main.java:26)

C:\Users\Dev\Documents\xxx\Key-Word Table Creator\dist>

How can I solve this?

PS: I have ran this program by using the max memory I can (java -jar -Xmx1g) but this doesn't work.

I think such huge numbers of inserts should be done via batches with a help of transactions — nikis
– nikis, Commented Apr 25, 2014 at 19:08
Why are you creating the prepared statement in the insert() method? You are creating/destroying that statement a gazillion times, which completely defeats the purpose of a prepared statement. create it once outside the method and pass it in, then just update the parameter values. — OldProgrammer
– OldProgrammer, Commented Apr 25, 2014 at 19:15

Community · Accepted Answer · 2017-05-23 12:28:35Z

2

In your implementation of the insert() method, be sure to call

ps.close();
result.close();

before you leave the method. Otherwise, all those statement and result set objects build up in memory until your code breaks.

Alternately, you could reuse the PreparedStatement object with each insert(). This shouldn't be necessary to prevent the OutOfMemoryError, but it might gain you some execution efficiency. For advice on that, see Reusing a PreparedStatement multiple times.

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Apr 25, 2014 at 19:09

frances

1,25210 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PeakGen Over a year ago

I have set the setAutoCommit() to false. So even I use batches to do this work, I have to use commit() method right?

frances Over a year ago

Yes. You'll want to call conn.commit() just once before you close the connection. That will be far more efficient than autocommit after each insert. There's less of an efficiency penalty to autocommit if you implement batches, but you may still want to make sure that you can recover properly from an interrupted process. If some of your batches were committed and others not, recovery might be harder.

Collectives™ on Stack Overflow

JDBC: OutOfMemoryError when running the `insert` query on MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related