Second in a series of articles adapted from Java Servlets: By Example by Alan R. Williamson, reproduced here by permission of Manning Publications.

One of the core building blocks of any system - distributed, local or virtual - is a database. At some point in the chain of processing, the ability to store and retrieve data needs to be addressed. The capacity to access a database successfully is a high priority for many projects. Coupled with the onslaught of the Web and the need to place some sort of front-end access to a database, the demand for database connectivity at the server side is at an all-time high.

To this end I'll present a number of solutions for connecting server-side processes to a database. This includes the well-known database pooling contrasted to the single connection.

Method #1: Connection Per Client
In this example, for each client request that comes in we will open up the connection to the database, perform a query and then output the results back to the client.

Connecting to the Database
Before we can run any queries on the database, we have to get a handle or reference to it. Once we have an instance of Connection, we can run as many queries as we want. However, we do not directly create an instance of Connection, but ask the DriverManager class to supply one for us.

The DriverManager will attempt to connect to the given database with the optional username and password. If it is successful, then an instance of Connection is returned. Listing 1 illustrates this procedure. All listings, 1-10, can be accessed on the JDJ Web Site.

Since we do not have the driver registered in the jdbc.properties file, we have to make sure the driver class is available to the virtual machine. We do this with a call to the forName method from the Class class. This will load and link the class name into the virtual machine. In this instance, we are looking to use the standard JDBC-ODBC driver that ships with the JDK. This is controlled by the class sun.jdbc.odbc.JdbcOdbcDriver.

Next, we have to call the method getConnection(...) from the DriverManager class. If all goes well, a newly created Connection will be returned. Be careful to get the driver case correct; some drivers are fussy. Another common problem is when the connection refuses to connect. Check to see that no one else has a session open to it. Most databases have a limit to the number of concurrent connections that can be open at any one time (this includes shutting down the file in MS-Access if it is open).

Running the Query
Now that we've successfully opened the connection to the database, it is ready for querying. Since this is such a generic example, letÕs run a query that will return the complete table data: every column and every row. We will print this data back out to the client with a row per line.

Using the JDBC API we don't need to know the name of the columns beforehand (or the data type, for that matter). We can ask the assistance of a helper class. YouÕll learn more about that later. The SQL query in our instance will be:

SELECT * FROM USER_TABLE

It will begin with the creation of a new Statement class. We make a call to the Connection class with the createStatement() method, which will return a new instance of Statement. From here we can execute the SQL statement above with a call to executeQuery(...).

This method will return with a ResultSet instance, which represents the new result table. Only one ResultSet can be active per statement.

Listing 2 shows the code used to execute the query. Since this example has asked for all the columns back, we get a handle to the ResultSetMetaData class from the ResultSet class. This class handles all the information that describes the data that was returned. In our example we are only interested in the number of columns that were returned. But this class gives access to all the data types of each column and the column name, if it's available.

Running through each row of the result table is done using the next() method. This method moves the table cursor on one row. Unless you are using a full JDBC version 2 driver, you will not be able to return to a previous row. If you need to, you will have to rerun the query.

This servlet does nothing fancy except return all the data, one row at a time per line. This is done by building up a temporary string of the column data by making a call to retrieve the data in each column of the result table. The getString(...) method returns the column data in the given column index in the form of String. The ResultSet method provides a getXXX(...) method for each of the data types available.

Once the query is complete, the result set, the statement and the connection are closed. It is important to close it down in the correct order, or a SQLException will be thrown.

Performance
From a technical point of view, the servlet in this example is perfect. It opens up a connection to a database, it runs a query and displays the results, and closes the connection down again. It's a textbook example, one might even say.

However, from a practical point of view, it is useless and you would not use it in a real-world example. Why, you ask?

For every client request that comes in, a new database connection is created. This is not a major problem if only one person at a time comes to your Web site. However, this isn't the case. We have to assume that many people will be accessing the servlet at once. Therefore, we could potentially use up all the concurrent slots on a database engine.

The servlet is also very inefficient. The JDBC API tells us we can happily reuse the Connection class, with no need to open and close it all the time. Ironically, making the connection to the database can be one of the most time-consuming operations performed. But this servlet does it for every client request.

One potential way around this problem is to have the Connection class static, starting off live as a null. When the first client request comes in, it can create the database connection, and each subsequent connection can then reuse that connection. However, you still have to safeguard against multiple hits; implementing the SingleThreadModel interface from the servlet API can easily resolve this issue.

Although technically correct, it is still very restrictive. First, only one client thread can run through the service(...) at any one time. Second, what if we develop another servlet to operate from the same database? Do we have to create a new connection to the database?

For these reasons, the implementation in the next section is a much better, cleaner solution.

Method #2: Connection Pool
This section will demonstrate the design and implementation of a class that will be used to manage all the connections for the database.

