Spring Boot : Unit Test with In-Memory Database – Example

If you want to see an minimal example how to use In-Memory database to do unit  test in Spring boot.

This page is for you.

This example is a part of  Spring Boot with all examples

Full Code with Spring Boot 1.5.8.RELEASE:

7_spring-boot-database-jdbi.zip

What you need for run this:

  • Maven 3
  • Java 8
  • Mysql 5.x

What the code will do:

  • Show you how to use In-Memory database with unit  tests

Warning

Explain:

This is the file structure:

In pom.xml you need this:

<?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>1.5.8.RELEASE</version>
    </parent>

    <groupId>com.surasint.example</groupId>
    <artifactId>spring-boot-07</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- for db annotation, ex @Transactional -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- mysql connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.10</version>
        </dependency>
        <!-- jdbi: db table mapper -->
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi</artifactId>
            <version>2.62</version>
        </dependency>
        <!-- junit: if you are using spring-boot-starter-test , you do not need this -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- mockito: if you are using spring-boot-starter-test , you do not need this -->
        <dependency>
            <groupId>org.mockito</groupId>
            <artifactId>mockito-core</artifactId>
            <version>1.10.19</version>
            <scope>test</scope>
        </dependency>
        <!-- h2db for in memory test-->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.196</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Line 42 – 54  is Unit test and Mockito.

Line 56 – 61 is h2db which is an In-Memory database.

In UserDaoInMemTest.java

package com.surasint.example.db;

import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mockito.InjectMocks;
import org.mockito.Spy;
import org.mockito.runners.MockitoJUnitRunner;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;

import static org.mockito.Mockito.doReturn;

@RunWith(MockitoJUnitRunner.class)
public class UserDaoInMemTest {

    private static Handle handle;

    @InjectMocks
    @Spy
    private UserDao target;

    @Before
    public void before() throws ClassNotFoundException, IOException {
        //setup in memory database
        Class.forName("org.h2.Driver");
        DBI dbi = new DBI("jdbc:h2:mem:test");
        handle = dbi.open();
        handle.execute("set mode MySQL;");

        //run sql scrip to create our table
        String sql = new String(Files.readAllBytes(Paths.get("./dbscript.sql")));
        handle.createScript(sql).executeAsSeparateStatements();

        //mock (as spy) to return handle ( it is like a DB connection, but for the JDBI framework)
        doReturn(handle).when(target).getHandle();

        //insert some data
        handle.execute("INSERT INTO users (id,username) VALUES (1,'username1');");
        handle.execute("INSERT INTO users (id,username) VALUES (2,'username2');");

    }



    @After
    public void after() {
        //clean data
        handle.execute("DROP ALL OBJECTS;");
    }

    @Test
    public void test_list_ok() {
        //given

        //when
        List<UserBean> userBeanList = target.list();

        //then
        Assert.assertEquals(userBeanList.size(), 2);
    }

    @Test
    public void test_insert_ok() {
        //given

        //when
        List<UserBean> beforeList = target.list();
        UserBean userBean = new UserBean();
        userBean.setUsername("abc");
        target.insert(userBean);
        List<UserBean> afterList = target.list();

        //then
        Assert.assertEquals(beforeList.size(), 2);
        Assert.assertEquals(afterList.size(), 3);
    }
}

The keys here are:

  • Line 27 : @Spy we are going to “spy” out target class so that we can override the UserDao.getHandle() method
  • Line 34 – 36 : To setup to the In-Memory database
  • Line 39 – 40: To create our table to the database
  • Line 43 : do override to UserDao.getHandle() to return “handle”

Then you do the unit tests like in test_list_ok() and test_insert_ok(). 

  • test_list_ok() : test that we read data correctly by verify that there are 2 rows
  •  test_insert_ok(): test that we can create data by verify that there are 3 rows after call target.insert(userBean)