测试JDBC查询与Prepared Statements：H2非实体表方法测试方案咨询

Got it, let's break down a solid, actionable test plan for your addUser method. Since you're already using H2, leaning into its in-memory mode is perfect—it's fast, isolated, and leaves no persistent mess after tests run. Here's a step-by-step approach with concrete code examples:

1. Prep Your Testing Dependencies #

First, make sure your build file (Maven/Gradle) includes these essentials for testing:

• JUnit 5 (for running tests)
• H2 database driver (for in-memory database instances)
• Optional: AssertJ (for more readable assertions, but JUnit's built-in tools work just fine too)

2. Build a Test Class with Isolated Setup/Teardown #

We'll spin up a fresh H2 in-memory database for every test, recreate the app_user table (tests should be self-contained, even if you have the table defined elsewhere), and clean up resources afterward.

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import static org.junit.jupiter.api.Assertions.*;

class UserDaoTest { // Replace with your actual class containing the addUser method
    private Connection connection;

    @BeforeEach
    void setUp() throws SQLException {
        // Initialize H2 in-memory database (DB_CLOSE_DELAY=-1 keeps it alive between connections)
        connection = DriverManager.getConnection("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "");
        
        // Recreate the app_user table to ensure a fresh state for each test
        try (var stmt = connection.createStatement()) {
            stmt.execute("CREATE TABLE app_user (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY," +
                    "login VARCHAR(50) NOT NULL UNIQUE," +
                    "password VARCHAR(100) NOT NULL," +
                    "description VARCHAR(255)" +
                    ")");
        }
    }

    @AfterEach
    void tearDown() throws SQLException {
        // Clean up: close the connection to shut down the in-memory database
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

3. Write Key Test Cases #

Test 1: Successful User Persistence #

Verify that calling addUser actually saves the user to the database.

@Test
void addUser_ShouldSaveUserToDatabase() throws SQLException {
    // Arrange
    var userDao = new UserDao(); // Your class with the addUser method
    String testLogin = "mike_tyson";
    String testPassword = "ironMike123";
    String testDescription = "Legendary boxer";

    // Act
    userDao.addUser(connection, testLogin, testPassword, testDescription);

    // Assert: Check if the user exists in the database
    String query = "SELECT login, password, description FROM app_user WHERE login = ?";
    try (var stmt = connection.prepareStatement(query)) {
        stmt.setString(1, testLogin);
        try (var rs = stmt.executeQuery()) {
            assertTrue(rs.next(), "User should be present in the database");
            assertEquals(testLogin, rs.getString("login"));
            assertEquals(testPassword, rs.getString("password"));
            assertEquals(testDescription, rs.getString("description"));
        }
    }
}

Test 2: Duplicate Login (With Unique Constraint) #

If your app_user table enforces a unique constraint on login, test that adding a duplicate throws the expected SQL exception.

@Test
void addUser_DuplicateLogin_ShouldThrowSQLException() throws SQLException {
    // Arrange
    var userDao = new UserDao();
    String duplicateLogin = "jane_doe";
    
    // First add the user successfully
    userDao.addUser(connection, duplicateLogin, "pass123", "First entry");

    // Act & Assert
    SQLException exception = assertThrows(SQLException.class, () -> 
        userDao.addUser(connection, duplicateLogin, "pass456", "Duplicate entry")
    );
    
    // Verify the exception matches H2's unique violation message
    assertTrue(exception.getMessage().contains("Unique index or primary key violation"));
}

Test 3: Invalid Null Parameters (Optional) #

If your schema enforces non-null constraints, test that passing null values triggers the right exception:

@Test
void addUser_NullLogin_ShouldThrowSQLException() throws SQLException {
    var userDao = new UserDao();
    
    // Act & Assert: Null login should fail due to NOT NULL constraint
    SQLException exception = assertThrows(SQLException.class, () -> 
        userDao.addUser(connection, null, "validPass", "User with null login")
    );
    
    assertTrue(exception.getMessage().contains("NULL not allowed for column \"LOGIN\""));
}

4. Quick Best Practices #

• Use try-with-resources: Always wrap Connection, Statement, and ResultSet in try-with-resources blocks to avoid resource leaks (we did this in the test code).
• Keep tests isolated: Each test starts with a fresh database state—our @BeforeEach and @AfterEach methods guarantee this.
• Cover edge cases: Don't stop at happy paths—test empty strings, too-long passwords, or any other edge cases relevant to your business rules.

内容的提问来源于stack exchange，提问作者valik