Use of TransactionTemplate with autocommit on in Spring 2.5 (JDK 1.4)

[Jacob Tom]

Hi,

Please find my code components below -

1) Spring config xml contents -

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
	<bean id="testDataSource" class="oracle.jdbc.pool.OracleDataSource"
		destroy-method="close">
		
		<property name="URL">
			<value>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.x)(PORT=1521)))(CONNECT_DATA=(service_name=test)))</value>
		</property>
		<property name="user">
			<value>test</value>
		</property>
		<property name="password">
			<value>test</value>
		</property>
		<property name="driverType">
			<value>oracle.jdbc.driver.OracleDriver</value>
		</property>
	</bean>
	<bean id="testBatch"
		class="com.springbatch.TestBatch"
		factory-method="getInstance">
		<property name="dataSource" ref="testDataSource" />
	</bean>
	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
                  <property name="dataSource" ref="testDataSource"/>
             </bean>
    
             <bean id="txTemplate" class="org.springframework.transaction.support.TransactionTemplate">
                  <property name="transactionManager" ref="txManager"/>
             </bean>
</beans>	

2) Test Java class -

public class TestBatch {
	private JdbcTemplate jdbcTemplate;

    private static TestBatch instance;
    
    public void setDataSource(DataSource dataSource) throws SQLException
    {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        
    }
    
    private TestBatch()
    {

    }
    
    public static synchronized TestBatch getInstance()
    {
        if (instance == null)
            instance = new TestBatch();
        return instance;

    }
    
    private int[] insertUsers(final List userLst, TransactionTemplate template)
    {
    	int[] totalRows = null;
    	try{
    		totalRows = (int[]) template.execute(new TransactionCallback()
            {
                public Object doInTransaction(
                    TransactionStatus transactionStatus)
                {
                	int[] totalRows = jdbcTemplate.batchUpdate(
                            "insert into USER (USER_ID,CREATE_TIMESTAMP, UPDATE_TIMESTAMP) values (?,systimestamp,systimestamp)",
                            new BatchPreparedStatementSetter()
                            {
                                public void setValues(PreparedStatement preparedStatement,
                                    int paramInt) throws SQLException
                                {
                                    String userid = (String)userLst.get(paramInt);
                                    preparedStatement.setString(1, userid);
                                    
                                }

                                public int getBatchSize()
                                {
                                    return userLst.size();
                                }

                            });
                	return totalRows;
                }
            });   
    	
    	System.out.println("Total rows inserted -> "+totalRows.length);
    	}catch (Exception e){
    		e.printStackTrace();
    	}
    	
    	return totalRows;
    }
	
	private int[] insertUsers(final List userLst)
    {
    	int[] totalRows = null;
    	try{
    		
    	totalRows =
            jdbcTemplate.batchUpdate(
                "insert into USER (USER_ID,CREATE_TIMESTAMP, UPDATE_TIMESTAMP) values (?,systimestamp,systimestamp)",
                new BatchPreparedStatementSetter()
                {
                    public void setValues(PreparedStatement preparedStatement,
                        int paramInt) throws SQLException
                    {
                        String userid = (String)userLst.get(paramInt);
                        preparedStatement.setString(1, userid);
                        
                    }

                    public int getBatchSize()
                    {
                        return userLst.size();
                    }

                });
    	
    	System.out.println("Total rows inserted -> "+totalRows.length);
    	}catch (Exception e){
    		e.printStackTrace();
    	}
    	
    	return totalRows;
    }
    
    
    private void deleteUsers(final List userLst, TransactionTemplate template)
    {
        
    	try{
    		
    			
    	int[] totalRows =
            jdbcTemplate.batchUpdate(
                "delete from USER where USER_ID in (?) ",
                new BatchPreparedStatementSetter()
                {
                    public void setValues(PreparedStatement preparedStatement,
                        int paramInt) throws SQLException
                    {
                        String userid = (String)userLst.get(paramInt);
                        preparedStatement.setString(1, userid);
                        
                    }

                    public int getBatchSize()
                    {
                        return userLst.size();
                    }

                });
    	
    	System.out.println("Total rows inserted -> "+totalRows.length);
    	}catch (Exception e){
    		e.printStackTrace();
    	}
    }
    
    public static void main (String args[]){
    	
    	ApplicationContext context =
            new ClassPathXmlApplicationContext("application-config.xml");
    	final TestBatch batch = TestBatch.getInstance();
    	final List userLst = new ArrayList();
    	userLst.add("TEST1");
    	userLst.add("TEST2");
    	userLst.add("TEST3");
    	final TransactionTemplate template = (TransactionTemplate)context.getBean("txTemplate");
    	
    	//batch.insertUsers(userLst,template);
    	
    	template.execute(new TransactionCallback() {
			public Object doInTransaction(
					TransactionStatus transactionStatus) {

				return batch.insertUsers(userLst);

			}
		});  	
    	
    	//batch.deleteUsers(userLst,template);
    }
}

I have following 3 methods -

1) insertUsers (List, TransactionTemplate)
2) insertUsers (List)
3) deleteUsers (List)

The first "insertUsers" method encompasses the call to "jdbcTemplate.batchUpdate" within the TransactionTemplate but in
case of second "insertUsers" method, the call to the method is encompassed within the TransactionTemplate as can be seen
in the "main" method.

In case of any exception, the first "insertUsers" method rollsback all INSERTS while second "insertUsers" method
does not i.e. all INSERTS done before exception get commited.

I am using JDBCTemplate.batchUpdate(String sql, BatchPreparedStatementSetter setter) to perform a batch INSERT operation in Oracle 11g database.
I believe the connection has 'autocommit' set to true by default.

Could you please advice the reason for the 2 behaviours?

Regards,
Jacob