当我直接在postgres中执行下面的查询时,它工作正常,但是当我使用JPA运行时,会出现以下错误。
查询示例:
select
*
from
products
where
clientId = :clientId
and ((:status is null
and status in (true,
false))
or status = :status)
and ((:anotherStatus is null
and anotherStatus in (true,
false))
or anotherStatus = :anotherStatus);
错误:
错误org.hibernate.engine.jdbc.spi.SqlExceptionHelper-错误: 运算符不存在:布尔值= bytea提示:没有运算符匹配 给定的名称和参数类型。您可能需要添加显式类型 演员表。
在JPA中进行如下配置:
@Query(value="select * from products where clientId = :clientId and ((:status is null and status in (true, false)) or status = :status) and ((:anotherStatus is null and anotherStatus in (true, false)) or anotherStatus = :anotherStatus)",nativeQuery=true)
List<Products> fetchProducts(@Param("clientId") Long clientId, @Param("status") Boolean status, @Param("anotherStatus") Boolean anotherStatus);
Basically what I am trying to achieve is, When user sends param as true
filter products with true
(active) or as false
(in active), and when user don't send anything (which is null
) then fetch all products (true
and false
)