CODEWITHSUNDEEP
javamysqljdbcprepared-statementc3p0
djechlin
djechlin

Reputation: 60808

Turn off server-side prepared statements in c3p0

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.

Upvotes: 0

Views: 1349

Answers (1)

Steve Waldman
Steve Waldman

Reputation: 14083

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.

Upvotes: 1

Related Questions