Are the parameter values automatically cleared after executing a batch of SQL commands with a Java PreparedStatemen?

Question

I have the following code snippet:

PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 100000; i++) {
    preparedStatement.setObject(1, someValue);
    preparedStatement.addBatch();
    if ((i + 1) % 100 == 0) {
        preparedStatement.executeBatch();
    }
}

so I want to execute one command around 100 000 times with different values. My question is: are the parameters from the PreparedStatement cleared after each call to executeBatch() or do I have to explicitly call preparedStatement.clearParameters() after calling executeBatch() in order to make sure that there will be executed only the last 100 commands?

Answer 1

YES. According to section 15.1.2 of JDBC 3.0 and section 14.1.2 of JDBC 4.1:

Calling the method executeBatch closes the calling Statement object’s current result set if one is open. The statement’s batch is reset to empty once executeBatch returns.

It's unfortunate that such an important detail ended up almost as a fine print.

In addition to the example already mentioned, the jtds driver also does a clearBatch() in the finally block of executeBatch().

Answer 2

As I can see here: http://javasourcecode.org/html/open-source/jdk/jdk-6u23/sun/jdbc/odbc/JdbcOdbcStatement.java.html

executeBatch() calls clearBatch() in the end.

But there is no guarantee for that will be exactly the same in other implementations.