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?

score 16 · Answer 1 · answered Feb 25 '14 at 05:11

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().

score 10 · Accepted Answer · answered Feb 08 '12 at 11:46

10

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.

answered Feb 08 '12 at 11:46

dhblah

9,751
12
56
92

2

*"But there is no guarantee for that will be exactly the same in other implementations."* Right, so this isn't really useful. – T.J. Crowder Feb 08 '12 at 11:50

T.J. Crowder · Answer 3 · 2018-03-25T15:37:52.543

9

I'd say the more relevant question was whether the batch is cleared. Although it seems like a reasonable assumption, barring the documentation explicitly saying that it's cleared (and I don't see anything on executeBatch saying that), I'd use clearBatch explicitly on the theory that if an implementation does clear the batch, clearBatch will be a quick no-op; and if it doesn't, well, then I needed to do it.

Update: While the executeBatch documentation doesn't say so, see Bogdan Calmac's answer: Apparently the JDBC documentation does say successful completion clears the batch.

edited Mar 25 '18 at 15:37

answered Feb 08 '12 at 11:44

T.J. Crowder

1,031,962
187
1,923
1,875

What do you think about the spec fragment I posted below? Do you think that's binding enough (or clear enough) for a driver developer? – Bogdan Calmac Feb 25 '14 at 05:19
@BogdanCalmac I'd say that one should check its choosen JDBC driver behavior. – Stephan Mar 25 '18 at 11:41

score 2 · Answer 4 · edited Jan 06 '14 at 05:04

2

I would rather say, that it is better to clear it explicitly. Some JDBC drivers don't clear internal buffers after executeBatch().
An explicit call to clearBatch() is necessary to avoid errors like IndexOutOfBoundException as some logical errors may also be introduced.

Thanks!

edited Jan 06 '14 at 05:04

Knowledge Craving

7,955
13
49
92

answered Jan 06 '14 at 04:21

Subodh Karwa

2,495
1
15
13

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

4 Answers4