前端之家

  1. 首页
  2. 问答

JPA存储库可以运行LISTAGG SQL聚合功能吗？

• 问答

我在我的项目中使用JPA / Hibernate，出于某些原因，我需要使用sql聚合函数-> LISTAGG方法，但我不知道该怎么做。

select LISTAGG(name,',') within GROUP (ORDER by grade) names,grade,age from student group by grade,age

我可以在sqldeveloper中得到想要的东西。 但是当我在JPA存储库中使用@query时，错误显示无效的列名。 实体如下

@Entity
@Data
public class Student{

@Id
private Long id;
private String name;
private Integer age;
private Double grade;

//private String names;
}

感谢您的帮助。

-------------------------更新--------------------- ------ 数据库是Oracle，表ER：

JPA存储库可以运行LISTAGG SQL聚合功能吗？

运行时

@query(value = "select LISTAGG(name,class from student group by grade,age",native = true)
List<Student> getaggResult();

完整跟踪堆栈为：


org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateaccessException(HibernateJpaDialect.java:352) ~[spring-orm-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:254) ~[spring-orm-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528) ~[spring-orm-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.dao.support.DataaccessUtils.translateIfNecessary(DataaccessUtils.java:242) ~[spring-tx-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153) ~[spring-tx-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodinvocation.proceed(ReflectiveMethodinvocation.java:186) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:144) ~[spring-data-jpa-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodinvocation.proceed(ReflectiveMethodinvocation.java:186) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$ExposeRepositoryinvocationInterceptor.invoke(CrudMethodMetadataPostProcessor.java:364) ~[spring-data-jpa-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodinvocation.proceed(ReflectiveMethodinvocation.java:186) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.aop.interceptor.ExposeinvocationInterceptor.invoke(ExposeinvocationInterceptor.java:93) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodinvocation.proceed(ReflectiveMethodinvocation.java:186) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[spring-data-commons-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodinvocation.proceed(ReflectiveMethodinvocation.java:186) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at com.sun.proxy.$Proxy135.findAggResults(Unknown Source) ~[na:na]
    at com.advancestores.enterprisecatalog.productfilter.service.imp.ProductServiceImp.getaggregationResults(ProductServiceImp.java:40) ~[classes/:na]
    at com.advancestores.enterprisecatalog.productfilter.service.imp.ProductServiceImp$$FastClassBySpringCGLIB$$e763c267.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:685) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at com.advancestores.enterprisecatalog.productfilter.service.imp.ProductServiceImp$$EnhancerBySpringCGLIB$$4394f285.getaggregationResults(<generated>) ~[classes/:na]
    at com.advancestores.enterprisecatalog.productfilter.service.imp.InmemoryCacheServiceImp.updateCache(InmemoryCacheServiceImp.java:28) ~[classes/:na]
    at com.advancestores.enterprisecatalog.productfilter.config.ScheduledCacheUpdate.onApplicationEvent(ScheduledCacheUpdate.java:33) ~[classes/:na]
    at sun.reflect.NativeMethodaccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
    at sun.reflect.NativeMethodaccessorImpl.invoke(Unknown Source) ~[na:1.8.0_131]
    at sun.reflect.DelegatingMethodaccessorImpl.invoke(Unknown Source) ~[na:1.8.0_131]
    at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_131]
    at org.springframework.context.event.ApplicationListenerMethodAdapter.doInvoke(ApplicationListenerMethodAdapter.java:261) ~[spring-context-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.context.event.ApplicationListenerMethodAdapter.processEvent(ApplicationListenerMethodAdapter.java:179) ~[spring-context-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.context.event.ApplicationListenerMethodAdapter.onApplicationEvent(ApplicationListenerMethodAdapter.java:142) ~[spring-context-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172) ~[spring-context-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165) ~[spring-context-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139) ~[spring-context-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:402) ~[spring-context-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:359) ~[spring-context-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.boot.context.event.EventPublishingRunListener.running(EventPublishingRunListener.java:103) ~[spring-boot-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.boot.SpringApplicationRunListeners.running(SpringApplicationRunListeners.java:77) ~[spring-boot-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:327) [spring-boot-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1204) [spring-boot-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at com.advancestores.enterprisecatalog.productfilter.ProductFilterApplication.main(ProductFilterApplication.java:13) [classes/:na]
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2790) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2770) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2604) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2599) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2254) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1069) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:170) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1506) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.query.Query.getResultList(Query.java:132) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at sun.reflect.NativeMethodaccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
    at sun.reflect.NativeMethodaccessorImpl.invoke(Unknown Source) ~[na:1.8.0_131]
    at sun.reflect.DelegatingMethodaccessorImpl.invoke(Unknown Source) ~[na:1.8.0_131]
    at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_131]
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryinvocationHandler.invoke(SharedEntityManagerCreator.java:404) ~[spring-orm-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at com.sun.proxy.$Proxy166.getResultList(Unknown Source) ~[na:na]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129) ~[spring-data-jpa-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) ~[spring-data-jpa-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) ~[spring-data-jpa-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:605) ~[spring-data-commons-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595) ~[spring-data-commons-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595) ~[spring-data-commons-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodinvocation.proceed(ReflectiveMethodinvocation.java:186) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.1.12.RELEASE.jar:2.1.12.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodinvocation.proceed(ReflectiveMethodinvocation.java:186) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) ~[spring-tx-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodinvocation.proceed(ReflectiveMethodinvocation.java:186) ~[spring-aop-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.1.11.RELEASE.jar:5.1.11.RELEASE]
    ... 36 common frames omitted
Caused by: java.sql.SQLException: Invalid column name
    at oracle.jdbc.driver.OracleStatement.getcolumnIndex(OracleStatement.java:3757) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
    at oracle.jdbc.driver.InsensitiveScrollableResultSet.findColumn(InsensitiveScrollableResultSet.java:264) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
    at oracle.jdbc.driver.GeneratedResultSet.getLong(GeneratedResultSet.java:558) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]
    at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java) ~[HikariCP-3.2.0.jar:na]
    at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:63) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:804) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:729) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.processResultSet(Loader.java:1002) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:960) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:351) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2787) ~[hibernate-core-5.3.13.Final.jar:5.3.13.Final]
    ... 65 common frames omitted

我希望该实体接受agg名称，在JPA存储库中我能得到什么？

再次更新：

嘿，如果我按如下所示更改实体：

@Id
private Long id;
//private String name;
private Integer age;
private Double grade;

private String names;

并且sql更改为

select LISTAGG(name,age,id from student group by grade,id

它不是我想要的那样工作。

由于我在“分组依据”条件中使用id列，因此名称:(

有什么主意吗？

->在groupby中使用id列的聚合函数失去了其含义。 ->不使用id列，JPA无法获取包含id的结果集，并抛出“无效的列名”异常。

fankangkangkang 回答：JPA存储库可以运行LISTAGG SQL聚合功能吗？
暂时没有好的解决方案，如果你有好的解决方案，请发邮件至：iooj@foxmail.com
hibernatejavajpaspring-data
本文链接：https://www.f2er.com/3113916.html

大家都在问