将空值绑定到准备好的语句时的Postgres bytea错误 [英] Postgres bytea error when binding null to prepared statements
问题描述
我正在使用使用JPA和Postgres数据库的Java应用程序，并且正在尝试创建一个灵活的准备好的语句，该语句可以处理可变数量的输入参数。一个示例查询将最好地解释这一点：
I am working with a Java application which uses JPA and a Postgres database, and I am trying to create a flexible prepared statement which can handle a variable number of input parameters. An example query would best explain this:
SELECT *
FROM my_table
WHERE
(string_col = :param1 OR :param1 IS NULL) AND
(double_col = :param2 OR :param2 IS NULL);
此技巧背后的想法是，如果用户仅指定一个参数，请说：param1 ，我们只需将 null 绑定到：param2 ，然后这样， WHERE 子句的行为就好像只检查了第一个参数一样。从理论上讲，这种方法使我们可以使用一个准备好的语句来处理任意数量的输入参数，而无需维护许多不同的语句。
The idea behind this "trick" is that if a user specifies only one parameter, say :param1, we can just bind null to :param2, and the WHERE clause would then behave as if only the first parameter were even being checked. This approach lets us handle, in theory, any number of input parameters using a single prepared statement, instead of needing to maintain many different statements.
我已经获得了一个简单的POC使用纯JDBC准备的语句在本地工作。但是，这样做需要在将参数与 NULL 比较之前强制转换参数，例如
I have gotten a simple POC working locally using pure JDBC prepared statements. However, doing so required casting the parameter before comparing it to NULL, e.g.
WHERE (double_col = ? OR ?::numeric IS NULL)
^^ does not work without cast
$ b时不起作用$ b
但是，我的实际应用程序正在使用JPA，并且不断收到以下持续错误：
However, my actual application is using JPA, and I keep getting the following persistent error:
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表中双精度的列。我已经尝试了所有类型的转换，但是没有任何效果：
The problem does not occur with string/text columns, but only with columns which are double precision in my Postgres table. I have tried all combinations of casting, and nothing works:
(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 转换为双精度的方法。
The error seems to be saying that JDBC is sending Postgres a bytea type for the double columns, and then Postgres is rolling over because it can't find a way to cast byte to double precision.
Java代码类似于：
The Java code looks something like:
Query query = entityManager.createNativeQuery(sqlString, MyEntity.class);
query.setParameter("param1", "some value");
// bind other parameters here
List<MyEntity> = query.getResultList();
作为参考，以下是我使用的所有版本：
For reference, here are the versions of everything I am using:
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
推荐答案
未收到表格中的任何反馈关于答案甚至评论，当我偶然发现这篇出色的博客文章时，我正准备放弃：
Not having received any feedback in the form of answers or even a comment, I was getting ready to give up, when I stumbled onto this excellent blog post:
该帖子提供了两个选项来控制JPA通过驱动程序传递给Postgres的类型（或底层数据库的实际位置）。我使用 TypedParameterValue 的方法。这是我的代码，继续上面的示例：
The post gives two options for controlling the types which JPA passes through the driver to Postgres (or whatever the underlying database actually is). I went with the approach using TypedParameterValue. Here is what my code looks like continuing with the example given above:
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 查询中的 every 参数，但是我这样做主要是为了显示text和double列的语法。实际上，我们希望至少有几个参数不是 null ，但是上面的语法可以处理所有值，无论是null还是其他。
Of course, it is trivial to be passing null for every parameter in the query, but I am doing this mainly to show the syntax for the text and double columns. In practice, we would expect at least a few of the parameters to be non null, but the above syntax handles all values, null or otherwise.
这篇关于将空值绑定到准备好的语句时的Postgres bytea错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
