Data Management

Check your database connection's physical status

Connection validation is important in real time apps, which must run with minimal downtime. In Java, you can test the physical database connection using vendor specific APIs or a test-fail query approach. Learn which method is best for your enterprise.

One of the cornerstone classes of the Java JDBC API is the java.sql.Connection class. There are many issues concerning its reuse, pooling, and validation. Connection validation is especially important in real time applications, which must run with no or minimal downtime.

In fact, there can be various situations where a connection can be lost (e.g., because a database is down or because there are some network problems). Unfortunately, the standard Java API doesn't provide methods to check connection’s physical status, so a workaround is required in each particular case. Let's look at two approaches to this problem. The first one uses vendor-specific APIs, and the second is a test-fail query approach.

Getting the connection
Before issuing any SQL statements, an application must connect to the database. One way to create a connection is to use the connection factory DriverManager.getConnection() from java.sql package. However, the a preferred way of establishing a connection is the javax.sql.DataSource.getConnection(). You should use this factory method every time an object requires a connection, instead of creating it once and then storing the connection in the object. Here is an example of this method:
void foo(){
   // ...
   Connection conn = myDataSource.getConnection();
   PreparedStatement ps = conn.prepareStatement(...);
   // ...

In addition, a class that implements the DataSource interface can be registered with a naming service and accessed using the JNDI API. The significant difference between the DriverManager connection factory and DataSource is that DataSource allows you to control connection creation and utilization.

Dealing with connection errors
When your application tries to use a connection that has been disconnected from a database, you will get an exception such as:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection

As a result, your SQL query will fail to execute. As there is no API to determine whether a connection to a database is valid or not; you can know that a connection is invalid only by catching a SQL exception. If an exception corresponds to a closed connection, you have to adjust the application, so it will try to re-establish a connection and re-execute a query. Obviously, this tangles business logic with database connection handling, making the code prone to errors and difficult to manage.

Trusting vendor’s API
One way to deal with a broken connection is to utilize a driver specific API, which you can use to connect to a database. Let’s examine the Oracle JDBC API. The OracleConnectionCacheImpl class implements the javax.DataSource interface and validates the connection each time you request it. In addition, it allows connection reuse by caching the connection for subsequent requests. In other words, if you shut down a database and then try to obtain a connection, you will receive a SQL exception clearly stating the problem. Look at Listing A for an example of data source usage.

Validating the connection
An alternative solution is to perform the connection validation yourself. You can write a connection factory that checks a connection before returning it to a requestor. It is good coding practice to abstract the validation mechanism from the connection factory. You can do this by applying the GoF Strategy pattern, which introduces an interface responsible for connection validation, as shown in Figure A.

Figure A
Class diagram

The AbstractDataSource class, shown in Listing B provides the base implementation of the DataSource interface adding convenient methods for setting and accessing connection properties, including JDBC driver class, URL, user name, and password.

The solid implementations of this class are JdbcDataSource (Listing C) and ValidDataSource (Listing D). The class in Listing C creates a Connection object on each request. The class in (Listing E) provides connection validation capability by making use of the ConnectionValidator.

The SqlConnectionValidator, as shown in Listing F, is the straightforward realization of this interface where a fast SQL query is executed against a connection you believe needs to have its validity checked. You do this by calling the validateConnection(Connection conn) method that throws a SQL exception in case of any problems with the connection.

Benefits of validation
No matter which method you choose, it is vital to establish a valid database connection, especially when dealing with a critical real-time application. While a custom connection validation approach affords you greater flexibility and API independence, it could also degrade overall performance when compared to a vendor's API. You must decide whether the increased control is worth the accompanying increase in complexity and cost.





Editor's Picks