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.

<mvc:annotation-driven/>

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"/>
</bean>

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"/>
</bean>

Next pass the datasourceProxy to the DataSourceTransactionManager

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

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

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

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

Share