JDBC Connection Status

1. Overview

In this article, we’ll discuss some aspects of the JDBC connection status. First, we’ll see the most common reasons for connection loss. Then, we’ll learn how to determine the connection status.

We’ll also learn how to validate the connection before running SQL statements.

2. JDBC Connection

The Connection class is responsible for communicating with the data source. The connection may be lost for various reasons:

• The database server is down
• Network connectivity
• Reusing a closed connection

Running any database operation on connection loss will result in an SQLException. In addition, we can inspect the exception for details about the issue.

3. Checking the Connection

There are different methods to check the connection. We’ll take a look at those methods to decide when to use each of them.

3.1. Connection Status

We can check the Connection status using the isClosed() method. With this method, a SQL operation cannot be granted. However, it’s helpful to check if the connection is opened.

Let’s create a status condition before running SQL statements:

public static void runIfOpened(Connection connection) throws SQLException
{
    if (connection != null && !connection.isClosed()) {
        // run sql statements
    } else {
        // handle closed connection path
    }
}

3.2. Connection Validation

Even if the connection is opened, it can be lost for the reasons described in the previous section. Therefore, it may be required to validate the connection before running any SQL statement.

Since version 1.6, the Connection class provides a validation method. First, it submits a validation query to the database. Second, it uses the timeout parameter as a threshold for the operation. Finally, the connection is marked as valid if the operation succeeds within the timeout.

Let’s see how to validate the connection before running any statement:

public static void runIfValid(Connection connection)
        throws SQLException
{
    if (connection.isValid(5)) {
        // run sql statements
    }
    else {
        // handle invalid connection
    }
}

In this case, the timeout is 5 seconds. A value of zero indicates timeout doesn’t apply to the validation. On the other hand, a value less than zero will throw an SQLException.

3.3. Custom Validation

There are good reasons to create a custom validation method. For instance, we could be using a legacy JDBC without the validation method. Similarly, our project may need a custom validation query to run before all statements.

Let’s create a method to run a predefined validation query:

public static boolean isConnectionValid(Connection connection)
{
    try {
        if (connection != null && !connection.isClosed()) {
            // Running a simple validation query
            connection.prepareStatement("SELECT 1");
            return true;
        }
    }
    catch (SQLException e) {
        // log some useful data here
    }
    return false;
}

First, the method checks the connection status. Second, it tries to run the validation query returning true when succeeded. Finally, it returns false if the validation query doesn’t run or fails.

Now we can use the custom validation before running any statement:

public static void runIfConnectionValid(Connection connection)
{
    if (isConnectionValid(connection)) {
        // run sql statements
    }
    else {
        // handle invalid connection
    }
}

Certainly, running a simple query is a good choice to validate database connectivity. However, there are other useful methods depending on the target driver and database:

• Auto-Commit – using connection.getAutocommit() and connection.setAutocommit()
• Metadata – using connection.getMetaData()

4. Connection Pooling

Database connections are expensive in terms of resources. Connection pooling is a good strategy to manage and configure these connections. In short, they can reduce the costs of connection life cycles.

All Java connection pooling frameworks have their own connection validation implementation. Additionally, most of them use a parametrizable validation query.

Here are some of the most popular frameworks:

• Apache Commons DBCP – validationQuery, validationQueryTimeout
• Hikari CP – connectionTestQuery, validationTimeout
• C3P0 – preferredTestQuery

5. Conclusions

In this article, we had a look at the basics of the JDBC Connection status. We reviewed some helpful methods of the Connection class. After that, we described some alternatives to validate connections before running SQL statements.