问题描述
我正在寻找一种在Servlet环境(特别是Tomcat 5.5)中缓存准备好的语句的方法.这是为了减少创建准备好的语句的次数，即减少connection.prepareStatement(sql)的调用次数.
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.
我最初的想法是将PreparedStatement对象存储在会话中，其中键(属性名称)是查询本身.这也可以懒惰地完成.
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.
但是，有人提醒我一个事实，即取决于JDBC驱动程序的实现，同一准备好的语句可能同时被2个线程(或请求)访问，从而导致例如设置了错误的参数.因此，对这些语句对象的访问需要同步.
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?
tomcat是否内置了用于执行此操作的方法?我看到了此答案，其中提到了poolPreparedStatements DBCP参数，但是从文档中还不清楚它是否具有与我要查找的含义相同的含义.
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.
推荐答案
PreparedStatement缓存通常由您使用的连接池提供.
PreparedStatement caching is usually provided by the connection pool you are using.
- 在 c3p0 中，您可以通过设置 maxStatements和maxStatementsPerConnection设置
- 在 DBCP 中，通过设置 poolPreparedStatements和maxOpenPreparedStatements参数
- 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
请注意，在连接池的工作方式中，一个线程获取了一个连接，将其用于某些sql查询，然后将其返回到池中.只有这样，连接才能用于另一个线程.除非连接池中存在错误，否则不会在线程之间同时共享共享.
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.
顺便说一句-我的建议是使用c3p0而不是DBCP.转移到c3p0后，我在DBCP上遇到了很多问题.
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.
这篇关于在Tomcat中缓存预备语句的好策略是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！