Exception java.lang.OutOfMemoryError: Java heap space

Question

IDE Used:Netbeans 8.1.

Reading large data from MySQL database. Below is my code:

List outer=new ArrayList<String>();
List inner=new ArrayList<String>();
Connection con;
Statement stmt;
ResultSet rs;
ResultSetMetaData rsmd;
int columnNumber;
try{
    Class.forName("com.mysql.jdbc.Driver");
    con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","root");
    stmt=con.createStatement();
    rs=stmt.executeQuery("select * from mytable where srno<1000");
    rsmd=rs.getMetaData();
    columnNumber=rsmd.getColumnCount();
    while(rs.next()){
        for(int i=1;i<columnNumber;i++){
            inner.add(rs.getString(i));
        }
        outer.add(inner);
    }           
    System.out.println("\t" + outer);
    rs.close();
    con.close();
}catch(Exception e){
    System.out.println(e);
}

I am getting error while running the code: Exception in thread "main" java.lang.OutOfMemoryError: Java heap space

I've tried to set vm options in properties>run>VM Options: -Xmx1024m

I am still getting the same error.How do I resolve it?

Query a smaller table or use a WHERE clause to limit how much data comes back. Run 64 bit JVM with lots more RAM. That's all you can do. — duffymo
– duffymo, Commented Aug 10, 2016 at 9:20
Give more details about select result: how many rows? how many columns? average size of each column content? — Aris2World
– Aris2World, Commented Aug 10, 2016 at 9:24
Suppose each column has an average size of x bytes you finish with x*43MB. If the average size of each column is 10 bytes you have 0,5GB in the heap. No way. — Aris2World
– Aris2World, Commented Aug 10, 2016 at 10:13

e4c5 · Accepted Answer · 2016-08-10 09:29:24Z

4

Just look at this code:

   while(rs.next()){
     for(int i=1;i<columnNumber;i++){
         inner.add(rs.getString(i));
     }
     outer.add(inner);
   }

For each row in the table, you are adding each column value into a list and each of those lists you are adding to another list. So if your table has 1 million rows and each row has 5 columns. You are creating 5 million rows. is it a surprise that memory is being exhausted? According to your update it's actually 10 Million rows and 45 columns. Do you realize that you are making 450 million objects!

Also remember that each item on the outer list will take more space than the amount of space those columns took on the database because of the object headers and other stuff.

You have explained what this is for, if it's for a swing app for example, you can choose a suitabl TableModel and avoid loading the whole db into memory at once.

edited Aug 10, 2016 at 9:29

answered Aug 10, 2016 at 9:22

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

3 Comments

iks_in Over a year ago

Oh my mistake. I wanted to store whole row in a list and there are multiple rows, that's why did it this way.

e4c5 Over a year ago

A completely different strategy is needed.

Aris2World Over a year ago

I agree with @e4c5. You need to use data while you're reading from db (lazy list or paginated list). It's never a good strategy to load in memory data from a potential unlimited data source.

Collectives™ on Stack Overflow

Exception java.lang.OutOfMemoryError: Java heap space

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related