Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL best usage in Tomcat?

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]

like image 598
mabuzer Avatar asked May 30 '10 06:05

mabuzer


People also ask

What is difference between Tomcat and MySQL?

When assessing the two solutions, reviewers found Apache Tomcat easier to use and set up. However, MySQL is easier to administer. Reviewers also preferred doing business with MySQL overall. Reviewers felt that Apache Tomcat meets the needs of their business better than MySQL.

How do I use data sources with Tomcat?

Tomcat DataSource JNDI Configuration Example - server.Add below code in the tomcat server. xml file. The code should be added in the GlobalNamingResources element. Also make sure that database driver is present in the tomcat lib directory, so in this case mysql jdbc jar have to be present in the tomcat lib.

Which JDBC driver is used for MySQL?

Driver in MySQL Connector/J is com. mysql. cj. jdbc.

What is maxWait in Tomcat?

maxWait: The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception.


2 Answers

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.

like image 128
jasonmp85 Avatar answered Sep 21 '22 23:09

jasonmp85


B), or C) use a connection pool

like image 39
Bozho Avatar answered Sep 19 '22 23:09

Bozho