调用存储过程返回多行数据触发NonUniqueResultException的解决方法

架构老林

2026-8-25

问题分析与解决方案

问题描述

我有一个包含多张表的数据库，通过复杂SQL查询汇总各表数据并排序后导出为CSV文件。使用HeidiSQL创建了存储过程FORMAT_REPORT_DATA，为避免新建实体，复用存储按钮点击日志的LogReport表的Repository来调用该存储过程。手动执行存储过程可正常返回155行结果，但通过Java按钮调用时抛出错误：

Caused by: javax.persistence.NonUniqueResultException: query did not return a unique result: 155

相关代码实现

LogReportRepository

@Repository
public interface LogReportRepository extends JpaRepository<LogReport, Long>, Serializable {
    @Query(value = "CALL FORMAT_REPORT_DATA(:vonMonatParam,:bisMonatParam,:aggregationThresholdParam,:teamParam);", nativeQuery = true)
    ResultSet formatReportData(@Param("vonMonatParam") LocalDateTime fromMonth,
                               @Param("bisMonatParam") LocalDateTime untilMonth,
                               @Param("aggregationThresholdParam") int aggregationThreshold,
                               @Param("teamParam") int team);
}

LogReportImpl（Service实现）

@Service
public class LogReportImpl implements LogReportService {
    @Autowired
    LogReportRepository logReportRepository;
    @Override
    public ResultSet formatReportData(LocalDateTime fromMonth, LocalDateTime untilMonth, int aggregationThreshold, int team) {
        return logReportRepository.formatReportData(fromMonth,untilMonth,aggregationThreshold,team);
    }
}

LogReportService接口

public interface LogReportService {
    ResultSet formatReportData(LocalDateTime fromMonth, LocalDateTime untilMonth, int aggregationThreshold, int team);
}

FormatButton

public class FormatButton extends Button {
    @SpringBean
    public LogReportService logReportService;
    public FormatButton(String id, IModel<String> model) {
        super(id, model);
    }
    @Override
    public void onSubmit() {
        LocalDateTime fromMonth = LocalDateTime.of(2022,6,30,23,59,0);
        LocalDateTime untilMonth = LocalDateTime.of(2022,8,1,0,0,0);
        int aggregationThreshold = 37;
        int team = 1;
        ResultSet resultSet = logReportService.formatReportData(fromMonth,untilMonth,aggregationThreshold,team);
    }
}

错误原因

JPA结果映射冲突：Spring Data JPA的@Query默认认为返回ResultSet类型的查询应该返回唯一结果，但存储过程实际返回了155行数据，触发了NonUniqueResultException。
不规范的返回类型：ResultSet是JDBC底层对象，Spring Data JPA并不支持直接返回该类型，无法正确管理其资源，还会导致结果映射逻辑混乱。

修复方案

方案1：定义DTO接收多行结果（推荐）

通过创建DTO类匹配存储过程返回的字段，让JPA将多行结果映射为DTO列表：

创建ReportDataDTO类

public class ReportDataDTO {
    // 根据存储过程返回的实际字段定义属性，示例如下
    private String teamName;
    private Integer dataCount;
    private LocalDateTime recordTime;

    // 生成全参构造方法、getter和setter
    public ReportDataDTO(String teamName, Integer dataCount, LocalDateTime recordTime) {
        this.teamName = teamName;
        this.dataCount = dataCount;
        this.recordTime = recordTime;
    }

    // getter和setter省略，自行生成
}

修改Repository方法
调整返回类型为List<ReportDataDTO>，并修改查询语句（MySQL需用SELECT * FROM调用存储过程，确保JPA能映射到DTO）：

@Repository
public interface LogReportRepository extends JpaRepository<LogReport, Long> {
    @Query(value = "SELECT * FROM FORMAT_REPORT_DATA(:vonMonatParam,:bisMonatParam,:aggregationThresholdParam,:teamParam);", nativeQuery = true)
    List<ReportDataDTO> formatReportData(@Param("vonMonatParam") LocalDateTime fromMonth,
                                         @Param("bisMonatParam") LocalDateTime untilMonth,
                                         @Param("aggregationThresholdParam") int aggregationThreshold,
                                         @Param("teamParam") int team);
}

同步修改Service层

public interface LogReportService {
    List<ReportDataDTO> formatReportData(LocalDateTime fromMonth, LocalDateTime untilMonth, int aggregationThreshold, int team);
}

@Service
public class LogReportImpl implements LogReportService {
    @Autowired
    LogReportRepository logReportRepository;

    @Override
    public List<ReportDataDTO> formatReportData(LocalDateTime fromMonth, LocalDateTime untilMonth, int aggregationThreshold, int team) {
        return logReportRepository.formatReportData(fromMonth, untilMonth, aggregationThreshold, team);
    }
}

调整Button调用逻辑

public class FormatButton extends Button {
    @SpringBean
    public LogReportService logReportService;

    public FormatButton(String id, IModel<String> model) {
        super(id, model);
    }

    @Override
    public void onSubmit() {
        LocalDateTime fromMonth = LocalDateTime.of(2022,6,30,23,59,0);
        LocalDateTime untilMonth = LocalDateTime.of(2022,8,1,0,0,0);
        int aggregationThreshold = 37;
        int team = 1;
        List<ReportDataDTO> reportDataList = logReportService.formatReportData(fromMonth,untilMonth,aggregationThreshold,team);
        // 后续处理CSV导出逻辑
    }
}

方案2：使用JdbcTemplate直接调用（无需DTO）

如果不想创建DTO，可直接用JdbcTemplate调用存储过程，返回键值对列表：

修改Service实现

@Service
public class LogReportImpl implements LogReportService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> formatReportData(LocalDateTime fromMonth, LocalDateTime untilMonth, int aggregationThreshold, int team) {
        return jdbcTemplate.queryForList("CALL FORMAT_REPORT_DATA(?,?,?,?)",
                fromMonth, untilMonth, aggregationThreshold, team);
    }
}

同步修改Service接口

public interface LogReportService {
    List<Map<String, Object>> formatReportData(LocalDateTime fromMonth, LocalDateTime untilMonth, int aggregationThreshold, int team);
}

调整Button层接收结果

public class FormatButton extends Button {
    @SpringBean
    public LogReportService logReportService;

    public FormatButton(String id, IModel<String> model) {
        super(id, model);
    }

    @Override
    public void onSubmit() {
        LocalDateTime fromMonth = LocalDateTime.of(2022,6,30,23,59,0);
        LocalDateTime untilMonth = LocalDateTime.of(2022,8,1,0,0,0);
        int aggregationThreshold = 37;
        int team = 1;
        List<Map<String, Object>> reportDataMapList = logReportService.formatReportData(fromMonth,untilMonth,aggregationThreshold,team);
        // 后续处理CSV导出逻辑
    }
}

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

调用存储过程返回多行数据触发NonUniqueResultException的解决方法

问题描述 #

相关代码实现 #