PostgreSQL JDBC 空字符串作为字节
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8211195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL JDBC Null String taken as a bytea
提问by laidlook
If entity.getHistory() is null following code snippet:
如果 entity.getHistory() 为 null 以下代码片段：
(getEntityManager() returns spring injected EntityManager, database field history type is: text or varchar2(2000)
(getEntityManager() 返回 spring 注入的 EntityManager，数据库字段历史类型为：text 或 varchar2(2000)
Query query = getEntityManager().createNativeQuery("insert into table_name(..., history, ....) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
[...]
.setParameter(6, entity.getHistory())
[...]
query.executeUpdate();
Gives strange exception:
给出奇怪的异常：
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.
提示：您需要重写或转换表达式。
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
仅在此配置中出现问题：
操作系统：CentOS release 5.6 (Final)
Java：1.6.0_26
DB：PostgreSQL 8.1
JDBC 驱动程序：postgresql-9.1-901.jdbc4
应用服务器：apache-tomcat-6.0.28
Everything is working fine on few other configurations or when history is empty string. Same statement executed from pgAdmin works fine.
在少数其他配置或历史记录为空字符串时一切正常。从 pgAdmin 执行的相同语句工作正常。
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?
我猜问题出在 PostgreSQL JDBC 驱动程序中，是否有一些合理的理由将空字符串视为 bytea 值？也许 Postgres 8 和 9 之间有一些奇怪的变化？
回答by Daniel Winterstein
There's a simple fix: when you're building the query string, if a parameter is going to be null -- use "null" instead of "?".
有一个简单的解决方法：当您构建查询字符串时，如果参数将为空——使用“null”而不是“?”。
As @araqnid says, Hibernate is incorrectly casting null values into type bytea because it doesn't know any better.
正如@araqnid 所说，Hibernate 错误地将空值转换为 bytea 类型，因为它不知道更好。
回答by araqnid
Since you're calling setParameter(int,Object)with a null value, at a guess the entity manager has no idea which persistence type to use to bind the parameter. Istr Hibernate having a predilection for using SerializableType, which would equate to a bytea.
由于您使用setParameter(int,Object)空值进行调用，因此猜测实体管理器不知道使用哪种持久性类型来绑定参数。Istr Hibernate 偏爱使用 SerializableType，这相当于一个字节。
Can you use the setParameter method that takes a Parameter object instead, so you can specify the type? I haven't really used JPA, so can't give a detailed pointer.
您可以使用带有 Parameter 对象的 setParameter 方法，以便指定类型吗？我没有真正使用过JPA，所以不能给出详细的指针。
(IMHO this is your just desserts for abusing EntityManager's native-sql query interface to do inserts, rather than actually mapping the entity, or just dropping through to JDBC)
（恕我直言，这是您滥用 EntityManager 的本机 sql 查询接口来进行插入的唯一甜点，而不是实际映射实体，或者只是下降到 JDBC）
回答by Hendy Irawan
Using Hibernate specific Session API works as a workaround:
使用 Hibernate 特定的 Session API 可以作为一种解决方法：
String sql = "INSERT INTO person (id, name) VALUES (:id, :name)";
Session session = em.unwrap(Session.class);
SQLQuery insert = session.createSQLQuery(sql);
sql.setInteger("id", 123);
sql.setString("name", null);
insert.executeUpdate();
I've also filed HHH-9165to report this issue, if it is indeed a bug.
我还提交了HHH-9165来报告这个问题，如果它确实是一个错误。
回答by Kaushik Mvsp
Most of above answers are meaningful and helped me to narrow down issue.
以上大多数答案都很有意义，并帮助我缩小了问题的范围。
I fixed issue for nulls like this:
我修复了这样的空值问题：
setParameter(8, getDate(), TemporalType.DATE);
回答by Matt
If you are willing to use the PreparedStatementclass instead of Query:
如果您愿意使用PreparedStatement类而不是 Query：
if (entity.getHistory() == null)
stmt.setNull(6, Types.VARCHAR);
else
stmt.setString(6, entity.getHistory());
(It is possible that using ?::textin your query string would also work, but I've never done it that way myself.)
（?::text在您的查询字符串中使用也可能有效，但我自己从未这样做过。）
回答by user250343
The JDBC setNull with sql type parameter is fallback for legacy JDBC drivers that do not pass the JDBC Driver Test Suite.
带有 sql 类型参数的 JDBC setNull 是未通过 JDBC 驱动程序测试套件的旧 JDBC 驱动程序的回退。
One can guard against null errors within the query as follows:
可以防止查询中的空错误，如下所示：
SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName
SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName
This should work in Postgresql after the fix of this issue.
修复此问题后，这应该可以在 Postgresql 中工作。
回答by dobrivoje
Had this issue in my springboot application, with postgres and hibernate. I needed to supply date from/to parameters, with possible nulls in every combination.
在我的 springboot 应用程序中遇到了这个问题，使用 postgres 和 hibernate。我需要提供日期 from/to 参数，在每个组合中都可能为空值。
Excerpt of the query :
查询摘录：
...
WHERE f.idf = :idCat
AND f.supplier = TRUE
AND COALESCE (pc.datefrom, CAST('2000-1-1' AS DATE) ) <= COALESCE (:datefrom, now())
AND COALESCE (pc.dateto, CAST('2200-12-31' AS DATE) ) >= COALESCE (:dateto, now())
Dao call :
道调用：
@Transactional(readOnly = true)
public List<PriceListDto> ReportCatPriceList(Long idk, Date dateFrom, Date dateTo) {
Query query = EM.createNativeQuery(queryReport, PersonProductsByDay.class)
.setParameter("idCat", idk)
.setParameter("datefrom", dateFrom, TemporalType.DATE)
.setParameter("dateto", dateTo, TemporalType.DATE);
return query.getResultList();
}
