Using Prepared Statement multiple times efficiently

Question

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());
            }
        }
    }
}

Answer 1

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.