How should I manage connecting to a database using java servlets, JSP & Tomcat

Question

Hi there,

I'm pretty new to Servlets and JSP and to using databases.

At the moment I have a class in the 'model' part of my web app which has lots of methods that I've written to perform database queries and updates. At the moment, in each of those methods I am creating a database connection, doing the SQL stuff, then closing the connection.

This works fine while I'm just making small apps for myself but I'm starting to realise that if lots of people were using my app concurrently then it would start to become apparent that creating database connections and closing them for each method call is a time costly process. So I need to change the way I do things.

In Head First Servlet & JSP by Basham, Sierra & Bates, they describe how it's possible to use a ServletContextListener implementation to create an object on the deployment of the web app that will be added as an attribute of the ServletContext. The authors don't go into it, but imply that people often add a database connection as an attribute of the ServletContext. I thought I would like to implement this for myself, but after reading this stackoverflow article on database connection management I'm not so sure.

However as I'm just starting with servlets and JSP, let alone the rest of J2EE, a lot of that article is beyond me.

The points that stand out for me from that article are:

• Something could happen to break that database connection and if we are relying only on that connection then we would need to redeploy our app in order to restart a connection. is this correct?

• We should reply on the container to manage the database connections for us. Great, but how is this acheived? How can I communicate with the container? (Please bear in mind that I've just started with Servlets and JSP).

• In terms of Servlet design in general, I have one servlet class per request type and that normally only has one type of call to a database ie a specific update or query. Instead of having a class with all the methods for querying the database, is it a better design for me to have the methods within their respective servlets or would that contravene the Model-View-Controller pattern?

I can't imagine that I'll be having too many problems with too many users slowing down the user experience just yet :) but I'd like to start doing things right if possible.

Many thanks in advance for your comments

Joe

Answer 1

I would check out connection pooling and specifically frameworks like C3P0 or Apache Commons DBCP.

Both these packages will look after maintaining and managing a collection of database connections for you.

Typically connections are established in advance, and handed out to requesting threads as they require them. Connections can be validated prior to being handed out (and remade in advance of the client using them if the connection is broken).

Answer 2

The following page on Tomcat's website describes how to connect Tomcat and mySQL in detail. You do not want to roll your own, there are too many DataSource pools already available that have been debugged and tried in production environments.

The main thing about using a pool is that a connection is not terminated when you call close, instead it is just returned to the pool. Therefore it is important to make sure that you close your resources in a try/finally block. Look here for a sample.

Answer 3

The way to go in a web application is to have a connection pool manage your connections. This will allow your threads of execution to share the database connections, which is an important point as connecting to a database is usually a costly operation. Using a connection pool is usually just a configuration task as most containers support managing a connection pool.

From the viewpoint of your code, there are very few changes. Basically:

• Connection pools are accessed through the DataSource interface, while non-pooled connections may be accessed through the old DriverManager.
• To get the DataSource you will usually have to use JNDI, as this is the standard method for publishing connection pools in a J2EE application.
• You want to close() Connection objects as soon as possible. This returns the connection to the pool wothout disconnecting from the DB, so that other threads can use it.

As always, you should call close() on every JDBC resource (connections, statements, resultsets) to avoid leaking. This is specially important in a server application because they are infrequently restarted so leaks accumulate over time and eventually will make your application malfunction.

This is some example code from http://download.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/datasource.html (NOTE: not exception-safe). As you can see, once you get the Connection reference there is nothing really special.

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/AcmeDB");
Connection con = ds.getConnection("genius", "abracadabra");
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement(
                            "SELECT NAME, TITLE FROM PERSONNEL WHERE DEPT = ?");
pstmt.setString(1, "SALES");
ResultSet rs = pstmt.executeQuery();

System.out.println("Sales Department:");
while (rs.next()) {
        String name = rs.getString("NAME");
        String title = rs.getString("TITLE");
        System.out.println(name + "  ; ;" + title);
}
pstmt.setString(1, "CUST_SERVICE");
ResultSet rs = pstmt.executeQuery();

System.out.println("Customer Service Department:");
while (rs.next()) {
        String name = rs.getString("NAME");
        String title = rs.getString("TITLE");
        System.out.println(name + "  ; ;" + title);
}
rs.close();
pstmt.close();
con.close();

Answer 4

The authors don't go into it, but imply that people often add a database connection as an attribute of the ServletContext.

That's not the standard way to handle this. The traditional approach is to use a connection pool i.e. a pool of ready to use connections. Then, applications borrow connections from and return them to the pool when done.

There are several standalone connection pool implementations available (C3P0, Commons DBCP, Bone CP) that you can bundle in your application. But when using a Servlet or Java EE container, I would use the connection pool provided by the container. Then, obtain a DataSource (a handle on a connection pool) via JNDI from the application to get a JDBC connection from it (and close it to return it to the pool).

The way to configure a pool is obviously container specific so you need to refer to the documentation of your container. The good news is that Tomcat provides several examples showing how to do this, how to obtain a datasource via JNDI and how to write proper JDBC code (read until the bottom of the page).

References

• Apache Tomcat User Guide
  • JDBC Data Sources
  • JNDI Datasource HOW-TO