Using prepared statement several times is effective

Question

Below is the code that I use to insert multiple records( around 5000-7000) into an Oracle database using a prepared statement.

The way I'm doing now, okay? Or can it be improved with the batch thing ?

 pstatement = db_connection.prepareStatement(PDSLnPConstants.UPSERT_SQL); for (Entry<Integer, LinkedHashMap<Integer, String>> entry : MAPPING.entrySet()) { pstatement.setInt(1, entry.getKey()); pstatement.setString(2, entry.getValue().get(LnPConstants.CGUID_ID)); pstatement.setString(3, entry.getValue().get(LnPConstants.PGUID_ID)); pstatement.setString(4, entry.getValue().get(LnPConstants.SGUID_ID)); pstatement.setString(5, entry.getValue().get(LnPConstants.UID_ID)); pstatement.setString(6, entry.getValue().get(LnPConstants.ULOC_ID)); pstatement.setString(7, entry.getValue().get(LnPConstants.SLOC_ID)); pstatement.setString(8, entry.getValue().get(LnPConstants.PLOC_ID)); pstatement.setString(9, entry.getValue().get(LnPConstants.ALOC_ID)); pstatement.setString(10, entry.getValue().get(LnPConstants.SITE_ID)); pstatement.executeUpdate(); pstatement.clearParameters(); } 

Udpated Code I use: -

 public void runNextCommand() { Connection db_connection = null; PreparedStatement pstatement = null; int batchLimit = 1000; boolean autoCommit = false; try { db_connection = getDBConnection(); autoCommit = db_connection.getAutoCommit(); db_connection.setAutoCommit(false); //Turn off autoCommit pstatement = db_connection.prepareStatement(LnPConstants.UPSERT_SQL); // create a statement for (Entry<Integer, LinkedHashMap<Integer, String>> entry : GUID_ID_MAPPING.entrySet()) { pstatement.setInt(1, entry.getKey()); pstatement.setString(2, entry.getValue().get(LnPConstants.CGUID_ID)); pstatement.setString(3, entry.getValue().get(LnPConstants.PGUID_ID)); pstatement.setString(4, entry.getValue().get(LnPConstants.SGUID_ID)); pstatement.setString(5, entry.getValue().get(LnPConstants.UID_ID)); pstatement.setString(6, entry.getValue().get(LnPConstants.ULOC_ID)); pstatement.setString(7, entry.getValue().get(LnPConstants.SLOC_ID)); pstatement.setString(8, entry.getValue().get(LnPConstants.PLOC_ID)); pstatement.setString(9, entry.getValue().get(LnPConstants.ALOC_ID)); pstatement.setString(10, entry.getValue().get(LnPConstants.SITE_ID)); pstatement.addBatch(); batchLimit--; if(batchLimit == 0){ pstatement.executeBatch(); pstatement.clearBatch(); batchLimit = 1000; } pstatement.clearParameters(); } } catch (SQLException e) { getLogger().log(LogLevel.ERROR, e); } finally { try { pstatement.executeBatch(); db_connection.commit(); db_connection.setAutoCommit(autoCommit); } catch (SQLException e1) { getLogger().log(LogLevel.ERROR, e1.getMessage(), e1.fillInStackTrace()); } if (pstatement != null) { try { pstatement.close(); pstatement = null; } catch (SQLException e) { getLogger().log(LogLevel.ERROR, e.getMessage(), e.fillInStackTrace()); } } if (db_connection!= null) { try { db_connection.close(); db_connection = null; } catch (SQLException e) { getLogger().log(LogLevel.ERROR, e.getMessage(), e.fillInStackTrace()); } } } } 

Answer 1

You can come up with addBatch() and execute the reverse statement in a single snapshot. Also, as @pst commented on your question, consider using trasaction .

How would you do it:

 boolean autoCommit = connection.getAutoCommit(); try{ connection.setAutoCommit(false //Turn off autoCommit pstatement = db_connection.prepareStatement(PDSLnPConstants.UPSERT_SQL); int batchLimit = 1000; try{ for (Entry<Integer, LinkedHashMap<Integer, String>> entry : MAPPING.entrySet()) { pstatement.setInt(1, entry.getKey()); pstatement.setString(2, entry.getValue().get(LnPConstants.CGUID_ID)); pstatement.setString(3, entry.getValue().get(LnPConstants.PGUID_ID)); pstatement.setString(4, entry.getValue().get(LnPConstants.SGUID_ID)); pstatement.setString(5, entry.getValue().get(LnPConstants.UID_ID)); pstatement.setString(6, entry.getValue().get(LnPConstants.ULOC_ID)); pstatement.setString(7, entry.getValue().get(LnPConstants.SLOC_ID)); pstatement.setString(8, entry.getValue().get(LnPConstants.PLOC_ID)); pstatement.setString(9, entry.getValue().get(LnPConstants.ALOC_ID)); pstatement.setString(10, entry.getValue().get(LnPConstants.SITE_ID)); pstatement.addBatch(); batchLimit--; if(batchLimit == 0){ pstatement.executeBatch(); pstatement.clearBatch batchLimit = 1000; } pstatement.clearParameters(); } }finally{ //for the remaining ones pstatement.executeBatch(); //commit your updates connection.commit(); } }finally{ connection.setAutoCommit(autoCommit); } 

The idea is to set a limit for batch updates and only upgrade the database when it reaches a certain limit. This way you limit the access to the database every time after every batchLimit that you define. This way it will be faster.

Also pay attention to transaction , I just showed how and when commit . This may not always be the right point to commit , because this decision will be based on your requirements. You can also do rollback in case of an exception. Therefore, you decide.

See "Using a Transaction" for a better understanding of how to use a transaction .

Answer 2

Your piece of code seems useful to me.

Just for the entry.getValue() clarity, I would put entry.getValue() in a variable (name it value ).
And there is no need to call clearParameters() .

Finally, remember to position the prepared statement correctly when you no longer need it ( close() ).

Answer 3

Yes, performing batch updates will greatly improve your productivity. Just google for this, my preferred answer is this one from Mkyong.com . Otherwise, the code looks fine. "clearParameters ()" is not actually required; it may even consume some processor cycles. Important: if AutoCommit was turned on, be sure to turn it off earlier and turn it on after upgrading, this will again lead to a huge improvement.

PS

The above recommendations are also based on my experience. I just noted that this question has already been asked by https://stackoverflow.com/a/166269/ and the answer is very detailed. More information about PreparedStatements and packages can be found in the Oracle docs here and about transactions (AutoCommit) here .