Java Enterprise in a Nutshell (In a Nutshell (OReilly))

8.2. Connecting to the Database

The java.sql.Connection object, which encapsulates a single connection to a particular database, forms the basis of all JDBC data-handling code. An application can maintain multiple connections , up to the limits imposed by the database system itself. A standard small office or web server Oracle installation can support 200 or so connections, while a major corporate database could host several thousand. The DriverManager.getConnection( ) method creates a connection:

Connection con = DriverManager.getConnection("url", "user", "password");

You pass three arguments to getConnection( ): a JDBC URL, a database username, and a password. For databases that don't require explicit logins, the user and password strings should be left blank. When the method is called, the DriverManager queries each registered driver, asking if it understands the URL. If a driver recognizes the URL, it returns a Connection object. Because the getConnection( ) method checks each driver in turn, you should avoid loading more drivers than are necessary for your application.

The getConnection( ) method has two other variants that are less frequently used. One variant takes a single String argument and tries to create a connection to that JDBC URL without a username or password or with a username and password embedded in the URL itself. The other version takes a JDBC URL and a java.util.Properties object that contains a set of name/value pairs. You generally need to provide at least username=value and password=value pairs.

When a Connection has outlived its usefulness, you should be sure to explicitly close it by calling its close( ) method. This frees up any memory being used by the object, and, more importantly, it releases any other database resources the connection may be holding on to. These resources (cursors, handles, and so on) can be much more valuable than a few bytes of memory, as they are often quite limited. This is particularly important in applications such as servlets that might need to create and destroy thousands of JDBC connections between restarts. Because of the way some JDBC drivers are designed, it is not safe to rely on Java's garbage collection to remove unneeded JDBC connections.

8.2.1. DataSource Objects

The DataSource interface provides an alternative to the DriverManager class and conventional JDBC URLs. Instead, information about a database is stored within a naming service and retrieved via the JNDI API. Connection information (drivers, server locations, and so forth) is stored within the DataSource object, which uses it to create the actual Connection object used to execute JDBC commands. DataSource objects are also used to provide native driver-level support for connection pooling and distributed transactions.

Each DataSource is assigned a logical name, by convention beginning with jdbc/. The logical name and associated connection metadata are configured in the J2EE setup process. This makes code more portable and allows for easy changes in drivers and connection information. Accessing a DataSource via JNDI is very simple:

Context ctx = new InitialContext( ); DataSource ds = (DataSource)ctx.lookup("jdbc/CamelDB"); Connection con = ds.getConnection("lawrence", "arabia");

The first two lines obtain the DataSource object from the naming service. The getConnection( ) method of DataSource then logs into the database and returns a Connection object. Unlike DriverManager, the only information required is a username and password.

8.2.2. Connection Pooling

Generally DriverManager provides the easiest way to retrieve a single database connection for a small application, but virtually every enterprise application, including J2EE and EJB systems, uses a DataSource instead for two main reasons. First, the DataSource interface is simpler to use. Second, DataSource objects also support connection pooling, which lets an application maintain several open database connections and spread the load among them. This is often necessary for enterprise-level applications, such as servlets, that may be called upon to perform tens of thousands of database transactions a day.

Connection pooling is implemented behind the scenes, either by an application server or a pooling utility such as Jakarta DBCP (http://jakarta.apache.org/commons/dbcp). This means you can write your application to simply rely on a DataSource object rather than worrying about the semantics of connection pooling.

When using a connection pool, the underlying driver creates a set of connections and distributes the connections to programs as needed (often on a per-thread basis). Once a connection has been used, it is returned to the pool to be reused later. This eliminates the substantial overhead of creating a new Connection for each request (a delay that can often be measured in seconds). For applications that make intensive use of database connections over extended periods, such as Java servlets (see Chapter 3), this added efficiency can be vital.

Like regular DataSources, connection pools are configured by the J2EE server administrator and must be supported by the database driver itself. Developers need only to remember to explicitly close all Connection objects after use, which is good programming practice anyway. The best way to handle this is with a try/catch/finally block:

Connection con = null; try { ds = (DataSource)context.lookup("jdbc/oasisDB"); con = ds.getConnection("larryl", "camel"); // ... some worthwhile action } catch (Exception e) { } finally { if(con != null) con.close( ); }