Hey Ric,
Thanks for asking these questions. Hopefully, this discussion will benefit
many on this list.
Here's the summary of your options obtaining connections in your
application.
As you suggested, database connections are typically obtained through a
javax.sql.DataSource object (wrapped pool of database connections). There
are few ways that database connections could be managed in a Tomcat web
application, e.g.
(1) NOT MANAGED.
Database connections are created on demand, they are not pooled, and
typically not a great way to get database connections, especially in a web
app where you have 1000s of users/requests. For simplicity, we might use
this for batch applications, since we typically control how often and how
many times the code is executed, i.e. not in 1000s of requests, as with web
application requests. Here's the code for that ...
Class.forName("some.jdbc.driver.here");
Connection c = DriverManager.getConnection();
...
So, don't use this if you are in a context of web application.
(2) CONTAINER-MANAGED CONNECTION POOL.
This is a typical JEE way to manage and pool your resources. Connection
pool is implemented as part of the application server (Tomcat, WebSphere,
Weblogic, JBoss, etc...) and each appserver has its own way to configure
and implement connection pools. In Tomcat, you need to define a <Resource
... /> element somewhere in your appserver configuration
(<GlobalNamingResources> will be in global context, or per application in
<Context> element of particular application). See more details here:
http://tomcat.apache.org/tomcat-7.0-doc/config/resources.html
On Tomcat, this is now implemented by Tomcat JDBC Connection Pool (
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html) and has replaced
the older traditional Apache Commons DBCP project (
http://commons.apache.org/proper/commons-dbcp).
Here's an example configuration:
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#As_a_Resource
In order to configure this, you also need to add specific database JDBC
drivers (e.g. postgresql JDBC drivers) to the server class path, typically
just copying them to CATALINA_HOME/lib directory.
Once it is configured, the connection pool will be created and registered
with JNDI on the startup of the server.
You would then get the reference to this connection pool through standard
JNDI lookups:
(a) using container injection
@Resource(name="....")
private DataSource datasource
(b) traditional JNDI lookup
Context naming = new InitialContext();
DataSource datasource = (DataSource) naming.lookup("....");
(c) or even using Spring JEE context, e.g.
<jee:jndi-lookup id="mydatasource" jndi-name="...."/>
For each database (schema), you will need to create a separate JDBC
connection pool. Each one will have a separate JNDI name that you can use
to lookup/inject into your code.
And finally, third way that developers manage their connections is:
(3) APPLICATION-MANAGED CONNECTION POOL.
This way, connection pools are created per application. Container doesn't
know about the connection pool. This is very typical in Spring
applications, where you define your own datasources. Here, you can to use
any of the connection pool implementations. You could use Tomcat JDBC
Connection Pool or any other third-party connection pools, e.g.
- C3P0 (http://www.mchange.com/projects/c3p0/)
- HikariCP (http://brettwooldridge.github.io/HikariCP/)
- Apache Commons DBCP (http://commons.apache.org/proper/commons-dbcp/)
or many other ones.
Spring also provides a simple JDBC wrapper class
(org.springframework.jdbc.datasource.DriverManagerDataSource) that is not
really implementing a connection pool, but rather simplification for the
lack of better connection pool implementation. We often use this
"connection pool" in development, as it is simple to configure and no need
for other connection pool libraries.
More details here:
http://docs.spring.io/spring-framework/docs/4.1.1.RELEASE/javadoc-api/org/springframework/jdbc/datasource/DriverManagerDataSource.html
The connection pool is managed by the application, typically created at the
application deployment. Since the drivers are isolated to a particular
application, you don't have to place drivers in shared library folder
(CATALINA_HOME/lib) as in (2) CONTAINER-MANAGED, but rather can include
drivers in the application, e.g. WEB-INF/lib folder.
It is important that the creation of the connection pool is done at some
application-scoped level, not per request! Because, the pool itself is
putting the limits on the total number of connections to a database. Each
connection pool is independent of other connection pools that might exist.
So, in this third way - connection pool management and configuration is
done by the developer, not administrator like in CONTAINER-MANAGED
CONNECTION POOLS.
Hopefully, this sheds some light on the options we have when we need a
connection pool.
I will try to answer your other specific questions in a separate email.
Cheers!
Neven
