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.
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