In-memory database testing in JUnit

Introduction

In-memory database testing helps you check your database code quickly without using a real database. It makes tests faster and easier to run.

When you want to test database queries without setting up a real database.

When you need fast tests that run in memory during development.

When you want to isolate database logic from external systems.

When you want to run tests on a continuous integration server without database setup.

When you want to test CRUD operations in your application quickly.

Syntax

JUnit

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import static org.junit.jupiter.api.Assertions.*;

@JdbcTest
public class YourTestClass {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testDatabaseOperation() {
        // Your test code here
    }
}

Use @JdbcTest to configure an in-memory database automatically.

JdbcTemplate helps run SQL queries in tests.

Examples

This test creates a table, inserts a user, and checks the name is correct.

JUnit

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import static org.junit.jupiter.api.Assertions.*;

@JdbcTest
public class SimpleInMemoryTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testInsertAndQuery() {
        jdbcTemplate.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50))");
        jdbcTemplate.update("INSERT INTO users(id, name) VALUES (?, ?)", 1, "Alice");
        String name = jdbcTemplate.queryForObject("SELECT name FROM users WHERE id = ?", String.class, 1);
        assertEquals("Alice", name);
    }
}

This test checks that a new table starts empty.

JUnit

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import static org.junit.jupiter.api.Assertions.*;

@JdbcTest
public class EmptyTableTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testEmptyTable() {
        jdbcTemplate.execute("CREATE TABLE products(id INT PRIMARY KEY, name VARCHAR(50))");
        Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM products", Integer.class);
        assertEquals(0, count);
    }
}

Sample Program

This test creates a users table in memory, inserts a user named Bob, then retrieves and checks the name.

JUnit

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import static org.junit.jupiter.api.Assertions.*;

@JdbcTest
public class UserDatabaseTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testUserInsertAndRetrieve() {
        jdbcTemplate.execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50))");
        jdbcTemplate.update("INSERT INTO users(id, name) VALUES (?, ?)", 1, "Bob");
        String retrievedName = jdbcTemplate.queryForObject("SELECT name FROM users WHERE id = ?", String.class, 1);
        assertEquals("Bob", retrievedName);
    }
}

Important Notes

In-memory databases like H2 or HSQLDB are often used with @JdbcTest.

Tests run faster because no real database connection is needed.

Remember to create tables and data inside each test or setup method.

Summary

In-memory database testing runs database code fast without a real database.

Use @JdbcTest and JdbcTemplate to write simple tests.

Create tables and data inside tests to keep tests independent and reliable.

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