Testing with embedded H2 database - importing data goes fine, when a test is run, it tries to init data again, then fails to find a table

Question

I am struggling with H2 embedded database which I want to use for my tests.

I see the process as two steps:

• build of my project during which the schema will be created and data initialized. This seems to be working fine because I can view the database via H2 console - tables are there, and they contain data
• run one of my tests. This fails. It seems like when a test is run that it tries to re-execute my data init script, then fails (errors in relation to primary key violation). Also, if I comment out the part in my context.xml where scripts are defined, and retry the test - I got Table xyz not found

It seems like the latter step does not use my embedded database at all. Somehow it might be trying to create a new one. But then, if so, I don't see why that wouldn't work - instead I get primary key violation which seems to me like the same data is being inserted in an existing table with data already there.

Answer 1

To answer my own question should anyone else have the same/similar problem.

• ...
• run one of my tests. This fails. It seems like when a test is run that it tries to re-execute my data init script, then fails (errors in relation to primary key violation). Also, if I comment out the part in my context.xml where scripts are defined, and retry the test - I got Table xyz not found

Basically, it did try to re-execute the scripts. The solution was to DROP table IF EXISTS each table before CREATEing it.

The second, and critical part: table xyz not found error. The problem was not that the table could not be found -- it was the schema name that was missing (actually, was not set) when the query was being executed.

This is what I needed (note the comment):

    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />

    <property name="persistenceUnitName" value="tests" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="generateDdl" value="false" />
            <property name="showSql" value="true"/>
            <property name="database" value="H2" />
        </bean>
    </property>
    <!-- BELOW PROPERTY, namely the default_schema key -->
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">none</prop>
            <prop key="hibernate.default_schema">TESTINGDB</prop>
        </props>
    </property>
</bean>

Without this piece of config, my test schema was not being assumed and tables were declared as not found.