PostgreSQL JDBC将Null字符串作为bytea。

Question

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

（getEntityManager()返回spring注入的EntityManager，database字段history类型为text或varchar2（2000））

```java TypedQuery query = getEntityManager().createQuery( "SELECT h FROM History h WHERE h.entityId = :entityId", History.class); query.setParameter("entityId", entity.getId()); List historyList = query.getResultList(); ```

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**

提示：您需要重写或转换表达式。

问题仅在以下配置中出现：
操作系统：CentOS release 5.6（Final）
Java：1.6.0_26
数据库：PostgreSQL 8.1
JDBC驱动程序：postgresql-9.1-901.jdbc4
应用服务器：apache-tomcat-6.0.28

在其他几个配置或历史记录为空字符串时，一切正常。 从pgAdmin执行相同语句可以正常工作。

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

- laidlook

---

我怀疑EntityManager在调用NULL值时调用了错误的PreparedStatement.setXXX()方法。您也可以尝试使用8.1 JDBC驱动程序。顺便说一句：您是否知道8.1已不再受支持？ - user330315

你能澄清一下你前两段的内容吗？它们似乎没有意义。 - Erwin Brandstetter

相同的代码在 PostgreSQL 9+ (@a_horse_with_no_name 客户端必须是8.1...)、Windows 7 等环境下可以正常工作。如果 entity.getHistory() 是 "" 而不是 null，它也可以正常工作。同样的插入操作在 pgAdmin 中执行也能正常工作。 - laidlook

@laidlook: 你试过将8.1的驱动程序与8.1数据库配合使用吗？（但我仍然认为这是那个实体管理器的问题） - user330315

有点。我没有访问这个服务器的权限，我所能做的就是要求客户执行这种操作。他们尝试过但没有结果，他们这样说...开发者的悲哀 :) 我会尝试在虚拟机上创建这个配置。 - laidlook

Answer 1

有一个简单的解决方法：在构建查询字符串时，如果参数将为空，则使用“null”而不是“？”。

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

- Daniel Winterstein

Answer 2

由于你使用 null 值调用 `setParameter(int,Object)` 方法，很有可能实体管理器不知道要使用哪种持久化类型来绑定参数。我猜想 Hibernate 偏爱使用 SerializableType，这将等同于 bytea。

可以使用接受 Parameter 对象的 setParameter 方法，以便您可以指定类型。我并没有真正使用过 JPA，因此无法给出详细的指针。

（在我的看法中，这是你滥用 EntityManager 的本地 SQL 查询接口进行插入操作所应得的报应，而不是映射实体或直接使用 JDBC）

- araqnid

Answer 3

以上大部分答案都很有意义，帮助我缩小了问题范围。

我是这样解决空值问题的：

setParameter(8, getDate(), TemporalType.DATE);

- Kaushik Mvsp

---

对我来说，.setParameter("yearLow",yearLow, IntegerType.INSTANCE) 就解决了问题。 - Humppakäräjät

这对我不起作用 - 当日期为空时，我会得到expected type [null] passed with TemporalType; expecting Date or Calendar的错误提示。 - LisekKL

Answer 4

使用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();

如果确实存在此问题，我也已经提交了HHH-9165报告此问题。

- Hendy Irawan

---

注意，我曾经遇到过一个问题，当你使用Short时，你不能使用setShort()，因为它需要一个非空的short。在Hibernate 4.x中，解决方案是这样的：.setParameter("short", ShortValue, ShortType.INSTANCE);（在3.x中，我认为应该使用Hibernate.SHORT而不是使用ShortType）。 - Emmanuel Touzery

试图将 JPA 转换为 Hibernate 已经折腾了好几个小时。谢谢！ - andrej

update语句的新问题：https://hibernate.atlassian.net/browse/HHH-14778 - cdalxndr

Answer 5

你可以在查询中将参数转换为适当的类型。我认为这个解决方案对其他数据库也适用。

之前：

@Query("SELECT person FROM people person"
     + " WHERE (?1 IS NULL OR person.name = ?1)")
List<Person> getPeopleWithName(final String name);

之后：

@Query("SELECT person FROM people person"
     + " WHERE (?1 IS NULL OR person.name = cast(?1 as string))")
List<Person> getPeopleWithName(final String name);

同样适用于 LIKE 语句：

person.name LIKE concat('%', cast(?1 as string), '%')

并且使用数字 (这里是 Double 类型):

person.height >= cast(cast(?1 as string) as double)

- Jezor

Answer 6

如果您愿意使用PreparedStatement类而不是Query：

if (entity.getHistory() == null)
    stmt.setNull(6, Types.VARCHAR);
else
    stmt.setString(6, entity.getHistory());

“在您的查询字符串中使用 ?::text 也可能有效，但我个人从未这样做过。”

- Matt

Answer 7

JDBC setNull与SQL类型参数一起使用是针对不通过JDBC驱动程序测试套件的传统JDBC驱动程序的后备方案。

可以按以下方式防止查询中的空错误：

SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName

在修复此问题后，这应该适用于Postgresql。

- user250343

Answer 8

我在使用Oracle数据库执行UPDATE时遇到了同样的问题。有时候更新的一些字段是空的，就会出现这个问题。显然JPA将我的null值转换为bytea，就像在这个话题中已经讨论过的那样。

我放弃使用Query对象来更新我的表，而是使用EntityManager.merge(Entity)方法。

我只需创建包含所有信息的Entity对象，当我执行merge(Entity)时，JPA执行SELECT，将已经持久化在数据中的信息与我的Entity对象进行比较，并正确地执行UPDATE。也就是说，JPA为那些为空的实体变量创建一个“field = NULL”的语句。

- Felipe Windmoller

Answer 9

我在我的Spring Boot应用程序中遇到了这个问题，使用Postgres和Hibernate。 我需要提供日期从/到参数，并且每种组合都可能为空。

查询摘录：

   ...
        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调用：

@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();
}

- dobrivoje

Answer 10

我和你一样遇到了同样的问题。在研究了这个问题之后，我在https://heowc.dev/2021/11/27/hibernate-nativequery-bug-with-postgres上找到了答案。

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

query.executeUpdate();

这可能会有所帮助。

- Vu Tran