Introduction
This tutorial will show you how to integrate in-memory H2 database with Spring. We will see here simple example to understand how it works. Here we will use Spring JDBC API to perform database activities. You can use this example with Spring based applications, such as, Spring Core, Spring MVC etc.
You may also like to read Integrate H2 In-memory Database with Spring Boot 2.1.
What is an in-memory database?
An in memory database is created when an application starts up and destroyed when the application is stopped.
Using in memory database has lots of advantages such as:
- No need to setup the database
- Almost zero configuration
- Almost zero maintenance
- It is very easy to use for learning, proof of concepts and unit testing
H2 is one of the popular in memory database and Spring Boot provides very easy configuration for an in memory database like H2.
Why is in memory database required?
Let’s consider a cenario when you want to do a quick proof of concept(POC) and using a traditional database involves a lot of overhead.
Another scenario, on your unit tests:
- You don’t want them to fail when some data/schema get changed in the database.
- Multiple developers might be running the tests in parallel.
Prerequisites
Spring 4.x, H2 1.4.196, Java 1.8, Eclipse 4.9, Maven 3.x
Example with Source Code
Here we will create standalone Spring project and we will use Spring JDBC to interact with H2 in-memory database.
Creating Project
Here we will create maven based standalone project in Eclipse with the following group and artifact id.
If you want to create Gradle based project then go to the section Build Script – build.gradle to see how to create build script.
Group id: com.roytuts, Artifact id: spring-h2
The following project structure gets created in Eclipse:

