Is it a good practice to execute PreparedStatement multiple times?

Question

I was wondering if it is a good practice to execute a PreparedStatement multiple times or we should reinitialize it each time.

For example:

while(i<100){
    PreparedStatement ptsmt = con.preparedStatement("INSERT into counts (counter) values (?)"); 
    ptsmt.setInt(1,i);
    ptsmt.executeUpdate();
}

Is worse or better than ?

PreparedStatement ptsmt = con.preparedStatement("INSERT into counts (counter) values (?)"); 
while(i<100){
    ptsmt.setInt(1,i);
    ptsmt.executeUpdate();
}

That is the whole point of preparedStatements. And 2nd option is the proper way to go. — myqyl4, Nov 11 '14 at 23:38

score 4 · Answer 1 · answered Nov 11 '14 at 23:57

The second code is recomended. The PreparedStatement is intended to be reused. Read what the documentation says:

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

Remember to always call clearParameters() before reusing it:

PreparedStatement pstmt = conn.preparedStatement(
        "INSERT INTO counts (counter) VALUES (?)"); 
while (i < 100) {
    pstmt.clearParameters();
    pstmt.setInt(1, ++i);
    pstmt.executeUpdate();
}

Calling `clearParameters` is not really necessary if you are going to set all parameter values again. — Mark Rotteveel, Nov 12 '14 at 08:32

score 1 · Answer 2 · answered Nov 11 '14 at 23:09

1

Prefer the second one. The whole point is to create it once and reuse it.

answered Nov 11 '14 at 23:09

duffymo

305,152
44
369
561

But is it wise to use it that way, or can we prefer updating in a batch? – BatScream Nov 11 '14 at 23:11
@BatScream That question doesn't make sense. It's not a question of one or the other. Updating with a batch also requires you to re-use the statement. – user207421 Nov 12 '14 at 00:28
@EJP, Thank you for responding. Actually i had misunderstood the question. – BatScream Nov 12 '14 at 00:31

Is it a good practice to execute PreparedStatement multiple times?

2 Answers2