PostgreSQL，Spring Data JPA：将整数空值解释为bytea

Question

在PostgreSQL中，我有这个表：

CREATE TABLE public.my_table
(
    id integer NOT NULL,
    ...

我想执行这个查询：显示给定ID的行。如果ID为空，则显示所有行。

我尝试使用以下方法：

public interface MyRepository extends JpaRepository<MyTable, Integer> {

    @Query(value = "SELECT * FROM my_table WHERE (?1 IS NULL OR id = ?1)", nativeQuery = true)
    List<MyTable> findAll(Integer id);

如果 id != null，那么一切正常。但是如果 id == null，我就会收到错误信息。

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261) ~[spring-orm-4.3.13.RELEASE.jar:4.3.13.RELEASE]
...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
...
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
...

很明显短路评估不起作用，null被转换为bytea。

为了解决这个问题，我已将查询值更改为

SELECT * FROM my_table WHERE (?1 IS NULL OR id = (CAST (CAST(?1 AS character varying) AS integer)))

但这不太好，因为int被转换为字符串再转换为int。你是否有更好的解决方法，例如更好的强制类型转换或SQL查询？

- Johannes Flügel

Answer 1

另一种解决方法是从EntityManager（例如示例中的em）手动创建查询，并使用非空值调用setParameter，然后再使用真实值调用一次。

private static final Integer exampleInt = 1;

List<MyTable> findAll(Integer id) {
    return em.createNativeQuery("SELECT * FROM my_table WHERE (:id IS NULL OR id = :id)", MyTable.class)
            .setParameter("id", exampleInt)
            .setParameter("id", id)
            .resultList();
}

这确保了Hibernate知道下一次调用时的值类型，即使它为空。

问题出在PostgreSQL服务器上，而不是Hibernate上，但他们拒绝修复它，因为它按预期工作。您只需要在服务器上有几百种类型的SQL NULL，并且它们大多不兼容，尽管它应该是一个特殊的单一值。

- coladict

---

PostgreSQL声称他们正确地实现了JDBC规范，并且Hibernate传递null值给JDBC的方式是错误的，因为他们发送了一个“类型化的null”而不是一个常量null。如果这确实是JDBC规范所说的（我没有检查），那么即使他们的技巧在其他数据库上正常工作，Hibernate的错误也应该被指出。 - Jodiug