Best way to perform massive # of inserts from java...
Posted on 2006-10-23
Hi experts. I am working on a batch process that is quite lengthy. It tries to find an association between one thing and another based on about 51 conditions. Each condition, if matched, adds a point so to speak. For each match that has received a certain number of points or more, I add that object (it's a business entity object) to an array list. The last thing the batch does before completing is call the Data Access method of this object and pass the arraylist of matches to the method. The following code is how I attempt to perform the insert. Keep in mind that we are using a prepared statement here.
" INSERT INTO Mytable " +
" ( F1, F2, F3, F4, F5) " ;
System.out.println("in insert method - before for loop. SCMLS size: " + scmls.size() );
for(int i=0; i < scmls.size() ; i++ )
//iterationIndex = i;
//System.out.println("in insert method - before for get in for loop. Iteration: " + i );
//scml = (myObj)scmls.get(i);
" SELECT " +
? ," +
" ? ," +
" ? ," +
" SYSDATE, " +
" ? " +
" FROM DUAL " +
" UNION " ;
if( INSERT_NEW_MATCHES.indexOf("UNION") > -1 )
INSERT_NEW_MATCHES = INSERT_NEW_MATCHES.substring(0, INSERT_NEW_MATCHES.lastIndexOf("UNION"));
System.out.println("FINAL INSERT QUERY: " + INSERT_NEW_MATCHES );
pstmt = con.prepareStatement(INSERT_NEW_MATCHES );
for(int a=0;a<scmls.size() ;a++)
iterationIndex = a;
scml = (StudentCampusMatchList)scmls.get(a);
pstmt.setInt(++count, scml.F1() );
pstmt.setString(++count, scml.F4() );
System.out.println("in insert method - before executeUpdate ");
int rc = pstmt.executeUpdate();
As you can imagine this is pretty heavy on the memory. I did not anticipate that this process would have to insert 1 million plus records, but it does. I realize that I will likely run into a memory issue with the string. My question is, how can I implement this in a better way? What ever the suggestion is, it must be able to utilize an arraylist of business objects.
Thanks ahead of time guys!