What is a good strategy for caching prepared statements in Tomcat?

Question

Hello,

I am looking for a way to cache prepared statements in a servlet environment (specifically, Tomcat 5.5). This is meant to reduce the number of times that prepared statements are created, i.e. the number of times that connection.prepareStatement(sql) is called.

My initial idea was to store PreparedStatement objects in the session, where the key (the attribute name) is the query itself. This can also be done lazily.

However, someone alerted me to the fact that, depending on the JDBC driver implementation, the same prepared statement may be accessed by 2 threads (or requests) simultaneously, resulting, for example, in the wrong parameters being set. Therefore, the access to these statement objects needs to be synchronized.

What would be a good strategy to achieve this?

Is there a method built in to tomcat for doing this? I have see this answer where it mentions the poolPreparedStatements DBCP parameter, but it's not entirely clear from the documentation if it carries the same meaning as what I'm looking for.

Answer 1

I'm not sure about other DBs, but if you're using Oracle, the JDBC client will cache the PreparedStatement. You might want to see if your DB does that.

Answer 2

PreparedStatement caching is usually provided by the connection pool you are using.

• In c3p0, you can activate it by setting the maxStatements and maxStatementsPerConnection settings
• In DBCP, it is done by setting the poolPreparedStatements and maxOpenPreparedStatements parameters

Notice that in the way connection pool works, one thread acquires a connection, use it for some sql queries and return it to the pool. Only then the connection is available to another thread. Unless there is a bug in in the connection pool, connections are not shared among threads concurrently.

BTW - my recommendation is to use c3p0 and not DBCP. I had a lot of issues with DBCP that were solved once I moved to c3p0.