Spring embedded database examples

转载 2016年05月30日 10:36:42

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

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


    <!-- Spring JDBC -->
    <!-- HyperSQL DB -->
    <!-- H2 DB -->
    <!-- Derby DB -->

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" />

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" />

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" />

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;
  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
    return db;
@ComponentScan({ "com.mkyong" })
public class SpringRootConfig {
  DataSource dataSource;
  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.


  name VARCHAR(30),
  email  VARCHAR(50)


INSERT INTO users VALUES (1, 'mkyong', '[email protected]
INSERT INTO users VALUES (2, 'alex', '[email protected]
INSERT INTO users VALUES (3, 'joel', '[email protected]

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;
  public void setUp() {
    //db = new EmbeddedDatabaseBuilder().addDefaultScripts().build();
    db = new EmbeddedDatabaseBuilder()
  public void testFindByname() {
    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(db);
    UserDaoImpl userDao = new UserDaoImpl();
    User user = userDao.findByName("mkyong");
    Assert.assertEquals(1, user.getId().intValue());
    Assert.assertEquals("mkyong", user.getName());
    Assert.assertEquals("[email protected]
   ", user.getEmail());
  public void tearDown() {


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.

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

Or Spring XML with MethodInvokingBean

<bean depends-on="dataSource"
  <property name="targetClass" value="org.hsqldb.util.DatabaseManagerSwing" />
  <property name="targetMethod" value="main" />
  <property name="arguments">

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" />
  <bean id="jdbcTemplate" 
    class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" >
    <constructor-arg ref="dbcpDataSource" />
  <bean id="dbcpDataSource" class="org.apache.commons.dbcp2.BasicDataSource"
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:dataSource" />
    <property name="username" value="sa" />
    <property name="password" value="" />

Download Source Code

Github – To Be Update…


理解Spring Boot

理解Spring Boot作者:chszs,未经博主允许不得转载。经许可的转载需注明作者和博客主页:http://blog.csdn.net/chszsSpring Boot是一个偏执的开源框架,它可...
  • chszs
  • chszs
  • 2016年02月16日 10:16
  • 34797


1、使用org.springframework.jdbc.datasource.DriverManagerDataSource  说明:DriverManagerDataSource建立连接是只要有...
  • yangyz_love
  • yangyz_love
  • 2012年11月19日 13:48
  • 91317


  • u011008029
  • u011008029
  • 2015年10月14日 14:38
  • 1757

spring 数据库

1.配置数据源 Spring提供了在Spring上下文中配置数据源Bean的多种方式,包括: 通过JDBC驱动程序定义的数据源 通过JNDI查找的数据源 连接池的数据源 1.1使用JNDI数据源 ...
  • GeoJava
  • GeoJava
  • 2017年02月18日 11:21
  • 350


1,Data access with JDBC 使用springJdbc非常简单。在我们写daoImpl的时候只需要继承spring的org.springframework.jdbc.core.su...
  • liuhenghui5201
  • liuhenghui5201
  • 2014年02月27日 22:51
  • 2267


spring配置jdbc连接池   class="org.apache.commons.dbcp.BasicDataSource">       value=...
  • oemoon
  • oemoon
  • 2013年08月26日 23:08
  • 10675


  • fenglibing
  • fenglibing
  • 2013年11月21日 12:27
  • 13712

Spring Boot 运行原理之自动配置

我们知道Springboot 提倡的是零配置,但是在我们开发工作中又是不可避免出现第三方软件的结合,需要我们对这些第三方的软件进行一些结合性或必要的属性进行赋值,而这时Spring boot 会帮我们...
  • mengdonghui123456
  • mengdonghui123456
  • 2017年07月25日 14:38
  • 2650

基于spring boot项目的多数据源配置与分布式事务处理总结

多数据源配置 项目存在10个数据源,如下 core_biz 业务逻辑 数据库core_sys 系统设置 数据库fund_pool 资金池 数据库 分用户拆分了8个库 针对这10个数据...
  • he90227
  • he90227
  • 2016年09月10日 10:53
  • 8762


  • liuhuanchao
  • liuhuanchao
  • 2016年04月08日 15:35
  • 806
您举报文章:Spring embedded database examples