StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POwhy to close result set when handling large dataset
text
Body
copied!<p>preparedbatch method takes resultset of size 1000. I am inserting data using prepared statement and batch.</p> <p>Using LIMIT I am pulling 1000 rows from mysql database, java.. and calculating some value from column values of each row.. </p> <pre><code>public class Method { void preparedBatch (ResultSet rs, PreparedStatement insert) throws Exception{ int y; int arr[]= new int[40]; while (rs.next()){ for(y=2;y<=41;y++){ arr[y-2]=rs.getInt(y); } insert.setString(1, rs.getString(1)); insert.setLong(2, calculate(arr)); insert.addBatch(); } { insert.executeBatch(); insert.clearBatch(); // I did this to avoid oOM } rs.close(); //if I do not close, then I get OOM error } long calculate (int[] arr){ long sum =0; for(int k=0;k<40;k++){ sum+= arr[k]*pow(2,k); } return sum; } long pow(long base, int exponent) { if (exponent == 0) return 1; // base case; double temp = pow(base, exponent/2); if (exponent % 2 == 0) return (long) (temp * temp); else return (long) (base * temp * temp); } void method(){ Method k = Insert.m1; System.out.println("in metho"); Database d1= new Database(); // for reading from tables d1.setPassword("abc"); d1.setUrl("jdbc:mysql://localhost/DB"); d1.setUser("root"); // object to calculate 2 raise to 39 stuff ResultSet rs =null;// result set for 2500 lines selected PreparedStatement ps=null,insert=null; //ps for selecting 1000 lines //insert for inserting into binary table final Connection con; try { con = DriverManager.getConnection(d1.getUrl(), d1.getUser(), d1.getPassword()); con.setAutoCommit(false); insert = con.prepareStatement("insert into binarydata values(?,?)"); int z; for(z=0;z<=850000;z=z+1000) { ps = con.prepareStatement("select * from table1 LIMIT "+z +", 1000"); rs= ps.executeQuery(); try { k.preparedBatch(rs,insert);//k is global object of Methodclass } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } rs.close(); if(z%200000==0){ System.out.println(z +" inserted this time"); } if(z==850000){ System.out.println(z +"mouse"); } } { ps = con.prepareStatement("select * from table1 LIMIT 851000 , 364"); insert = con.prepareStatement("insert into binarydata values(?,?)"); rs= ps.executeQuery(); try { k.preparedBatch(rs,insert); System.out.println("finally inserting"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); System.out.println(rs.getString(1)); } rs.close(); } try { con.commit(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ con.close(); ps.close(); insert.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } </code></pre> <p>My main class creates object of Method and invokes method from it, my array is 0,1,0,1,1,1,1,1,,0,0,0,0,0,0,0,1,0 ( it is an example, all 851364 rows contain such data)</p> <p>if my arr for example is 0,1,0, then I am calculating sum+= arr[index]*2 (power index).. like this..</p> <p>I have no knowledge of Springs, otherwise I would have used that to handle data of my size.</p> <p>Please help me understand, why in method preparedBatch, <strong>I have to close resultset?</strong></p> <p>Also, please suggest optimized code of this. It took me some 9 minutes, to read 85136 lines and insert respective sum..</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload