Using Prepared Statement multiple times efficiently

java, jdbc, prepared-statement, sql

Solution

You can think of using `addBatch()` and executing a back of statements in one shot. Also, as @pst commented in your question, consider using `trasaction`.

The way you would do is as follows:

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 execute a database update only when you reach a particular limit. This way you're limiting a database call to once every `batchLimit` that you've defined. This way it would be faster.

Also note for the `transaction`, I've just shown how and when to `commit`. This might not always be the correct point to `commit` because this decision would be based on your requirement. You might also want to perform a `rollback` in case of an exception. So it's upto you to decide.

Have a look at "Using Transaction" tutorial to get a better picture of how to use `transaction`.

Problem

Below is the code which I am using to insert `multiple records( around 5000-7000)` in the Oracle Database using Prepared Statement. The way I am doing currently is good? Or it can be improve more using some `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 That I am Using:- ``` 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()); } } } } ```

Original source

Related problems