Using Prepared Statements and Connection Pooling together in MySQL

Question

Presently, for each query, a Prepared Statement is created and reused. I am not using any connection pool. C3P0 is a widely recommended library for the same.

But, as a PreparedStatement is tied to a connection. In the pooled environment, the connections are returned to the pool, effectively making PreparedStatement unusable. Am I correct about this conclusion? Is there any way to use Prepared Statement and connection pooling together?

Update: This is stand-alone application. So, I cannot use a framework just to get connection pooling right.

Answer 1

I believe if you close the connection the PreparedStatement will be "lost", but as long as the same connection remains open, the same PreparedStatement should be usable. You should look at Spring to do this for you using a JdbcTemplate. It will abstract away all of that so you don't have to worry about it. Just pass it a DataSource and you're good to go.

Here's how you can use c3p0 as a DataSource object: http://forum.springframework.org/showthread.php?t=13078

You can then make a JdbcTemplate bean and pass in the datasource as a constructor argument, and then inject the JdbcTemplate into whatever DAO objects you're using.

Answer 2

It depends on the pooling mechanism you use. Most JEE appservers have connection pool implementations where there is a prepared statement cache alongwith each connection in the pool. So the prepared statements are reused as well as the connections. I am not aware of any standalone pooling mechanisms though which have this functionality.

Answer 3

When I saw this material from "High Performance MySQL", it made me think that you had to do something to your MySQL configuration above and beyond anything you'd set in Java. Have you edited your my.cnf file?

Also, did you look at other SO questions on the topic?

EDIT: Spring is one framework that can help with pooling that's perfectly well-suited to stand-alone applications.