检测JDBC连接中尚未提交的打开事务。

Question

如何检测事务是否仍然处于打开状态，在JDBC COMMIT或JDBC 连接上仍然处于挂起状态？

我要通过连接池获取连接对象。所以我想在使用之前检查连接的状态。

使用Postgres 9.x和Java 8。

Answer 1

我不知道使用标准JDBC方法在Connection上检测当前事务状态的任何方法。

但是，具体来说，对于PostgreSQL，有AbstractJdbc2Connection.getTransactionState()，您可以将其与常量ProtocolConnection.TRANSACTION_IDLE进行比较。PostgreSQL的JDBC4 Connection扩展了这个类，因此您应该能够转换您的Connection来访问该属性。

该常量是pgjdbc驱动程序源代码中定义的三个值之一。

/**
 * Constant returned by {@link #getTransactionState} indicating that no
 * transaction is currently open.
 */
static final int TRANSACTION_IDLE = 0;

/**
 * Constant returned by {@link #getTransactionState} indicating that a
 * transaction is currently open.
 */
static final int TRANSACTION_OPEN = 1;

/**
 * Constant returned by {@link #getTransactionState} indicating that a
 * transaction is currently open, but it has seen errors and will
 * refuse subsequent queries until a ROLLBACK.
 */
static final int TRANSACTION_FAILED = 2;

Answer 2

据我所知，您使用的是普通JDBC，这就是您有此问题的原因。因为您了解了Tomcat的JDBC连接池，所以可以使用JDBCInterceptor.invoke()，在这里您可以跟踪每个Connection发生了什么。更多细节这里。

Answer 3

接受答案 by 埃尼是正确的。

示例代码

这个答案发布了一个助手类的源代码。这个源代码是基于想法，如果接受的答案。

package com.powerwrangler.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.slf4j.LoggerFactory;

/**
 *
 * Help with database chores.
 *
 * © 2015 Basil Bourque 
 * This source code available under terms of the ISC License. http://opensource.org/licenses/ISC
 *
 * @author Basil Bourque.
 *
 */
public class DatabaseHelper
{

    static final org.slf4j.Logger logger = LoggerFactory.getLogger( DatabaseHelper.class );

    public enum TransactionState
    {

        IDLE,
        OPEN,
        FAILED;

    }

