In-memory database testing in JUnit - Test Execution Trace

Test Overview

This test verifies that data can be inserted and retrieved correctly using an in-memory H2 database with JUnit. It checks that the stored user name matches the expected value.

Test Code - JUnit 5

JUnit

import static org.junit.jupiter.api.Assertions.assertEquals;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

public class InMemoryDatabaseTest {
    private Connection connection;

    @BeforeEach
    public void setUp() throws Exception {
        connection = DriverManager.getConnection("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1");
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255))");
        }
    }

    @AfterEach
    public void tearDown() throws Exception {
        connection.close();
    }

    @Test
    public void testInsertAndRetrieveUser() throws Exception {
        try (PreparedStatement insertStmt = connection.prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)");
             PreparedStatement selectStmt = connection.prepareStatement("SELECT name FROM users WHERE id = ?")) {
            insertStmt.setInt(1, 1);
            insertStmt.setString(2, "Alice");
            insertStmt.executeUpdate();

            selectStmt.setInt(1, 1);
            try (ResultSet rs = selectStmt.executeQuery()) {
                if (rs.next()) {
                    String name = rs.getString("name");
                    assertEquals("Alice", name);
                } else {
                    throw new AssertionError("No user found with id 1");
                }
            }
        }
    }
}

Execution Trace - 5 Steps

Step	Action	System State	Assertion	Result
1	Test starts and sets up in-memory H2 database connection	In-memory database 'testdb' is created and connection is open	-	PASS
2	Creates table 'users' with columns 'id' and 'name'	Table 'users' exists in the in-memory database	-	PASS
3	Inserts a user with id=1 and name='Alice' into 'users' table	Table 'users' contains one row: (1, 'Alice')	-	PASS
4	Queries the 'users' table for user with id=1	ResultSet contains one row with name='Alice'	Check that retrieved name equals 'Alice'	PASS
5	Test completes and closes database connection	In-memory database connection closed, data lost	-	PASS

Failure Scenario

Failing Condition: User with id=1 is not found in the database after insertion

Execution Trace Quiz - 3 Questions

What does the test verify after inserting data into the in-memory database?

A That the retrieved user name matches 'Alice'

B That the database connection is closed

C That the table 'users' does not exist

D That the user id is zero

Key Result

Using an in-memory database allows fast, isolated tests without external dependencies, ensuring tests run quickly and cleanly.