Is it resource-safely to use prepared statements multiple times?

Question

Hello. My application uses some prepared statement in every iteration of infinity loop. Is it safely to create preparedStatement before the loop and to run only pstmt.execute in the loop?

Also it is interesting about simple statement.

Answer 1

That's the point of prepared statements - to prepare them once and use them multiple times.

Answer 2

Yes, that's fine and moreover is one of the points of using PreparedStatements (the statement gets compiled once and then can be run multiple times).

So long as you're not calling the same statement from multiple (different) threads, this will work as you expect. Calling the same statement from multiple threads is likely to cause problems as these objects are not guaranteed to be threadsafe under these conditions.

Answer 3

"infinity loop"? Sorry, this sounds like a bad idea.

People are correct when they say that calling a prepared statement multiple times in a loop is a good idea. But implicit in that is a loop with a definite beginning and end.

I'm suspicious of your "infinite loop". It sounds more like a listener that you want to keep alive to respond to some database event.

The answer "it's fine" is correct, but it might be interesting to go into more detail about what you're doing.

UPDATE after comment:

I'd have an Atom RSS server that listened for events. When a request came in, I'd have the RSS listener hand it off to a service that would get a connection out of the connection pool, start a transaction, create a prepared statement in method scope, insert the message, close the prepared statement in a finally block within method scope, commit the transaction, and return the connection to the pool.

The JDBC driver or database will cache PreparedStatements, so it won't be inefficient. No need to hang onto one.

No infinite loop. No threading issues that way.