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 about the configuration of the H2 database with a spring boot application.
Table of Contents
- Creating spring boot application with H2 database configuration
- H2 database console
- Loading the H2 database with tables and table rows.
- Fetching database records using JPA
- Conclusion
Creating spring boot application with H2 database configuration
Create a spring boot project with the 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 the 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 folder 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. We will not loose the database content 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. We cam use the default username of the H2 database (sa) and password we can leave 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.
Click on the connect button. We should be able to access the H2 database now.
You may be wondering where the H2 database files are getting stored. Well, the answer is under the 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.
We can set the database URL with the value jdbc:h2:./asbdb.mv.db This will create database files inside the current directory, which is our spring boot application folder.
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. Spring executes these statements during the application startup.
Create a file with the name data.sql under /src/main/resources directory of the spring boot application.
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 the H2 console again, we should be able to observe the Employee table with two table rows.
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 the H2 in-memory database.
Conclusion
In conclusion, we learned how to use the H2 in-memory database and how to configure the spring boot application to use the 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.
Finally, the sample application code is available on Github.
