CODEWITHSUNDEEP
unit-testingspringembedded-databaseh2
Dr. Hans-Peter Störr
Dr. Hans-Peter Störr

Reputation: 25978

Spring configuration for embedded H2 database for tests

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.

Upvotes: 43

Views: 83447

Answers (4)

matsev
matsev

Reputation: 33749

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.

Upvotes: 51

ejboy
ejboy

Reputation: 4181

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.

Upvotes: 10

Dr. Hans-Peter St&#246;rr
Dr. Hans-Peter St&#246;rr

Reputation: 25978

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.

Upvotes: 20

Bozho
Bozho

Reputation: 596996

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)

Upvotes: 0

Related Questions