How to rollback transaction in groovy

Question

I can't use @Transactional annotation in my TestCase. I've got workaround - using directly TransactionalManager. Unfortunately when I'm creating Sql object in groovy based on DataSource from SpringContext and then insert a row to the database it doesn't rollback.

@ContextConfiguration(locations = [ "../dao/impl/ibatis/spring-data-context-config.xml"])
@RunWith(SpringJUnit4ClassRunner.class)
public class OrganizationTest {

@Autowired
DataSource dataSource;

@Autowired
DataSourceTransactionManager transactionManager;

private TransactionStatus transactionStatus;

@Before
public void setUp() {
    transactionStatus = transactionManager.getTransaction(new DefaultTransactionDefinition());
}
@After
public void tearDown() {
    transactionManager.rollback(transactionStatus);
    transactionStatus = null;
}


@Test
public void shallObtainSequenceNo() throws Exception {

    Connection connection = dataSource.getConnection();
    connection.setAutoCommit(false);
    Sql sql = new Sql(dataSource);

    //given
    Organization organization = new Organization("KongregatzionIX", "bisut000000000000001");
    //when
    organization.insert(sql);
    //then
    assertNotNull(organization.getId());
    }
}

The SQL query looks like this:

public class Organization {

String name;
String id;
String parentId;

Organization(String name, String parentId){
    this.name = name;
    this.parentId = parentId;
}

public void insert(Sql sql){
    String createdBy = GlobalConstant.SABA_ADMIN_ID.getValue();
    String updatedBy = GlobalConstant.SABA_ADMIN_ID.getValue();
    String companyType = "2";
    String flags = "1000000000";

    id = sql.firstRow( "select 'bisut' || LPAD(TPT_COMPANY_SEQ.NEXTVAL,  15, '0') as id from dual ").id;

    def timeStamp = sql.firstRow("select  to_char(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF') as ts FROM DUAL ").ts;
    def nameIns = name;
    def today = new java.sql.Date(new Date().getTime());
    sql.executeInsert('''
                INSERT INTO TPT_COMPANY(ID, TIME_STAMP, CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON, CI_NAME, NAME, CI_NAME2, NAME2, COMPANY_TYPE, FLAGS, PARENT_ID)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
                ''' ,
                [id, timeStamp, createdBy, today, updatedBy, today, nameIns.toLowerCase(), nameIns, nameIns.toLowerCase(), nameIns, companyType, flags, parentId]);
 }
}

Of course I want to set transaction that spans all over tested method.

// EDIT

I cannot answer because of too small reputation but TransactionAwareDataSourceProxy is what I've been looking for.

Answer 1

OK, for anyone reading this who has been banging their head against the keyboard, get ready to scream. Here it is: A Groovy Sql object created using a DataSource, as in:

DataSource dsobj = null;
Sql sqlobj = null;

try{
  dsobj = (...get your DataSource obj...);
  sqlobj = new Sql(dsobj);
} catch (...) {...}

will of course get you a valid Sql object that you can run commands with, etc., but you'll get no transaction support. HOWEVER...

If you create a Sql object using a Connection object, you will get that transaction support. Now, why this is, I have no idea, since you can supposedly access the Connection object contained in the DataSource object with: sqlobj.getDataSource().getConnection(). But YET, if you try to do this:

sqlobj.getDataSource().getConnection().setAutoCommit(false);

it will have zero effect. Any commands you run will be automatically committed whether you like it or not. So likewise, calling:

sqlobj.getDataSource().getConnection().rollback();

will do nothing.

So what do you do if you have to get DB connections from your application server because you have organizational standards, for example, that insist that you get them from an app server-managed connection pool with data sources defined by the server admin? Happens a lot for people developing apps for such common app servers like IBM WAS, etc. So, no going off and using basic JDBC code to create connections with hard-coded references of this and that when you can use just one hard-coded or better yet, property-file-stored or database-stored name of a JNDI data source. What ya gonna do now? You seem to HAVE to use a DataSource object and not enjoy the most common and valuable feature of any modern RDBMS, the transaction.

Here's the work-around, and here's where you really need to get ready to scream, because it's totally non-sensical, but it works. You need to create a DataSource object as above but then use its Connection object in the Sql object's constructor rather than the DataSource object. As ridiculous as this is, it's the fix. Example:

DataSource dsobj = null;
Sql sqlobj = null;

try{
  dsobj = (...get your DataSource obj...);
  sqlobj = new Sql(dsobj.getConnection());
} catch (...) {...}

Now, you'd use sqlobj to do your transaction-sensitive stuff. You can set AutoCommit to false [sqlobj.getConnection().setAutoCommit(false)], run your updates, then do sqlobj.getConnection().commit() or sqlobj.getConnection().rollback() as needed.

It also seems dsobj can go out of direct scope of sqlobj and sqlobj will still function. I speculate that the VM is smart enough to keep sqlobj alive as long as sqlobj is in scope. That makes sense, given that dsobj's Connection object is undoubtedly treated as a server-managed resource and so any object that has it attached to it is kept alive until all other objects referencing the enclosing subscribing object (i.e., sqlobj) are finally out of scope. Just a guess.

So, where is the smoking gun over this issue? It's here:

http://groovy.codehaus.org/api/groovy/sql/Sql.html#commit()

I quote:

commit public void commit() throws java.sql.SQLExceptionIf this SQL object was created with a Connection then this method commits the connection. If this SQL object was created from a DataSource then this method does nothing. Throws: java.sql.SQLException - if a database access error occurs

rollback public void rollback() throws java.sql.SQLExceptionIf this SQL object was created with a Connection then this method rolls back the connection. If this SQL object was created from a DataSource then this method does nothing. Throws: java.sql.SQLException - if a database access error occurs

Just another mystery to ponder in life... hope this helps.