将null绑定到准备好的语句时，Postgres bytea错误

Question

SELECT *
FROM my_table
WHERE
    (string_col = :param1 OR :param1 IS NULL) AND
    (double_col = :param2 OR :param2 IS NULL);

WHERE (double_col = ? OR ?::numeric IS NULL)
                         ^^ does not work without cast

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: double precision = bytea
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

(double_col = :param2 OR CAST(:param2 AS double precision) IS NULL);
(CAST(double_col AS double precision) = :param2 OR :param2 IS NULL);
(CAST(double_col AS double precision) = :param2 OR CAST(:param2 AS double precision) IS NULL);

Java代码如下所示：

Query query = entityManager.createNativeQuery(sqlString, MyEntity.class);
query.setParameter("param1", "some value");
// bind other parameters here
List<MyEntity> = query.getResultList();

以下是我正在使用的所有东西的版本，以供参考：

Hibernate version         | 4.3.7.Final
Spring data JPA vesion    | 1.7.1.RELEASE
Postgres driver version   | 42.2.2
Postgres database version | 9.6.10
Java version              | 1.8.0_171

Answer 1

我没有收到任何以答案形式的反馈，甚至没有评论，我正准备放弃，这时我偶然发现了这篇优秀的博客文章：

如何将自定义Hibernate参数类型绑定到JPA查询

post给出了两个控制JPA通过驱动程序传递到Postgres（或者不管底层数据库是什么）的类型的选项。我采用了使用TypedParameterValue的方法。下面是我的代码，继续上面给出的示例：

Query query = entityManager.createNativeQuery(sqlString, MyEntity.class);
query.setParameter("param1", new TypedParameterValue(StringType.INSTANCE, null));
query.setParameter("param2", new TypedParameterValue(DoubleType.INSTANCE, null));
List<MyEntity> = query.getResultList();