如何用JPA自定义查询实现两表字段相减并解决EmptyResultDataAccessException

• 触发org.springframework.dao.EmptyResultDataAccessException的核心原因是JPA自定义查询调用了要求返回非空结果的方法（比如getSingleResult()、定义返回类型为非Optional的单值查询），但对应的workWithdraw ID不存在或查询逻辑错误导致没有返回值。
• 自定义查询计算结果不符合预期，大概率是OneToOne关联映射配置错误（比如关联方向搞反、外键字段名不匹配），无法正确关联查询到WorkWithdraw表的quantity字段。

1. 修正实体类关联映射 #

优先确保Deposit和WorkWithdraw的OneToOne关联配置正确，示例代码如下：

// Deposit实体类
@Entity
public class Deposit {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    // 本次归还总量
    private BigDecimal totalDeposit;
    // 待归还物料量
    private BigDecimal pending;
    // 外键存在Deposit表，关联WorkWithdraw的主键id
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "work_withdraw_id", referencedColumnName = "id", nullable = false)
    private WorkWithdraw workWithdraw;
    // 省略getter、setter
}

// WorkWithdraw实体类
@Entity
public class WorkWithdraw {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    // 申领总物料量
    private BigDecimal quantity;
    // 反向关联可选，无需从WorkWithdraw查询Deposit可删除
    @OneToOne(mappedBy = "workWithdraw")
    private Deposit deposit;
    // 省略getter、setter
}

2. 调整业务逻辑实现 #

不推荐把计算逻辑放到自定义查询中，直接在Service层完成关联校验和差值计算，可完全避免空结果异常和计算错误问题：

Repository层代码 #

public interface WorkWithdrawRepository extends JpaRepository<WorkWithdraw, Long> {
}

public interface DepositRepository extends JpaRepository<Deposit, Long> {
}

Service层代码 #

@Service
@Transactional
public class DepositService {
    private final DepositRepository depositRepository;
    private final WorkWithdrawRepository workWithdrawRepository;

    // 构造方法注入
    public DepositService(DepositRepository depositRepository, WorkWithdrawRepository workWithdrawRepository) {
        this.depositRepository = depositRepository;
        this.workWithdrawRepository = workWithdrawRepository;
    }

    public Deposit recordReturn(Long workWithdrawId, BigDecimal totalDeposit) {
        // 先校验关联的申领记录是否存在，从根源避免空查询
        WorkWithdraw workWithdraw = workWithdrawRepository.findById(workWithdrawId)
                .orElseThrow(() -> new IllegalArgumentException("申领记录不存在，ID：" + workWithdrawId));
        // 计算待归还量，额外兜底负数情况（归还量超过申领量时待归还量为0）
        BigDecimal pending = workWithdraw.getQuantity().subtract(totalDeposit);
        pending = pending.compareTo(BigDecimal.ZERO) < 0 ? BigDecimal.ZERO : pending;
        // 封装保存对象
        Deposit deposit = new Deposit();
        deposit.setWorkWithdraw(workWithdraw);
        deposit.setTotalDeposit(totalDeposit);
        deposit.setPending(pending);
        return depositRepository.save(deposit);
    }
}

Controller层代码 #

@RestController
@RequestMapping("/deposit")
public class DepositController {
    private final DepositService depositService;

    public DepositController(DepositService depositService) {
        this.depositService = depositService;
    }

    @PostMapping
    public ResponseEntity<Deposit> saveReturnRecord(@RequestParam Long workWithdrawId, @RequestParam BigDecimal totalDeposit) {
        return ResponseEntity.ok(depositService.recordReturn(workWithdrawId, totalDeposit));
    }
}

3. 异常残留排查 #

如果调整后仍有问题，优先检查两点：

• 传入的workWithdrawId在work_withdraw表中真实存在，没有被逻辑删除
• 实体类属性名和数据库表字段名匹配，驼峰转下划线的命名规则没有配置错误

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