Advanced Connection Management

The simplistic database connection setup with a database.properties file, as described in the preceding sections, is suitable for small test programs, but it won't scale for larger applications.

When a JDBC application is deployed in an enterprise environment, the management of database connections is integrated with the Java Naming and Directory Interface (JNDI). The properties of data sources across the enterprise can be stored in a directory. Using a directory allows for centralized management of user names, passwords, database names, and JDBC URLs.

In such an environment, you use the following code to establish a database connection:

 Context jndiContext = new InitialContext(); DataSource source = (DataSource) jndiContext.lookup("java:comp/env/jdbc/corejava"); Connection conn = source.getConnection(); 

Note that the DriverManager is no longer involved. Instead, the JNDI service locates a data source. A data source is an interface that allows for simple JDBC connections as well as more advanced services, such as executing distributed transactions that involve multiple databases. The DataSource interface is defined in the javax.sql standard extension package.

Of course, the data source needs to be configured somewhere. If you write database programs that execute in a servlet container such as Apache Tomcat or in an application server such as BEA WebLogic, then you place the database configuration (including the JDBC URL, user name, and password) in a configuration file.

Management of user names and logins is just one of the issues that require special attention. A second issue involves the cost of establishing database connections.

Our simple database programs established a single database connection at the start of the program and closed it at the end of the program. However, in many programming situations, this approach won't work. Consider a typical web application. Such an application serves multiple page requests in parallel. Multiple requests may need simultaneous access the database. With many databases, a connection is not intended to be shared by multiple threads. Thus, each request needs its own connection. A simplistic approach would be to establish a connection for each page request and close it afterward, but that would be very costly. Establishing a database connection can be quite time consuming. Connections are intended to be used for multiple queries, not to be closed after one or two queries.

The solution is to pool the connections. This means that database connections are not physically closed but are kept in a queue and reused. Connection pooling is an important service, and the JDBC specification provides hooks for implementors to supply it. However, the Java SDK itself does not provide any implementation, and database vendors don't usually include one with their JDBC driver either. Instead, vendors of application servers such as BEA WebLogic or IBM WebSphere supply connection pool implementations as part of the application server package.

Using a connection pool is completely transparent to the programmer. You acquire a connection from a source of pooled connections by obtaining a data source and calling getConnection. When you are done using the connection, call close. That doesn't close the physical connection but tells the pool that you are done using it.

You have now learned about the JDBC fundamentals and know enough to implement simple database applications. However, as we mentioned at the beginning of this chapter, databases are complex and quite a few of its advanced topics are beyond the scope of an introductory chapter. For an overview of advanced JDBC capabilities, check out the JDBC API Tutorial and Reference or the JDBC specifications at http://java.sun.com/products/jdbc.