In our multi-user Tomcat application there is a loop that obtains a connection from the pool, creates a preparedStatement (PS), uses it, and then closes() the PS and the connection. Being a loop the PS is invoked with different run time argument values each time.
From the c3p0 documentation:
maxStatements defines the total number PreparedStatements a DataSource will cache.
This sounds simple, but it's actually a strange approach, because cached statements
conceptually belong to individual Connections; they are not global resources.
To figure out a size for maxStatements that does not "churn" cached statements, you need to consider the number of frequently used PreparedStatements in your application, and multiply that by the number of Connections you expect in the pool (maxPoolSize in a busy application).
Since the PS is code that is executed after being bound with the run time arguments, does each connection need a separate copy of the PS? Or is there a separate preparedStatement cache for each connection? Shouldn't preparedStatements be in a "global" cache that any pooled connection can use when needed? In which case only a global maxStatement limit is needed? I guess I am a little confused by the interplay between maxStatements and maxStatementsPerConnection.