MySQL best usage in Tomcat?

Question

Which one is better way of using MySQL in Tomcat :
A) assign a DB connection for user as long as it's session is valid. [OR]
B) open connection to DB, on every request come to server and when it's done close that.
C) Connection pool. [BEST answer]

Answer 1

B), or C) use a connection pool

Answer 2

Impetus

In any sort of request-reply system—be it http, ftp, or a database call—it makes sense to keep a pool of connections open for use in by a client. The cost of building and tearing down a connection during every single request is high (both for the client and for the server), so having a pool from which multiple threads may "check out" a connection for their use is a good pattern.

Implementation

The JDBC API provides a wrapper around any number of database implementations, meaning that callers can be (mostly) agnostic as to what sort of database they're calling. This abstraction has allowed coders to create generic libraries which provide connection pooling for any type of JDBC connection.

Here's the Sun page on connection pooling, and here's one from MySQL.

Since the caller is presumably using only JDBC methods, the checkout can look like a request to create a connection, and the checkin is just the caller closing the connection, i.e. the caller is unaware they're using connection pooling because the semantics are indistinguishable from using the single connection create/tear down solution. This is a good thing; this is real OO.

Libraries

So what libraries are available to make this easy?

• c3p0 — Named after everyone's favorite protocol droid, this library provides connection pooling and prepared statement pooling (I believe this is an object pool of PreparedStatement objects).

  The documentation on their website is pretty thorough. I've actually got a physical copy printed out in my desk because I need to consult it when I'm doing tuning. All configuration is done in a JavaBeans style which makes working with it a breeze.

  It's widely deployed and stands up under pressure. I've used it for doing tens if not hundreds of thousands of transactions per second with multiple machines and multiple threads per machine connecting to multiple databases.

  They appear to have an appendix specifically about configuring c3p0 for use by Tomcat, so you may wish to check that out.

• DBCP — The less creatively-named Apache DBCP (for "Database Connection Pooling") does pretty much the same things as c3p0. This discussion seems to discourage its use, arguing that c3p0 is more actively maintained. I can't really remember why I chose c3p0 over DBCP for my last project (probably familiarity), but if you want to give DBCP a look, go ahead.

  Here are some Stack Overflow questions about DBCP:

  • Connection pooling options with JDBC: DBCP vs C3P0
  • Is DBCP (Apache Commons Database Connection Pooling) still relevant?

  I hate to be a negative Nancy, but I don't think DBCP is what you want.

• BoneCP — Perhaps creatively-named, but a bit weird sounding. I've never used it. The author says it's really fast, and he may be right. It seems the least mature—at least temporally—of all your options, but you should give it a go and see whether it meets your need.

Disadvantages

You're wrapping your DataSource in some proxy-like other class, so vendor-specific methods will likely not be accessible. This isn't a big deal: you shouldn't be writing vendor-specific DB code anyways.