The answer from heenenee is correct.

Sample code

This answer hosts the source code for the helper class. This source code is based on ideas if this is the accepted answer.

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;
    }

}

      

        
        
        
      

    

Usage example:

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

      

        
        
        
      

    

Include JDBC driver in project but omit from assembly

The problem with this code is that at compile time we have to refer to classes specific to a particular JDBC driver , not generic JDBC interfaces .

You might be thinking, "Okay, just add the JDBC driver JAR file to the project." But no, in a Servlet web application environment, we shouldn't include the JDBC driver in our assembly (our WAR file / folder ). In a web application, technical problems mean that we have to put our JDBC driver in a Servlet container. For me this means Apache Tomcat where we put the JDBC driver jar file in its own /lib

Tomcats folder and not in our WAR file / WAR folder for web applications.

So how do I include the JDBC JAR driver in our project at compile time, excluding our WAR file from the build? See this question, Include the library at programming and compile time, but exclude from assembly in a NetBeans Maven project . The solution in Maven is a tag scope

with a value provided

.

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