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

Question

I am looking for a way to cache prepared statements in a servlet environment (specifically Tomcat 5.5). This is intended to reduce the number of created operators, i.e. The number of calls connection.prepareStatement(sql).

My initial idea was to store objects PreparedStatementin a session where the key (attribute name) is the request itself. This can also be done lazily.

However, someone warned me that, depending on the implementation of the JDBC driver, two threads (or queries) can be available to the same prepared statement at the same time, which leads, for example, to incorrect parameters. Therefore, access to these operator objects must be synchronized.

What would be a good strategy to achieve this?

Is there a method created for tomcat for this? I see this answer where it mentions the poolPreparedStatementsDBCP parameter , but it is not entirely clear from the documentation if it has the same meaning as what I'm looking for.

Answer 1

PreparedStatement caching is usually provided by the connection pool that you use.

• In c3p0 you can activate it by setting maxStatements and maxStatementsPerConnection
• DBCP poolPreparedStatements maxOpenPreparedStatements

, , , , sql . . , .

- - c3p0, DBCP. DBCP, c3p0.

Answer 2

, Oracle, JDBC PreparedStatement. , , .