Spring Data JPA 原生查询中如何处理空参数（PostgreSQL）

Question

问题是如何使我的本地查询接受空参数？

以下是我的查询：

    @Query(value = "SELECT " +
            "   summaries.event_type as \"eventType\", " +
            "   summaries.institution_id as \"institutionId\", " +
            "   identifiers.id as \"studentIdentifierId\", " +
            "   identifiers.name as \"studentIdentifierName\" " +
            "FROM summaries " +
            "LEFT JOIN roles ON summaries.student_id=roles.id " +
            "LEFT JOIN identifiers ON roles.identifier_id=identifiers.id " +
            "WHERE " +
            "   summaries.student_id IS NOT NULL AND " +
            "   summaries.event_type IN (:eventTypes) AND " +
            "   (:identifierId IS NULL OR roles.identifier_id=:identifierId) " +
            "GROUP BY " +
            "   summaries.institution_id, " +
            "   summaries.student_id, " +
            "   identifiers.id, " +
            "   identifiers.name, " +
            "   summaries.event_type",
            nativeQuery = true)
    List<CustomProjection> findByCustomCondition(
            @Param("identifierId") Long identifierId,
            @Param("eventTypes") List<String> eventTypes);

现在当我将identifierId传入null时，我收到以下错误:

InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet.
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = bytea
  HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

看起来这一行代码 (:identifierId IS NULL OR roles.identifier_id=:identifierId) 在这里引起了问题，但是我真的找不到一个好的解决办法，因为据我理解它应该正常工作的（当我直接在pgAdmin上使用这个查询时，它按照预期工作，所以我相当确定这是与映射相关的问题）。

我尝试将标识符强制转换为（cast(:identifierId as bigint)），但没有帮助。

PS。这是一个有点陈旧的项目，所以我使用的是以下版本：

compile group: 'org.springframework.data', name: 'spring-data-jpa', version: '1.11.6.RELEASE'
compile group: 'org.postgresql', name: 'postgresql', version: '42.2.2'
compile group: 'org.hibernate', name: 'hibernate-core', version: '5.2.17.Final'

- degath

---

你在 roles.identifier_id 中使用了什么类型？ - SternK

我使用 bigint。 - degath

错误似乎与结果集的提取有关，自定义投影的字段是什么？ - Thomas

字符串 - eventType，长整型 - institutionId，长整型 - studentIdentifierId，字符串 - studentIdentifierName。当我将identifierId作为例如0L传递时，它不会中断，因此我不确定它是否与投影相关。 - degath

Answer 1

这是Hibernate和PostgreSQL常见的问题，解决方法是自己实现该方法，而不是让Spring为您实现。在实现中，您必须执行类似以下操作：

List<CustomProjection> findByCustomCondition(
        @Param("identifierId") Long identifierId,
        @Param("eventTypes") List<String> eventTypes) {
     // entityManager is acquired with the @PersistenceContext annotation as an injectable
     Query q = entityManager.createNativeQuery(..., CustomProjection.class);
     // the important part:
     q.setParameter("identifierId", 0L);
     q.setParameter("identifierId", identifierId);
     ...

第一次调用 setParameter 确保Hibernate使用正确的类型设置器，第二次调用会覆盖第一个值而不执行查询，并跳过类型检测。

- coladict

Answer 2

当 booleanParameterValue 为 null 时，以下代码也适用于我。我的数据库是 Oracle，应用程序使用 jpql 和 hibernate。

Query query = getEntityManager().createNamedQuery("findBankAccountInfos");
query.setParameter("booleanParameter", Boolean.FALSE);
query.setParameter("booleanParameter", booleanParameterValue);

否则应用程序会抛出以下异常：

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER

- Onur Akan

---

你知道吗，我曾经认为这只是在使用PostgreSQL时的问题，但今年我在MSSQL中遇到了它，现在你又证实了另一个数据库也有这个问题？我真希望Hibernate 6中的新类型系统能解决这个问题。 - coladict

Answer 3

所以这不是上面问题的确切答案，但我遇到了类似的问题，我想把它添加在这里，为那些遇到这个问题的人提供帮助。

我正在使用本地查询，其中查询的in子句可能为空，即：

WHERE (cm.first_name in (:firstNames) OR :firstNames is NULL)

我遇到了 bytea 错误，最终我能够发送一个空列表。

(null == entity.getFirstName()? Collections.emptyList() : entity.getFirstName())

在这种情况下，将空列表发送到解析器是有效的，而null则不行。

希望这能为那些寻找如何将null传递给本地查询的列表参数的人节省一些时间。

- Paul Snow

Answer 4

通过在roles.identifier_id=:identifierId之间添加空格来修改查询，应该像这样：roles.identifier_id= :identifierId

- Banyenzachi

Answer 5

你需要使用以下内容代替：(cast(:identifierId as bigint) IS NULL OR roles.identifier_id=:identifierId)

- Christian Beikov

Answer 6

正如@coladict的回答中所提到的，很遗憾，您不能使用纯净的Spring Data JPA解决方案来实现您想要的功能。因此，您可以使用EntityManager和Hibernate特定的setParameter重写查询，该方法允许显式指定绑定参数类型：

import org.hibernate.type.LongType;

// ...

List<CustomProjection> results = entityManager.createNamedQuery("find_by_custom_condition" )
.unwrap(org.hibernate.query.Query.class)
.setParameter("identifierId", identifierId, LongType.INSTANCE)
.getResultList();

然后您可以使用@NamedNativeQuery和@SqlResultSetMapping将本地查询结果映射到CustomProjection。请参阅Hibernate 文档中的示例。

还可以查看此文章，了解JPA和Hibernate查询中setParameter的用法。

- SternK