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 :
- Spring 4.1.6.RELEASE
- jUnit 4.1.2
- Maven 3
Embedded databases tested :
- HSQLDB 2.3.2
- H2 1.4.187
- 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 :
- HSQL –
jdbc:hsqldb:mem:dataSource
- H2 –
jdbc:h2:mem:dataSource
- 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 :
- HSQL –
jdbc:hsqldb:mem:testdb
- H2 –
jdbc:h2:mem:testdb
- 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', '[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;
@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>
it wont works, wont works at all!
You have typed “spring-mvc” instead of “spring-jdbc”.
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!
Thanks for your inputs, may be I should change the name to Integration test 🙂
why dont you go sell watermelons instead of copying other tutorials here ,also you dont test them before coping them what a lazy person
@junkins :Do one thing go and write a tutorial yourself..It is easy to blame others but difficult to take a step and do something
In what package or dependency is DatabaseManagerSwing located?
Thanks for this, you got me 1000x closer. My database scripts aren’t being run, any ideas why? Thank you.
Hello!
Thank you for a nice lesson it’s really useful.
Let me ask you a question about
UserDaoTest
class. What exactly it intended to testUserDao
orUserDaoImpl
?If it should test
UserDao
why it does not useuserDao
instance variable?If it should test
UserDaoImpl
why it doesn’t calledUserDaoImplTest
?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.
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 of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/model/User.class’.
Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/servlet3/MyWebInitializer.class’.
Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/com/mkyong/web/controller/WelcomeController.class’.
Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/db/sql/create-db.sql’.
Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/db/sql/insert-data.sql’.
Resource is out of sync with the file system: ‘/spring-mvc-embedded-db/target/classes/logback.xml’.
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
Had few issues with HSQL configuration and gone through this. Great learning. My issues was resolved. Thanks.
i deployed the war generated into tomcat , but 404 error is comming when i am trying to access using the url
Why DataBase name you have mentioned as “datasource” quite confusing with actual DataSource
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?
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”, “”});
}
Use the System.setProperty(“java.awt.headless”, “false”); has solved the problem.
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?
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 you had successfully managed to query the database with H2 whilst running your unit tests.
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.
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.
Muchas gracias por esta publicacion!