Which is much better method of using MySQL in Tomcat :
A) assign a DB connection for user as lengthy as it is session applies. [OR]
B) open link with DB, on every request arrived at server so when it's done close that.
C) Connection pool. [Best solution]

Impetus

In almost any kind of request-reply system—be it http, ftp, or perhaps a database call—it is sensible to help keep a swimming pool of connections open to be used in with a client. The price of building and tearing lower an association throughout each and every request is high (for both the customer but for the server), so getting a swimming pool that multiple threads may "take a look atInch an association for his or her use is a great pattern.

Implementation

The JDBC API supplies a wrapper around a variety of database implementations, and therefore phone callers could be (mostly) agnostic in regards to what kind of database they are calling. This abstraction has permitted programmers to produce generic libraries which offer connection pooling for any kind of JDBC connection.

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

Because the caller is most probably only using JDBC techniques, the checkout look just like a request to produce a connection, and also the checkin is only the caller closing the bond, i.e. the caller is not aware they are using connection pooling since the semantics are indistinguishable by using the only connection create/destroy solution. This can be a positive thing this really is real OO.

Libraries

What exactly libraries are for sale to get this to easy?

  • c3p0 — Named after everyone's favorite protocol android, this library provides connection pooling and prepared statement pooling (In my opinion it is really an object pool of PreparedStatement objects).

    The documentation online is fairly thorough. I have really got an actual copy printed in my desk because I have to consult it when I am doing tuning. All configuration is completed in a JavaBeans style making dealing with it very simple.

    It's broadly used and stacks up pressurized. I have tried on the extender for doing hundreds otherwise 100s of 1000's of transactions per second with multiple machines and multiple threads per machine hooking up to multiple databases.

    They have the symptoms of an appendix particularly about setting up c3p0 to be used by Tomcat, to desire to make sure that out.

  • DBCP — The less artistically-named Apache DBCP (for "Database Connection Pooling") does virtually exactly the same things as c3p0. This discussion appears to discourage its use, quarrelling that c3p0 is much more positively maintained. I can not really remember why I selected c3p0 over DBCP for my last project (most likely familiarity), but when you need to give DBCP a glance, proceed.

    Here are a few Stack Overflow questions regarding DBCP:

    I personally don't like to become an adverse Nancy, however i don't believe DBCP is what you would like.

  • BoneCP — Perhaps artistically-named, but a little strange sounding. I have not used at all it. The writer states it's really fast, and that he might be right. It appears minimal mature—at least temporally—of all of your options, however, you should give it a try and find out whether or not this meets your need.

Disadvantages

You are wrapping your DataSource in certain proxy-like other class, so vendor-specific techniques will not be around. This is not a large deal: you should not be writing vendor-specific DB code anyways.

B), or C) make use of a connection pool