StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can think of using <a href="http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html#addBatch%28%29" rel="nofollow"><code>addBatch()</code></a> and executing a back of statements in one shot. Also, as @pst commented in your question, consider using <code>trasaction</code>.</p> <p>The way you would do is as follows:</p> <pre><code>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); } </code></pre> <p>The idea is to set a limit for batch updates and execute a database update only when you reach a particular limit. This way you're limiting a database call to once every <code>batchLimit</code> that you've defined. This way it would be faster.</p> <p>Also note for the <code>transaction</code>, I've just shown how and when to <code>commit</code>. This might not always be the correct point to <code>commit</code> because this decision would be based on your requirement. You might also want to perform a <code>rollback</code> in case of an exception. So it's upto you to decide. </p> <p>Have a look at <a href="http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html" rel="nofollow">"Using Transaction"</a> tutorial to get a better picture of how to use <code>transaction</code>.</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