Updating pom.xml
Please make sure you have the following dependencies in pom.xml file.
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>spring-h2</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>spring-h2</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.1.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.1.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.1.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.6.RELEASE</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.196</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
Look in the above pom file we have added the required dependencies for Spring and H2 database.
Build Script – build.gradle
If you want to create gradle based project, then use below build.gradle file:
buildscript {
ext {
springVersion = '4.1.6.RELEASE'
}
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
classpath("io.spring.gradle:dependency-management-plugin:1.0.7.RELEASE")
}
}
apply plugin: 'java'
apply plugin: 'io.spring.dependency-management'
sourceCompatibility = 1.8
targetCompatibility = 1.8
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
compile("org.springframework:spring-core:${springVersion}")
compile("org.springframework:spring-beans:${springVersion}")
compile("org.springframework:spring-context:${springVersion}")
compile("org.springframework:spring-jdbc:${springVersion}")
runtime("com.h2database:h2:1.4.196")
}
Creating SQL Scripts
We are using in-memory database, so we want to create table or insert data into the in-memory database using SQL scripts. You can also enable H2 database console and access the database using browser but here we will only see how to create table or insert data into H2 using SQL scripts.
Create Table Script
Create a script called create-table.sql and put it under src/main/resources/sql folder.
CREATE TABLE `user_detail` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) NOT NULL,
`last_name` varchar(15) NOT NULL,
`email` varchar(100) NOT NULL,
`dob` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
);
Insert Data Script
Create a file called insert-data.sql and put it under directory src/main/resources/sql.
insert into `user_detail`(`id`,`first_name`,`last_name`,`email`,`dob`) values (7,'Soumitra','Roy','contact@roytuts.com','30-08-1986'),(8,'Souvik','Sanyal','souvik.sanyal@email.com','30-09-1991');
Creating DAO
We will create DAO layer code to interact with the H2 database.
DAO Interface
Create below interface to perform CRUD operations.
package com.roytuts.spring.h2.jdbc.dao;
import java.util.List;
import com.roytuts.spring.h2.jdbc.model.UserDetail;
public interface UserDetailDao {
public UserDetail getUserDetail(int id);
public List<UserDetail> getAllUserDetail();
public int addUserDetail(UserDetail userDetail);
public int updateUserDetail(UserDetail userDetail);
public int deleteUserDetail(int id);
}
DAO Implementation
Create corresponding DAO implementation for the above interface to provide actual operations.
We will configure this class using XML file, if you want you may configure it using annotation also instead of XML configuration.
package com.roytuts.spring.h2.jdbc.dao.impl;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.transaction.annotation.Transactional;
import com.roytuts.spring.h2.jdbc.dao.UserDetailDao;
import com.roytuts.spring.h2.jdbc.model.UserDetail;
import com.roytuts.spring.h2.jdbc.rowmapper.UserDetailRowMapper;
public class UserDetailDaoImpl implements UserDetailDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Transactional
public UserDetail getUserDetail(int id) {
UserDetail userDetail = (UserDetail) jdbcTemplate.queryForObject("select * from user_detail where id = ?",
new Object[] { id }, new UserDetailRowMapper());
return userDetail;
}
@Transactional
public List<UserDetail> getAllUserDetail() {
List<UserDetail> userDetail = (List<UserDetail>) jdbcTemplate.query("select * from user_detail",
new UserDetailRowMapper());
return userDetail;
}
@Transactional
public int addUserDetail(UserDetail userDetail) {
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
simpleJdbcInsert.withTableName("user_detail").usingGeneratedKeyColumns("id");
Map<String, Object> parameters = new HashMap<String, Object>(4);
parameters.put("first_name", userDetail.getFirstName());
parameters.put("last_name", userDetail.getLastName());
parameters.put("email", userDetail.getEmail());
parameters.put("dob", userDetail.getDob());
Number insertedId = simpleJdbcInsert.executeAndReturnKey(parameters);
return insertedId.intValue();
}
@Transactional
public int updateUserDetail(UserDetail userDetail) {
String sql = "update user_detail set first_name = ?, last_name = ?, email = ?, dob = ? where id = ?";
int resp = jdbcTemplate.update(sql, new Object[] { userDetail.getFirstName(), userDetail.getLastName(),
userDetail.getEmail(), userDetail.getDob(), userDetail.getId() });
return resp;
}
@Transactional
public int deleteUserDetail(int id) {
int resp = jdbcTemplate.update("delete from user_detail where id = ?", id);
return resp;
}
}
Notice in the above class we have used Spring JdbcTemplate to perform our queries.
Creating RowMapper Class
Notice in the above DAO implementation we have used Spring JDBC API along with RowMapper. So we need to provide our custom RowMapper class in order to map table data rows to corresponding model class.
package com.roytuts.spring.h2.jdbc.rowmapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.roytuts.spring.h2.jdbc.model.UserDetail;
public class UserDetailRowMapper implements RowMapper<UserDetail> {
public UserDetail mapRow(ResultSet rs, int row) throws SQLException {
UserDetail userDetail = new UserDetail();
userDetail.setId(rs.getInt("id"));
userDetail.setFirstName(rs.getString("first_name"));
userDetail.setLastName(rs.getString("last_name"));
userDetail.setEmail(rs.getString("email"));
userDetail.setDob(rs.getString("dob"));
return userDetail;
}
}
Creating Model Class
We need to have a model class to represent our table row to Java class attributes.
package com.roytuts.spring.h2.jdbc.model;
public class UserDetail {
private int id;
private String firstName;
private String lastName;
private String email;
private String dob;
public UserDetail() {
}
public UserDetail(int id, String firstName, String lastName, String email, String dob) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.dob = dob;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getDob() {
return dob;
}
public void setDob(String dob) {
this.dob = dob;
}
@Override
public String toString() {
return "UserDetail [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", email=" + email
+ ", dob=" + dob + "]";
}
}
Creating Service
We need to create Spring service class to interact with the DAO layer.
Service Interface
Create service interface to provide loose coupling between the components.
package com.roytuts.spring.h2.jdbc.service;
import java.util.List;
import com.roytuts.spring.h2.jdbc.model.UserDetail;
public interface UserDetailService {
public UserDetail getUserDetail(int id);
public List<UserDetail> getAllUserDetail();
public int addUserDetail(UserDetail userDetail);
public int updateUserDetail(UserDetail userDetail);
public int deleteUserDetail(int id);
}
Service Implementation
Create corresponding service implementation class.
package com.roytuts.spring.h2.jdbc.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import com.roytuts.spring.h2.jdbc.dao.UserDetailDao;
import com.roytuts.spring.h2.jdbc.model.UserDetail;
import com.roytuts.spring.h2.jdbc.service.UserDetailService;
public class UserDetailServiceImpl implements UserDetailService {
@Autowired
private UserDetailDao userDetailDao;
@Override
public UserDetail getUserDetail(int id) {
return userDetailDao.getUserDetail(id);
}
@Override
public List<UserDetail> getAllUserDetail() {
return userDetailDao.getAllUserDetail();
}
public UserDetailDao getUserDetailDao() {
return userDetailDao;
}
@Override
public int addUserDetail(UserDetail userDetail) {
return userDetailDao.addUserDetail(userDetail);
}
@Override
public int updateUserDetail(UserDetail userDetail) {
return userDetailDao.updateUserDetail(userDetail);
}
@Override
public int deleteUserDetail(int id) {
return userDetailDao.deleteUserDetail(id);
}
}
Creating Spring Configuration
Now we have written Spring DAO, Spring Service layer code but we need to let container know where our Spring Service or Spring DAO reside.
So create below spring-config.xml file and put it under classpath directory src/main/resources.
Putting the SQL scripts into classpath and and configuring them using <jdbc:embedded-database/> will execute during application startup.
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">
<!-- Support annotation -->
<context:annotation-config />
<!-- support annotation transaction -->
<tx:annotation-driven
transaction-manager="txManager" />
<jdbc:embedded-database id="dataSource"
type="H2">
<jdbc:script location="classpath:sql/create-table.sql" />
<jdbc:script location="classpath:sql/insert-data.sql" />
</jdbc:embedded-database>
<bean id="txManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- spring jdbc template -->
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- service -->
<bean id="userDetailService"
class="com.roytuts.spring.h2.jdbc.service.impl.UserDetailServiceImpl" />
<!-- dao -->
<bean id="userDetailDao"
class="com.roytuts.spring.h2.jdbc.dao.impl.UserDetailDaoImpl" />
</beans>
So in the above configuration, we have configured for annotation support, transaction support and defined the beans for Spring Service and Spring DAO classes.
Creating Main Class
As you know it’s not a web application, so we need to create main class to test our application.
If you are using the similar configuration for H2 in-memory database then you don’t need to create main class or any special operation. The SQL scripts will be executed while application starts up in the server.
package com.roytuts.spring.h2;
import java.util.List;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.roytuts.spring.h2.jdbc.model.UserDetail;
import com.roytuts.spring.h2.jdbc.service.UserDetailService;
import com.roytuts.spring.h2.jdbc.service.impl.UserDetailServiceImpl;
public class H2Application {
public static void main(String[] args) {
ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("spring-config.xml");
UserDetailService service = ctx.getBean("userDetailService", UserDetailServiceImpl.class);
List<UserDetail> userDetails = service.getAllUserDetail();
System.out.println("All User Details:");
for (UserDetail userDetail : userDetails) {
System.out.println(userDetail);
}
System.out.println();
UserDetail userDetail = service.getUserDetail(7);
System.out.println("User Detail: " + userDetail);
ctx.close();
}
}
In the above main class, I have load the spring-config.xml file to work with the beans.
Then we get the UserDetailService bean and we fetch all users from the database. We also fetch the user detail for a given user id.
You can also perform rest of the operations for database.
Testing the Application
Enough Coding! Let’s test the application.
Run the above main class you will get below output in the console:
All User Details:
UserDetail [id=7, firstName=Soumitra, lastName=Roy, email=contact@roytuts.com, dob=30-08-1986]
UserDetail [id=8, firstName=Souvik, lastName=Sanyal, email=souvik.sanyal@email.com, dob=30-09-1991]
User Detail: UserDetail [id=7, firstName=Soumitra, lastName=Roy, email=contact@roytuts.com, dob=30-08-1986]
You may also like to read Integrate H2 In-memory Database with Spring Boot 2.1.
Source Code
Thanks for reading.
I was struggled for MVC with H2 database.
This example gave me insight about H2 configuration in MVC, especially spring-config.xml.
How to map DAO classes with datasources.
Thank you.