During spring boot application development, we may want to test the application locally. We may not want to connect the application to the production or other physical databases. The in-memory databases can be used to achieve this.

We can use in-memory databases like H2 while running test cases. We can have a separate test profile, where we can configure H2 database connection details instead of actual database connection details.

In this article, we will learn how to configure the H2 database on spring boot application.

Creating spring boot application with H2 database configurations

Create a spring boot project with a name spring-boot-h2-config-example with the required dependencies. Make sure to add spring-boot-starter-web, spring-boot-starter-data-jpa(or JDBC dependency) and H2 database dependencies on applications pom.xml file while creating the application.

For in-memory databases like H2, Hyper SQL database or derby, if we add the required dependency in the classpath, spring boot automatically creates required configurations.

Here is the pom.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<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>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.6.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.asb.example</groupId>
	<artifactId>spring-boot-h2-config-example</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring-boot-h2-config-example</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
		<maven-jar-plugin.version>3.1.1</maven-jar-plugin.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Update the application.properties file under src/main/resources directory with the below content.

spring.datasource.url=jdbc:h2:~/asbdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.data-username=sa
spring.datasource.data-password=

#Enable H2 console with path:h2-db-console
spring.h2.console.enabled=true
spring.h2.console.path=/h2-db-console
spring.h2.console.settings.trace=true
  • spring.datasource.url: This is the database connection URL. Database content will not be lost if we set the property DB_CLOSE_DELAY=1 on the closure of the database connection. Content will be available as long as the virtual machine is running.
  • To avoid the database from getting closed on the virtual machine exit, we can use DB_CLOSE_ON_EXIT=FALSE property.
  • spring.datasource.data-username/password: Username and password of database. The default username of the H2 database is sa and password can be left out empty.
  • spring.h2.console.enabled: Enables the H2 database console, in the specified path.
  • spring.h2.console.path: This property is used to set the H2 console path. Default console path is /h2-console.
  • spring.h2.console.settings.trace: Prints the trace logs to console.

H2 database console

In the above section, we have configured the H2 database details. Now start the spring boot application.

We can access the H2 database console on http://localhost:8080/h2-db-console.

H2 database console

Click on the connect button. We should be able to access the H2 database now.

H2 database console spring boot

You may be wondering where the H2 database files are getting stored. Well, the answer is under user directory. In a Windows machine, in our example, we can find the database files under C:/Users/ directory. This is because we have used the database URL with the value jdbc:h2:~/asbdb.

H2 DB Files

We can set the database URL with the value jdbc:h2:./asbdb. This will create database files inside the current directory, which is our spring boot application folder.

H2 database files

Loading the H2 database with tables and table rows.

We can load the H2 in-memory database with tables and table rows during spring boot application startup. For this, we have to create a data.sql file in our classpath.

The file should contain the SQL statements. These statements get executed during application startup.

Create a file with name data.sql under /src/main/resources directory of the spring boot application.

Load initial data to H2 database

Add the following SQL statements to the data.sql file.

DROP TABLE IF EXISTS EMPLOYEE;

CREATE TABLE EMPLOYEE(
	ID INT PRIMARY KEY, 
	NAME VARCHAR(255)
);

INSERT INTO EMPLOYEE VALUES(1, 'Arun');
INSERT INTO EMPLOYEE VALUES(2, 'John');

If we restart the spring boot application and log in to H2 console again, we should be able to observe the Employee table with two table rows.

H2 load initial data tables - spring boot

Fetching database records using JPA

Create a class called Employee.java JPA entity class.

package com.asb.example;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Employee {

	@Id
	private int id;
	private String name;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

This is the JPA entity class, which is mapped with the EMPLOYEE table of our H2 database.

Create a JPA repository by creating the EmployeeRepository.java interface.

package com.asb.example;

import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

}

Create a REST controller EmployeeController.java class.

package com.asb.example;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class EmployeeController {

	@Autowired
	private EmployeeRepository employeeRepository;
	
	@GetMapping("/employees")
	public List<Employee> getAllEmployees() {
		return employeeRepository.findAll();
	}
}

We have exposed a /employees endpoint to return all available employee records from the H2 database.

Restart the application, and we can fetch available employee records from H2 in-memory database.

Conclusion

In this article, we learned how to use the H2 in-memory database and how to configure spring boot application to use H2 database.

We also learned how to load the H2 database with initial SQL scripts. We then fetched database records from the H2 database using JPA.

The sample application code is available on Github. Happy coding! 🙂

You may also interested in