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 ?

Answer 1

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

Answer 2

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