Spring Data JPA查询报错：按账户与日期范围查询交易记录失败

问题描述 #

开发小型银行应用时，需根据用户账户、起始日期、结束日期查询交易记录，数据库transactionDate字段为Date类型。要求当用户提供的起始日期晚于结束日期（如2022-10-21作为起始日、2022-08-02作为结束日）时，仍能返回该日期区间内的所有交易。但当前自定义@Query查询抛出QueryCreationException错误。

报错信息 #

Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.elijah.onlinebankingapp.repository.transaction.TransactionTypeRepository.findByBankAccountAndTransactionDate(com.elijah.onlinebankingapp.model.account.BankAccount,java.util.Date,java.util.Date)! Reason: Validation failed for query for method public abstract java.util.List com.elijah.onlinebankingapp.repository.transaction.TransactionTypeRepository.findByBankAccountAndTransactionDate(com.elijah.onlinebankingapp.model.account.BankAccount,java.util.Date,java.util.Date)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.elijah.onlinebankingapp.repository.transaction.TransactionTypeRepository.findByBankAccountAndTransactionDate(com.elijah.onlinebankingapp.model.account.BankAccount,java.util.Date,java.util.Date)!

相关代码 #

TransactionType实体类 #

@Entity
@NoArgsConstructor
@Data
public class TransactionType {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Date transactionDate;
    private double amount;
    private double currentBalance;
    private String transactionType;
    private String description;
    private String depositorOrWithDrawalName;
    @ManyToOne
    @JoinColumn(name = "account_id")
    private BankAccount bankAccount;
}

BankAccount实体类 #

@Entity
@Data
@NoArgsConstructor
@Slf4j
public class BankAccount {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String accountType;
    private String accountStatus;
    private String accountNumber;
    private double currentBalance;
    private LocalDate createdDate;
    @OneToOne
    @JoinColumn(name = "customer_id")
    private Customer customer;
}

BankAccountStatement类 #

@Data
@NoArgsConstructor
public class BankAccountStatement {
    private double id;
    private String transactionDate;
    private String transactionType;
    private String description;
    private double amount;
    private double currentBalance;
    private String depositorOrWithDrawalName;
}

BankAccountStatementDto类 #

@Data
@NoArgsConstructor
public class BankAccountStatementDto {
    private Date startDate;
    private Date endDate;
}

Repository代码 #

@Repository
public interface TransactionTypeRepository extends JpaRepository<TransactionType,Long> {
    @Query("select t from TransactionType t where t.bankAccount =:bankAccount, t.transactionDate <=:startDate AND t.transactionDate >=:endDate")
    List<TransactionType> findByBankAccountAndTransactionDate(BankAccount bankAccount, @Param("startDate") Date startDate,@Param("endDate") Date endDate);
}

Service代码 #

@Service
public class TransactionTypeService {
    @Autowired
    private TransactionTypeRepository transactionTypeRepository;
    @Autowired
    private BankAccountService bankAccountService;
    public List<BankAccountStatement> getUserAccountStatement(BankAccountStatementDto bankAccountStatementDto, String accountNumber) throws DataNotFoundException {
        BankAccount bankAccount = bankAccountService.getAccountByAccountNumber(accountNumber);
        List<TransactionType> transactionTypeList = transactionTypeRepository.findByBankAccountAndTransactionDate(bankAccount,bankAccountStatementDto.getStartDate(),bankAccountStatementDto.getEndDate());
        List<BankAccountStatement> bankAccountStatementList = new ArrayList<>();
        BankAccountStatement bankAccountStatement = new BankAccountStatement();
        for (TransactionType transactionType: transactionTypeList){
            bankAccountStatement.setId(transactionType.getId());
            bankAccountStatement.setTransactionType(transactionType.getTransactionType());
            bankAccountStatement.setTransactionDate(transactionType.getTransactionDate().toString());
            bankAccountStatement.setDescription(transactionType.getDescription());
            bankAccountStatement.setDepositorOrWithDrawalName(transactionType.getDepositorOrWithDrawalName());
            bankAccountStatement.setCurrentBalance(transactionType.getCurrentBalance());
            bankAccountStatement.setAmount(transactionType.getAmount());
            bankAccountStatementList.add(bankAccountStatement);
        }
        return bankAccountStatementList;
    }
}

