Transaction Problem

[tripperm]

I am having some issues using Spring's declarative transaction features. I've created what I thought would be a farily simple example just to get my head around some of Spring's features. But I can't seem to get the transaction manager to work correctly.

Here is my Spring setup:

    <!-- struts actions -->
    <bean name="/getAccounts" class="com.pdsisoft.GetAccountsAction">
        <property name="account">
            <ref bean="account"/>
        </property>
    </bean>
    
    <!-- Business Tier Objects -->
    <bean id="accountTarget" class="com.pdsisoft.AccountImpl">
        <property name="dataSource">
            <ref local="dataSource"/>
        </property>
    </bean>
    <bean id="account" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
        <property name="transactionManager">
            <ref bean="transactionManager"/>
        </property>
        <property name="target">
            <ref local="accountTarget"/>
        </property>
        <property name="transactionAttributes">
            <props>
                <prop key="updateTestTables*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED</prop>
            </props>
        </property>
    </bean>

    <!-- Our transaction manager -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource">
            <ref local="dataSource"/>
        </property>
    </bean>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName">
            <value>com.informix.jdbc.IfxDriver</value>
	</property>
	<property name="url">
            <value>&#91;ommitted&#93;</value>
	</property>
	<property name="username">
            <value>&#91;ommitted&#93;</value>
	</property>
	<property name="password">
            <value>&#91;ommitted&#93;</value>
	</property>
    </bean>

My code for the Account business object has three methods that are fairly trivial:

    public void updateTestTables() {
        this.updateTestTableOne();
        this.updateTestTableTwo();
        int i = 5 / 0;
    }
    
    public void updateTestTableOne() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("update test_tbl set test_dt = ? where test_no_in = 1");
            ps.setTimestamp(1, new java.sql.Timestamp( System.currentTimeMillis() ));
            ps.executeUpdate();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try { ps.close(); }catch(Exception d){};
            try { conn.close(); }catch(Exception d){};
        }
    }
    
    public void updateTestTableTwo() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("update test2_tbl set test_dt = ? where test_no_in = 1");
            ps.setTimestamp(1, new java.sql.Timestamp( System.currentTimeMillis() ));
            ps.executeUpdate();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            try { ps.close(); }catch(Exception d){};
            try { conn.close(); }catch(Exception d){};
        }
    }

I have thus wrapped updateTestTables in a transaction, and would expect the two table updates to be rolled back when I purposefully throw the ArithmaticException as my final line of code in that method. But both updates go through and are not rolled back when the exception is thrown.

I then tried modifying the TransactionProxyFactory to this:

    <bean id="account" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
        <property name="transactionManager">
            <ref bean="transactionManager"/>
        </property>
        <property name="target">
            <ref local="accountTarget"/>
        </property>
        <property name="transactionAttributes">
            <props>
                <prop key="updateTestTables*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED</prop>
                <prop key="updateTestTableOne*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED</prop>
                <prop key="updateTestTableTwo*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED</prop>
            </props>
        </property>
    </bean>

But it still did not work. Interestingly enough the following output is generated from Tomcat when I run the test:

May 23, 2005 6:00:23 PM org.springframework.jdbc.datasource.JdbcTransactionObjec
tSupport <clinit>
INFO&#58; JDBC 3.0 Savepoint class is available
May 23, 2005 6&#58;00&#58;23 PM org.apache.struts.action.RequestProcessor processExcepti
on
WARNING&#58; Unhandled Exception thrown&#58; class java.lang.ArithmeticException

This would seem to suggest to me that the Transaction is being created before my exception is purposefully thrown, but for some reason the updates are not being rolled back.

Any help would be appreciated.

Thanks

[Lawrence Ho]

Time to let go the habit of dataSource.getConnection()!

You can read more about how to let Spring to handle all these using DataSourceUtils, just scroll down to near the end of section 7.2:
http://static.springframework.org/sp...n.html#d0e4501

Of course, it is even easier to use Spring JDBC abstraction, i.e. JdbcTemplate:
http://static.springframework.org/sp...ence/jdbc.html

Lawrence

[tripperm]

That did the trick! I used the DataSourceUtils class and all my connections were enlisted in the transaction and rolled back successfully. Since I will be using the JDBCTemplate class, which hides all of this connection retrieval and releasing for you, this wont be a problem. But its good to know how to use it just in case.

Thanks again.