Rollback batch execution when using jdbc with autocommit=true

Question

Im using JDBC, with autocommit=true. In one of the operation, I'm doing a batch inserts, using prepared statements.

public void executeBatchInsert(String query, List<Object[]> entityList)  {
        try {
            pstmt = conn.prepareStatement(query);
            for(int i=0; i<entityList.size(); i++) {
                int j=1;
                for(Object o: entityList.get(i)) {
                    pstmt.setObject(j++, formatColumnValue(o));
                }

                pstmt.addBatch();
                if((i+1)%1000 == 0) {
                    pstmt.executeBatch();
                }
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
        }
    }

If I get an exception while executing it, when I close this connection, will all the locks be released and the rollback happens?

-- B. Teja.

Answer 1

The direct answer to your question is: no. If an exception occurs you have to manually call the rollback method. And before doing so you must setAutoCommit to false. By default auto commit is set to true. With auto commit set to true you can't do a rollback, an exception will occur telling you that.

Later on do not forget to set autoCommit back to true or you may have non expectable results with other methods you may have.

Here's an example on how to implement this feature. This is just sketch and you should probably pay more attention on how to handle the connection, prepared statment, exception and so on.

public void insertAndRollback(Connection connection) {
    try {
        final ArrayList parameters = new ArrayList();

        // Add your parameters to the arraylist
        parameters.add("John");
        parameters.add("Lee");
        parameters.add("Mary");
        parameters.add("Peter");
        parameters.add("Lewis");
        parameters.add("Patrick");

        final String parameterizedQuery = "insert into person (name) values (?)";

        final int batchSize = 5; // Set your batch size here
        int count = 0;
        int aux = 0;

        // Get the total number of '?' in the query
        int totalQueryParameters = Utils.countCharOccurrences(parameterizedQuery, '?');
        final int auxTotalQueryParameters = totalQueryParameters;

        final PreparedStatement preparedStatement = connection.prepareStatement(parameterizedQuery);

        // Auto Commit must be set to false
        connection.setAutoCommit(false);

        for(int i = 0; i < parameters.size(); i++)
        {
            Object obj = parameters.get(i);

            aux++;
            preparedStatement.setObject(aux, obj);

            if(totalQueryParameters == i + 1) { // Because the ArrayList starts from zero.
                // First query "parsed" - > Add to batch
                preparedStatement.addBatch();
                // One query has been added to the batch. Re-adapt the cycle.
                totalQueryParameters = totalQueryParameters + auxTotalQueryParameters;
                aux = 0;
            }

            if(++count % batchSize == 0) {
                preparedStatement.executeBatch();
            }
        }

        preparedStatement.executeBatch(); // insert remaining queries
        preparedStatement.close();
        connection.setAutoCommit(true); // Make it back to default.
    } catch (SQLException ex) {
        // Do the rollback
        doRollback(connection);

        try {
            // Make it back to default.
            connection.setAutoCommit(true);
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }

        // Dont forget to close the preparedStatement and the connection
        // if you don't need the connection open any more.

        ex.printStackTrace();
    }
}


private void doRollback(Connection c) {
    try {
        c.rollback();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

Answer 2

As a matter of fact PreparedStatement.executeBatch does not clarify the issue, maybe somewhere else, but I am sure it is not an atomic operation because SQL has no batch operation so executeBatch executes each statement separately at DB level. I tested it on MySQL:

t1 is an empty table that has n1 INT(11) Not Null column, autocommit = true

    ResultSet rs1 = conn.createStatement().executeQuery("select count(*) from t1");
    rs1.next();
    System.out.println(rs1.getInt(1));

    String query = "insert into t1 (n1) values(?)";
    PreparedStatement ps = conn.prepareStatement(query);
    ps.setObject(1, 1);
    ps.addBatch();
    ps.setObject(1, null);
    ps.addBatch();
    try {
        ps.executeBatch();
    } catch (Exception e) {
        System.out.println(e);
    }

    ResultSet rs2 = conn.createStatement().executeQuery("select count(*) from t1");
    rs2.next();
    System.out.println(rs2.getInt(1));

it prints

0
java.sql.BatchUpdateException: Column 'n1' cannot be null
1

that is, there were 2 inserts in the batch; first succeded, the second failed, still t1 got 1 row

Answer 3

tricky one , autocommit=true Strongly not recommended, when executing batch.

Having said that, i recommend use getUpdateCount() and build logic around to execute remaining.

finally commit