JDBC Connection Status – JDBC连接状态

最后修改: 2022年 2月 7日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

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.

在这篇文章中，我们将讨论JDBC连接状态的一些方面。首先，我们将看到连接丢失的最常见原因。然后，我们将学习如何确定连接状态。

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

我们还将学习如何在运行SQL语句前验证连接。

2. JDBC Connection

2.JDBC连接

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

Connection类负责与数据源进行通信。连接可能因各种原因而丢失。

  • 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.

在连接丢失时运行任何数据库操作都会导致SQLException。此外，我们可以检查异常，了解问题的细节。

3. Checking the Connection

3.检查连接情况

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

3.1.连接状态

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.

我们可以使用isClosed()方法检查Connection状态。通过这个方法，不能授予SQL操作。然而，检查连接是否被打开是很有帮助的。

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

让我们在运行SQL语句之前创建一个状态条件。

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

3.2.连接验证

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.

即使连接被打开，也可能因为上一节所述的原因而丢失。因此，可能需要在运行任何SQL语句之前验证连接。

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.

从1.6版本开始，连接类提供了一个验证方法。首先，它向数据库提交一个验证查询。其次，它使用timeout参数作为操作的阈值。最后，如果操作在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.

在这种情况下，timeout是5秒。值为0表示超时不适用于验证。另一方面，一个小于零的值将抛出一个SQLException。

3.3. Custom Validation

3.3.自定义验证

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.

有很好的理由来创建一个自定义的验证方法。例如，我们可能正在使用一个没有验证方法的传统JDBC。同样，我们的项目可能需要一个自定义的验证查询，在所有语句之前运行。

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.

首先，该方法检查连接状态。其次，它尝试运行验证查询，如果成功，则返回true。最后，如果验证查询没有运行或失败，则返回false。

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

4.连接池

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.

就资源而言，数据库连接是很昂贵的。C连接池是管理和配置这些连接的良好策略。简而言之，它们可以降低连接生命周期的成本。

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

所有Java连接池框架都有自己的连接验证实现。此外，它们中的大多数都使用了一个可参数化的验证查询。

Here are some of the most popular frameworks:

以下是一些最受欢迎的框架。

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

5. Conclusions

5.结论

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.

在这篇文章中，我们看了一下JDBC连接状态的基本知识。我们回顾了Connection类的一些有用方法。之后，我们描述了一些在运行SQL语句前验证连接的替代方法。

As usual, all the code samples shown in this article are available over on GitHub.

像往常一样，本文中显示的所有代码样本都可以在GitHub上找到。