title: How to do testing with Hibernate, H2 using TestNg?
body: <p>What is the proper way of testing DAO layer?</p>
  
  <p>I had <code>@BeforeMethod</code> and <code>@AfterMethod</code> annotated methods where I created and destroyed <code>SessionFactory</code> 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 <code>@BeforeGroup</code> and <code>@AfterGroup</code> method where I did the same thing with Hibernate.</p>
  
  <p>So I did something like this:</p>
  
  <pre><code>@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();
      }
  }
  </code></pre>
  
  <p>and each of my test extended this class like so</p>
  
  <pre><code>@Test(groups = "integration")
  public class RateRepositoryHibernateTest  extends IntegrationTest
  {
      ...
  }
  </code></pre>
  
  <p>and then I noticed that only one extending test had <code>sessionFactory</code> set and rest were <code>null</code> which is no surprise because that method was supposed to be run once. Now I really don't know what to do.</p>
  
  <p>How to pass data from <code>@BeforeGroup</code> method around group test methods?</p>
  
  <p>or</p>
  
  <p>How to do it differently?</p>
  
  <p>or</p>
  
  <p>How to setUp and tearDown <code>SessionFactory</code> before and after each test but in a way that I won't get any pesimistic locking exceptions with multiple tests?</p>
  
  <p>--
  edit:</p>
  
  <p>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.</p>
  
  <p>--
  edit 2:</p>
  
  <p>stack trace, code of tests below</p>
  
  <pre><code>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></pre>
  
  <p>code for test:</p>
  
  <pre><code>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&lt;Rate&gt; 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);
      }
  
  }
  </code></pre>
  
  <p>hibernate cfg:</p>
  
  <pre><code>&lt;hibernate-configuration&gt;
  
      &lt;session-factory&gt;
          &lt;!-- Database connection settings --&gt;
          &lt;property name="connection.driver_class"&gt;org.h2.Driver&lt;/property&gt;
          &lt;property name="connection.url"&gt;jdbc:h2:mem:db1;DB_CLOSE_DELAY=0;MVCC=true&lt;/property&gt;
          &lt;property name="connection.username"&gt;sa&lt;/property&gt;
          &lt;property name="connection.password"/&gt;
  
          &lt;!-- JDBC connection pool (use the built-in) --&gt;
          &lt;property name="connection.pool_size"&gt;1&lt;/property&gt;
  
          &lt;!-- SQL dialect --&gt;
          &lt;property name="dialect"&gt;org.hibernate.dialect.H2Dialect&lt;/property&gt;
  
          &lt;!-- Disable the second-level cache  --&gt;
          &lt;property name="cache.provider_class"&gt;org.hibernate.cache.internal.NoCachingRegionFactory&lt;/property&gt;
  
          &lt;!-- Enable Hibernate's automatic session context management --&gt;
          &lt;property name="current_session_context_class"&gt;thread&lt;/property&gt;
  
          &lt;!-- Echo all executed SQL to stdout --&gt;
          &lt;property name="show_sql"&gt;true&lt;/property&gt;
  
          &lt;!-- Drop and re-create the database schema on startup --&gt;
          &lt;property name="hbm2ddl.auto"&gt;create-drop&lt;/property&gt;
  
         &lt;!-- skipped mappings --&gt;
  
      &lt;/session-factory&gt;
  
  &lt;/hibernate-configuration&gt;
  </code></pre>