Are addBatch() and executeBatch() thread-safe?

Question

Can i call executeBacth from a thread while another one keeps calling addBatch() on the same Statement ( or PreparedStatement ) object?

Update: Does anyone have exprerience with this issue? 'cause i get incorrect results. Not all updates i added to the the batch is executed.

Answer 1

I would take a step back and deeply reconsider the design. Why on earth would you like to share the same Statement (and thus implicitly also the Connection) between two threads?

The normal JDBC practice is that you should acquire and close the Connection, Statement and ResultSet in the shortest possible scope. That is, inside the very same method block. Here's a basic example:

public void update(List<Item> items) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = database.getConnection();
        statement = connection.prepareStatement(sql);
        for (Item item : items) {
            statement.setObject(1, item.getSomething());
            statement.addBatch();
        }
        statement.executeBatch();
    } finally {
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }
}

If all you want is just improving connecting performance, then use a connection pool. For example C3P0. But certainly do not share expensive DB resources among threads! This way you also don't need to worry about threadsafety. That's an implementation detail.

Oh, if that's not clear yet: you will not improve database performance by sharing the same statement and connection among multiple threads. Even worse, it would only slowdown and you'll run into threadsafety issues at both the Java and the Database side.