Spring Boot Repository中findOrdersByFilters方法参数调用顺序引发类型转换异常

我在Spring Boot的Repository里卡了一周，问题出在findOrdersByFilters方法：程序启动后首次调用如果传了date参数，查询正常执行；但首次调用仅传入非date参数时，会抛出错误：cannot cast type bytea to date。

错误日志 #

2025-01-06T14:28:02.035+03:00  WARN 4997 --- [nio-8082-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42846
2025-01-06T14:28:02.035+03:00 ERROR 4997 --- [nio-8082-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: cannot cast type bytea to date
  Позиция: 910
2025-01-06T14:28:02.042+03:00 ERROR 4997 --- [nio-8082-exec-5] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select o1_0.id,o1_0.client_id,o1_0.comment,o1_0.date_create,o1_0.date_end,o1_0.machine,o1_0.manager_username,o1_0.order_status,o1_0.payment_status from orders o1_0 join clients c1_0 on o1_0.client_id=c1_0.id left join employee e1_0 on o1_0.manager_username=e1_0.username where (? is null or o1_0.id=?) and (? is null or c1_0.company_name like ('%'||?||'%') escape '') and (? is null or (e1_0.first_name is not null and e1_0.last_name is not null and (e1_0.first_name like ('%'||?||'%') escape '' or e1_0.last_name like ('%'||?||'%') escape '' or (e1_0.first_name||' '||e1_0.last_name) like ('%'||?||'%') escape '' or (e1_0.last_name||' '||e1_0.first_name) like ('%'||?||'%') escape ''))) and (? is null or o1_0.payment_status like ('%'||?||'%') escape '') and (? is null or o1_0.order_status like ('%'||?||'%') escape '') and (? is null or o1_0.comment like ('%'||?||'%') escape '') and (cast(? as date) is null or o1_0.date_end=cast(? as date)) and (? is null or o1_0.manager_username=?) order by o1_0.date_create desc] [ERROR: cannot cast type bytea to date
  Позиция: 910] [n/a]; SQL [n/a]] with root cause

相关代码 #

OrderRepo.java #

package ru.limon4egtop.printingCRM.repos;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import ru.limon4egtop.printingCRM.dto.OrderFilterDto;
import ru.limon4egtop.printingCRM.models.Orders;

import java.util.List;

@Repository
public interface OrderRepo extends JpaRepository<Orders, Long> {

    @Query("""
    SELECT o FROM Orders o
    JOIN Clients c ON o.clientId = c.id
    LEFT JOIN Employee e ON o.managerUsername = e.username
    WHERE (:#{#filter.id} IS NULL OR o.id = :#{#filter.id})
      AND (:#{#filter.companyName} IS NULL OR c.companyName LIKE CONCAT('%', :#{#filter.companyName}, '%'))
      AND (
            :#{#filter.managerName} IS NULL OR 
            (
                e.firstName IS NOT NULL AND e.lastName IS NOT NULL AND
                (
                    e.firstName LIKE CONCAT('%', :#{#filter.managerName}, '%') OR
                    e.lastName LIKE CONCAT('%', :#{#filter.managerName}, '%') OR
                    CONCAT(e.firstName, ' ', e.lastName) LIKE CONCAT('%', :#{#filter.managerName}, '%') OR
                    CONCAT(e.lastName, ' ', e.firstName) LIKE CONCAT('%', :#{#filter.managerName}, '%')
                )
            )
        )
      AND (:#{#filter.paymentStatus} IS NULL OR o.paymentStatus LIKE CONCAT('%', :#{#filter.paymentStatus}, '%'))
      AND (:#{#filter.orderStatus} IS NULL OR o.orderStatus LIKE CONCAT('%', :#{#filter.orderStatus}, '%'))
      AND (:#{#filter.comment} IS NULL OR o.comment LIKE CONCAT('%', :#{#filter.comment}, '%'))
      AND (CAST(:#{#filter.dateEnd} AS DATE) IS NULL OR o.dateEnd = CAST(:#{#filter.dateEnd} AS DATE))
      AND (:#{#filter.currentUsername} IS NULL OR o.managerUsername = :#{#filter.currentUsername})
    ORDER BY o.dateCreate DESC
""")
    List<Orders> findOrdersByFilters(@Param("filter") OrderFilterDto filter);
} 

OrderServiceImp.java #

package ru.limon4egtop.printingCRM.Services.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import ru.limon4egtop.printingCRM.Services.OrderService;
import ru.limon4egtop.printingCRM.dto.OrderFilterDto;
import ru.limon4egtop.printingCRM.models.Orders;
import ru.limon4egtop.printingCRM.repos.OrderRepo;

import java.util.List;

@Service
public class OrderServiceImp implements OrderService {
    private OrderRepo orderRepo;

    @Autowired
    public OrderServiceImp(OrderRepo orderRepo) {
        this.orderRepo = orderRepo;
    }

    @Override
    public List<Orders> getOrdersByFilters(final OrderFilterDto orderFilterDto) {
        System.out.println(orderFilterDto.toString());
        return this.orderRepo.findOrdersByFilters(orderFilterDto);
    }
}

OrderFilterDto.java #

package ru.limon4egtop.printingCRM.dto;

import java.time.LocalDate;

public class OrderFilterDto {
    private Long id;
    private String companyName;
    private String managerName;
    private String paymentStatus;
    private String orderStatus;
    private String comment;
    private LocalDate dateEnd;
    private String currentUsername;

    public OrderFilterDto(Long id, String companyName, String managerName, String paymentStatus, String orderStatus, String comment, LocalDate dateEnd, String currentUsername) {
        this.id = id;
        this.companyName = companyName;
        this.managerName = managerName;
        this.paymentStatus = paymentStatus;
        this.orderStatus = orderStatus;
        this.comment = comment;
        this.dateEnd = dateEnd;
        this.currentUsername = currentUsername;
    }

    @Override
    public String toString() {
        return "OrderFilterDto {" +
                "id=" + id +
                ", companyName='" + companyName + '\'' +
                ", managerName='" + managerName + '\'' +
                ", paymentStatus='" + paymentStatus + '\'' +
                ", orderStatus='" + orderStatus + '\'' +
                ", comment='" + comment + '\'' +
                ", dateEnd=" + dateEnd +
                ", currentUsername=" + currentUsername +
                '}';
    }
}

已尝试的解决方案 #

试过使用CAST和不使用CAST的多种查询写法，问题仍未解决。

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