Spring embedded database examples

hsql database manager tool

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.

pom.xml

	<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-db.sql

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name VARCHAR(30),
  email  VARCHAR(50)
);
insert-data.sql

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.

UserDaoTest.java
 
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.

hsql database manager tool

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>

Download Source Code

References

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

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Johaness
Guest
Johaness

it wont works, wont works at all!

Jatin
Guest
Jatin

How can we join two separate script files with custom separators and create a single datasource from their combination?

.addScript(“abc.sql”).setSeparator(“;”).addScript(“def.sql”).setSeparator(“/;”).build();
The above code sets separator as “/;” which is at the end.

Kalahasthi Satyanarayana
Guest
Kalahasthi Satyanarayana
Publishing failed with multiple errors File not found: L:UsersDOS59529Desktopspring-embedded-database-mastertargetm2e-wtpweb-resourcesMETA-INFMANIFEST.MF. File not found: L:UsersDOS59529Desktopspring-embedded-database-mastertargetm2e-wtpweb-resourcesMETA-INFmavencom.mkyong.commonspring-mvc-embedded-dbpom.properties. File not found: L:UsersDOS59529Desktopspring-embedded-database-mastertargetm2e-wtpweb-resourcesMETA-INFmavencom.mkyong.commonspring-mvc-embedded-dbpom.xml. Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/config/db/DerbyDataSource.class’. Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/config/db/H2DataSource.class’. Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/config/db/HsqlDataSource.class’. Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/config/SpringRootConfig.class’. Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/config/SpringWebConfig.class’. Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/dao/UserDao.class’. Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/dao/UserDaoImpl$UserMapper.class’. Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/dao/UserDaoImpl.class’. Resource is out… Read more »
Edwin
Guest
Edwin

I like the post when it having project in github :) There is no need for me to download and unzip the source when i just need some reference

Vizwa
Guest
Vizwa

Had few issues with HSQL configuration and gone through this. Great learning. My issues was resolved. Thanks.

Ramesh Babu Y
Guest
Ramesh Babu Y

i deployed the war generated into tomcat , but 404 error is comming when i am trying to access using the url

Tristan
Guest
Tristan

You have typed “spring-mvc” instead of “spring-jdbc”.

Sakthivel P
Guest
Sakthivel P

Why DataBase name you have mentioned as “datasource” quite confusing with actual DataSource

Jabsiri Jjab
Guest
Jabsiri Jjab

Posting an article is always good to see. Thank you.

I have one question.

Use the “DatabaseManagerSwing” What “java.awt.HeadlessException” How to solve this happen?

Jabsiri Jjab
Guest
Jabsiri Jjab

Use the System.setProperty(“java.awt.headless”, “false”); has solved the problem.

Marian
Guest
Marian

In case you are using SpringBootApplication, you can run the DB manager right after the application like this:

public static void main(String[] args) {
SpringApplicationBuilder builder = new SpringApplicationBuilder(YourApplication.class);
builder.headless(false).run(args);
DatabaseManagerSwing.main(
new String[] { “–url”, “jdbc:hsqldb:mem:testdb”, “–user”, “sa”, “–password”, “”});
}

Scott
Guest
Scott

Thanks! This was a big help.

I am doing everything without the webapp stuff and it seems to be working. I have a test class (like UserDaoTest) with the EmbeddedDatabaseBuilder call that calls the sql scripts and it works great. If I try to do anything in my Main class and call main in a test class, I am getting nullPointer issues. Shouldn’t it be initialized since I am building the embedded db in config.db?

Ketan Soni
Guest
Ketan Soni
Thanks for the tutorial – very helpful. I had question on how you go about querying the database given the database is only alive for the duration of the test after which it is shutdown and there is no way to understand what may have caused the test to fail. I tried putting a breakpoint and then tried using the h2 console but it seemed it would not work as it was currently stopped at a breakpoint. I haven’t tried the HSQL as yet that was going to be the next thing I was going to try. Just wandering if… Read more »
mkyong
Guest
mkyong

This is what I do, simple and works, rerun the failed test, add more print statements to display the database values. The concept is same with the mocking test, when the test is completed, the values will be deleted. Last resort, just switch to the real database for testing.

For H2 console, make sure it start within the same unit test, else you will unable to connect to the database. Refer Step 5.

Ketan Soni
Guest
Ketan Soni

Thanks for your response. The way I got round the problem was to include a very long sleep at the end of the test to give me the time to run the queries I need via the console. Yes print statements work but sometime’s you want to be able to see the data to confirm the test is correct. The problem was around placing a breakpoint in the code where you want to check the results which stops any processing of queries via the console too.

4lberto
Guest
4lberto

Great Lesson.

Just one detail… Form my point fo view if your test connects to database you shuldn’t call it “Unit test” but “Integration test (maybe simple)”. Unit test only test the code on the class, mocking dependencies, thus not using the real ones as you were doing.

Cheers!

mkyong
Guest
mkyong

Thanks for your inputs, may be I should change the name to Integration test :)

junkins
Guest
junkins

why dont you go sell watermelons instead of copying other tutorials here ,also you dont test them before coping them what a lazy person

Diego Manuel Benitez Enciso
Guest
Diego Manuel Benitez Enciso

Muchas gracias por esta publicacion!