JPA按ElementCollection字段排序自定义查询抛出UnsupportedOperationException

实体里有个用@ElementCollection标注的字符串列表字段keywords，用自定义JPA查询按这个字段排序时会抛出UnsupportedOperationException，但用Spring Data自带的findAll()按它排序就没问题，不带排序的自定义查询也能正常跑。给keywords加@OrderBy注解也没用，不想用Specification，因为实际查询还有很多其他参数。

实体和Repository代码 #

public class SomeParent {
  @ElementCollection
  private List<String> keywords;
}

public interface SomeParentRepository extends JpaRepository<SomeParent, Integer>, JpaSpecificationExecutor<SomeParent> {

  @Query("select sp from SomeParent sp")
  Page<SomeParent> findBySomething(Pageable pageable);
}

报错的调用方式 #

var pageable = PageRequest.of(0, 20, Sort.by("keywords"));
someParentRepository.findBySomething(pageable);

正常的调用方式 #

1. 用findAll()排序：

var pageable = PageRequest.of(0, 20, Sort.by("keywords"));
someParentRepository.findAll(pageable);

2. 自定义查询不带排序：

var pageable = PageRequest.of(0, 20);
someParentRepository.findBySomething(pageable);

错误堆栈 #

java.lang.UnsupportedOperationException: null
    at org.hibernate.query.sqm.sql.internal.PluralValuedSimplePathInterpretation.accept(PluralValuedSimplePathInterpretation.java:72) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.visitSortSpecification(AbstractSqlAstTranslator.java:3764) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.visitSortSpecification(AbstractSqlAstTranslator.java:3745) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.renderOrderBy(AbstractSqlAstTranslator.java:3351) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.visitOrderBy(AbstractSqlAstTranslator.java:3335) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.visitQuerySpec(AbstractSqlAstTranslator.java:3086) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.dialect.PostgreSQLSqlAstTranslator.visitQuerySpec(PostgreSQLSqlAstTranslator.java:186) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.tree.select.QuerySpec.accept(QuerySpec.java:119) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.visitSelectStatement(AbstractSqlAstTranslator.java:988) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.translateSelect(AbstractSqlAstTranslator.java:851) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.translate(AbstractSqlAstTranslator.java:801) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:372) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:268) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:244) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:518) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:367) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.hibernate.query.Query.getResultList(Query.java:119) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:204) ~[spring-data-jpa-3.1.5.jar:3.1.5]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92) ~[spring-data-jpa-3.1.5.jar:3.1.5]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:149) ~[spring-data-jpa-3.1.5.jar:3.1.5]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:137) ~[spring-data-jpa-3.1.5.jar:3.1.5]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136) ~[spring-data-commons-3.1.5.jar:3.1.5]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120) ~[spring-data-commons-3.1.5.jar:3.1.5]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164) ~[spring-data-commons-3.1.5.jar:3.1.5]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) ~[spring-data-commons-3.1.5.jar:3.1.5]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.13.jar:6.0.13]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:72) ~[spring-data-commons-3.1.5.jar:3.1.5]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.13.jar:6.0.13]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.0.13.jar:6.0.13]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) ~[spring-tx-6.0.13.jar:6.0.13]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.13.jar:6.0.13]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.13.jar:6.0.13]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.0.13.jar:6.0.13]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.13.jar:6.0.13]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-3.1.5.jar:3.1.5]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.13.jar:6.0.13]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-6.0.13.jar:6.0.13]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.13.jar:6.0.13]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244) ~[spring-aop-6.0.13.jar:6.0.13]
    at jdk.proxy2/jdk.proxy2.$Proxy248.findBySomething(Unknown Source) ~[na:na]

Spring Data的findAll()会自动处理集合字段的关联和排序逻辑，但自定义@Query不会——keywords是@ElementCollection，对应数据库里单独的一张表，自定义查询里没关联这张表，直接按keywords排序的话，Hibernate找不到对应的字段，自然报错。

方案1：自定义查询里手动关联集合表，用聚合函数排序 #

根据你用的数据库方言，修改自定义查询，关联keywords对应的表，然后用聚合函数把集合转成可排序的类型：

PostgreSQL环境示例 #

public interface SomeParentRepository extends JpaRepository<SomeParent, Integer>, JpaSpecificationExecutor<SomeParent> {

  @Query("select sp from SomeParent sp left join sp.keywords k group by sp.id order by array_agg(k) asc")
  Page<SomeParent> findBySomething(Pageable pageable);
}

如果要动态适配传入的Sort参数，可以用SpEL表达式：

@Query(value = "select sp from SomeParent sp left join sp.keywords k group by sp.id", 
       countQuery = "select count(sp) from SomeParent sp",
       sort = "order by array_agg(k) :#{#pageable.sort}")
Page<SomeParent> findBySomething(Pageable pageable);

MySQL环境示例 #

用GROUP_CONCAT函数把集合转成字符串排序：

@Query("select sp from SomeParent sp left join sp.keywords k group by sp.id order by group_concat(k separator ',') asc")
Page<SomeParent> findBySomething(Pageable pageable);

方案2：用Spring Data的查询派生方法 #

如果你的实际查询参数可以通过Spring Data的派生方法规则写出来，直接用派生方法代替自定义@Query就行，这样Spring Data会自动处理排序：

// 举个例子，假设实际查询要按name模糊匹配
Page<SomeParent> findByNameContaining(String name, Pageable pageable);

这种方式和findAll()的行为一致，不用自己写关联逻辑。

方案3：自定义Hibernate排序函数（适合复杂场景） #

如果需要更灵活的集合排序逻辑，可以自定义Hibernate的SQL扩展函数，不过这个需要对Hibernate的扩展机制有了解，适合复杂需求的场景。

• 用聚合函数的时候，要确认你的数据库支持对应的函数，比如PostgreSQL的array_agg、MySQL的GROUP_CONCAT。
• 分组查询必须包含实体的主键字段，所以group by sp.id就够了（假设id是主键）。

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