Using The JDBC Connection in Java

The database connection obtained with the JDBC Get Connection activity has the following restrictions:

  • Checkpoints should not be taken after a JDBC Get Connection activity completes.

  • Do not pass the JavaConnectionAccessor object reference to a subprocess that spawns a new process.

The JDBC Get Connection activity creates a JavaConnectionAccessor object reference that can be passed to an input parameter of a Java Code or Java Method activity. For more information about how to pass Java object references between activities, see Passing Java Objects Between Java Code Activities.

JavaConnectionAccessor has the following methods:

  • java.sql.Connection getDBConnection(); — retrieves a database connection from the connection pool.

  • void releaseConnection(); — releases a database connection so that it can return to the connection pool.

  • boolean isTransactional(); — returns true if the activity using the JDBC connection is in a transaction group and the Override Transaction Behavior on the Configuration tab of the JDBC Get Connection activity is unchecked.

  • void releaseConnection(SQLException ex) — releases the connection obtained by way of the getDBConnection() method. Use this method instead of the standard releaseConnection method because this method detects if the SQL Exception passed as input is due to a connection problem. If the connection is no longer usable, it is destroyed and it is not returned to the connection pool.

When the connection is part of a transaction, the JavaConnectionAccessor object prevents you from committing or rolling back the transaction. The transaction completes when the transaction group either commits or rolls back. If the connection is part of an XA transaction group, the enlist and delist operations are performed by the getDBConnection and releaseConnection methods.

When the connection is not part of a transaction, the database connection is in auto commit mode, where each operation is committed as it is performed. You can disable the auto commit mode, if desired, and your code is then responsible for performing the commit or rollback operation before releasing the connection.

Warning: If your Java code does not release the connection, the connection is automatically released when the process instance terminates. This could have undesirable consequences.

Your code should check for SQL Exceptions returned when executing JDBC statements. If you encounter an unexpected SQL Exception, you can use the releaseConnection(SQLException ex) method to release the connection. This allows you to check if the connection is no longer valid. If the connection is no longer valid, your Java code should return an appropriate exception for subsequent activities so that transactions can be rolled back, if necessary.