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:


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



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">

        <!-- for db annotation, ex @Transactional -->
        <!-- mysql connector -->
        <!-- jdbi: db table mapper -->
        <!-- junit: if you are using spring-boot-starter-test , you do not need this -->
        <!-- mockito: if you are using spring-boot-starter-test , you do not need this -->
        <!-- h2db for in memory test-->

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;

public class UserDaoInMemTest {

    private static Handle handle;

    private UserDao target;

    public void before() throws ClassNotFoundException, IOException {
        //setup in memory database
        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")));

        //mock (as spy) to return handle ( it is like a DB connection, but for the JDBI framework)

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


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

    public void test_list_ok() {

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

        Assert.assertEquals(userBeanList.size(), 2);

    public void test_insert_ok() {

        List<UserBean> beforeList = target.list();
        UserBean userBean = new UserBean();
        List<UserBean> afterList = target.list();

        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)