How to do testing with Hibernate, H2 using TestNg?

Question

What is the proper way of testing DAO layer?

I had @BeforeMethod and @AfterMethod annotated methods where I created and destroyed SessionFactory but it didn't work with multiple tests. Tests were passing if run one by one but not with the maven build when they were run all together so I decided that I should group them using TestNg groups and do @BeforeGroup and @AfterGroup method where I did the same thing with Hibernate.

So I did something like this:

@Test(groups = {"integration"})
public class IntegrationTest
{
    protected SessionFactory sessionFactory;

    @BeforeGroups(groups = {"integration"})
    public void setUpHibernate() throws Exception
    {
        // here I configure sessionFactory
        this.sessionFactory = ...
    }

    @AfterGroups(groups = {"integration"})
    public void putItDown() throws Exception
    {
        sessionFactory.close();
    }
}

and each of my test extended this class like so

@Test(groups = "integration")
public class RateRepositoryHibernateTest  extends IntegrationTest
{
    ...
}

and then I noticed that only one extending test had sessionFactory set and rest were null which is no surprise because that method was supposed to be run once. Now I really don't know what to do.

How to pass data from @BeforeGroup method around group test methods?

or

How to do it differently?

or

How to setUp and tearDown SessionFactory before and after each test but in a way that I won't get any pesimistic locking exceptions with multiple tests?

-- edit:

Answer explaining how to do it differently is also welcome. I'd like to know what is state of the art in integration testing using TestNg, Hibernate and some in-memory database.

-- edit 2:

stack trace, code of tests below

Hibernate: select roomtype_.type_name from ROOM_TYPES roomtype_ where roomtype_.type_name=?
Hibernate: call next value for rates_sequence
Hibernate: call next value for rates_sequence
Hibernate: insert into ROOMS (prefix, housekeepingStatus, availability, maxExtraBeds, standard, maximum, type, name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into RATES (standardPrice, upchargeExtraPerson, upchargeExtraBed, room, RATE_TYPE, id) values (?, ?, ?, ?, 'R', ?)
Hibernate: insert into RATES (standardPrice, upchargeExtraPerson, upchargeExtraBed, room, seasonId, RATE_TYPE, id) values (?, ?, ?, ?, ?, 'S', ?)
Hibernate: insert into ROOM_TYPES (type_name) values (?)
Hibernate: select roomtype_.type_name from ROOM_TYPES roomtype_ where roomtype_.type_name=?
Hibernate: call next value for rates_sequence
Hibernate: insert into ROOMS (prefix, housekeepingStatus, availability, maxExtraBeds, standard, maximum, type, name) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into RATES (standardPrice, upchargeExtraPerson, upchargeExtraBed, room, RATE_TYPE, id) values (?, ?, ?, ?, 'R', ?)
org.hibernate.PessimisticLockException: Timeout trying to lock table ; SQL statement:
insert into RATES (standardPrice, upchargeExtraPerson, upchargeExtraBed, room, RATE_TYPE, id) values (?, ?, ?, ?, 'R', ?) [50200-168]
    at org.hibernate.dialect.H2Dialect$2.convert(H2Dialect.java:317)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at $Proxy13.executeUpdate(Unknown Source)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:56)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2962)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3403)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:362)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:354)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:275)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:326)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1210)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:399)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175)
    at net.mklew.hotelms.persistance.RatesPersistanceTest.should_save_rates_and_retrieve_them_with_success(RatesPersistanceTest.java:80)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:673)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:842)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1166)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109)
    at org.testng.TestRunner.runWorkers(TestRunner.java:1178)
    at org.testng.TestRunner.privateRun(TestRunner.java:757)
    at org.testng.TestRunner.run(TestRunner.java:608)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
    at org.testng.SuiteRunner.run(SuiteRunner.java:240)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1158)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1083)
    at org.testng.TestNG.run(TestNG.java:999)
    at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:203)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:174)
    at org.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:111)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
insert into RATES (standardPrice, upchargeExtraPerson, upchargeExtraBed, room, RATE_TYPE, id) values (?, ?, ?, ?, 'R', ?) [50200-168]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:158)
    at org.h2.command.Command.filterConcurrentUpdate(Command.java:276)
    at org.h2.command.Command.executeUpdate(Command.java:232)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    ... 47 more
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table "PRIMARY_KEY_4": another transaction has updated or deleted the same row [90131-168]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.table.RegularTable.addRow(RegularTable.java:146)
    at org.h2.command.dml.Insert.insertRows(Insert.java:124)
    at org.h2.command.dml.Insert.update(Insert.java:84)
    at org.h2.command.CommandContainer.update(CommandContainer.java:75)
    at org.h2.command.Command.executeUpdate(Command.java:230)
    ... 54 more

