Clear the in memory database after every testcase

Question

I am using hsqldb for testing some of the data access layer in Java. I have certain test cases like 100 around. I create a in memory database and then insert some values in the table so that with my test case i can load it, but the problem is for every test case i need to clear the in memory database, only the values not the tables.

Is it possible, one thing is i need to manually delete the rows from the table and is there some thing else I can use.

Thanks

Answer 1

If you use DbUnit in unit-tests, you can specify that DbUnit should perform a clean-and-insert operation before every test to ensure that the contents of the database are in a valid state before every test. This can be done in a manner similar to the one below:

@Before
public void setUp() throws Exception
{
    logger.info("Performing the setup of test {}", testName.getMethodName());
    IDatabaseConnection connection = null;
    try
    {
        connection = getConnection();
        IDataSet dataSet = getDataSet();
        //The following line cleans up all DbUnit recognized tables and inserts and test data before every test.
        DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);
    }
    finally
    {
        // Closes the connection as the persistence layer gets it's connection from elsewhere
        connection.close();
    }
}

Note that it is always recommended to perform any setup activities in a @Before setup method, rather than in a @After teardown method. The latter indicates that you are creating new database objects in a method being tested, which IMHO does not exactly lend easily to testable behavior. Besides, if you are cleaning up after a test, to ensure that a second test runs correctly, then any such cleanup is actually a part of the setup of the second test, and not a teardown of the first.

The alternative to using DbUnit is to start a new transaction in your @Before setup method, and to roll it back in the @After teardown method. This would depend on how your data access layer is written.

If your data access layer accepts Connection objects, then your setup routine should create them, and turn off auto-commit. Also, there is an assumption that your data access layer will not invoke Connection.commit. Assuming the previous, you can rollback the transaction using Connection.rollback() in your teardown method.

With respect to transaction control, the below snippet demonstrates how one would do it using JPA for instance:

@Before
public void setUp() throws Exception
{
    logger.info("Performing the setup of test {}", testName.getMethodName());
    em = emf.createEntityManager();
    // Starts the transaction before every test
    em.getTransaction.begin();
}

@After
public void tearDown() throws Exception
{
    logger.info("Performing the teardown of test {}", testName.getMethodName());
    if (em != null)
    {
        // Rolls back the transaction after every test
        em.getTransaction().rollback();
        em.close();
    }
}

Similar approaches would have to be undertaken for other ORM frameworks or even your custom persistence layer, if you have written one.

Answer 2

Could you use HSQLDB transactions?

Before every test, start a new transaction:

START TRANSACTION;

After every test, roll it back:

ROLLBACK;

This would also allow you to have some permanent data.