PostgreSQL JDBC 空字符串作为字节 [英] PostgreSQL JDBC Null String taken as a bytea

问题描述

如果 entity.getHistory() 为空，以下代码片段:

If entity.getHistory() is null following code snippet:

(getEntityManager() 返回 spring 注入的 EntityManager，数据库字段历史类型为:text 或 varchar2(2000)

(getEntityManager() returns spring injected EntityManager, database field history type is: text or varchar2(2000)

Query query = getEntityManager().createNativeQuery("insert into table_name(..., history, ....) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
[...]
.setParameter(6, entity.getHistory())
[...]

query.executeUpdate();

给出奇怪的异常:

17/11/11 06:26:09:009 [pool-2-thread-1]  WARN util.JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42804
17/11/11 06:26:09:009 [pool-2-thread-1] ERROR util.JDBCExceptionReporter:101 - ERROR: **column "history" is of type text but expression is of type bytea**

提示:您需要重写或转换表达式.

Hint: You will need to rewrite or cast the expression.

问题仅出现在此配置中:
操作系统:CentOS 5.6 版(最终版)
Java:1.6.0_26
数据库:PostgreSQL 8.1
JDBC 驱动程序:postgresql-9.1-901.jdbc4
应用服务器:apache-tomcat-6.0.28

Problem occurred only in this configuration:
OS: CentOS release 5.6 (Final)
Java: 1.6.0_26
DB: PostgreSQL 8.1
JDBC Driver: postgresql-9.1-901.jdbc4
Application Server: apache-tomcat-6.0.28

在少数其他配置或历史记录为空字符串时一切正常.从 pgAdmin 执行的相同语句工作正常.

Everything is working fine on few other configurations or when history is empty string. Same statement executed from pgAdmin works fine.

我猜问题出在 PostgreSQL JDBC 驱动程序中，是否有合理的理由将空字符串视为 bytea 值?也许 Postgres 8 和 9 之间有一些奇怪的变化?

I guess problem is in PostgreSQL JDBC driver, is there some sensible reason to treat null string as a bytea value? Maybe some strange changes between Postgres 8 and 9?

推荐答案

有一个简单的解决方法:当您构建查询字符串时，如果参数将为 null -- 使用null"而不是?".

There's a simple fix: when you're building the query string, if a parameter is going to be null -- use "null" instead of "?".

正如@araqnid 所说，Hibernate 错误地将空值转换为 bytea 类型，因为它不知道更好.

As @araqnid says, Hibernate is incorrectly casting null values into type bytea because it doesn't know any better.

这篇关于PostgreSQL JDBC 空字符串作为字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！