使用find_in_set()的JPA查询报Unexpected AST node错误求助

场景代码 #

DAO层代码 #

@Repository
public interface AbcDAO extends JpaRepository<AbcEntity, Integer>, JpaSpecificationExecutor<AbcEntity> {

    @Query("SELECT mb FROM AbcEntity mb where if(mb.assignto_user is NOT NULL , find_in_set(:user , mb.assignto_user) , mb.assignto_user IS NULL ) "
            + "and  find_in_set(:role , mb.assigned_to) group by mb.id order by mb.id DESC")
    public List<AbcEntity> abcInboxList(@Param("user") Integer user, @Param("role") Integer role);
   
}

调用代码 #

List<AbcEntity> abcList = abcDAO.abcInboxList(abcDTO.getUser_id(), abcDTO.getRole_id());

错误信息 #

运行代码时触发"unexpected AST node"错误，错误栈如下：

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.xxx.dao.AbcDAO.abcInboxList(java.lang.Integer,java.lang.Integer)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:96) ~[spring-data-jpa-2.5.6.jar:2.5.6]
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:66) ~[spring-data-jpa-2.5.6.jar:2.5.6]
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:51) ~[spring-data-jpa-2.5.6.jar:2.5.6]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:163) ~[spring-data-jpa-2.5.6.jar:2.5.6]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:252) ~[spring-data-jpa-2.5.6.jar:2.5.6]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:87) ~[spring-data-jpa-2.5.6.jar:2.5.6]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:102) ~[spring-data-commons-2.5.6.jar:2.5.6]
    ... 71 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 84 [SELECT mb FROM com.xxx.model.AbcEntity mb where if(mb.assignto_user is NOT NULL , find_in_set(:user , mb.assignto_user) , mb.assignto_user IS NULL ) and  find_in_set(:role , mb.assigned_to) group by mb.id order by mb.id DESC]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:734) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362) ~[spring-orm-5.3.12.jar:5.3.12]
    at com.sun.proxy.$Proxy166.createQuery(Unknown Source) ~[na:na]
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:90) ~[spring-data-jpa-2.5.6.jar:2.5.6]
    ... 77 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 84 [SELECT mb FROM com.xxx.model.AbcEntity mb where if(mb.assignto_user is NOT NULL , find_in_set(:user , mb.assignto_user) , mb.assignto_user IS NULL ) and  find_in_set(:role , mb.assigned_to) group by mb.id order by mb.id DESC]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:282) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:613) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
    ... 85 common frames omitted

注意：该查询语句在MySQL Workbench中可正常执行并返回正确结果。

问题原因及解决方法 #

原因 #

JPA默认使用HQL（Hibernate查询语言），HQL不支持MySQL的IF()函数语法，同时find_in_set()也不是HQL的内置函数，导致Hibernate无法解析该查询。

解决方法 #

方法1：使用原生SQL查询

在@Query注解中添加nativeQuery = true，直接使用MySQL原生SQL，这样就能兼容IF()和find_in_set()函数：

@Repository
public interface AbcDAO extends JpaRepository<AbcEntity, Integer>, JpaSpecificationExecutor<AbcEntity> {

    @Query(value = "SELECT mb.* FROM abc_entity mb where if(mb.assignto_user is NOT NULL , find_in_set(:user , mb.assignto_user) , mb.assignto_user IS NULL ) "
            + "and  find_in_set(:role , mb.assigned_to) group by mb.id order by mb.id DESC", nativeQuery = true)
    List<AbcEntity> abcInboxList(@Param("user") Integer user, @Param("role") Integer role);
   
}

注意：原生SQL中需使用数据库实际表名（如abc_entity）和字段名，而非实体类名和属性名。

方法2：用HQL的CASE表达式替代MySQL的IF函数

如果想继续使用HQL，可将IF()替换为HQL支持的CASE WHEN表达式，同时通过FUNCTION()调用MySQL的find_in_set()函数：

@Repository
public interface AbcDAO extends JpaRepository<AbcEntity, Integer>, JpaSpecificationExecutor<AbcEntity> {

    @Query("SELECT mb FROM AbcEntity mb WHERE " +
           "CASE WHEN mb.assignto_user IS NOT NULL THEN FUNCTION('find_in_set', :user, mb.assignto_user) ELSE 1 END = 1 " +
           "AND FUNCTION('find_in_set', :role, mb.assigned_to) = 1 " +
           "GROUP BY mb.id ORDER BY mb.id DESC")
    List<AbcEntity> abcInboxList(@Param("user") Integer user, @Param("role") Integer role);
}

说明：FUNCTION('find_in_set', ...)会让Hibernate将其转换为对应的MySQL函数调用；CASE表达式实现了原IF的逻辑：当assignto_user不为NULL时，判断find_in_set的返回值是否为1（找到返回位置，找不到返回0），为NULL时直接返回1满足条件。

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