Spring embedded database examples

Let us see how to configure the embedded database engines like HSQL, H2 and Derby in Spring framework.

Technologies used :

Spring 4.1.6.RELEASE
jUnit 4.1.2
Gradle

Embedded databases used :

HSQLDB 2.3.2
H2 1.4.187
Derby 10.11.1.1

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.

Project Dependency
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.

build.gradle

buildscript {
	ext {
		springBootVersion = '2.0.2.RELEASE'
	}
	repositories {
		mavenCentral()
	}
	dependencies {
		classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
	    classpath("net.sf.proguard:proguard-gradle:6.0.3")
	}
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

group = 'com.javaskool'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = 1.8

repositories {
	mavenCentral()
}


dependencies {
	compile('org.springframework.boot:spring-boot-starter-batch')
	compile('org.springframework.boot:spring-boot-starter-cache')
	compile('org.springframework.boot:spring-boot-starter-data-jpa')
	compile('org.springframework.boot:spring-boot-starter-data-rest')
	compile('org.springframework.boot:spring-boot-starter-freemarker')
	compile('org.springframework.boot:spring-boot-starter-integration')
	compile('org.springframework.boot:spring-boot-starter-jersey')
	compile('org.springframework.boot:spring-boot-starter-mail')
	compile('org.springframework.boot:spring-boot-starter-security')
	compile('org.springframework.boot:spring-boot-starter-validation')
	compile('org.springframework.boot:spring-boot-starter-web')
	compile('org.springframework.session:spring-session-core')
	compile('org.springframework.boot:spring-boot-autoconfigure')
	compile ('com.zaxxer:HikariCP:2.7.9')
	compileOnly('org.projectlombok:lombok')

	compile("com.fasterxml.jackson.core:jackson-databind")
	compile('org.springframework.boot:spring-boot-starter-web-services')
	
	
	testCompile('org.springframework.boot:spring-boot-starter-test')
	testCompile('org.springframework.batch:spring-batch-test')
	testCompile('org.springframework.security:spring-security-test')
	
compile group: 'commons-dbcp', name: 'commons-dbcp', version: '1.4'
compile group: 'commons-lang', name: 'commons-lang', version: '2.6'
compile group: 'mysql', name: 'mysql-connector-java', version: '5.1.47'

// https://mvnrepository.com/artifact/org.hsqldb/hsqldb
compile group: 'org.hsqldb', name: 'hsqldb', version: '2.4.1'

}

Embedded Database in Spring XML

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”.

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>

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>

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>

Here is the “JDBC Driver Connection” :

HSQL – jdbc:hsqldb:mem:dataSource
H2 – jdbc:h2:mem:dataSource
DERBY DB – jdbc:derby:memory:dataSource

Embedded Database In Spring code

You can also 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.javaskool" })
@Configuration
public class SpringRootConfig {

	@Autowired
	DataSource dataSource;

	@Bean
	public JdbcTemplate getJdbcTemplate() {
		return new JdbcTemplate(dataSource);
	}

Here is the “JDBC Driver Connection” :

HSQL – jdbc:hsqldb:mem:testdb
H2 – jdbc:h2:mem:testdb
DERBY – jdbc:derby:memory:testdb

Unit Test

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

SQL scripts to create table and insert data

<i>create-db.sql</i>

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

<i>insert-data.sql</i>

INSERT INTO users VALUES (1, 'javaskool', 'javaskool@gmail.com');
INSERT INTO users VALUES (2, 'alex', 'alex@yahoo.co.in');
INSERT INTO users VALUES (3, 'james', 'joel@gmail.com');

Unit Test a UserDao with H2 embedded database

UserDaoTest.java

package com.javaskool.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.javaskool.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("javaskool");
  
    	Assert.assertNotNull(user);
    	Assert.assertEquals(1, user.getId().intValue());
    	Assert.assertEquals("javaskool", user.getName());
    	Assert.assertEquals("javaskool@gmail.com", user.getEmail());

    }

    @After
    public void tearDown() {
        db.shutdown();
    }

}

View the embedded database content

To view the embedded database, “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 tool, you need to 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", "" });

}

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.

Connection Pool

How 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>