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.

Practice

1. What is the main advantage of using an in-memory database for JUnit testing?

easy

A. It stores data permanently for production use

B. It requires complex setup and external servers

C. Tests run faster without needing a real database connection

D. It only works with web applications

Solution

1. Step 1: Understand in-memory database purpose

   In-memory databases keep data in RAM, so no disk access is needed, making tests faster.

2. Step 2: Compare options

   The incorrect options describe permanent storage, complex setup, and limitation to web apps, which are not advantages of in-memory testing databases.

3. Final Answer:

   Tests run faster without needing a real database connection -> Option C

4. Quick Check:

   In-memory = fast tests [OK]

Hint: In-memory means fast, no real DB needed [OK]

Common Mistakes:

• Thinking in-memory DB stores data permanently
• Assuming it needs external servers
• Confusing test DB with production DB

2. Which annotation is commonly used in JUnit to configure a test with an in-memory database and JdbcTemplate?

easy

A. @DataJpaTest

B. @JdbcTest

C. @Entity

D. @SpringBootApplication

Solution

1. Step 1: Identify annotation for JDBC testing

   @JdbcTest sets up an in-memory database and JdbcTemplate for simple JDBC tests.

2. Step 2: Review other annotations

   @SpringBootApplication is for main app config, @Entity marks entities, @DataJpaTest is for JPA tests, not simple JDBC.

3. Final Answer:

   @JdbcTest -> Option B

4. Quick Check:

   @JdbcTest = in-memory JDBC test setup [OK]

Hint: Use @JdbcTest for fast in-memory JDBC tests [OK]

Common Mistakes:

• Confusing @JdbcTest with @DataJpaTest
• Using @Entity as a test annotation
• Using @SpringBootApplication in tests

3. Given this JUnit test snippet using JdbcTemplate with an in-memory database:

jdbcTemplate.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50))");
jdbcTemplate.update("INSERT INTO users VALUES (1, 'Alice')");
int count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class);

What is the value of count after execution?

medium

A. 1

B. 0

C. Syntax error

D. NullPointerException

Solution

1. Step 1: Analyze table creation and insertion

   The table 'users' is created and one row with id=1 and name='Alice' is inserted.

2. Step 2: Query the count of rows

   The query "SELECT COUNT(*) FROM users" returns the number of rows, which is 1 after insertion.

3. Final Answer:

   1 -> Option A

4. Quick Check:

   Inserted 1 row, count = 1 [OK]

Hint: Count rows after insert to get 1 [OK]

Common Mistakes:

• Assuming count is 0 before insertion
• Expecting syntax error from correct SQL
• Confusing queryForObject return type

4. In this JUnit test using JdbcTemplate, what is the error?

@JdbcTest
public class UserTest {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  @Test
  public void testInsert() {
    jdbcTemplate.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50))");
    jdbcTemplate.update("INSERT INTO users VALUES (1, 'Bob')");
    int count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM user", Integer.class);
    assertEquals(1, count);
  }
}

medium

A. Table name typo in SELECT query

B. Missing @Test annotation

C. JdbcTemplate not autowired

D. Incorrect assertion syntax

Solution

1. Step 1: Check table creation and query

   The table created is named 'users' but the SELECT query uses 'user', causing an error.

2. Step 2: Verify annotations and syntax

   @Test is present, JdbcTemplate is autowired, and assertion syntax is correct.

3. Final Answer:

   Table name typo in SELECT query -> Option A

4. Quick Check:

   Table 'users' vs query 'user' mismatch [OK]

Hint: Match table names exactly in queries [OK]

Common Mistakes:

• Ignoring table name mismatch
• Thinking @Test is missing
• Assuming autowiring fails silently

5. You want to write a JUnit test using an in-memory database that creates a products table, inserts multiple rows, and verifies the total price sum. Which approach is best to keep tests independent and reliable?

hard

A. Create table once in a static block and reuse data across tests

B. Skip table creation and assume it exists

C. Use a real database server for accurate results

D. Create table and insert data inside each test method

Solution

1. Step 1: Understand test independence

   Creating tables and inserting data inside each test ensures no leftover data affects other tests.

2. Step 2: Evaluate other options

   Static blocks or real DBs reduce isolation and speed; skipping creation risks failures if table missing.

3. Final Answer:

   Create table and insert data inside each test method -> Option D

4. Quick Check:

   Setup per test = independent, reliable [OK]

Hint: Setup fresh data inside each test method [OK]

Common Mistakes:

• Sharing data across tests causing flaky results
• Using real DB slowing tests
• Assuming tables exist without creation