    /**
     * If using the Postgres database, and the official "org.postgresql" JDBC driver, get the current state of the
     * current transaction held by a Connection. Translate that state to a convenient Enum value.
     *
     * @param connArg
     *
     * @return DatabaseHelper.TransactionState
     */
    public DatabaseHelper.TransactionState transactionStateOfConnection ( Connection connArg ) {
        // This code is specific to Postgres.
        // For more info, see this page on StackOverflow:  https://stackoverflow.com/q/31754214/642706

        // Verify arguments.
        if ( connArg == null ) {
            logger.error( "Received null argument for Connection object. Message # 6b814e3c-80e3-4145-9648-390b5315243e." );
        }

        DatabaseHelper.TransactionState stateEnum = null;  // Return-value.

        Connection conn = connArg;  // Transfer argument to local variable.

        // See if this is a pooled connection.
        // If pooled, we need to extract the real connection wrapped inside.
        // Class doc: http://docs.oracle.com/javase/8/docs/api/javax/sql/PooledConnection.html
        // I learned of this via the "Getting the actual JDBC connection" section of the "Tomcat JDBC Connection Pool" project.
        // Tomcat doc: https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Getting_the_actual_JDBC_connection
        if ( conn instanceof javax.sql.PooledConnection ) {
            javax.sql.PooledConnection pooledConnection = ( javax.sql.PooledConnection ) conn;
            try { // Can throw java.sql.SQLException. So using a Try-Catch.
                // Conceptually we are extracting a wrapped Connection from with in a PooledConnection. Reality is more complicated.
                // From class doc: Creates and returns a Connection object that is a handle for the physical connection that this PooledConnection object represents.
                conn = pooledConnection.getConnection();
            } catch ( SQLException ex ) {
                // We could just as well throw this SQLException up the call chain. But I chose to swallow it here. --Basil Bourque
                logger.error( "Failed to extract the real Connection from its wrappings in a PooledConnection. Message # ea59e3a3-e128-4386-949e-a70d90e1c19e." );
                return null; // Bail-out.
            }
        }

        // First verify safe to cast.
        if ( conn instanceof org.postgresql.jdbc2.AbstractJdbc2Connection ) {
            // Cast from a generalized JDBC Connection to one specific to our expected Postgres JDBC driver.
            org.postgresql.jdbc2.AbstractJdbc2Connection aj2c = ( org.postgresql.jdbc2.AbstractJdbc2Connection ) conn; // Cast to our Postgres-specific Connection.

            // This `getTransactionState` method is specific to the Postgres JDBC driver, not general JDBC.
            int txnState = aj2c.getTransactionState();
            // We compare that state’s `int` value by comparing to constants defined in this source code:
            // https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/ProtocolConnection.java#L27
            switch ( txnState ) {
                case org.postgresql.core.ProtocolConnection.TRANSACTION_IDLE:
                    stateEnum = DatabaseHelper.TransactionState.IDLE;
                    break;

                case org.postgresql.core.ProtocolConnection.TRANSACTION_OPEN:
                    stateEnum = DatabaseHelper.TransactionState.OPEN;
                    break;

                case org.postgresql.core.ProtocolConnection.TRANSACTION_FAILED:
                    stateEnum = DatabaseHelper.TransactionState.FAILED;
                    break;

                default:
                    // No code needed.
                    // Go with return value having defaulted to null.
                    break;
            }
        } else {
            logger.error( "The 'transactionStateOfConnection' method was passed Connection that was not an instance of org.postgresql.jdbc2.AbstractJdbc2Connection. Perhaps some unexpected JDBC driver is in use. Message # 354076b1-ba44-49c7-b987-d30d76367d7c." );
            return null;
        }
        return stateEnum;
    }

    public Boolean isTransactionState_Idle ( Connection connArg ) {
        Boolean b = this.transactionStateOfConnection( connArg ).equals( DatabaseHelper.TransactionState.IDLE );
        return b;
    }

    public Boolean isTransactionState_Open ( Connection conn ) {
        Boolean b = this.transactionStateOfConnection( conn ).equals( DatabaseHelper.TransactionState.OPEN );
        return b;
    }

    public Boolean isTransactionState_Failed ( Connection conn ) {
        Boolean b = this.transactionStateOfConnection( conn ).equals( DatabaseHelper.TransactionState.FAILED );
        return b;
    }

}

示例用法：

if ( new DatabaseHelper().isTransactionState_Failed( connArg ) ) {
    logger.error( "JDBC transaction state is Failed. Expected to be Open. Cannot process source row UUID: {}. Message # 9e633f31-9b5a-47bb-bbf8-96b1d77de561." , uuidOfSourceRowArg );
    return null; // Bail-out.
}

在Project中包含JDBC驱动程序，但在构建中省略

这段代码面临的挑战是，在编译时，我们必须处理特定于特定的JDBC驱动程序接口的类，而不是广义JDBC接口。

您可能会想，“好了，只需将JDBC驱动程序jar文件添加到项目中即可”。但是，不，在web应用程序Servlet环境中，我们不能在构建中包括JDBC驱动程序(我们的WAR文件/文件夹 )。在web应用程序中，技术问题意味着我们应该将JDBC驱动程序存放在Servlet容器中。对我来说，这意味着Apache将JDBC驱动程序jar文件放在Tomcat自己的/lib文件夹中，而不是放在web应用程序的WAR文件/文件夹中。

那么，如何在编译时将JDBC驱动程序jar包含在我们的项目中，同时将其排除在WAR文件构建之外？看这个问题，在基于NetBeans Maven的项目中，在编程和编译时包括一个库，但将其排除在构建之外。。Maven中的解决方案是scope标记，其值为provided。

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4-1201-jdbc41</version>
    <scope>provided</scope>
</dependency>