我正在使用一个使用 JPA 和 Postgres 数据库的 Java 应用程序,我正在尝试创建一个灵活的预准备语句,它可以处理可变数量的输入参数。 示例查询可以最好地解释这一点:
SELECT *
FROM my_table
WHERE
(string_col = :param1 OR :param1 IS NULL) AND
(double_col = :param2 OR :param2 IS NULL);
这个"技巧"背后的想法是,如果用户只指定一个参数,比如:param1
,我们可以null
绑定到:param2
,然后WHERE
子句的行为就好像只检查了第一个参数一样。 从理论上讲,这种方法允许我们使用单个预准备语句来处理任意数量的输入参数,而不需要维护许多不同的语句。
我已经得到了一个简单的 POC 使用纯 JDBC 准备语句在本地工作。 但是,这样做需要在将其与NULL
进行比较之前强制转换参数,例如
WHERE (double_col = ? OR ?::numeric IS NULL)
^^ does not work without cast
但是,我的实际应用程序使用的是 JPA,并且不断收到以下持续错误:
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.
字符串/文本列不会出现此问题,而只会发生在我的 Postgres 表中double precision
的列中。 我已经尝试了所有铸造组合,但没有任何效果:
(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);
该错误似乎是在说 JDBC 正在向 Postgres 发送双列的bytea
类型,然后 Postgres 正在滚动,因为它找不到将byte
转换为双精度的方法。
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
没有收到任何答案甚至评论形式的反馈,当我偶然发现这篇出色的博客文章时,我正准备放弃:
如何将自定义休眠参数类型绑定到 JPA 查询
该帖子提供了两个选项来控制 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();
当然,为查询中的每个参数传递null
是微不足道的,但我这样做主要是为了显示文本和双列的语法。 在实践中,我们希望至少有一些参数是非null
的,但上面的语法处理所有值,null 或其他值。
如果要继续使用具有自动参数绑定的纯查询,可以尝试以下操作。
WHERE (? IS NULL OR (CAST(CAST(? AS TEXT) AS DOUBLE PRECISION) = double_col
这似乎满足了PostgreSQL驱动程序的类型检查以及产生正确的结果。我没有做太多测试,但性能影响似乎很小,因为CAST
发生在常量值而不是数据库中的行上。