带有空参数的java Spring数据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)），但没有效果

另外，这是一个遗留项目，所以我使用以下版本：

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'

Answer 1

您将不得不使用以下选项：(cast(:identifierId as bigint) IS NULL OR roles.identifier_id=:identifierId)

Answer 2

这是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的第一次调用确保Hibenate使用正确的类型设置器，第二次调用在没有执行查询的情况下覆盖第一个值，并跳过类型检测

Answer 3

所以这不是对上面问题的确切答案，但我面临着一个类似的问题，我想我会在这里加上它，对于那些遇到这个问题的人

我使用的是一个本机查询，其中查询的in子句可能为null，即：

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

我得到了bytea错误，最后我能够发送一个空列表

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

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

希望这能为那些遇到这个问题的人节省一些时间，他们想知道如何将null传递给本机查询的列表参数

Answer 4

通过在roles.identifier_id=:identifierId之间添加空格来修改查询应该类似于roles.identifier_id= :identifierId

Answer 5

正如@coladict的回答中所提到的，不幸的是，您不能用clean-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 documentation中的示例

另请参见this article关于JPA和Hibernate查询中的setParameter用法