Chapter 4. JDBC Best Practices

Most programmers learn JDBC using the JDBC-ODBC Bridge, which is a Type 1 JDBC driver. Nevertheless, the JDBC-ODBC Bridge is a bridging driver . In other words, it's a driver that falls into the Type 1 or Type 3 categories. These types of drivers provide much weaker database performance than direct access drivers, and the reason for the weakness is fairly simple: bridging technologies require more hops to communicate with the database.

Though avoiding Type 1 and Type 3 drivers is a good rule of thumb, sometimes they cannot be avoided. Not all databases are supported by a Type 2 or Type 4 driver. For example, if you want an application to talk to Microsoft Access, you must use a Type 1 or Type 3 driver.

4.1.1.1 When to use a Type 1 or Type 3 driver

You should use a Type 1 driver under the following circumstances:

• No Type 2 or Type 4 driver exists for your database engine.

• Your application has a specific target platform or must run standalone.

• Your database engine supports an open API such as ODBC that has a bridging driver.

You should use a Type 3 driver when:

• No Type 4 driver exists for your database engine.

• Your application is a WORA client/server application or no Type 1 driver exists for your database engine.

• Your database engine has either a Type 1 or Type 2 driver supported by your Type 3 driver.

4.1.1.2 Choosing between Type 2 and Type 4 drivers

As I just mentioned, you should always try to use a Type 2 or Type 4 driver whenever possible. The only time you should choose a Type 3 driver rather than a Type 2 driver is when your application requires portability. It never makes sense to use a Type 1 bridging driver instead of a Type 2 or Type 4 driver.

That said, which is better, a Type 2 or a Type 4? This largely depends on how you intend to use it. The choice between Type 2 and Type 4 comes down to portability and an understanding of the underlying mechanics of the two drivers. Here are some guidelines:

• Always choose a Type 4 driver when portability is critical.

• For standalone applications or server applications, favor Type 2 drivers unless you understand the mechanics of how the two drivers are implemented.

• If the Type 4 driver is proven to be more efficient, use the Type 4 driver.

4.1.2 Use DataSource Whenever Possible

If you have a choice, you always should use javax.sql.DataSource to get a JDBC connection. Data sources, which were introduced with JDBC 2.0, make the job of configuring a database connection and connecting to a database nearly foolproof. Consider the following data source code fragment:

InitialContext ctx = new InitialContext(  );
DataSource ds = (DataSource)ctx.lookup("dsname");
Connection conn = ds.getConnection(  );

This code contains nothing proprietary, involves no parsing of configuration files, and uses no information dependent on the deployment environment other than a data source name . Now, compare that code to the following:

Connection conn;
   
Class.forName("org.gjt.mm.mysql.Driver").newInstance(  );
conn = DriverManager.getConnection("jdbc:mysql://carthage:/db", "user", "password");

This code is the simplest alternative to using a data source. However, the latter example is harder to read, contains proprietary code (i.e., specific reference to the Driver implementation class, which is the JDBC URL for a specific driver), and requires hard-coded information about the runtime environment (e.g., the host and database in the URL, the username, and the password). In short, you have to compile the DriverManager version for a specific target runtime environment.

To be fair, it is possible to achieve the same level of portability with the driver manager approach as with the data source approach. However, doing so makes your application code much more complex. In this case, you would have to specifically introduce code to parse a resource file containing the formerly hardcoded information. In addition, you would need to handle exceptional cases in which information is missing from your resource file. Finally, you would need to write in logic to handle any proprietary connection properties that help optimize the performance of your JDBC driver. All these items are better handled with the DataSource class.

4.1.3 Leverage Proprietary Connection Properties

Java has conditioned us to embrace open, generic standards and shy away from technology-specific tools. The JDBC API, however, allows databases to specify options that enable you to optimize the functionality of your database applications without impacting their openness. These options are called connection properties .

Every JDBC driver comes with a set of connection properties that allow you to specially configure it. Put another way, they allow you to tell the driver a bit about your application so that it can efficiently communicate with the database on the other side. These properties answer common JDBC questions, such as how big the result sets tend to be, which sorting rules should be used, and how long the driver should wait for information from the database before timing out. The properties that are available to you, and how they are best set, depend extensively on the database you are using and how your application is using that database. However, you should always take advantage of these properties in a production environment.

The two most common methods of specifying connection properties are to hardcode them when you configure your data source, or to place them inside a driver manager resource file. However, the following code snippets show a simpler way to keep your code free of platform-dependent property strings: use the system properties hashtable to embed the connection properties.

First, pass the system properties into the constructor of an InitialContext object:

InitialContext ctx = new InitialContext(System.getProperties(  ));
DataSource ds = (DataSource)ctx.lookup("dsname");
Connection conn = ds.getConnection(  );

Or, if you use the DriverManager class (see my earlier warning against this), pass the system properties hashtable into the getConnection( ) method:

Connection conn = DriverManager.getConnection(url, System.getProperties(  ));

You can then pass in JDBC connection properties by specifying them directly on the command line, configuring them accordingly based on the database that is used:

java -Duser=uid -Dpassword=pw -Dencoding=8859-1 -Dautocommit=true DBApp

4.1.4 Pool Database Connections

A sure way to bog down any JDBC application is to repeatedly open and close database connections. This problem is especially troublesome for high-volume applications such as web sites and enterprise applications. The clear and obvious answer to this problem is connection pooling . Connection pooling allows your application to use preexisting database connections by "loaning out" from a pool of connection objects.

Before JDBC 2.0, you had to write your own connection pooling support or look for a third-party tool. Put succinctly, connection pooling was a coding problem. Thanks to JDBC 2.0, however, connection pooling is now just a configuration issue. This means you simply have to convert your data source to a pooled data source; the code to access data from the connection remains the same, regardless of whether you use connection pooling. However, even though connection pooling is becoming more commonplace in JDBC code, it should still be included as a JDBC best practice.

How you configure your application to use a pooled connection depends on the environment in which you are running it. First, you must use a DataSource that supports connection pooling, which typically means obtaining an object that implements the javax.sql.ConnectionPoolDataSource interface. Your application then grabs its pooled connections from this data source. The only part that varies from one environment to the next is how to configure the environment to use a particular data source. For example, in a Java 2 Enterprise Edition (J2EE) application server, you will typically configure an XML file to store that data source for JNDI lookups. In other environments, you might manually configure and serialize a data source to be read later by the application.

Example 4-1 shows code used to obtain and release a pooled connection.

Example 4-1. Obtaining and releasing a pooled database connection

import javax.sql.*;
import javax.naming.*;
   
public void connectToDatabase(  ) {
   
  try {
   
      InitialContext ctx = new InitialContext(parms);
   
      ConnectionPoolDataSource poolDataSource =
          (ConnectionPoolDataSource)ctx.lookup(
              cpsource);
      poolDataSource.setLoginTimeout(30); // seconds
   
      PooledConnection poolConnection =
          poolDataSource.getPooledConnection(  );
   
      Connection connection = poolConnection.getConnection(  );
   
      //  Do whatever you would typically do with the connection
      //  here.
   
  } catch (Exception e) {
      //  Handle exceptions.
  } finally {
      connection.close(  );
  }





}