Controller代码 #

@RestController
public class TransactionController {
    @Autowired
    private TransactionTypeService transactionTypeService;
    @GetMapping("/account/statement/from/enteredDate")
    public ResponseEntity<List<BankAccountStatement>> getCustomerAccountStatement(@RequestBody BankAccountStatementDto bankAccountStatementDto,@RequestParam("accountNumber")String accountNumber) throws DataNotFoundException {
        return new ResponseEntity<>(transactionTypeService.getUserAccountStatement(bankAccountStatementDto,accountNumber),HttpStatus.OK);
    }
}

问题解决步骤 #

1. 修复Repository查询语法错误 #

原@Query中用逗号分隔where条件属于JPQL语法错误，需改为AND连接。同时为支持起始/结束日期任意顺序的查询，使用LEAST和GREATEST函数确保查询区间正确：

@Repository
public interface TransactionTypeRepository extends JpaRepository<TransactionType,Long> {
    @Query("select t from TransactionType t where t.bankAccount = :bankAccount AND t.transactionDate >= LEAST(:startDate, :endDate) AND t.transactionDate <= GREATEST(:startDate, :endDate)")
    List<TransactionType> findByBankAccountAndTransactionDateBetween(@Param("bankAccount") BankAccount bankAccount, @Param("startDate") Date startDate,@Param("endDate") Date endDate);
}

2. 修复Service中的对象复用问题 #

循环中重复使用同一个BankAccountStatement对象会导致列表所有元素引用同一实例，需将对象创建移至循环内部：

@Service
public class TransactionTypeService {
    @Autowired
    private TransactionTypeRepository transactionTypeRepository;
    @Autowired
    private BankAccountService bankAccountService;
    public List<BankAccountStatement> getUserAccountStatement(BankAccountStatementDto bankAccountStatementDto, String accountNumber) throws DataNotFoundException {
        BankAccount bankAccount = bankAccountService.getAccountByAccountNumber(accountNumber);
        List<TransactionType> transactionTypeList = transactionTypeRepository.findByBankAccountAndTransactionDateBetween(bankAccount,bankAccountStatementDto.getStartDate(),bankAccountStatementDto.getEndDate());
        List<BankAccountStatement> bankAccountStatementList = new ArrayList<>();
        for (TransactionType transactionType: transactionTypeList){
            BankAccountStatement bankAccountStatement = new BankAccountStatement();
            bankAccountStatement.setId(transactionType.getId());
            bankAccountStatement.setTransactionType(transactionType.getTransactionType());
            bankAccountStatement.setTransactionDate(transactionType.getTransactionDate().toString());
            bankAccountStatement.setDescription(transactionType.getDescription());
            bankAccountStatement.setDepositorOrWithDrawalName(transactionType.getDepositorOrWithDrawalName());
            bankAccountStatement.setCurrentBalance(transactionType.getCurrentBalance());
            bankAccountStatement.setAmount(transactionType.getAmount());
            bankAccountStatementList.add(bankAccountStatement);
        }
        return bankAccountStatementList;
    }
}

3. 修复Controller的请求方式问题 #

@GetMapping不建议搭配@RequestBody使用，改为@PostMapping更符合REST规范：

@RestController
public class TransactionController {
    @Autowired
    private TransactionTypeService transactionTypeService;
    @PostMapping("/account/statement/from/enteredDate")
    public ResponseEntity<List<BankAccountStatement>> getCustomerAccountStatement(@RequestBody BankAccountStatementDto bankAccountStatementDto,@RequestParam("accountNumber")String accountNumber) throws DataNotFoundException {
        return new ResponseEntity<>(transactionTypeService.getUserAccountStatement(bankAccountStatementDto,accountNumber),HttpStatus.OK);
    }
}

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