JPQL中COALESCE在Repository测试失效及IntelliJ语法报错问题

算力阿峰

2026-7-16

JPQL中COALESCE语法错误及单元测试SQL异常修复

问题现象

IntelliJ IDEA 标记JPQL语句里的COALESCE(:questionIds)为语法错误
该语句在JPA控制台执行、通过Rest接口调用均正常返回结果，但单元测试时抛出SQL语法异常，关键报错信息：

Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
...
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ) required: , in statement [select ... where .. and (coalesce(?) is null or psq0_.id not in (?)) and (psq_.companyId in (?))]

相关代码

SomeEntity.class

@NamedQuery(name = SomeEntity.QUERY_COUNT_QUESTIONS, query = "SELECT COUNT( DISTINCT psq.id) FROM SomeEntity AS psq WHERE ...AND (COALESCE(:questionIds) IS NULL OR psq.id NOT IN (:questionIds)) AND ... ")

SomeRepository.class

public Long countQuestions(..., final List<Long> questionIds) {
    final Query countQuery = em.createNamedQuery(SomeEntity.QUERY_COUNT_QUESTIONS);
    final QueryParameter queryParameter = QueryParameter.with(...).and(QUESTION_IDS, !questionIds.isEmpty() ? questionIds : null);
    JPAUtils.setQueryParameters(countQuery, queryParameter);
    return  (Long) countQuery.getSingleResult();
}

SomeRepositoryTest.class

public void testCountQuestions_shouldFilterQuestion() {
    final Long questionsCount = repo.countQuestions(...,List.of(questionId));
    assertEquals((Long)2L, questionsCount);
}

修复方案

步骤1：移除JPQL中的COALESCE语句

更新SomeEntity.class中的NamedQuery，去掉COALESCE相关判断：

@NamedQuery(name = SomeEntity.QUERY_COUNT_QUESTIONS, query = "SELECT COUNT( DISTINCT psq.id) FROM SomeEntity AS psq WHERE ...AND (psq.id NOT IN (:questionIds)) AND ... ")

步骤2：为空列表参数设置兜底值

修改SomeRepository.class的参数赋值逻辑，当questionIds为空时，传入一个业务中不可能存在的ID（如-1L）作为兜底，避免空列表导致的SQL语法问题：

final QueryParameter queryParameter = QueryParameter.with(...).and(QUESTION_IDS, !questionIds.isEmpty() ? questionIds : Collections.singletonList(-1L));

注：原方案中的questionIds.add(-1L)存在副作用风险（会修改原传入的空列表），建议使用Collections.singletonList(-1L)创建独立的单元素列表。

内容的提问来源于stack exchange，提问作者codeCad