CODEWITHSUNDEEP
javamysqldatabase-connectionprepared-statementc3p0
Jack Russel
Jack Russel

Reputation: 33

Prepared Statement lifetime and c3p0

I have a question about using prepared statements and a connection pool such as c3p0. I have a script running that interacts with my mysql database around once a second, ad infinitum. Each interaction executes a prepared statement. According to the documentation, a connection should be open and closed before and after a database interaction. My understanding is that the Connection object doesn't actually get destroyed, but added back to the pool. Since prepared statements are connection dependent, how do I use Prepared Statements without having to rebuild them every time that I get a connection from the pool - or do I just rebuild the statement after a connection is received by the pool and rely on the pool to do this efficiently via caching?

Upvotes: 2

Views: 2150

Answers (1)

Steve Waldman
Steve Waldman

Reputation: 14083

If your pool implements JDBC transparent Statement caching (as c3p0 does), you just use the ordinary JDBC PreparedStatement API and reuse of cached statements is handled for you.

Internally what happens is that when you call conn.prepareStatement(...) on some Connection, a lookup is performed on an internal hashtable using a key that includes the Connection's identity, the SQL text, and other characteristics of the requested prepared statement. If a suitable PreparedStatement is found, that is what is passed to the client. In none is, then the prepareStatement call gets passed to the Connection, and the returned PreparedStatement is cached for later reuse.

Statement caching itself has some overhead, and can be tricky to configure. Some newer Connection pools, most notably HikariCP, simply omit support, arguing that caching of PreparedStatements is better left to the DBMS. That, of course, is an empirical question, and will vary from DBMS to DBMS. If you do use Statement caching, the crucial point is that you need to allow for

[num_frequently_used_prepared_statments] * [num_connections]

distinct Statements to be cached. This is tricky to reason about, given the JDBC standard global maxStatements config property defines a global limit, even though PreparedStatements are scoped per-connection.

Much better, if you use c3p0, to set only the (nonstandard) maxStatementsPerConnection property. That should be set to at least the number of PreparedStatements frequently used by your application. You don't have to worry about how many Connections will be open, since maxStatementsPerConnection is scoped per Connection like the Statements themselves are.

I hope this helps!

Upvotes: 3

Related Questions