How should I reuse prepared statements to perform multiple inserts in Java?

So, I have a collection of DTOs that I need to save off. They are backed with a temporary table, and they also need to have their data inserted into a "real" table.

I don't have time to do the proper batch process of these records, and the expected number of results, while it can be theoretically very high, is probably around 50 or less anyways. There are several other issues with this application (it's a real cluster**), so I just want to get something up and running for testing purposes.

I was thinking of doing the following psuedocode (in a transaction):

PreparedStatement insert1 = con.prepareStatement(...);
PreparedStatement insert2 = con.prepareStatement(...);
for(DTO dto : dtos) {
    prepareFirstInsertWithParameters(insert1, dto);
    insert1.executeUpdate();
    prepareSecondInsertWithParameters(insert2, dto);
   开发者_运维百科 insert2.executeUpdate();
}

FIrst off, will this work as is - can I reuse the prepared statement without executing clearParameters(), or do I have to do a close() on them, or keep getting more prepared statements?

Secondly, aside from batching, is there a more efficient (and cleaner) way of doing this?

This is easy:

conn = dataSource.getConnection();
conn.setAutoCommit( false );
pStatement = conn.prepareStatement( sqlStr );

ListIterator<DTO> dtoIterator = dtoList.listIterator();
while( dtoIterator.hasNext() ) {
DTO myDTO = dtoIterator.next();

pStatement.setInt( 1, myDTO.getFlibble() );
pStatement.setInt( 2, myDTO.getNuts() );

     pStatement.addBatch();

}
int[] recordCount = pStatement.executeBatch();
conn.commit();

MetroidFan2002,

I don't know what you mean by 'aside from batching', but I'm assuming you mean executing a single batch SQL statement. You can however, batch the prepared statement calls which will improve performance by submitting multiple calls at a time:

PreparedStatement insert1 = con.prepareStatement(...);
PreparedStatement insert2 = con.prepareStatement(...);
for(DTO dto : dtos) {
    prepareFirstInsertWithParameters(insert1, dto); 
    prepareSecondInsertWithParameters(insert2, dto);
    insert1.addBatch();
    insert2.addBatch(); 
}

insert1.executeBatch(); 
insert2.executeBatch(); 

// cleanup

Now if your dataset can grow large, like you alluded to, you'll want to put some logic in to flush the batch every N number of rows, where N is a value tuned to the optimal performance for your setup.

JDBC supports Batch Insert/Update. See example here.