[[jdbc-embedded-database-support]]
= Embedded Database Support

The `org.springframework.jdbc.datasource.embedded` package provides support for embedded
Java database engines. Support for https://www.hsqldb.org[HSQL],
https://www.h2database.com[H2], and https://db.apache.org/derby[Derby] is provided
natively. You can also use an extensible API to plug in new embedded database types and
`DataSource` implementations.


[[jdbc-why-embedded-database]]
== Why Use an Embedded Database?

An embedded database can be useful during the development phase of a project because of its
lightweight nature. Benefits include ease of configuration, quick startup time,
testability, and the ability to rapidly evolve your SQL during development.


[[jdbc-embedded-database]]
== Creating an Embedded Database

You can expose an embedded database instance as a bean as the following example shows:

include-code::./JdbcEmbeddedDatabaseConfiguration[tag=snippet,indent=0]

The preceding configuration creates an embedded H2 database that is populated with SQL from
the `schema.sql` and `test-data.sql` resources in the root of the classpath. In addition, as
a best practice, the embedded database is assigned a uniquely generated name. The
embedded database is made available to the Spring container as a bean of type
`javax.sql.DataSource` that can then be injected into data access objects as needed.

See the {spring-framework-api}/jdbc/datasource/embedded/EmbeddedDatabaseBuilder.html[javadoc for `EmbeddedDatabaseBuilder`]
for further details on all supported options.


[[jdbc-embedded-database-types]]
== Selecting the Embedded Database Type

This section covers how to select one of the three embedded databases that Spring
supports. It includes the following topics:

* xref:data-access/jdbc/embedded-database-support.adoc#jdbc-embedded-database-using-HSQL[Using HSQL]
* xref:data-access/jdbc/embedded-database-support.adoc#jdbc-embedded-database-using-H2[Using H2]
* xref:data-access/jdbc/embedded-database-support.adoc#jdbc-embedded-database-using-Derby[Using Derby]

[[jdbc-embedded-database-using-HSQL]]
=== Using HSQL

Spring supports HSQL 1.8.0 and above. HSQL is the default embedded database if no type is
explicitly specified. To specify HSQL explicitly, set the `type` attribute of the
`embedded-database` tag to `HSQL`. If you use the builder API, call the
`setType(EmbeddedDatabaseType)` method with `EmbeddedDatabaseType.HSQL`.

[[jdbc-embedded-database-using-H2]]
=== Using H2

Spring supports the H2 database. To enable H2, set the `type` attribute of the
`embedded-database` tag to `H2`. If you use the builder API, call the
`setType(EmbeddedDatabaseType)` method with `EmbeddedDatabaseType.H2`.

[[jdbc-embedded-database-using-Derby]]
=== Using Derby

Spring supports Apache Derby 10.5 and above. To enable Derby, set the `type`
attribute of the `embedded-database` tag to `DERBY`. If you use the builder API,
call the `setType(EmbeddedDatabaseType)` method with `EmbeddedDatabaseType.DERBY`.


[[jdbc-embedded-database-types-custom]]
== Customizing the Embedded Database Type

While each supported type comes with default connection settings, it is possible
to customize them if necessary. The following example uses H2 with a custom driver:

[tabs]
======
Java::
+
[source,java,indent=0,subs="verbatim,quotes"]
----
	@Configuration
	public class DataSourceConfig {

		@Bean
		public DataSource dataSource() {
			return new EmbeddedDatabaseBuilder()
					.setDatabaseConfigurer(EmbeddedDatabaseConfigurers
							.customizeConfigurer(H2, this::customize))
					.addScript("schema.sql")
					.build();
		}

		private EmbeddedDatabaseConfigurer customize(EmbeddedDatabaseConfigurer defaultConfigurer) {
			return new EmbeddedDatabaseConfigurerDelegate(defaultConfigurer) {
				@Override
				public void configureConnectionProperties(ConnectionProperties properties, String databaseName) {
					super.configureConnectionProperties(properties, databaseName);
					properties.setDriverClass(CustomDriver.class);
				}
			};
		}
	}
----

