求指导编写JdbcTemplate查询方法的JUnit测试用例

架构老林

2026-7-23

如何为JdbcTemplate的两个方法编写JUnit测试用例？

我需要为以下两个JdbcTemplate查询方法编写JUnit测试用例，但毫无头绪。已尝试搜索相关方案，未找到合适的解决办法，恳请各位提供帮助。

方法1:

public int savePreBookDetails(String orderId, String preBookAmount, String prodId, String status, String userName, String locationId) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        try {
            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(insertOrder, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(1, Integer.parseInt(prodId));
                ps.setDouble(2, Double.parseDouble(preBookAmount));
                ps.setString(3, userName);
                ps.setString(4, status);
                ps.setInt(5, Integer.parseInt(locationId));
                ps.setString(6, orderId);
                return ps;
            }, keyHolder);
        } catch (Exception e) {
            LOGGER.error("Error during saving prebook with prodId {} and locationId {} ", prodId,locationId + e);
            throw e;
        }
        return (int) keyHolder.getKeys().get("id");
    }

方法2:

public String findPreBookAmountByOrderId(String orderId) {
        try {
            List<String> list = jdbcTemplate.query(findPreBookAmountByOrderId, (rs, rowNum) -> rs.getString("preBookAmount"), orderId);
            LOGGER.info("Executed query : " + findPreBookAmountByOrderId);
            return list.isEmpty() ? null : list.get(0);
        } catch (Exception e) {
            LOGGER.warn("Error in finding the preBookAmount of orderId {}", orderId + e);
            return null;
        }
    }

解决方案

一、单元测试（用Mockito模拟JdbcTemplate）

这种方式不需要真实数据库，专注于验证方法逻辑和参数传递是否正确。

测试方法1（savePreBookDetails）

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.mockito.InjectMocks;
import org.mockito.Mock;
import org.mockito.junit.jupiter.MockitoExtension;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.mockito.ArgumentMatchers.any;
import static org.mockito.Mockito.*;

@ExtendWith(MockitoExtension.class)
public class PreBookDaoTest {

    @Mock
    private JdbcTemplate jdbcTemplate;

    @InjectMocks
    private PreBookDao preBookDao; // 替换成你的DAO类名

    @Test
    void savePreBookDetails_ShouldReturnGeneratedId() throws Exception {
        // 测试数据
        String orderId = "ORD123";
        String preBookAmount = "100.50";
        String prodId = "101";
        String status = "SUCCESS";
        String userName = "user1";
        String locationId = "5";
        int expectedId = 1;

        // 模拟数据库连接和预处理语句
        Connection mockConn = mock(Connection.class);
        PreparedStatement mockPs = mock(PreparedStatement.class);
        when(mockConn.prepareStatement(any(String.class), eq(Statement.RETURN_GENERATED_KEYS))).thenReturn(mockPs);

        // 模拟JdbcTemplate的update行为，设置返回的自增ID
        doAnswer(invocation -> {
            GeneratedKeyHolder keyHolder = invocation.getArgument(1);
            keyHolder.getKeys().put("id", expectedId);
            return 1; // 返回受影响行数
        }).when(jdbcTemplate).update(any(), any(GeneratedKeyHolder.class));

        // 执行测试
        int actualId = preBookDao.savePreBookDetails(orderId, preBookAmount, prodId, status, userName, locationId);

        // 验证结果
        assertEquals(expectedId, actualId);
        // 验证参数是否正确设置到预处理语句
        verify(mockPs).setInt(1, Integer.parseInt(prodId));
        verify(mockPs).setDouble(2, Double.parseDouble(preBookAmount));
        verify(mockPs).setString(3, userName);
        verify(mockPs).setString(4, status);
        verify(mockPs).setInt(5, Integer.parseInt(locationId));
        verify(mockPs).setString(6, orderId);
    }

    @Test
    void savePreBookDetails_ShouldThrowException_WhenParsingFails() {
        // 传入无效的数值字符串，预期抛出格式异常
        String invalidProdId = "abc";

        assertThrows(NumberFormatException.class, () -> {
            preBookDao.savePreBookDetails("ORD123", "100.50", invalidProdId, "SUCCESS", "user1", "5");
        });
    }
}

测试方法2（findPreBookAmountByOrderId）

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.mockito.InjectMocks;
import org.mockito.Mock;
import org.mockito.junit.jupiter.MockitoExtension;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.Collections;
import java.util.List;
import static org.junit.jupiter.api.Assertions.*;
import static org.mockito.ArgumentMatchers.*;
import static org.mockito.Mockito.when;

@ExtendWith(MockitoExtension.class)
public class PreBookDaoTest {

    @Mock
    private JdbcTemplate jdbcTemplate;

    @InjectMocks
    private PreBookDao preBookDao;

    @Test
    void findPreBookAmountByOrderId_ShouldReturnAmount_WhenExists() {
        String orderId = "ORD123";
        String expectedAmount = "100.50";

        when(jdbcTemplate.query(anyString(), any(), eq(orderId))).thenReturn(List.of(expectedAmount));

        String actualAmount = preBookDao.findPreBookAmountByOrderId(orderId);
        assertEquals(expectedAmount, actualAmount);
    }

    @Test
    void findPreBookAmountByOrderId_ShouldReturnNull_WhenNoResult() {
        String orderId = "ORD999";

        when(jdbcTemplate.query(anyString(), any(), eq(orderId))).thenReturn(Collections.emptyList());

        String actualAmount = preBookDao.findPreBookAmountByOrderId(orderId);
        assertNull(actualAmount);
    }

    @Test
    void findPreBookAmountByOrderId_ShouldReturnNull_WhenExceptionOccurs() {
        String orderId = "ORD123";

        when(jdbcTemplate.query(anyString(), any(), eq(orderId))).thenThrow(new RuntimeException("DB Error"));

        String actualAmount = preBookDao.findPreBookAmountByOrderId(orderId);
        assertNull(actualAmount);
    }
}

二、集成测试（用H2嵌入式数据库）

这种方式使用内存数据库，验证真实的SQL执行和数据交互逻辑。

步骤1：添加测试依赖（Maven）

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

步骤2：配置测试环境（application-test.properties）

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create-drop

步骤3：初始化表结构（src/test/resources/schema.sql）

CREATE TABLE pre_book (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prod_id INT,
    pre_book_amount DOUBLE,
    user_name VARCHAR(255),
    status VARCHAR(50),
    location_id INT,
    order_id VARCHAR(255) UNIQUE
);

步骤4：编写集成测试类

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
@ActiveProfiles("test")
public class PreBookDaoIntegrationTest {

    @Autowired
    private PreBookDao preBookDao;

    @Test
    void saveAndFindPreBookDetails_ShouldWorkCorrectly() {
        // 插入测试数据
        String orderId = "ORD_INTEG_001";
        String preBookAmount = "200.75";
        String prodId = "202";
        String status = "PENDING";
        String userName = "testUser";
        String locationId = "10";

        int generatedId = preBookDao.savePreBookDetails(orderId, preBookAmount, prodId, status, userName, locationId);
        assertTrue(generatedId > 0);

        // 查询验证
        String foundAmount = preBookDao.findPreBookAmountByOrderId(orderId);
        assertEquals(preBookAmount, foundAmount);

        // 查询不存在的订单
        String notFoundAmount = preBookDao.findPreBookAmountByOrderId("ORD_NOT_EXIST");
        assertNull(notFoundAmount);
    }
}

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