0

There's a business reason for this related to how queries can be routed between slave and master. From this question I know it's possible in Connector/J, but reading c3p0's documentation it never seems to distinguish between server side and client side statement caching, and given its discussion on memory use and such I assume it means client side. (IOW, I can't even confirm c3p0 uses server side prepared statements at all). How can I control this?

Per answer on related question

The Connector/J driver handles prepared statements locally unless you turn on real server side statements using the connection parameter useServerPrepStmts=true.

Community
  • 1
  • 1
djechlin
  • 59,258
  • 35
  • 162
  • 290
  • Most database have server-side prepared statements by definition: there usually is no way to disable this. MySQL is an exception that it defaults to 'simulating' prepared statements instead of using serverside prepared statements. – Mark Rotteveel Jun 10 '14 at 08:08

1 Answers1

1

c3p0 implements its own PreparedStatement caching, if you enable that by setting a nonzero maxStatements or maxStatementsPerConnection. It is what you would describe as "client side" — PreparedStatements are cached within the DataSource.

If your DBMS implements some sort of PreparedStatement caching at the server side, you'll need to configure that within the DBMS. (If your JDBC driver makes use of nonstandard config to enable this feature, point me to some docs and I'll probably be able to explain how to pass that through c3p0 to the driver.) c3p0 just works with Connection objects via standard JDBC. If calls to prepareStatement(...) quietly return cached objects, that's what c3p0 will return as well.

If your DBMS supports such a feature, it might make sense to keep c3p0 Statement caching disabled (the default, maxStatements == maxStatementsPerConnection == 0), and just use the JDBC driver or server's cache. But ultimately it's an empirical question, whether your dbms/JDBC driver's cache, or c3p0's, or both, or neither, performs best. You should be able to configure things any way you'd like.

Steve Waldman
  • 13,689
  • 1
  • 35
  • 45