Overview
We want to be able to open up a pool of connections. Every time a class needs to run a query, it will ask for a connection from the pool. If one is available, the connection is temporarily lent to the class on the condition that it is returned after it's used. If one isn't available, then the class can wait for one to become available.

One of the things we don't want to have to do is to carry around a reference to the connection pool. This would make it awkward, as we would have to make sure all classes had a reference to it. Fortunately, with Java we donÕt have to worry about this.

We will design a class, dbBroker, to handle all the connections. It will also be responsible for the distribution of the actual connections. In order not to have to carry an instance to this class around with us, we will make all the public methods static, with the class itself holding the reference to an instance of itself.

Listing 3 shows how to set up this class. Before a method retrieves a connection from the pool, it first must make a call to dbBroker.getInstance(). This is a call to verify that an instance has been created and is ready to serve.

To make sure this class isn't created outside of this, we will make the constructor private. The next section will look at what happens in this constructor.

Managing New Connections
The connection pool will manage the connections to the database, including all the checking and administration of lending out the connections to using classes. To make things a little easier, we will define a wrapper class for each Connection and these classes will be used to store all the necessary information associated with the "hire" of the connection.

The class, dbConnection (shown in Listing 4), shows all the methods and data for each Connection. In addition to the Connection object, a flag to indicate its current status will be kept. This flag will be set when a class is using the Connection, using the setActive() method.

In multiuser systems, it can often be difficult to estimate the number of concurrent connections that are actually needed. In order for this decision to be an easier one to make, we will keep a little statistical information on each Connection, including the number of times the Connection has been used, the average time for each use and the maximum time a connection has been kept out for. The dbConnection handles all this information through the use of the setActive() and setInActive() methods.

When a class makes a call to dbBroker.getInstance(), the constructor shown in Listing 5 is run. This constructor will create the number of necessary connections and make them available for use.

One of the criteria of the connection pool was not to have any intelligence about the opening of the database distributed all over the system. Therefore, this class will open up a special file that will describe the complete connection parameters. We can therefore control all the parameters through a simple text file, and access these parameters with the java.util.Properties class.

The database driver, database name, username and password will be stored in the dbBroker class. This will allow us to reopen any connections if necessary without the need to reload the file. Another parameter that is read in is the number of connections the pool manager will manage.

Each connection will be stored in a list using the Vector class. Knowing the number of connections to be created makes filling up this list a trivial matter. For each connection, a call to the method in Listing 6 is made and the openConnection() method attempts to create a new Connection instance. If it's successful, then a new instance of dbConnection is created and inserted into the list.

You can see that the method for creating the Connection instance is no different from the method we used in the servlet in the first section.

Controlling Connections
We will allow classes access to the connection pool through two methods: pop() and push(...). The pop() method will look through the list of connections for a connection that is not in use. If one is found, then it is flagged as active and the Connection is returned.

If one is not available, then this suggests that all the connections are being used. If this is the case, then the method call will be suspended until one does become available. We can do this with a call to wait(). When this returns, we will reattempt to get a free connection. The method shown in Listing 7 illustrates this process.

The method called getFreeConnection(), which can be seen in the complete source code, simply runs through the Vector of dbConnections looking for an inactive connection.

Once a class has finished using the connection, it is returned with a call to the push(...) method shown in Listing 8. The method looks for the corresponding wrapper class that holds this connection. Once the wrapper class is found, the Connection is cleaned up with a call to commit() and clearWarnings(). This guarantees that no errors or warnings roll over to the next use.

If something goes wrong with this cleanup procedure, an Exception will be thrown. In this instance, the Connection is closed and a reattempt to open it is made. After the Connection has been placed back into the list as inactive, a call to notifyAll() notifies any waiting classes that are waiting on a free class.

Verifying Connections
It would be useful to print out all the statistical information that is being held every so often. To do this, we can set the dbBroker class as a threaded class and have it print out the statistics of each dbConnection class once every period.

The method shown in Listing 9 sleeps for 30 minutes before printing out a status report detailing the average use time and maximum time, and the number of times the connection has been accessed.

Using the Pool Manager
Now that we've created the pool manager, we can use it. We will use the same example we used before and replace the service(...) method with a much improved version (see Listing 10).

The complete database creation section has been replaced with a simple call to dbBroker.getInstance() and then dbBroker.pop(). The dbBroker class will do all the necessary loading and connecting to the database, and return a clean Connection instance.

After we have finished using it, we return it back to the pool manager with a call to dbBroker.push(...). As you can see, there is no need to hold a separate instance to dbBroker, as all the methods are accessed through static calls.

Summary
This article presented the user with an alternative to the highly inefficient method of database handling. A servlet is not like a normal application where you have a degree of control over the usage patterns. A servlet is called into action when a client makes a request; therefore, the traditional way of handling database connections has to be rethought.

Although it is already highly efficient, the dbBroker could be extended to include the ability to handle multiple pools. This would allow connections to different databases to be handled and manipulated at once. This is an essential feature for applications that require a distributed database layer.