可选的hql查询参数

Question

我有一个rest存储库：

@Query(
            "SELECT ord FROM Order as ord where " +
                    " (:#{#customer} is null or :#{#customer}='' or  ord.customer = :#{#customer}) and " +
                    " (:#{#supplier} is null or :#{#supplier}='' or ord.supplier = :#{#supplier} ) and " +
                    " (:#{#startDate} is null or :#{#startDate}='' or ord.date >= :#{#startDate} ) and " +
                    " (:#{#endDate} is null or :#{#endDate}='' or ord.date >= :#{#endDate} )"
    )
    Page<Order> query(
            @Param("customer") Organization customer,
            @Param("supplier") Organization supplier,
            @DateTimeFormat(pattern = "dd-MM-yyyyy") @Param("startDate") Date startDate,
            @DateTimeFormat(pattern = "dd-MM-yyyyy") @Param("endDate") Date endDate,
            Pageable pageable
    );

当我在没有任何参数的情况下调用查询时，它工作得很好，并且返回每个订单。如果我提供了任何一个参数，而没有提供其余的参数，我就会得到org.hibernate.QueryException: Not all named parameters have been set。对此有解决方案吗？

Answer 1

您混合了实体名称和绑定参数的SpEL。对于绑定使用':parameterName‘而不是':#{#parameterName}’

@Query(
            "SELECT ord FROM Order as ord where " +
                    " (:customer is null or :customer='' or  ord.customer = :customer) and " +
                    " (:upplier is null or supplier='' or ord.supplier = :supplier) and " +
                    " (:startDate is null or :startDate='' or ord.date >= :startDate) and " +
                    " (:endDate is null or :endDate='' or ord.date >= :endDate )"
    )
    Page<Order> query(
            @Param("customer") Organization customer,
            @Param("supplier") Organization supplier,
            @DateTimeFormat(pattern = "dd-MM-yyyyy") @Param("startDate") Date startDate,
            @DateTimeFormat(pattern = "dd-MM-yyyyy") @Param("endDate") Date endDate,
            Pageable pageable
    );