code for test:

public class RatesPersistanceTest
{

    protected SessionFactory sessionFactory;
    protected HibernateSessionFactory hibernateSessionFactory;

    @BeforeMethod
    public void setUpHibernate() throws Exception
    {
        Logger logger = mock(Logger.class);
        NativelyConfiguredHibernateSessionFactory hibernateSessionFactory = new
                NativelyConfiguredHibernateSessionFactory(logger);
        this.sessionFactory = hibernateSessionFactory.getSessionFactory();
        this.hibernateSessionFactory = hibernateSessionFactory;
    }

    @AfterMethod
    public void putItDown() throws Exception
    {
        sessionFactory.close();
    }

    @Test
    public void should_save_rates_and_retrieve_them_with_success()
    {
        // given
        Money standardPrice = Money.parse("USD 85");
        Money upchargeExtraPerson = Money.parse("USD 80");
        Money upchargeExtraBed = Money.parse("USD 75");
        RoomType roomType = getMeRoomType();
        Room room = getMeRoom(roomType);

        AvailabilityPeriod availabilityPeriod = new AvailabilityPeriod(DateTime.now(), DateTime.now().plusDays(5),
                true);
        Season season = new BasicSeason("season name", availabilityPeriod);
        Rate seasonRate = new SeasonRate(standardPrice, upchargeExtraPerson, upchargeExtraBed, room, season);

        Session session = sessionFactory.openSession();
        session.beginTransaction();

        session.save(roomType);
//        session.save(room);

        session.getTransaction().commit();
        session.close();


        session = sessionFactory.openSession();
        session.beginTransaction();

        //        session.save(roomType);
        session.save(room);

        session.getTransaction().commit();
        session.close();

        session = sessionFactory.openSession();
        session.beginTransaction();
        session.save(season);

        session.save(seasonRate);

        session.getTransaction().commit();
        session.close();
        // when
        session = sessionFactory.openSession();
        session.beginTransaction();
        final List<Rate> list = session.createQuery("from Rate").list();

        // then
        assertThat(list).contains(seasonRate);
        session.getTransaction().commit();
        session.close();

    }

    @Test( expectedExceptions = org.hibernate.exception.ConstraintViolationException.class)
    public void season_rate_should_violate_db_constraints_when_saved_without_season()
    {
        // given
        Money standardPrice = Money.parse("USD 85");
        Money upchargeExtraPerson = Money.parse("USD 80");
        Money upchargeExtraBed = Money.parse("USD 75");
        RoomType roomType = getMeRoomType();
        final RoomName roomName = new RoomName("103");

        final Money roomStandardPrice = Money.parse("USD 100");
        final Money roomUpchargeExtraPerson = Money.parse("USD 50");
        final Money roomUpchargeExtraBed = Money.parse("USD 20");
        final RackRate rackRate = new RackRate(roomStandardPrice, roomUpchargeExtraPerson, roomUpchargeExtraBed, null);
        final int maxExtraBeds = 2;
        final Occupancy occupancy = new Occupancy(4, 2);
        Room room = new Room("C", roomName, roomType, HousekeepingStatus.CLEAN, RoomAvailability.AVAILABLE,
                maxExtraBeds, occupancy, standardPrice, upchargeExtraPerson, upchargeExtraBed);


        AvailabilityPeriod availabilityPeriod = new AvailabilityPeriod(DateTime.now(), DateTime.now().plusDays(5),
                true);
        Season season = new BasicSeason("season name", availabilityPeriod);
        Rate seasonRate = new SeasonRate(standardPrice, upchargeExtraPerson, upchargeExtraBed, room, null);

        Session session = sessionFactory.openSession();
        session.beginTransaction();

        session.save(room);
        // when
        session.save(seasonRate);

        session.getTransaction().commit();
        session.close();
        // then exception should be thrown
    }

//    @Test
//    public void package_rate_should_violate_db_constraints_when_saved_without_package()
//    {
//
//    }

    private RoomType getMeRoomType()
    {
        final RoomType roomType = new RoomType("cheap" + DateTime.now().toString());
        return roomType;
    }

