OutOfMemory Error when transferring data from text file to MySQL table in JAVA

Question

I have a text file containing some data. When I tried to put the data in MySQL I get this error "java.lang.OutOfMemoryError: Java heap space".

My code:

        try {
        // create a buffer reader
        java.io.BufferedReader br = new BufferedReader(new FileReader(f));
        // define line
        String line = null;
        // connect to the database
        connect = (Connection) DbConnection.establishConnection();
        while ((line = br.readLine()) != null) {
            // create preparedStatement
            preparedStatement = (PreparedStatement) connect.prepareStatement("insert into  IdentifiedExpertList values (?,?)");

            String[] rowData = line.split("\\s+", 2);
            String firstColumnData = rowData[0];
            String secondColumnData = null;
            if (rowData[1].trim().isEmpty()) {
                secondColumnData = null;
            } else {
                secondColumnData = rowData[1];
            }

            preparedStatement.setString(1, firstColumnData.trim());
            preparedStatement.setString(2, secondColumnData);
            preparedStatement.executeUpdate(); 

        }
        br.close();
        }

Why are you preparing a statement on every loop iteration? YOu should only prepare ONCE. that's the whole point of prepared statements. prepare once, use many times. — Marc B
– Marc B, Commented Sep 10, 2012 at 21:28
This is not your problem, but -- given that you're splitting line on \s+, you don't need to write firstColumnData.trim(): it's guaranteed that firstColumnData won't contain any whitespace. — ruakh
– ruakh, Commented Sep 10, 2012 at 21:29
Not only do you create PreparedStatements on every iteration, you're not closing them either, hence the OOME. You may want to create a transaction around the loop, too. — Frank Pavageau
– Frank Pavageau, Commented Sep 10, 2012 at 21:32
Yes guys the PreparedStatement was the problem. Putting it out of the loop solve it. Many thanks — user692704
– user692704, Commented Sep 10, 2012 at 21:35

kosa · Accepted Answer · 2012-09-10 21:46:58Z

4

java.lang.OutOfMemoryError: Java heap space

indicates that your allotted memory for JVM is not enough to keep up with the memory required to load the file into memory.

You may use -Xms and -Xmx flags to allocate memory for JVM. If the memory being used is very less, increase it by using those flags and see.

If still you are having memory issues, then another possible solution would be read chunks of the file and upload to DB instead of reading whole file at once.

If none of them works, that means there may be memory leak in your code. You need to fix it.

edited Sep 10, 2012 at 21:46

answered Sep 10, 2012 at 21:28

kosa

66.7k15 gold badges134 silver badges170 bronze badges

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

1 Comment

Ed Griebel Over a year ago

Although this is in general the cause of an OOME, this is not the reason in this case. Look at the comments to the original answer, not calling close() on the prepared statement is the problem.

Collectives™ on Stack Overflow

OutOfMemory Error when transferring data from text file to MySQL table in JAVA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related