Grails transactions (not GORM based but using Groovy Sql)

My Grails application is not using GORM but instead uses my own SQL and DML code to read and write the database (The database is a huge normalized legacy one and this was the only viable option).

So, I use the Groovy Sql Class to do the job. The database calls are done in Services that are called in my Controllers.

Furthermore, my datasource is declared via DBCP in Tomcat - so it is not declared in Datasource.groovy.

My problem is that I need to write some transaction code, that means to open a transaction and commit after a series of successful DML calls or rollback the whole thing back in case of an error.

I thought that it would be enough to use groovy.sql.Sql#commit() and groovy.sql.Sql#rollback() respectively.

But in these methods Javadocs, the Groovy Sql documentation clearly states

If this SQL object was created from a DataSource then this method does nothing.

So, I wonder: What is the suggested way to perform transactions in my context? Even disabling autocommit in Datasource declaration seems to be irrelevant since those two methods "...do nothing"

Solution

Thanks James. I also found the following solution, reading http://grails.org/doc/latest/guide/services.html:

I declared my service as transactional

static transactional = true

This way, if an Error occurs, the previously performed DMLs will be rolled back.

For each DML statement I throw an Error describing the message. For example:

try{
    sql.executeInsert("""
       insert into mytable1 (col1, col2) values (${val1}, ${val2})
    """)
catch(e){
    throw new Error("you cant enter empty val1 or val2")
}

try{
    sql.executeInsert("""
       insert into mytable2 (col1, col2) values (${val1}, ${val2})
    """)
catch(e){
    throw new Error("you cant enter empty val1 or val2. The previous insert is rolledback!")
}

Final gotcha! The service when called from the controller, must be in a try catch, as follows:

try{
    myService.myMethod(params)
}catch(e){
    //http://jts-blog.com/?p=9491
    Throwable t = e instanceof UndeclaredThrowableException ? e.undeclaredThrowable : e
    // use t.toString() to send info to user (use in view)
    // redirect / forward / render etc
}