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.
1 2 3 4 5 6 7
Setup annotation driven transaction manager and ensure that proxy-target-class is true.
Next setup TransactionAwareDataSourceProxy using the datasource, previously defined. The transactionAwareDataSourceProxy is named datasourceProxy here.
1 2 3
Next pass the datasourceProxy to the DataSourceTransactionManager
1 2 3
This datasourceProxy can now be used to setup Groovy.SQL.
1 2 3
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Edit: Changed SQLUtil to Sql to clarify example, based on feedback from @robpatrick