Mark Set Mark Set - 9 days ago 5
Java Question

Spring Data Jpa Select new issue

I have a simple Query where i want to select a property which is a SET of my custom entity but i encounter an ambiguous exception.

@Query("SELECT new com.my.package.MyModel(ar.name, ar.adminRoles) " +
" FROM AdminRole ar ")


This is my class:

public class AdminRole {

private String name;
private Set<AdminRole> adminRoles;

}


Here ar.adminRoles is a
Set<AdminRole> adminRoles
. My constructor is correct and if I want to retrieve a MyModel(ar.name) it works fine. It seems to have a problem with passing a SET in the constructor, do you have any idea how to fix that?

ERROR:

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.getResultSet(Loader.java:2117) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:919) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2617) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2600) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.Loader.list(Loader.java:2424) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:118) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:114) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:104) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482) ~[spring-data-commons-1.12.3.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460) ~[spring-data-commons-1.12.3.RELEASE.jar:na]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) ~[spring-data-commons-1.12.3.RELEASE.jar:na]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
... 63 common frames omitted
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=from;select . as col_0_0_;JOIN, DRIVER=4.21.29
at com.ibm.db2.jcc.am.kd.a(kd.java:810) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.kd.a(kd.java:66) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.kd.a(kd.java:140) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.sp.c(sp.java:2796) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.sp.d(sp.java:2784) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.sp.a(sp.java:2212) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.tp.a(tp.java:7997) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.t4.bb.i(bb.java:148) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.t4.bb.b(bb.java:41) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.t4.p.a(p.java:32) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.t4.vb.i(vb.java:145) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.sp.kb(sp.java:2181) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.tp.yc(tp.java:3698) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.tp.a(tp.java:4578) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.tp.b(tp.java:4173) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.tp.lc(tp.java:770) ~[db2jcc4.jar:na]
at com.ibm.db2.jcc.am.tp.executeQuery(tp.java:735) ~[db2jcc4.jar:na]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
... 93 common frames omitted

Answer

You cannot select a multi-valued field in a JPQL SELECT clause. The JPA spec is very clear. Here is the appropriate part of the JPQL BNF

select_clause ::= SELECT [DISTINCT] select_item {, select_item}*
select_item ::= select_expression [[AS] result_variable]
select_expression ::= single_valued_path_expression | scalar_expression | aggregate_expression | 
    identification_variable | OBJECT(identification_variable) | constructor_expression
constructor_expression ::= NEW constructor_name ( constructor_item {, constructor_item}* )
constructor_item ::= single_valued_path_expression | scalar_expression | aggregate_expression |
    identification_variable