    private Room getMeRoom(RoomType roomType)
    {
        final RoomName roomName = new RoomName("101001" + DateTime.now().toString());

        final Money standardPrice = Money.parse("USD 100");
        final Money upchargeExtraPerson = Money.parse("USD 50");
        final Money upchargeExtraBed = Money.parse("USD 20");
        final RackRate rackRate = new RackRate(standardPrice, upchargeExtraPerson, upchargeExtraBed, null);
        final int maxExtraBeds = 2;
        final Occupancy occupancy = new Occupancy(4, 2);
        return new Room("C", roomName, roomType, HousekeepingStatus.CLEAN, RoomAvailability.AVAILABLE, maxExtraBeds,
                occupancy, standardPrice, upchargeExtraPerson, upchargeExtraBed);
    }

}

hibernate cfg:

<hibernate-configuration>

    <session-factory>
        <!-- Database connection settings -->
        <property name="connection.driver_class">org.h2.Driver</property>
        <property name="connection.url">jdbc:h2:mem:db1;DB_CLOSE_DELAY=0;MVCC=true</property>
        <property name="connection.username">sa</property>
        <property name="connection.password"/>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.H2Dialect</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCachingRegionFactory</property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create-drop</property>

       <!-- skipped mappings -->

    </session-factory>

</hibernate-configuration>

Answer 1

My preferred way to test code that uses Hibernate is to use @BeforeMethod and @AfterMethod test approach. I don't have any experience with TestNg, but I assume the way it treats those functions is similar to what JUnit3.8.1 does with setUp() and tearDown(), which is what I use.

The key point to me is the idea that the order of unit test execution shouldn't matter. If you're using @BeforeGroup and @AfterGroup, then you have the same SessionFactory instance, and therefore the same database (in memory or not), and any mutating operations that a test runs on that SessionFactory will affect later tests that do read operations. That may be desired (although the order probably has to be predictable), in which case you're really talking about a single 'integration test', but for your case it sounds like you want the tests to be independent.

Next question is how to do that. What I do is configure Hibernate to create and connect to in-memory database, run the create schema script on it, and create a SessionFactory, for each test method.

@Override
protected void setUp() throws Exception {
    super.setUp();
    String dialectClassName = HSQLDialect.class.getName();
    AnnotationConfiguration config = new AnnotationConfiguration().addAnnotatedClass(DividendScheduleGeneratorImpl.class);

    config.setProperty(Environment.DIALECT, dialectClassName);
    config.setProperty(Environment.DRIVER, jdbcDriver.class.getName());
    config.setProperty(Environment.URL, "jdbc:hsqldb:mem:testDB");
    config.setProperty(Environment.USER, "SA");
    config.setProperty(Environment.PASS, "");

    SchemaExport export = new SchemaExport(config);
    export.create(false, true);

    sessions = config.buildSessionFactory();
}

@Override
protected void tearDown() throws Exception {
    sessions.close();
    sessions = null;

    super.tearDown();
}

Couple of notes:

1. The schema creation is not all that fast, at least with HSQLDB, which I use. But as long as you cleanly separate tests that are really testing Hibernate, from tests which use a mockable persistence layer, you should be fine because there won't be that many tests having to work with an actual database.
2. My tests are run single-threaded. If you're running multi-threaded, you'll need to set the database name so that it's unique for concurrently running tests.

EDIT:

Based on your error stack trace and code, and the fact the tests run independently, I suspect the PessimisticLockExceptions are being caused by the tests running on separate threads against the same database. Possibly you even have a database-centered deadlock on the RATES table. Two options for dealing with it are having the tests generate independent databases, or to tell TestNG to run the tests serially.

For the first, you would have to programatically edit the Hibernate connection string from

jdbc:h2:mem:db1;DB_CLOSE_DELAY=0;MVCC=true

to

jdbc:h2:mem:<TESTNAME>;DB_CLOSE_DELAY=0;MVCC=true

or similar. In JUnit, I'd use the TestCase.getName(), I assume there is a similarly available feature in TestNG.

The alternative is to run serially. According to the TestNG documentation, serializing the tests should be possible by annotating the class (not the methods) to add @Test(singleThreaded=true)

@Test(singleThreaded=true)
public class RatesPersistanceTest

That's how it's supposed to work at least, perhaps when you tried it you annotated the method?