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

pom.xml

<properties>		
    <spring.version>4.1.6.RELEASE</spring.version>
    <hsqldb.version>2.3.2</hsqldb.version>
    <h2.version>1.4.187</h2.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>${h2.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', '[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.

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("[email protected]
 
   ", 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>

Download Source Code

Github – To Be Update…

References

<LearnWLS> Setting Up Examples to Run with a Derby Database

The examples installed with Oracle WebLogic Server include the Derby "examples" database. The wl_se...
  • sun_xo
  • sun_xo
  • 2017年03月07日 13:34
  • 89

使用embedded database

首先新建  embeddedDataSource.xml,         xmlns="http://www.springframework.org/schema/beans" ...

the Serverless design of embedded database system(嵌入式数据库的无服务设计的概念)

本文有选择性的翻译。 VistaDB Embedded Serverless Design VistaDB.Net > See It! > Technical > Serverl...

The interpretation of Embedded Database(嵌入式数据库的介绍)

What is an embedded database?(有空再翻译) 第一段对嵌入式数据库的定义,比较简练。 An embedded database (Wikipedia Entry) ...

Spring Boot Examples

  • 2017年12月02日 16:36
  • 206KB
  • 下载

Spring SimpleJdbcTemplate Querying examples

Here are few examples to show how to use SimpleJdbcTemplate query() methods to query or extract data...

Spring JdbcTemplate Querying examples

Here are few examples to show you how to use JdbcTemplate query() methods to query or extract data f...

Spring Data JPA Examples 官方源码

  • 2013年01月22日 11:52
  • 71KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Spring embedded database examples
举报原因:
原因补充:

(最多只允许输入30个字)