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:

MEMBERS
ID      | NAME        |
INTEGER | VARCHAR(64) |

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, Member.java, SqlRepository.java, H2Repository.java 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
<dependencies>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.191</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.11</version>
    </dependency>
</dependencies>

Member.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) {
        this.id = id;
        this.name = name;
    }

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

    public int id() {
        return id;
    }

    public String name() {
        return name;
    }

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

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

SqlRepository.java

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 CREATE_MEMBERS = "CREATE TABLE MEMBERS(ID INTEGER, NAME VARCHAR(64))";
    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 (membersResultSet.next()) {
            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));
        insertMembers.executeBatch();
    }

    private void insertMember(Member member, PreparedStatement insertMembers) {
        try {
            insertMembers.setInt(1, member.id());
            insertMembers.setString(2, member.name());
            insertMembers.addBatch();
        } catch (SQLException e) {
            throw new UnsupportedOperationException(e.getMessage());
        }
    }
}

H2Repository.java

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 {
        super(connectionString);
    }

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

H2MembersRepositoryTest.java

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;

    @BeforeClass
    public static void
    setup_database() throws SQLException {
        h2Repository = new H2Repository(H2_CONNECTION_STRING);
        initializeMembers();
    }

    @Test
    public void
    it_correctly_inserts_members_to_a_database() throws SQLException {
        h2Repository.createTable();
        h2Repository.insertMembers(MEMBERS);

        assertThat(h2Repository.allMembers()).isEqualTo(MEMBERS);
    }

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

    @AfterClass
    public static void
    tear_down_database() throws SQLException {
        h2Repository.closeConnection();
    }
}

TearDown

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

Keep the tests going!

References:

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

Let’s make the tests green

Two weeks ago we started running coding dojo sessions at our offices in Beirut, the first sessions we had so far were much better than what we expected. In this article, I will be sharing my experience (as the one running those sessions) starting from the preparation and ending by the feedback on those sessions.

 

What is CodingDojo?

 
CodingDojo is a meeting where a group of people gets together to solve a programming challenge following TDD. We select those challenges from several online sites like Google Code Jam and Cyber Dojo.
Each dojo session is planned to take 2 hours including a 15-minute retrospective at the end and is scheduled once every week.  
All the participants are contributors, as we rotate on the driver (one who is coding) every 5 minutes. Thus, it is important to limit the number of participants per session to a maximum of 8
We carefully chose the session timings to be during lunchtime from 12:00 to 14:00 in order not to interrupt participants’ usual tasks. 
Finally, it is important to note that the participation to those sessions is not restricted to specific teams, rather anyone regardless of their programming expertise is welcome to join. After all, codingdojo is a place where we take knowledge sharing to the extreme. 

 

From Paris to Beirut 

 

It started almost 2 years ago as an activity within our team members only, but 8 months ago it was changed to involve interested people from other teams (in Paris only) as well. By the end of each session a 15-minute retrospective was held, during which we have always received positive feedback from the participants. For them, it was a chance to learn how to write code following TDD, learn new programming languages and techniques and enjoy a teamwork spirit to solve a programming challenge.  
We decided to leverage on the experience we gained in Paris and start similar sessions in Beirut. At that point, we had no clue on the participation level or its success rate but it was a risk we were willing to take. 

                     

 

Preparation 

 

The first step of the preparation was to contact the HR team asking them for sponsorship as such an activity involves communication and involvement of many teams. We had a meeting with them, explained what codingdojo is and what is expected from those sessions; as expected, they were very supportive to such an initiative. They granted us their sponsorship and offered their help in global communication, coordination with the administration to provide lunch for participants and whenever needed later. 

Second came the technical preparation for better running and managing such an activity. Writing code following TDD became a habit as I have been doing that for more than a year and a half (since I joined my new team). But still I decided to practice more by solving some challenges outside working hours.
For me managing such sessions was the main challenge and a new experience. For that, I started attending (via Visio conference) the sessions managed by Philippe (my teammate in Paris). During which, I tried to benefit from Philippe’s experience and save tips and advice he provided. Finally, we also agreed with Antoine (my teammate in Paris) to visit Beirut and assist me in running the first two sessions.

 

Communication 

 
After setting the start date to be on Wednesday, July 15, 2015, we sent a communication email to all Beirut employees announcing the kick-off of codingdojo and opening the door for registration. In less than one day, more than 30 participants were registered. That was a surprise!!! We weren’t expecting that many participants, especially that many of them were not developers. Since running a single session with 30 participants was impossible, we decided to divide the participants into 4 groups/sessions over the following 4 weeks.

 

First Two Sessions 

 
For the first 2 sessions, Antoine and I prepared very well by selecting and solving the problems ahead of time. Despite that, we made sure not to impose our solution, rather we tried to have an open discussion and agree with the rest on the next tests to write.

The first 30 minutes of both sessions were the hardest. Almost all participants were not familiar with the concept of writing the test before the code that is why we made sure to be the first 2 to drive the code and when someone was coding you would hear us saying “That is correct, but you forgot to write the test!” when others were coding. After that, things started to go smoother! Participants got acquainted with the concept and were aware when they missed the tests.  

For us, the first two sessions were great and much better than what we expected. We expected to have someone trying to control keyboard or dictating his solution. On the contrary, everyone respected the 5-minute limit they had. They were all sharing the solution with others. 
It was clear to us, from the feedback (listed below) we got from participants during the retrospective, that they enjoyed their time and are willing to attend future sessions:

  • Nice, good experience; you learn from others 
  • Nice technique to learn when trying to write the first test and nice way to think incrementally
  • It is hard to do it alone; it is easier with a group 
  • I am not a developer and not familiar to Java, but it was good to see the mindset of the developers
  • Nice to meet people from other teams
  • Nice practice because you are enforced to follow a convention; taught us new best practices 
  • I like the idea of refactoring; the complex code was refactored into couple lines of code that made it more readable 
  • TDD is a bit slow; the pros are the interaction between each other 
 
 

My Feedback

Running those sessions is not an easy task, it requires a lot of effort before and during each session. Before any session, the leader should prepare the right environment for the participants. During the session, the leader should always be focused on what the driver is writing and at the same time listen to suggestion proposed by others and answer the questions raised.
Despite that, it is a great experience from which you learn and gain a lot.
Later in coming sessions we might be introducing some new challenges like increasing the problem complexity or trying new programming languages. I hope that people will remain motivated and enthusiastic to attend the coming sessions!

P.S: The code we write during those sessions will be committed to this GitHub repository.