Spring embedded database examples

In this tutorial, we will show you a few examples to configure the embedded database engines like HSQL, H2 and Derby in Spring framework.

Technologies used :

1. Spring 4.1.6.RELEASE
2. jUnit 4.1.2
3. Maven 3

Embedded databases tested :

1. HSQLDB 2.3.2
2. H2 1.4.187
3. Derby 10.11.1.1

The embedded database concept is very helpful during the development phase, because they are lightweight, fast, quick start time, improve testability, ease of configuration, it lets developer focus more on the development instead of how to configure a data source to the database, or waste time to start a heavyweight database to just test a few lines of code.

P.S This embedded database feature has been available since Spring 3.

1. Project Dependency

The embedded database features are included in spring-mvc. For example, to start a HSQL embedded database, you need to include both spring-mvc and hsqldb.

	<properties>		
		<spring.version>4.1.6.RELEASE</spring.version>
		<hsqldb.version>2.3.2</hsqldb.version>
		<dbh2.version>1.4.187</dbh2.version>
		<derby.version>10.11.1.1</derby.version>
	</properties>
	<dependencies>
		<!-- Spring JDBC -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<!-- HyperSQL DB -->
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>${hsqldb.version}</version>
		</dependency>
		<!-- H2 DB -->
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>${dbh2.version}</version>
		</dependency>
		<!-- Derby DB -->
		<dependency>
			<groupId>org.apache.derby</groupId>
			<artifactId>derby</artifactId>
			<version>${derby.version}</version>
		</dependency>
	</dependencies>

2. Embedded Database in Spring XML

Example to create an embedded database using Spring XML and initial some scripts to create tables and insert data. Spring will create the database name by using the value of id tag, in below examples, the database name will be “dataSource”.

2.1 HSQL example.

	<jdbc:embedded-database id="dataSource" type="HSQL">
		<jdbc:script location="classpath:db/sql/create-db.sql" />
		<jdbc:script location="classpath:db/sql/insert-data.sql" />
	</jdbc:embedded-database>

2.2 H2 example.

	<jdbc:embedded-database id="dataSource" type="H2">
		<jdbc:script location="classpath:db/sql/create-db.sql" />
		<jdbc:script location="classpath:db/sql/insert-data.sql" />
	</jdbc:embedded-database>

2.3 Derby example.

	<jdbc:embedded-database id="dataSource" type="DERBY">
		<jdbc:script location="classpath:db/sql/create-db.sql" />
		<jdbc:script location="classpath:db/sql/insert-data.sql" />
	</jdbc:embedded-database>

The following “JDBC Driver Connection” will be created :

1. HSQL – jdbc:hsqldb:mem:dataSource
2. H2 – jdbc:h2:mem:dataSource
3. DERBY – jdbc:derby:memory:dataSource

3. Embedded Database In Spring code

Examples to create an embedded database programmatically. If no database name is defined via EmbeddedDatabaseBuilder.setName(), Spring will assign a default database name “testdb”.

