Support for embedded databases

The org.springframework.jdbc.datasource.embedded package provides support for embedded storage engines in Java. Support HSQL, H2 and Derby are provided natively. You can also use the extensible API to connect new built-in database types and DataSource implementations.

Why use an embedded database?

An embedded database can be useful at the project development stage due to its simplicity. Benefits include ease of setup, fast startup time, testability, and the ability to quickly refine SQL during development.

Creating an embedded database using Spring XML

If you need to expose an instance of an embedded database in As a bean in ApplicationContext in Spring, you can use the embedded-database tag in the spring-jdbc namespace:

<jdbc:embedded-database id="dataSource" generate-name="true">
    <jdbc:script location="classpath:schema.sql"/>
    <jdbc:script location="classpath:test-data.sql"/>
</jdbc:embedded-database>

The previous configuration creates an embedded HSQL database that is populated with SQL from schema.sql resources and test-data.sql in the root of the classpath. Additionally, as a best practice, the embedded database is given a unique name. The embedded database is made available to the Spring container as a bean of type javax.sql.DataSource, which can then be injected into data access objects as needed.

Creating an embedded database programmatically

The EmbeddedDatabaseBuilder class provides a free API for creating an embedded database programmatically. This can be used if you need to create an embedded database in a standalone environment or in a standalone integration test, as in the following example:

EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
        .generateUniqueName(true)
        .setType(H2)
        .setScriptEncoding("UTF-8")
        .ignoreFailedDrops(true)
        .addScript("schema.sql")
        .addScripts("user_data.sql", "country_data.sql")
        .build();
// perform actions with the database (EmbeddedDatabase extends javax.sql.DataSource)
db.shutdown()

val db = EmbeddedDatabaseBuilder()
        .generateUniqueName(true)
        .setType(H2)
        .setScriptEncoding("UTF-8")
        .ignoreFailedDrops(true)
        .addScript("schema.sql")
        .addScripts("user_data.sql", "country_data.sql")
        .build()
// perform actions on the database (EmbeddedDatabase extends javax.sql.DataSource)
db.shutdown()

Cm. javadoc EmbeddedDatabaseBuilder for more details on all supported options.

You can also use EmbeddedDatabaseBuilder to create an embedded database using Java configuration , as shown in the following example:

@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .setType(H2)
                .setScriptEncoding("UTF-8")
                .ignoreFailedDrops(true)
                .addScript("schema.sql")
                .addScripts("user_data.sql", "country_data.sql")
                .build();
    }
}

@Configuration
class DataSourceConfig {
    @Bean
    fun dataSource(): DataSource {
        return EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .setType(H2)
                .setScriptEncoding("UTF-8")
                .ignoreFailedDrops(true)
                .addScript("schema.sql")
                .addScripts("user_data.sql", "country_data.sql")
                .build()
    }
}

Selecting a built-in database type

This section explains how to select one of the three built-in databases data that Spring supports. The section includes the following topics:

• Using HSQL

• Using H2

• Using Derby

Using HSQL

Spring supports HSQL 1.8.0 and higher. HSQL is the default embedded database if the type is not explicitly specified. To explicitly specify HSQL, set the type attribute of the embedded-database tag to HSQL. If you are using the build tool API, call the setType(EmbeddedDatabaseType) method with the EmbeddedDatabaseType.HSQL parameter.

Using H2

Spring supports the H2 database. To enable H2, set the type attribute of the embedded-database tag to H2. If you are using the build tool API, call the setType(EmbeddedDatabaseType) method with the EmbeddedDatabaseType.H2 parameter.

Using Derby

Spring supports Apache Derby 10.5 and higher. To enable Derby, set the type attribute of the embedded-database tag to DERBY. If you are using the build tool API, call the setType(EmbeddedDatabaseType) method with the EmbeddedDatabaseType.DERBY parameter.

Testing Data access logic using an embedded database

Embedded databases provide an easy way to test code to retrieve a sample of data. The following example is a data access integration test template that uses an embedded database. Using such a pattern can be useful for one-off use cases where the built-in database does not need to be reused across all test classes. However, if you need to create an embedded database that will be shared among the test suite, use Spring TestContext Framework and configure embedded database as a bean in ApplicationContext from Spring, as described above. The following listing shows the test pattern:

public class DataAccessIntegrationTestTemplate {
    private EmbeddedDatabase db;
    @BeforeEach
    public void setUp() {
        // creates an in-memory resident HSQL database filled with default scripts
        // classpath:schema.sql and classpath:data.sql
        db = new EmbeddedDatabaseBuilder()
                .generateUniqueName(true )
                .addDefaultScripts()
                .build();
    }
    @Test
    public void testDataAccess() {
        JdbcTemplate template = new JdbcTemplate(db);
        template.query( /* ... */ );
    }
    @AfterEach
    public void tearDown() {
        db.shutdown();
    }
}

class DataAccessIntegrationTestTemplate {
    private lateinit var db: EmbeddedDatabase
    @BeforeEach
    fun setUp() {
        // creates an in-memory resident HSQL database filled with default scripts
        // classpath:schema.sql and classpath:data.sql
        db = EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .addDefaultScripts()
                .build()
    }
    @Test
    fun testDataAccess() {
        val template = JdbcTemplate(db)
        template.query( /* ... */)
    }
    @AfterEach
    fun tearDown() {
        db.shutdown()
    }
}

Creating unique names for embedded databases

Development teams often encounter errors when working with embedded databases if their test suite accidentally tries to recreate additional instances of the same Database. This can happen quite easily if an XML configuration file or class with the @Configuration annotation is responsible for creating the embedded database, and the corresponding configuration is then reused across multiple test cases within a single test suite (i.e. within single JVM process) - for example, integration tests against embedded databases whose ApplicationContext configuration differs only in which bean definition profiles are active.

The root cause of such errors is the fact that the EmbeddedDatabaseFactory factory from Spring (used internally by both the XML namespace element <jdbc:embedded-database> and the EmbeddedDatabaseBuilder configuration for Java) sets the embedded database name to testdb unless otherwise specified. In the case of <jdbc:embedded-database>, the embedded database is typically given a name equal to the id of the bean (often something like dataSource). Therefore, subsequent attempts to create the embedded database do not result in the creation of a new database. Instead, the same JDBC connection URL is reused, and attempts to create a new embedded database actually point to an existing embedded database created from the same configuration.

To solve this common problem in Spring Framework 4.2 provides support for generating unique names for embedded databases. To enable the feature to use generated names, use one of the following parameters.

• EmbeddedDatabaseFactory.setGenerateUniqueDatabaseName()

• EmbeddedDatabaseBuilder.generateUniqueName()

• <jdbc:embedded-database generate-name="true" …​ >

Extending native database support

You can extend Spring's native JDBC database support in two ways:

• By implementing EmbeddedDatabaseConfigurer to provide support for a new type of embedded database.

• By implementing DataSourceFactory to provide support for a new DataSource implementation, such as a connection pool to manage connections to the embedded database.

We encourage you to contribute suggestions for extensions to Spring community on GitHub Issues.