Transaction handling with JDBC Connection Pooling in Tomcat7

Question

I am developing a JSF application which uses JNDI connection pooling to a DB2 database through the Tomcat 7 configuration. I am having problems managing transactions and more specifically rolling back when an exception is thrown from a method which shares a connection.

The following code establishes a connection and passes this down through all other methods being called. This connection is then only closed in the finally block of the method. My issue is that for example...if an exception is thrown at any point in the method, any work completed prior to this is committed and not rolled back. I was under the impression that when closing a connection in the finally block, this would not commit if any exceptions were thrown during the life time of this connection and then the connection is returned to the pool regardless if the work has been committed or rolled back.

public boolean updateSubCountry(SubCountryDTO dto,
        List<FeatureAliasDTO> aliases, List<FeatureCodeDTO> codes)
        throws ReferenceDataException {
    boolean success = true;
    Connection c = null;
    try {
        c = fDao.getConnection();
        success = fDao.updateSubCountry(c, dto, TEMP_USERNAME) && success;
        success = updateAliases(c, aliases, dto.getOdi(), TEMP_USERNAME,
                dto.getSubCountryId()) && success;
        for (FeatureCodeDTO codeDto : codes) {
            success = updateFeatureCode(c, codeDto, dto.getSubCountryId())
                    && success;
        }
    } finally {
        closeConnection(c, DEFAULT_CONNECTION_ERROR_MSG);
    }
    return success;
}

My JNDI configuration is as follows (sensitive info starred out):

<Resource name="jdbc/core" auth="Container" type="javax.sql.DataSource"
         url="****"
         driverClassName="com.ibm.db2.jcc.DB2Driver"
         username="****" password="****"
         maxActive="20" maxIdle="3" maxWait="10000"
         poolPreparedStatements="true"
         maxOpenPreparedStatements="100"
         validationQuery="SELECT 1 FROM SYSIBM.SYSDUMMY1" />

Any help is appreciated.

Thanks.

UPDATE

I have attempted to implement the suggestions made by @Tiny. However, the when purposely making updateFeatureCode() method throw an exception on the database level, any work before this is still not rolled back?

@Transactional (rollbackFor={Exception.class, ReferenceDataException.class})
    public boolean updateSubCountry(SubCountryDTO dto,
            List<FeatureAliasDTO> aliases, List<FeatureCodeDTO> codes)
            throws ReferenceDataException {
        boolean success = true;
        Connection c = null;
        try {
            c = fDao.getConnection();
            success = fDao.updateSubCountry(c, dto, TEMP_USERNAME) && success;
            success = updateAliases(c, aliases, dto.getOdi(), TEMP_USERNAME,
                    dto.getSubCountryId()) && success;
            for (FeatureCodeDTO codeDto : codes) {
                success = updateFeatureCode(c, codeDto, dto.getSubCountryId())
                        && success;
            }
        } finally {
            closeConnection(c, DEFAULT_CONNECTION_ERROR_MSG);
        }
        return success;
    }

Answer 1

The IBM JDBC driver, when connected to a DB2 database, defaults to autoCommit=true, which means that an explicit COMMIT is issued after each statement. If you wish to control transactions yourself, disable autocommit and commit or rollback as necessary. Alternatively, as suggested by @Tiny, use a transaction manager.