import javax.sql.DataSource;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
//...
	@Bean
	public DataSource dataSource() {
		// no need shutdown, EmbeddedDatabaseFactoryBean will take care of this
		EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
		EmbeddedDatabase db = builder
			.setType(EmbeddedDatabaseType.HSQL) //.H2 or .DERBY
			.addScript("db/sql/create-db.sql")
			.addScript("db/sql/insert-data.sql")
			.build();
		return db;

@ComponentScan({ "com.mkyong" })
@Configuration
public class SpringRootConfig {
	@Autowired
	DataSource dataSource;
	@Bean
	public JdbcTemplate getJdbcTemplate() {
		return new JdbcTemplate(dataSource);

The following “JDBC Driver Connection” will be created :

1. HSQL – jdbc:hsqldb:mem:testdb
2. H2 – jdbc:h2:mem:testdb
3. DERBY – jdbc:derby:memory:testdb

4. Unit Test

A simple unit test example to test a DAO with embedded database.

4.1 SQL scripts to create table and insert data.

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name VARCHAR(30),
  email  VARCHAR(50)
);

INSERT INTO users VALUES (1, 'mkyong', 'mkyong@gmail.com');
INSERT INTO users VALUES (2, 'alex', 'alex@yahoo.com');
INSERT INTO users VALUES (3, 'joel', 'joel@gmail.com');

4.2 Unit Test a UserDao with H2 embedded database.

 
package com.mkyong.dao;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import com.mkyong.model.User;
public class UserDaoTest {
    private EmbeddedDatabase db;
    UserDao userDao;
    @Before
    public void setUp() {
        //db = new EmbeddedDatabaseBuilder().addDefaultScripts().build();
    	db = new EmbeddedDatabaseBuilder()
    		.setType(EmbeddedDatabaseType.H2)
    		.addScript("db/sql/create-db.sql")
    		.addScript("db/sql/insert-data.sql")
    		.build();
    @Test
    public void testFindByname() {
    	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(db);
    	UserDaoImpl userDao = new UserDaoImpl();
    	userDao.setNamedParameterJdbcTemplate(template);
    	User user = userDao.findByName("mkyong");
    	Assert.assertNotNull(user);
    	Assert.assertEquals(1, user.getId().intValue());
    	Assert.assertEquals("mkyong", user.getName());
    	Assert.assertEquals("mkyong@gmail.com", user.getEmail());
    @After
    public void tearDown() {
        db.shutdown();

Done.

5. View the embedded database content?

In order to access or view the embedded database, the particular “database manager tool” must start with the same Spring container or JVM, which started the embedded database. Furthermore, the “database manager tool” must start after the embedded database bean, best resolve this by observing the Spring’s log to identify loading sequence of the beans.

The “HSQL database manager” is a good tool, just start within the same Spring container.

@PostConstruct
public void startDBManager() {
	//hsqldb
	//DatabaseManagerSwing.main(new String[] { "--url", "jdbc:hsqldb:mem:testdb", "--user", "sa", "--password", "" });
	//derby
	//DatabaseManagerSwing.main(new String[] { "--url", "jdbc:derby:memory:testdb", "--user", "", "--password", "" });
	//h2
	//DatabaseManagerSwing.main(new String[] { "--url", "jdbc:h2:mem:testdb", "--user", "sa", "--password", "" });

Or Spring XML with MethodInvokingBean

<bean depends-on="dataSource"
	class="org.springframework.beans.factory.config.MethodInvokingBean">
	<property name="targetClass" value="org.hsqldb.util.DatabaseManagerSwing" />
	<property name="targetMethod" value="main" />
	<property name="arguments">
		<list>
			<value>--url</value>
			<value>jdbc:derby:memory:dataSource</value>
			<value>--user</value>
			<value>sa</value>
			<value>--password</value>
			<value></value>
		</list>
	</property>
</bean>

Figure : HSQL database manager tool, access the embedded database.

6. Connection Pool

Example to connect a dbcp connection pool.

	<!-- jdbc:hsqldb:mem:dataSource -->
	<jdbc:embedded-database id="dataSource" type="HSQL">
		<jdbc:script location="classpath:db/sql/create-db.sql" />
		<jdbc:script location="classpath:db/sql/insert-data.sql" />
	</jdbc:embedded-database>
	<bean id="jdbcTemplate" 
		class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" >
		<constructor-arg ref="dbcpDataSource" />
	</bean>
	<bean id="dbcpDataSource" class="org.apache.commons.dbcp2.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
		<property name="url" value="jdbc:hsqldb:mem:dataSource" />
		<property name="username" value="sa" />
		<property name="password" value="" />
	</bean>

References

1. JDBC embedded database support
2. Spring MethodInvokingFactoryBean Example
3. Spring – View content of HSQLDB embedded database