How to rollback a specific record after a test?

I have a spock / spring test which modifies some database content, and I wonder how to rollback that record.

Currently I execute a raw sql statement, save the field data and after successful test I restore that data. But my guess is that this can be done in an easier way?

@ContextConfiguration(locations = "classpath*:applicationContext-test.xml")
class RepositoryTest extends Specification {

    @Shared sql = Sql.newInstance("jdbc:sqlserver://...")
    ResourceRepository resourceRepository;

    def "Save test"() {
        setup:
        // copy the row before we do changes! we need to restore this later on!
        def row = sql.firstRow("select id, content, status from table-name where id = 12345")

        when:
        ...

        then:
        ..

        cleanup:
        sql.execute("""
                    update table-name set content = ${row.content}, status = ${row.status}
                    where id = ${row.id}
                    """)
    }
}

Solution

The best way I've found to do this is:

start test
start transaction
(optional) load any DB data the test requires using something like DBUnit
run test
rollback transaction

Notice that all DB operations occur within the same transaction. Because this transaction is rolled back at the end of the test (even if an exception is thrown), the database should always be in the same state at the end of the test as it was at the start.

Spring provides some really useful classes that will take care of starting and rolling back the transaction for each test. If you're using Spring & JUnit4 and don't mind that your test classes have to extend a Spring class the easiest option is probably to extend AbstractTransactionalJUnit4SpringContextTests

// Location of the Spring config file(s)
@ContextConfiguration(locations = {"/application-context-test.xml", "classpath*:/application-context-persistence-test.xml"})

// Transaction manager bean name
@TransactionConfiguration(transactionManager = "hsqlTransactionManager", defaultRollback = true)
@Transactional(propagation=Propagation.REQUIRES_NEW)
public class MyTransactionalTests extends AbstractTransactionalJUnit4SpringContextTests {

    @Test
    public void thisWillRunInATransactionThatIsAutomaticallyRolledBack() {}
}

If you don't want to extend a Spring class, you can configure a test-runner instead using annotations. Spring also supports many of the other major unit-testing frameworks and older versions of JUnit.