I. Overview

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

We will also learn how to verify the connection before running the SQL statement .

2. JDBC connection

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

• Database server is down

• Internet connection

• Reuse closed connections

Running any database operations while the connection is lost will resultSQLException . Also, we can check for exceptions to get details about the problem.

3. Check the connection

There are different ways to check the connection. We'll look at these methods to decide when to use them.

3.1. Connection Status

We can use isClosed()methods to check the Connectionstatus . Using this method, SQL operations cannot be granted. However, it would be helpful to check if the connection is open.

Let's create a status condition before running the SQL statement:

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

3.2. Connection verification

Even if the connection is open, it may be lost for the reasons described in the previous section. Therefore, it may be necessary to verify the connection before running any SQL statements .

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

Let's see how to verify 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, timeout5 seconds. A value of zero indicates that the timeout does not apply to authentication. On the other hand, values less than zero will throw SQLException.

3.3. Custom Validation

There are good reasons for creating custom validation methods . For example, we can use legacy JDBC without authentication methods. Likewise, our project may require custom validation queries to be 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 on success true. Finally, returns if the validation query did not run or failed false.

Now we can use custom validation before running any statement:

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

Of course, running a simple query is a good option to verify database connectivity. However, depending on the target driver and database, there are other useful methods :

• Auto Commit – using connection.getAutocommit()andconnection.setAutocommit()

• Metadata - useconnection.getMetaData()

4. Connection pool

Database connections are expensive in terms of resources. Connection pools are a good strategy for managing and configuring these connections. .In short, they reduce the cost of connection lifetime.

All Java connection pooling frameworks have their own implementation of connection validation. Also, most of them use parameterizable validation queries.

Here are some of the most popular frameworks:

• Apache Commons DBCP -validationQuery, validationQueryTimeout

• Hikari CP——connectionTestQuery, validationTimeout

• C3P0 – preferredTestQueryTest query

5 Conclusion

In this article, we learned the basics of JDBC connection state. We reviewed Connectionsome useful methods of classes. After that, we describe some alternatives for validating the connection before running the SQL statement.