错误：运算符不存在：spring jpa查询和postgres中的boolean = bytea

由 发布于 Javapostgresqlsqlspring-data-jpa
 收藏

当我直接在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)

评论