Chapter 8: Advanced Techniques

This chapter introduces and shows you how to deal with some advanced techniques of Structured Query Language (SQL) and Java Database Connectivity (JDBC). With these techniques, you can add flexibility, better performance, and dynamic features to your applications.

Using DataSource Objects and Connection Pooling

In earlier chapters, you use JDBC Driver, the DriverManager, and Connection objects to obtain connections to a database. These provide a simple way to establish a session, execute SQL statements, retrieve results, and close the connection. This was the usual way of working with databases at the time client/server architectures were in place: Connections were established as soon as clients had to access data, whatever the number of involved clients was.

As soon as three-tiered applications appeared (especially applications that were Web-driven), opening and closing connections all the time, possibly for every Web request, was no longer an option. Indeed, opening and closing connections involves a lot of processing in both the JDBC driver and the database itself. Both have to allocate resources and maintain some state about connections and then release them when the connections are closed. A simple solution to circumvent this was easily achieved by using pooled connections. In such systems, a middle tier opens a number of connections at startup and maintains them in a pool. Connection pools enable you to set a minimum of connections that must remain open, an increment value, a connection time-out, and a shrinking value, for example.

A connection is taken out of the pool as soon as a request that necessitates database access comes in and is returned to the pool as soon as the request has been serviced. The pool basically maintains a list of free connections and used connections. In general, all recent Java 2 Enterprise Edition (J2EE) application servers such as iPlanet Application Server, BEA Weblogic, or IBM WebSphere provide a pool of connections. Such connections are all opened for a specific user. This means that some kind of global account must be created in the database. It is actually no more required or recommended to use a different database account (user login and password) for every user of the system. Only one login to the database is sufficient to serve the needs of accessing persistent data, using several connections in parallel. Of course, an application may use several pools for different purposes or for different types or classes of users because some requests may necessitate more access rights than others. The big difference between this approach and former approaches is that connections are maintained outside of the application code, also called business logic.

You can imagine the enormous performance advantage of using connection pooling in a scenario in which thousands of simultaneous Web users perform requests that necessitate database access. As Web requests are usually handled in a stateless manner, opening and closing the connections for each one of those requests simply isn’t feasible. Another advantage of using data sources and connection pools is that in some cases, caching of prepared statements is also provided.

The benefits of using the DataSource object is that a DataSource is registered in a Java Naming and Directory Interface (JNDI) namespace. This namespace may be provided by a Lightweight Directory Access Protocol (LDAP) directory server or an internal Registry of a given application server. The advantage of this is that the application code no longer refers to a specific JDBC driver, but rather to a data source. The link between the data source name and an actual JDBC driver is usually provided as a deployment parameter to a J2EE component or as a configuration setting of the application server. A DataSource may be a pooled data source or not; this is totally transparent to the developer.

The base interface of javax.sql.DataSource is quite simple, as shown in the following.

DataSource’s Connection Methods

Connection getConnection() Connection getConnection(String login, String password)

getConnection() and getConnection(String login, String password) return a Connection object from the pool. This object can be used as usual. As soon as the application no longer needs this connection, it releases it using connection.close(), which returns the connection to the pool.