Java – Using Prepared Statements and Connection Pooling together in MySQL

connection-poolingjavaMySQLprepared-statement

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.

Best Answer

It depends on the pooling mechanism you use. Most Java EE 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.

Related Solutions

Java – Connection pooling options with JDBC: DBCP vs C3P0

DBCP is out of date and not production grade. Some time back we conducted an in-house analysis of the two, creating a test fixture which generated load and concurrency against the two to assess their suitability under real life conditions.

DBCP consistently generated exceptions into our test application and struggled to reach levels of performance which C3P0 was more than capable of handling without any exceptions.

C3P0 also robustly handled DB disconnects and transparent reconnects on resume whereas DBCP never recovered connections if the link was taken out from beneath it. Worse still DBCP was returning Connection objects to the application for which the underlying transport had broken.

Since then we have used C3P0 in 4 major heavy-load consumer web apps and have never looked back.

UPDATE: It turns out that after many years of sitting on a shelf, the Apache Commons folk have taken DBCP out of dormancy and it is now, once again, an actively developed project. Thus my original post may be out of date.

That being said, I haven't yet experienced this new upgraded library's performance, nor heard of it being de-facto in any recent app framework, yet.

Mysql – How to get a list of user accounts using the command line in MySQL

Use this query:

SELECT User FROM mysql.user;

Which will output a table like this:

+-------+
| User  |
+-------+
| root  |
+-------+
| user2 |
+-------+

As Matthew Scharley points out in the comments on this answer, you can group by the User column if you'd only like to see unique usernames.

Best Answer

Related Solutions

Java – Connection pooling options with JDBC: DBCP vs C3P0

Mysql – How to get a list of user accounts using the command line in MySQL

Related Topic