Spring configuration for embedded H2 database for tests

Solution 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.

Solution 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.

Solution 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.