Transactions using Groovy.SQL with Spring annotations and connection pools

When using Groovy with Spring framework,  interacting with the database can be done using the Groovy.SQL class which provides a easy to use interface. When using Groovy.SQL, if we have a need to do transactions, we have the .withTransaction method that accepts a closure, to which we can pass in code to execute within the transaction. In our project since we were using spring already, using annotations to define transactions would be a great. Standard @Transactional annotations with Groovy.SQL will not work, since every place where the Groovy.SQL is used a new connection is acquired from the connection pool causing the database work to span multiple connections, which can result in dead-locks on the database. What we really want is that the database connection be the same across all invocations of Groovy.SQL with-in the same transaction started by the annotated method. This series of spring configuration helped us get the right behavior. Setup annotations for spring using.


Setup connection pooling using the the BoneCP connection pool library, note that we have set autoCommit to false, since autoCommit is true by default.

<bean id="datasource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
	<property name="driverClass" value="${db.driver}"/>
	<property name="jdbcUrl" value="${db.url}"/>
	<property name="username" value="${db.user}"/>
	<property name="password" value="${db.passwd}"/>
	<property name="defaultAutoCommit" value="false"/>

Setup annotation driven transaction manager and ensure that proxy-target-class is true.

<tx:annotation-driven transaction-manager="txManager" proxy-target-class="true"/>

Next setup TransactionAwareDataSourceProxy using the datasource, previously defined. The transactionAwareDataSourceProxy is named datasourceProxy here.

<bean id="datasourceProxy" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
	<constructor-arg ref="datasource"/>

Next pass the datasourceProxy to the DataSourceTransactionManager

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<property name="dataSource" ref="datasourceProxy"/>

This datasourceProxy can now be used to setup Groovy.SQL.

<bean id="sql" class="groovy.sql.Sql">
	<constructor-arg ref="datasourceProxy"/>

This sql bean can now be autowired inside any class to be used by read or write methods and with annotations so that we can have transactions.

``` Java Annotation based transactions in Groovy and Spring class ProductGateway { @Autowired Sql sql List findBy(productId) { def productRows = sql.rows(….) // retrieve and return list of products // handle exceptions }

@Transactional int create(product) { // Create rows, update data under transaction sql.executeInsert(…) sql.executeInsert(…) // handle exceptions } } ```

Edit: Changed SQLUtil to Sql to clarify example, based on feedback from @robpatrick