一个接口模糊查询startDate和endDate查不到数据

事情是这样的：

在postman里调我的这个接口传一个contractStartDate，可以查到数据，如下：共10条

入参：pageSize：10，pageIndex：1和quoteId：f29cc3c3-2c4e-4e9c-ba75-cfd61d11cc81，contractStartDate：2021-06-30

image

在postman里调我的这个接口传一个contractEndDate，查不到数据，开始以为是我自己哪个字母写错了或者自己的sql问题，前提是我写的unit test都跑通了，并且navicat里查可以查到，如下：共10条，这两个字段的类型是date类型

image

image

我在postman里把contractEndDate传2021-10-01可以查到，如下：

image

贴上我的代码里的sql：

@Query(value ="SELECT * FROM mc_contract contract WHERE contract.quote_id = :quoteId " +

"and (:country is null or contract.country = :country) " +

"and (:dmu is null or contract.dmu = :dmu) " +

"and contract.status is not null and (:status is null or contract.status = :status) " +

"and (:contractType is null or contract.contract_type = :contractType) " +

"and (:fulfillmentMethod is null or contract.fulfillment_method = :fulfillmentMethod) " +

"and (:contractEndDate is null or contract.contract_end_date <= :contractEndDate) " +

"and (:contractStartDate is null or contract.contract_start_date >= :contractStartDate) ", nativeQuery =true)

Page<MCContract> query(@Param("quoteId") String quoteId, @Param("country") String country,

                      @Param("dmu") String dmu, @Param("status") String status, @Param("contractType") String contractType,

                      @Param("fulfillmentMethod") String fulfillmentMethod, @Param("contractStartDate") LocalDate contractStartDate,

                      @Param("contractEndDate") LocalDate contractEndDate, Pageable pageable);

最终修改

将传进来的contractEndDate加一天

image.png

原生sql改成了jpa封装sql

@Query(value = "SELECT contract FROM MCContract contract WHERE contract.quoteId = :quoteId " +
        "and (:country is null or contract.country = :country) " +
        "and (:dmu is null or contract.dmu = :dmu) " +
        "and contract.status is not null and (:status is null or contract.status = :status) " +
        "and (:contractType is null or contract.contractType = :contractType) " +
        "and (:fulfillmentMethod is null or contract.fulfillmentMethod = :fulfillmentMethod) " +
        "and (:contractEndDate is null or contract.contractEndDate <= :contractEndDate) " +
        "and (:contractStartDate is null or contract.contractStartDate >= :contractStartDate) ")
    Page<MCContract> query(@Param("quoteId") String quoteId, @Param("country") String country,
                           @Param("dmu") String dmu, @Param("status") String status, @Param("contractType") String contractType,
                           @Param("fulfillmentMethod") String fulfillmentMethod, @Param("contractStartDate") LocalDate contractStartDate,
                           @Param("contractEndDate") LocalDate contractEndDate, Pageable pageable);