Kotlin::
+
[source,kotlin,indent=0,subs="verbatim,quotes"]
----
	@Configuration
	class DataSourceConfig {

		@Bean
		fun dataSource(): DataSource {
			return EmbeddedDatabaseBuilder()
				.setDatabaseConfigurer(EmbeddedDatabaseConfigurers
					.customizeConfigurer(EmbeddedDatabaseType.H2) { this.customize(it) })
				.addScript("schema.sql")
				.build()
		}

		private fun customize(defaultConfigurer: EmbeddedDatabaseConfigurer): EmbeddedDatabaseConfigurer {
			return object : EmbeddedDatabaseConfigurerDelegate(defaultConfigurer) {
				override fun configureConnectionProperties(
					properties: ConnectionProperties,
					databaseName: String
				) {
					super.configureConnectionProperties(properties, databaseName)
					properties.setDriverClass(CustomDriver::class.java)
				}
			}
		}
	}
----
======


[[jdbc-embedded-database-dao-testing]]
== Testing Data Access Logic with an Embedded Database

Embedded databases provide a lightweight way to test data access code. The next example is a
data access integration test template that uses an embedded database. Using such a template
can be useful for one-offs when the embedded database does not need to be reused across test
classes. However, if you wish to create an embedded database that is shared within a test suite,
consider using the xref:testing/testcontext-framework.adoc[Spring TestContext Framework] and
configuring the embedded database as a bean in the Spring `ApplicationContext` as described
in xref:data-access/jdbc/embedded-database-support.adoc#jdbc-embedded-database[Creating an Embedded Database].
The following listing shows the test template:

[tabs]
======
Java::
+
[source,java,indent=0,subs="verbatim,quotes"]
----
	public class DataAccessIntegrationTestTemplate {

		private EmbeddedDatabase db;

		@BeforeEach
		public void setUp() {
			// creates an HSQL in-memory database populated from 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();
		}

	}
----

Kotlin::
+
[source,kotlin,indent=0,subs="verbatim,quotes"]
----
	class DataAccessIntegrationTestTemplate {

		private lateinit var db: EmbeddedDatabase

		@BeforeEach
		fun setUp() {
			// creates an HSQL in-memory database populated from 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()
		}
	}
----
======


[[jdbc-embedded-database-unique-names]]
== Generating Unique Names for Embedded Databases

Development teams often encounter errors with embedded databases if their test suite
inadvertently attempts to recreate additional instances of the same database. This can
happen quite easily if an XML configuration file or `@Configuration` class is responsible
for creating an embedded database and the corresponding configuration is then reused
across multiple testing scenarios within the same test suite (that is, within the same JVM
process) -- for example, integration tests against embedded databases whose
`ApplicationContext` configuration differs only with regard to which bean definition
profiles are active.

The root cause of such errors is the fact that Spring's `EmbeddedDatabaseFactory` (used
internally by both the `<jdbc:embedded-database>` XML namespace element and the
`EmbeddedDatabaseBuilder` for Java configuration) sets the name of the embedded database to
`testdb` if not otherwise specified. For the case of `<jdbc:embedded-database>`, the
embedded database is typically assigned a name equal to the bean's `id` (often,
something like `dataSource`). Thus, subsequent attempts to create an embedded database
do not result in 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 address this common issue, Spring Framework 4.2 provides support for generating
unique names for embedded databases. To enable the use of generated names, use one of
the following options.

* `EmbeddedDatabaseFactory.setGenerateUniqueDatabaseName()`
* `EmbeddedDatabaseBuilder.generateUniqueName()`
* `<jdbc:embedded-database generate-name="true" ... >`


[[jdbc-embedded-database-extension]]
== Extending the Embedded Database Support

You can extend Spring JDBC embedded database support in two ways:

* Implement `EmbeddedDatabaseConfigurer` to support a new embedded database type.
* Implement `DataSourceFactory` to support a new `DataSource` implementation, such as a
  connection pool to manage embedded database connections.

We encourage you to contribute extensions to the Spring community at
{spring-framework-issues}[GitHub Issues].
