In-memory database testing in JUnit - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this JUnit test using H2 in-memory database?

Consider this JUnit 5 test that uses H2 in-memory database to insert and query a user count. What will be the printed output?

JUnit

import org.junit.jupiter.api.*;
import java.sql.*;

public class UserCountTest {
    private static Connection conn;

    @BeforeAll
    static void setup() throws SQLException {
        conn = DriverManager.getConnection("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1");
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100))");
            stmt.execute("INSERT INTO users VALUES(1, 'Alice'), (2, 'Bob')");
        }
    }

    @Test
    void testUserCount() throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users")) {
            if (rs.next()) {
                System.out.println(rs.getInt(1));
            }
        }
    }

    @AfterAll
    static void teardown() throws SQLException {
        conn.close();
    }
}

A 1

B 2

C 0

D SQLException thrown

💡 Hint

Think about how many rows were inserted before the count query.

The test inserts two users into the in-memory H2 database before querying the count. The query SELECT COUNT(*) FROM users returns 2, which is printed.

❓ assertion

intermediate

Which assertion correctly verifies the number of rows in an in-memory database table?

Given a ResultSet rs from executing SELECT COUNT(*) FROM users, which JUnit assertion correctly checks that the count is 5?

JUnit

int count = 0;
if (rs.next()) {
    count = rs.getInt(1);
}

A assertTrue(count == 5);

B assertFalse(count != 5);

C assertEquals(5, count);

D assertNull(count);

💡 Hint

Use the assertion that compares expected and actual values directly.

assertEquals(expected, actual) is the clearest and most direct way to check that count equals 5 in JUnit.

🔧 Debug

advanced

Why does this in-memory database test fail with "Table not found" error?

This JUnit test using H2 in-memory database fails with an error: "Table 'users' not found". What is the most likely cause?

JUnit

import org.junit.jupiter.api.*;
import java.sql.*;

public class UserTest {
    private Connection conn;

    @BeforeEach
    void setup() throws SQLException {
        conn = DriverManager.getConnection("jdbc:h2:mem:testdb");
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100))");
        }
        conn.close();
    }

    @Test
    void testInsert() throws SQLException {
        conn = DriverManager.getConnection("jdbc:h2:mem:testdb");
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("INSERT INTO users VALUES(1, 'Alice')");
        }
    }

    @AfterEach
    void teardown() throws SQLException {
        conn.close();
    }
}

A The connection URL lacks DB_CLOSE_DELAY=-1, so the table is dropped after setup method ends.

B The table name 'users' is reserved and cannot be used.

C The test method does not commit the transaction, so table is invisible.

D The @BeforeEach method is not executed before the test.

💡 Hint

In-memory H2 databases are destroyed when the last connection closes unless configured otherwise.

Without DB_CLOSE_DELAY=-1, the in-memory database is dropped when the connection from @BeforeEach closes. The test method opens a new connection without the table.

❓ framework

advanced

Which JUnit 5 annotation is best to initialize an in-memory database once for all tests in a class?

You want to create and populate an H2 in-memory database once before all tests run and close it after all tests finish. Which annotation should you use for the setup method?

A @BeforeAll

B @BeforeEach

C @AfterEach

D @AfterAll

💡 Hint

Think about running setup only once before any tests start.

@BeforeAll runs once before all tests in the class, ideal for initializing shared resources like an in-memory database.

🧠 Conceptual

expert

What is the main advantage of using an in-memory database for integration testing?

Why do many developers prefer using in-memory databases like H2 or HSQLDB for integration tests instead of connecting to a real external database?

A In-memory databases require no setup or teardown, so tests never need cleanup.

B In-memory databases support all production database features exactly, so tests are always identical to production.

C In-memory databases automatically generate test data without manual insertion.

D In-memory databases are faster and isolate tests from external dependencies, improving test reliability and speed.

💡 Hint

Consider speed and test environment isolation benefits.

In-memory databases run faster and avoid external system dependencies, making tests more reliable and quicker to run. However, they may not support all production features exactly.