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.