Best way of using PreparedStatement executeBatch()

Question

I am trying to figure out the best way of using PreparedStatement executeBatch() method.

One way i tried is:

try{
    prepStmt1 = conn.prepareStatement("update table set done='yes' where phone=?");
    while (operatorsQuery.next() ) {
          logger.info("phone: "+ phone + ",  operator: " + operator);

          process(); //0.5-1 second long
          prepStmt1.setString(1, "0"+phone);
          prepStmt1.addBatch();
    }
prepStmt1.executeBatch();
}
catch{...}
finally{
    closeStatmentand(prepStmt1);
}

The problem i am having with this code is that the program can exit in the middle and then it might not reach the executeBatch() method.

The second way i tried:

try{
    prepStmt1 = conn.prepareStatement("update table set done='yes' where phone=?");
    while (operatorsQuery.next() ) {
          logger.info("phone: "+ phone + ",  operator: " + operator);

          process(); //0.5-1 second long
          prepStmt1.setString(1, "0"+phone);
          prepStmt1.addBatch();
          if ((j + 1) % 100 == 0) {
               prepStmt1.executeBatch();
          }
    }
prepStmt1.executeBatch();
}
catch{...}
finally{
    closeStatmentand(prepStmt1);
}

Which is the most preferred way to do this ?

Are you asking whether you should break the batch up into known sizes ( 100 in the second case ? ) — DaveH, Jun 20 '13 at 07:23
It seems odd that the program can be closed in the middle of the execution. If the end of program is user interaction based, tell the user that he is not done yet and inform him of the consequences. That way the user can decide if he wants to have the data lost or wants to wait. — Angelo Fuchs, Jun 20 '13 at 07:37

mel3kings · Answer 1 · 2013-06-20T08:40:42.413

by using batch update, queries are not sent to the database unless there is a specific call to executeBatch(); if you are worried that the user might exit the program and the execute is not reached, why not execute the updates one by one. and connections are set autoCommit(true); by default.

you cannot invoke a commit if the application is closed, and with batch updates queries are not yet sent to the database until an explicit call to execute is called.

executing an incremental batch would do.

=======EDIT=======

If your problem really is performance and you have issues with abrupt exit of your application try using Java Message Service or JMS. JMS enables you to send messages asynchronously, meaning your application would forward these "data" to the JMS and not wait for the response, you will then program JMS to insert them to the database. JMS also is persistent enough that when application/server goes down, the data sent (also known as the queue) will still be alive once it goes back up.

Although JMS is not for beginners and will be hard to implement from scratch. hope this helps: http://docs.oracle.com/javaee/6/tutorial/doc/bncdq.html

i have tried it but it simply takes to long, executing batch every 100 iterates seems like alot more efficient. — susparsy, Jun 20 '13 at 07:31
May i know why the application would exit in the middle of a process? it is unusual that you would want a commit when an application unexpectedly exits. — mel3kings, Jun 20 '13 at 08:19
yea , i know its unusual, the app makes thousends of process() as you see in the code. each process should be done only once! so i keep the id(phone_num in my case) of each process that has been done in the DB. — susparsy, Jun 20 '13 at 08:34

score 0 · Answer 2 · answered May 11 '18 at 18:07

https://examples.javacodegeeks.com/core-java/sql/jdbc-batch-update-example/

public void batchUpdateUsingStatement() throws SQLException {

// This is to hold the response of executeBatch()
int[] result = null;
try {
        Statement stmt = connection.createStatement();

        connection.setAutoCommit(false); // Setting auto-commit off
        String SQL = "update person set firstName='New First Name', lastName='New Last Name' where id=1";
        stmt.addBatch(SQL); // add statement to Batch
        SQL = "update person set firstName='First Name',lastName='Last Name' where id=2";
        stmt.addBatch(SQL); // add second statement to Batch
        result = stmt.executeBatch(); // execute the Batch
        connection.commit(); // commit
    } catch (SQLException e) {
        connection.rollback(); // rollBack in case of an exception
        e.printStackTrace();
    } finally {
        if (connection != null)
            connection.close(); // finally close the connection
    }
    System.out.println("Number of rows affected: " + result.length);
}

Best way of using PreparedStatement executeBatch()

2 Answers2

Linked