Using prepared statements with BoneCP

Question

I am using BoneCP with MySQL in Java.

I have several threads, and overtime they will be requesting a connection from the pool to perform some query. The queries are repetitive so I'd like to use prepared statements.

I've used prepared statements before, when I only worked with one connection (and no pooling). But now that I have pooling, I'm not sure how's it done:

As far as I am concerned, a prepared statement is linked to a particular Connection instance. If you return the connection to the pool, then the prepared statement is (or should be) destroyed. My threads only request a connection when they need to do a query - they return it after it is done so they don't retain any connection for a long time.

How do I use prepared statements along with connection pooling?

Answer 1

Nothing is done for you. The right way to do it is to clean up all you SQL resources as soon as you're done with them. You should use them in the narrowest method scope possible.

The JDBC driver may cache the PreparedStatements, but that's not a problem.

Answer 2

I personally recommend disabling pool-level PreparedStatement caching, and enabling it at the driver-level. You can read here how to do it.