Spring configuration for embedded H2 database for tests

Question

What does your Spring configuration for a datasource with embedded h2 database that you use for integration (JUnit-)tests look like? My first try with a SingleConnectionDataSource basically worked, but failed on more complicated tests involving suspended transactions. You might start h2 in tcp based server mode 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? And 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 to be fast
• 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 Unittests

Answer 1

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)

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.