unit testing - 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

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.