Using H2 In-Memory to test your DAL

How should we test the Data Access Layer code?

Many developers ask that question. Similar to the other layers of your system, it should be fully tested to prevent unexpected and random behavior in production. There are many ways to achieve that, among of which are mocks or in-memory database.

The problem with mocks is that instead of testing the validity of your SQL queries (syntax and execution), you will only be testing the validity of the system’s flow. On the other hand, using the in-memory database will validate both! That is why I prefer it over mocking. But, you should keep in mind that the SQL syntax might differ from one database engine to another.

In this blog, I will be giving an example of using “H2 In-Memory” in unit tests. The code of this example is available on my GitHub account.

H2 In-Memory in Action

So, let us see H2 in action 🙂

For the sake of this blog, I will assume we want to test three SQL operations on the table “Members” having the following model:

ID      | NAME        |

The SQL operations to be covered in this blog are:

  1. Create Table
  2. Insert (batch of prepared statements)
  3. Select *

Code Explanation

The example will be based on five files (pom.xml,,, and H2MembersRepositoryTest). In this section, I will give a brief explanation of each file.

Pom.xml (Maven Dependencies):

First, let us modify our pom file (as shown below) to make our project depend on two projects:

  1. com.h2database: Using this dependency, Maven will take care of downloading the h2 jar file we will be referencing in our tests.
  2. Junit: a unit testing framework for Java

This class represents a Member record. It consists of a factory method that returns an instance of the Member class and two getter methods to return the values of the Id and Name fields.

package dal;

import java.util.Objects;

public final class Member {
    private final int id;
    private final String name;

    private Member(int id, String name) { = id; = name;

    public static Member aMember(int id, String name) {
        return new Member(id, name);

    public int id() {
        return id;

    public String name() {
        return name;

    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Member member = (Member) o;
        return id == &&

    public int hashCode() {
        return Objects.hash(id, name);

In this class, we establish a connection to our database. What is important in this class is that we pass the connection string as a parameter to the constructor thus making our code unbounded to any specific database engine (MySql, H2, Sybase, Oracle, etc.). This will make writing our tests much easier!

The class consists of:

  1. Constructor: initializes an instance of SQL Connection using the connection string passed as parameter
  2. Three public methods:
    1. createTable: executes an update query to create the”MEMBERS” table.
    2. allMemebers: executes a select query and returns the found records in a list of Members.
    3. insertMembers: takes a list of “Member” as a parameter and inserts the values into the “MEMBERS” table.
package dal;

import dal.Member;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SqlRepository {

    protected final Connection connection;
    private static final String SELECT_MEMBERS = "SELECT * FROM MEMBERS";
    private static final String INSERT_MEMBERS = "INSERT INTO MEMBERS(ID, NAME) VALUES(?, ?)";

    public SqlRepository(String connectionString) throws SQLException {
        connection = DriverManager.getConnection(connectionString);

    public boolean createTable() throws SQLException {
        Statement createStatement = connection.createStatement();
        return createStatement.execute(CREATE_MEMBERS);

    public List<Member> allMembers() throws SQLException {
        List<Member> allMembers = new ArrayList<>();
        Statement selectStatement = connection.createStatement();
        ResultSet membersResultSet = selectStatement.executeQuery(SELECT_MEMBERS);
        while ( {
            allMembers.add(Member.aMember(membersResultSet.getInt(1), membersResultSet.getString(2)));
        return allMembers;

    public void insertMembers(List<Member> members) throws SQLException {
        final PreparedStatement insertMembers = connection.prepareStatement(INSERT_MEMBERS);
        members.forEach(member -> insertMember(member, insertMembers));

    private void insertMember(Member member, PreparedStatement insertMembers) {
        try {
        } catch (SQLException e) {
            throw new UnsupportedOperationException(e.getMessage());

I added this class under “Test Sources Root” because it is only used by the tests.

As you notice, it extends the SqlRepository class implemented previously. Thus, we don’t have a lot to implement here. The only method added is a new method “closeConnection” that drops all the existing tables from the database.

You might wonder why would we need that since we are using an In-Memory database. That might be true for this simple example, but it will be a necessity when running multiple tests classes. That is because, in Java, all the tests are run in the same JVM which means that the H2 instance initialized in the first test will be shared with the next test classes. This approach might lead to an unexpected behavior when using the same tables in the different test classes.

package dal;

import java.sql.SQLException;

public final class H2Repository extends SqlRepository {
    public H2Repository(String connectionString) throws SQLException {

    public void closeConnection() throws SQLException {
        connection.createStatement().execute("DROP ALL OBJECTS");

This is the simple test class! Our single test (it_correctly_inserts_members_to_a_database) is invoking the three methods we implemented before (createTable, insertMember and allMembers). If any of those methods is badly written our test would fail.

package dal;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static dal.Member.aMember;
import static org.fest.assertions.Assertions.assertThat;

public class H2MembersRepositoryTest {

    private static final String H2_CONNECTION_STRING = "jdbc:h2:mem:test";
    private static final List<Member> MEMBERS = new ArrayList<>(10);
    private static H2Repository h2Repository;

    public static void
    setup_database() throws SQLException {
        h2Repository = new H2Repository(H2_CONNECTION_STRING);

    public void
    it_correctly_inserts_members_to_a_database() throws SQLException {


    private static void initializeMembers() {
        for (int index = 0; index < 10; index++) {
            MEMBERS.add(aMember(index, "Name_" + index));

    public static void
    tear_down_database() throws SQLException {


If you can test your DAL, you can test anything! (I just came up with this ;))

Keep the tests going!


  1. H2-Database Engine
  2. H2-Maven
  3. JUnit Maven
  4. GitHub

Author: Ahmad Atwi

Ahmad Atwi is a Software Developer at Murex Systems, which he joined in 2009. Overall he has ten years of experience. Currently, he is a member of an agile team distributed between Paris and Beirut developing a real-time risk engine. He is an active member of the agile community at Murex and the animator of the CodingDojo sessions and meetups at the Beirut office. At the beginning of this year, he started his blog aiming to share his experience with the other developers. He spends most of his free time reading/listening to books or learning and enhancing his technical skills. On a personal level, he is squash player and a licensed scuba diver. ​

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s