How to Test with Database Using JUnit: Simple Guide

To test with a database in JUnit, use an in-memory database like H2 or an embedded database to isolate tests. Set up the database connection in a @BeforeEach method, run your tests, and clean up in @AfterEach to keep tests independent and repeatable.

Syntax

Here is the basic structure to test database operations with JUnit:

• @BeforeEach: Setup database connection and initialize schema.
• @Test: Write test methods to perform database operations and assert results.
• @AfterEach: Clean up database or close connections.

java

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

public class DatabaseTest {

    private Connection connection;

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

    @Test
    void testInsertAndQuery() throws SQLException {
        try (PreparedStatement insert = connection.prepareStatement("INSERT INTO users VALUES (?, ?)");
             PreparedStatement query = connection.prepareStatement("SELECT name FROM users WHERE id = ?")) {

            insert.setInt(1, 1);
            insert.setString(2, "Alice");
            insert.executeUpdate();

            query.setInt(1, 1);
            ResultSet rs = query.executeQuery();

            Assertions.assertTrue(rs.next());
            Assertions.assertEquals("Alice", rs.getString("name"));
        }
    }

    @AfterEach
    void cleanup() throws SQLException {
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("DROP TABLE users");
        }
        connection.close();
    }
}

Example

This example demonstrates how to use JUnit 5 with an in-memory H2 database to test inserting and querying data. It shows setup, test execution, and cleanup to keep tests isolated and repeatable.

java

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

public class UserDatabaseTest {

    private Connection connection;

    @BeforeEach
    void init() throws SQLException {
        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))");
        }
    }

    @Test
    void insertUserAndVerify() throws SQLException {
        try (PreparedStatement insert = connection.prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)");
             PreparedStatement select = connection.prepareStatement("SELECT name FROM users WHERE id = ?")) {

            insert.setInt(1, 10);
            insert.setString(2, "Bob");
            insert.executeUpdate();

            select.setInt(1, 10);
            ResultSet rs = select.executeQuery();

            Assertions.assertTrue(rs.next());
            Assertions.assertEquals("Bob", rs.getString("name"));
        }
    }

    @AfterEach
    void tearDown() throws SQLException {
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("DROP TABLE users");
        }
        connection.close();
    }
}

Output

Test passed successfully with no errors.

Common Pitfalls

Common mistakes when testing with databases in JUnit include:

• Not cleaning up the database after tests, causing data to leak between tests.
• Using a real production database, which can cause data corruption and slow tests.
• Not using transactions or rollback, making tests non-repeatable.
• Failing to handle SQL exceptions properly in tests.

Always use an isolated test database and clean state for each test.

java

/* Wrong way: Using a shared database without cleanup */

@Test
void testWithoutCleanup() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:h2:mem:shared");
    Statement stmt = conn.createStatement();
    stmt.execute("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name VARCHAR(255))");
    stmt.execute("INSERT INTO users VALUES (1, 'Eve')");
    // No cleanup, next test will see this data
}

/* Right way: Setup and cleanup for each test */

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

@AfterEach
void cleanup() throws SQLException {
    try (Statement stmt = connection.createStatement()) {
        stmt.execute("DROP TABLE users");
    }
    connection.close();
}

Quick Reference

• Use @BeforeEach to set up an in-memory or embedded database.
• Write tests with @Test that perform database operations and assertions.
• Use @AfterEach to clean up and close connections.
• Prefer in-memory databases like H2 for fast, isolated tests.
• Use transactions and rollbacks if supported to keep tests independent.

Key Takeaways

Use an in-memory database like H2 for isolated and fast JUnit database tests.

Set up and clean the database in @BeforeEach and @AfterEach methods to keep tests independent.

Write assertions to verify database state after operations within @Test methods.

Avoid using production databases for tests to prevent data corruption and slowdowns.

Handle SQL exceptions properly to avoid test failures unrelated to logic errors.