Verify user data insertion and retrieval using in-memory H2 database

Preconditions (4)

Step 1: Start the in-memory H2 database

Step 2: Create a User object with id=1, name='Alice', email='alice@example.com'

Step 3: Save the User object to the database using UserRepository.save()

Step 4: Retrieve the User object by id=1 using UserRepository.findById()

Step 5: Verify that the retrieved User is not null

Step 6: Verify that the retrieved User's name is 'Alice'

Step 7: Verify that the retrieved User's email is 'alice@example.com'

✅ Expected Result: The User is saved and retrieved correctly from the in-memory database with matching id, name, and email

Automation Requirements - JUnit 5 with H2 in-memory database

Assertions Needed:

assertNotNull for retrieved User

assertEquals for User name

assertEquals for User email

Best Practices:

Use @BeforeEach to set up database connection

Use @AfterEach to clean up resources

Use explicit assertions with clear messages

Use try-with-resources or proper resource management

Keep test isolated and repeatable

Automated Solution

JUnit

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

class User {
    private int id;
    private String name;
    private String email;

    public User(int id, String name, String email) {
        this.id = id;
        this.name = name;
        this.email = email;
    }

    public int getId() { return id; }
    public String getName() { return name; }
    public String getEmail() { return email; }
}

class UserRepository {
    private Connection connection;

    public UserRepository(Connection connection) {
        this.connection = connection;
    }

    public void save(User user) throws SQLException {
        String sql = "INSERT INTO users (id, name, email) VALUES (?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, user.getId());
            stmt.setString(2, user.getName());
            stmt.setString(3, user.getEmail());
            stmt.executeUpdate();
        }
    }

    public User findById(int id) throws SQLException {
        String sql = "SELECT id, name, email FROM users WHERE id = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return new User(rs.getInt("id"), rs.getString("name"), rs.getString("email"));
                } else {
                    return null;
                }
            }
        }
    }
}

public class InMemoryDatabaseTest {
    private Connection connection;
    private UserRepository userRepository;

    @BeforeEach
    void setUp() 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), email VARCHAR(255))");
        }
        userRepository = new UserRepository(connection);
    }

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

    @Test
    void testSaveAndFindUser() throws SQLException {
        User user = new User(1, "Alice", "alice@example.com");
        userRepository.save(user);

        User retrieved = userRepository.findById(1);

        assertNotNull(retrieved, "Retrieved user should not be null");
        assertEquals("Alice", retrieved.getName(), "User name should match");
        assertEquals("alice@example.com", retrieved.getEmail(), "User email should match");
    }
}

The test class InMemoryDatabaseTest uses JUnit 5 to test database operations on an in-memory H2 database.

Setup: The @BeforeEach method creates a fresh in-memory database and a users table before each test. This ensures tests are isolated and repeatable.

Teardown: The @AfterEach method drops the table and closes the connection to clean up resources.

Test: The testSaveAndFindUser method saves a User object and then retrieves it by ID. Assertions check that the retrieved user is not null and that the name and email match the expected values.

This approach uses explicit SQL and JDBC to keep the example simple and clear for beginners. It follows best practices like resource management with try-with-resources and clear assertion messages.

Common Mistakes - 4 Pitfalls

Not creating the database table before running tests

Not closing database connections or statements

Hardcoding data without cleanup, causing tests to interfere

Using assertEquals without checking for null first

Bonus Challenge

Now add data-driven testing with 3 different User inputs to verify save and retrieval

Show Hint