Posted in Spring Core, Spring JDBC

Integrate H2 In-memory Database with Spring

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:

integrate h2 in-memory database with spring

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

download source code

Thanks for reading.

1 thought on “Integrate H2 In-memory Database with Spring”

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *