Spring configuration for embedded H2 database for tests

Question

What does your Spring configuration for integration tests look like using an embedded h2 datasource and, optionally, JUnit?

My first try with a SingleConnectionDataSource basically worked, but failed on more complicated tests where you need several connections at the same time or suspended transactions. I think h2 in tcp based server mode might work as well, but this is probably not the fastest communication mode for a temporary embedded database in memory.

What are the possibilities and their advantages / disadvantages? Also, how do you create the tables / populate the database?

---

Update: Let's specify some concrete requirements that are important for such tests.

• The database should be temporary and in memory
• The connection should probably not use tcp, for speed requirements
• It would be nice if I could use a database tool to inspect the content of the database during debugging
• We have to define a datasource since we can't use the application servers datasource in unit tests

Handicapped

Answer 1

With the reservation that I do not know if there is any tool that can inspect the database, I think that a simple solution would be to use the Spring embedded database (3.1.x docs, current docs) which supports HSQL, H2, and Derby.

Using H2, your xml configuration would look like the following:

<jdbc:embedded-database id="dataSource" type="H2">
    <jdbc:script location="classpath:db-schema.sql"/>
    <jdbc:script location="classpath:db-test-data.sql"/>
</jdbc:embedded-database>

If you prefer Java based configuration, you can instantiate a DataSource like this (note that EmbeddedDataBase extends DataSource):

@Bean(destroyMethod = "shutdown")
public EmbeddedDatabase dataSource() {
    return new EmbeddedDatabaseBuilder().
            setType(EmbeddedDatabaseType.H2).
            addScript("db-schema.sql").
            addScript("db-test-data.sql").
            build();
}

The database tables are created by the db-schema.sql script and they are populated with test data from the db-test-data.sql script.

Don't forget to add the H2 database driver to your classpath.

Barocchio

If database configured for automatic DDL generation from models in hibernate (hbm2ddl) , scripts runs before data structure generation via hibernate. Is there any solution for execute these scripts after hbm2ddl? – Unconventional

Answer 2

I currently include in a test-only springconfig-file as a datasource:

<bean id="database.dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
    <constructor-arg>
        <bean class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
            <property name="driverClass" value="org.h2.Driver" />
            <property name="url"
                value="jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=Oracle;TRACE_LEVEL_SYSTEM_OUT=2" />
        </bean>
    </constructor-arg>
</bean>

<!-- provides a H2 console to look into the db if necessary -->
<bean id="org.h2.tools.Server-WebServer" class="org.h2.tools.Server" 
    factory-method="createWebServer" depends-on="database.dataSource" 
    init-method="start" lazy-init="false">
    <constructor-arg value="-web,-webPort,11111" />
</bean>

Creating / dropping the tables can be done by using executeSqlScript when overriding AbstractAnnotationAwareTransactionalTests.onSetUpBeforeTransaction, or with SimpleJdbcTestUtils.executeSqlScript in an appropriate place.

Compare also this posting.

Handicapped

"I did not find a replacement for executeSqlScript with JUnit4 tests" try extending org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests – Grommet

Thank you for this answer. My unit tests were failing with the in mem DB until I added DB_CLOSE_DELAY – Ariadne

Answer 3

H2 is bundled with a built-in connection pool implementation. The following XML provides an example of using it as a Datasource bean without a need to introduce additional dependencies on DBCP or C3P0:

<bean id="dataSource" class="org.h2.jdbcx.JdbcConnectionPool" destroy-method="dispose">
    <constructor-arg>
        <bean class="org.h2.jdbcx.JdbcDataSource">
            <property name="URL" value="jdbc:h2:dbname"/>
            <property name="user" value="user"/>
            <property name="password" value="password"/>
         </bean>
    </constructor-arg>
</bean> 

The database will be shut down by calling a dispose method when Spring application context closes.

Levasseur

Answer 4

I think it's best to use your production DataSource implementation (only with different connection-string) for the unit-tests.

Anyway "failed on more complicated tests" doesn't give enough information for a more detailed answer.

(Self-ad : check this)

Barroom

The production database for unit-tests? If you really use e.g. an oracle database for automated tests during e.g. a maven build you can easily run into trouble. That's quite slow, depends on the data that is there and nobody else should do a build at the same time. 8-) I much prefer h2 in oracle mode. BTW: this question is rather general about the available possibilities, less about my specific problem. – Obadias

no, the production DataSource implementation. With difference only in connection string – Barroom

Ah, OK, but that would be a datasource in an application server. So this is not possible. – Obadias

which one is the DataSource implementation that you are using in production? – Barroom

Yea I don't really think that's practical if you are looking to do TDD as part of a CI process. Maybe you could do that for some secondary integration testing or load testing, but for the test driven development part of things it will slow the execution of your tests to a crawl if you have to manage a full instance of a database like MySQL just to run tests aimed at code coverage. One day I'll be wrong about this, but right now I'm not. – Wriggler