Unable to unit test - Unitils, Hibernate

Question

I'm trying to setup some unit test for my application, I'd like to test with Unitils datasets, but so far I'm encountered many problems. I'm convinced I have wrong setup can anybody review it for problems.

I have hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration
   PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
   "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

   <session-factory>
       <!-- database connection settings -->
       <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
       <property name="hibernate.connection.driver_class">org.hsqldb.jdbc.JDBCDriver</property>

       <property name="hibernate.connection.url">jdbc:hsqldb:mem:testdb</property>
       <!--<property name="hibernate.hbm2ddl.auto">create</property>-->



       <!-- Enable Hibernate's second level cache -->
       <property name="hibernate.cache.use_second_level_cache">false</property>
       <property name="hibernate.cache.provider_class">net.sf.ehcache.hibernate.EhCacheProvider</property>

       <!-- helper debug settings -->
       <property name="hibernate.show_sql">true</property>
       <property name="hibernate.format_sql">true</property>

       <mapping class="cz.test.Request"/>  
   </session-factory>

</hibernate-configuration>

Unitils properties as follows

database.dialect=hsqldb
database.driverClassName=org.hsqldb.jdbc.JDBCDriver

database.url=jdbc:hsqldb:mem:testdb
database.schemaNames=PUBLIC

And finally my test is

@DataSet("DaoTest.xml")
@HibernateSessionFactory({"test_hibernate.cfg.xml"})
public class DaoTest extends UnitilsJUnit4 {

    private SessionFactory sessionFactory;

    private Session session = null;

    @Test
    public void testMappingToDatabase() {
        HibernateUnitils.assertMappingWithDatabaseConsistent();
    }
}

I'm getting

SEVERE: Table 'TrnRequest' not found in tableMap=org.dbunit.dataset.OrderedTableNameMap[_tableNames=[], _tableMap={}, _caseSensitiveTableNames=false]
Caused by: org.dbunit.dataset.NoSuchTableException: Request

I guess the schema is not created, setting something in hibernate.cfg.xml doesn't help as the creation is done after this error and fails anyway with

X 15, 2015 9:44:19 DOP. org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: exporting generated schema to database
X 15, 2015 9:44:19 DOP. org.hibernate.tool.hbm2ddl.SchemaExport create
SEVERE: Unsuccessful: create table Request (id bigint generated by default as identity (start with 1), amount bigint, amountCbk bigint, applicationId varchar(4), batch integer, cardIssuer varchar(20), cardSeq integer, currency varchar(3), emvData varchar(1024), entryMode varchar(3), exp varchar(4), maskedPan varchar(32), originalTime timestamp, ORIGINALTIME_UTC timestamp, pan varchar(64), panKey varchar(64), posData varchar(512), repeated NUMBER(1,0) DEFAULT 0  NOT NULL, reqFlag varchar(255), reqProcessing varchar(255), reqType varchar(255), requestTime timestamp, REQUESTTIME_UTC timestamp, sequenceNumber varchar(10), stan bigint, termid varchar(16), transactionTime timestamp, primary key (id))
X 15, 2015 9:44:19 DOP. org.hibernate.tool.hbm2ddl.SchemaExport create
SEVERE: type not found or user lacks privilege: NUMBER
X 15, 2015 9:44:19 DOP. org.hibernate.tool.hbm2ddl.SchemaExport create
SEVERE: Unsuccessful: alter table TrnReleatedTrn add constraint FK135128224355CCD7 foreign key (releatedRequest_id) references TrnRequest
X 15, 2015 9:44:19 DOP. org.hibernate.tool.hbm2ddl.SchemaExport create
SEVERE: user lacks privilege or object not found: REQUEST
X 15, 2015 9:44:19 DOP. org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: schema export complete

Answer 1

so finally I made it work the test now looks like that

public class SettlementDaoTest extends UnitilsJUnit4{

    @HibernateSessionFactory("test_hibernate.cfg.xml")
    SessionFactory hsfLocal;

    @Before
    public void before(){
        SessionFactoryProvider.setSessionFactory(hsfLocal);
    }

    @Test
    @DataSet
    public void testGetTransaction() throws Exception {


        SessionFactoryProvider.getSessionFactory().getCurrentSession().beginTransaction();

        List<Long> customerIds = Factory.getAtFileSettlementDao().getCustomerIds("TB01");
        Assert.assertNotNull(customerIds);
        Assert.assertEquals(4924L , (long)customerIds.get(0));
        SessionFactoryProvider.getSessionFactory().getCurrentSession().getTransaction().commit();


    }
}

The settings I changed in unitils.properties unitils.module.dbunit.runAfter=spring - this postponed loading of datasets to after the schema is created according to

<property name="hibernate.hbm2ddl.auto">create-drop</property>

DatabaseModule.Transactional.value.default=disabled this somehow disabled the transactional deadlock, that was probably caused because I'm using

<property name="hibernate.current_session_context_class">thread</property>

DatabaseModule.Transactional.value.default=disabled