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.

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