Setting Up H2 for Testing in Spring Boot

Photo by Christina @ wocintechchat.com on Unsplash

Unit testing is an essential part of software development. It helps to ensure that your code is working as expected and that it is free of bugs. H2 is an in-memory database that is often used for unit testing. It is easy to set up and use, and it provides a fast and reliable way to test your code.

In this article, I will show you how to use H2 for unit testing and integration testing, using different application properties for testing and production app. We will create a simple API project in Spring Boot to help us have a quick view of the setup.

Let’s get started, go to start.spring.io and create that demo project. We can call this project UsersService and the dependencies as below

Start Spring Web Page

pom.xml

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

   <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <scope>runtime</scope>
   </dependency>
   <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <scope>runtime</scope>
   </dependency>
   <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
   </dependency>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
   </dependency>
       <dependency>
           <groupId>junit</groupId>
           <artifactId>junit</artifactId>
           <scope>test</scope>
       </dependency>
   </dependencies>

Project file Structure

IDE: Intellji

A bit of details: we are using MySQL as an example for a production database and H2 as a testing database, to achieve having both configuration for easier test we create an application.properties file inside the test folder under resources and turn the mode to MySQL and use the MySQL Dialect so that H2 can perform MySQL based custom queries.

spring.datasource.url=jdbc:h2:mem:test;MODE=MySQL;
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=
spring.datasource.password=
# We add the MySQL Dialect so that it understands and generates the query based on MySQL
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

spring.h2.console.enabled=true
spring.jpa.defer-datasource-initialization=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy

spring.sql.init.mode=always

Next we need to give H2 the base schema and sample data, for us to achieve this we need to create two files inside the resources folder of our test folder. Note: H2 looks for schema.sql and data.sql to create and preload data, if the schema.sql is not found it will use hibernate for table creation.

CREATE TABLE `users_table` (
  `usersId` int NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL,
  `firstName` varchar(100) DEFAULT NULL,
  `lastName` varchar(100) DEFAULT NULL,
  `userRole` varchar(100) DEFAULT NULL,
  `createdAt` date DEFAULT NULL,
  `updatedAt` date DEFAULT current_timestamp(),
  PRIMARY KEY (`usersId`),
  UNIQUE KEY `users_table_un` (`username`)
);

INSERT INTO users_table (username, firstName, lastName, userRole, createdAt, updatedAt)  VALUES('paul_pop', 'paulous', 'cool', 'admin', '2023-06-25','2023-06-25');
INSERT INTO users_table (username, firstName, lastName, userRole, createdAt, updatedAt)  VALUES('kaySlow', 'Kay', 'Slow', 'admin', '2023-06-25','2023-06-25');
INSERT INTO users_table (username, firstName, lastName, userRole, createdAt, updatedAt)  VALUES('ghostNinja', 'Ghost', 'Ninja', 'user', '2023-06-25','2023-06-25');
INSERT INTO users_table (username, firstName, lastName, userRole, createdAt, updatedAt)  VALUES('fastBean', 'Fast', 'Bean', 'user', '2023-06-25','2023-06-25');

Next up, let’s write some unit test for our service class.

package com.paulous.service;

import com.paulous.entity.UsersEntity;
import com.paulous.pojo.UsersPojo;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Date;
import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
@RunWith(SpringRunner.class)
class UsersServiceTest {

    @Autowired
    private UsersService usersService;
    @Test
    void fetchUserByUsername() {
        UsersEntity usersEntity = usersService.fetchUserByUsername("paul_pop");
        assertEquals(usersEntity.getFirstName(), "paulous");

    }

    @Test
    void addNewUser() {
        UsersEntity usersEntity = usersService.addNewUser(new UsersPojo("testUser", "Test", "User", "user"));
        assertEquals(usersEntity.getUsersId(), 5);
        assertEquals(usersEntity.getUsername(), "testUser");
    }

    @Test
    void fetchAllUsers() {
        List<UsersEntity> usersEntityList = usersService.fetchAllUsers();
        assertEquals(usersEntityList.size(),4);
        assertEquals(usersEntityList.get(0).getFirstName(), "paulous");
    }
}

and integration test for our repository and controller

package com.paulous.controller;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.paulous.pojo.UsersPojo;
import com.paulous.repository.UsersRepository;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.http.MediaType;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.web.servlet.MockMvc;

import static org.assertj.core.api.AssertionsForInterfaceTypes.assertThat;
import static org.hamcrest.Matchers.is;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.post;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.*;

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.MOCK)
@AutoConfigureMockMvc
class UsersControllerTest {

    @Autowired
    private MockMvc mockMvc;

    @Autowired
    private UsersRepository usersRepository;



    @Test
    void fetchUserByUsernameEndpoint() throws Exception {

        mockMvc.perform(get("/api/v1/fetchUserByUsername/paul_pop")
                .contentType(MediaType.APPLICATION_JSON))
                .andExpect(status().isOk())
                .andExpect(content()
                        .contentTypeCompatibleWith(MediaType.APPLICATION_JSON))
                .andExpect(jsonPath("statusCode" , is(200)))
                .andExpect(jsonPath("message.firstName", is("paulous")));
        assert(usersRepository.findByUsername("kaySlow").isPresent());
    }

    @Test
    void addUserEndpoint() throws Exception {
        UsersPojo usersPojo=new UsersPojo("bidMore","Bid","More","admin");
        ObjectMapper mapper = new ObjectMapper();
        mockMvc.perform(post("/api/v1/addUser")
                        .contentType(MediaType.APPLICATION_JSON)
                        .content(mapper.writeValueAsString(usersPojo))
                        .accept(MediaType.APPLICATION_JSON))
                .andExpect(status().isCreated())
                .andExpect(content()
                        .contentTypeCompatibleWith(MediaType.APPLICATION_JSON))
                .andExpect(jsonPath("statusCode" , is(201)))
                .andExpect(jsonPath("message.usersId", is(5)));

    }

    @Test
    void fetchAllUsersEndpoint() throws Exception{
        mockMvc.perform(get("/api/v1/fetchAllUsers")
                        .contentType(MediaType.APPLICATION_JSON))
                .andExpect(status().isOk())
                .andExpect(content()
                        .contentTypeCompatibleWith(MediaType.APPLICATION_JSON))
                .andExpect(jsonPath("statusCode" , is(200)))
                .andExpect(jsonPath("message.[0].username", is("paul_pop")));

        assertThat(usersRepository.findAll()).hasSize(5);

    }
}

Finally run your tests with the IDE or using the Command Line. The github repository:

GitHub - paulouskunda/testing_in_spring_using_h2_database: This Repo is a demo on how to setup an…

Happy Writing Tests…