Correct usage of PreparedStatement

Question

Let's say we have a class that writes in a database a log message. This class is called from different parts of the code and executes again and again the same INSERT statement. It seems that is calling to use a PreparedStatement.

However I am wondering what is the right usage of it. Do I still get the benefit of using it, like the DBMS using the same execution path each time it is executed, even if I create a new PreparedStatement each time the method is called or should I have a PreparedStatement as a class member and never close it in order to re use it and get benefit from it?

Now, if the only way to obtain benefit using the PreparedStatement in this scenario is to keeping it opened as class member, may the same connection have different PreparedStatement's (with different queries) opened at the same time? What happens when two of these PreparedStatements are executed at the same time? Does the JDBC driver queue the execution of the PreparedStatements?

Thanks in advance, Dani.

Answer 1

For all I know and experienced, statements don't run in parallel on one connection. And as you observed correctly, PreparedStatements are bound to the Connection they were created on.

As you probably don't want to synchronize your logging call (one insert at a time plus locking overhead), you'd have to keep the connections reserved for this logging statement.

But having a dedicated pool for only one statement seems very wasteful - don't want to do that as well.

So what options are left?

• prepare the statement for every insert. As you'll have I/O operations to send data to the db, the overhead of preparing is relatively small.

• prepare the statement inside your pool on creating a new connection and build a Map <Connection,PreparedStatement> to reference them later. Makes creating new connections a bit slower but allowes to recycle the statement.

• Use some async way to queue your logs (JMS) and do the Insert as batch inside a message driven bean or similar

Probably some more options - but that's all I could think of right now.

Good luck with that.