I'm developing a Spring Boot application with Spring Data JPA. I'm using a custom JPQL query to group by some field and list of object.
My entity class:
public class Review {
@ManyToOne
private ApplicationUser applicationUser;
private String month;
private Integer monthNumber;
private Integer year;
private String rating;
}
My model class:
public class UserInformationExport {
private String userGid;
private String lastName;
private String firstName;
private List<MonthlyReview> grades;
}
public class MonthlyReview {
private String month;
private Integer monthNumber;
private String rating;
}
I try the following query, but didn't work. How can I build that list of objects ?
UPDATE solution without GROUP BY...
return entityManager.createQuery("select new path.UserInformationExport(app.gid, app.lastName, app.firstName," +
" (select new path.MonthlyReview(r.month, r.monthNumber, r.qualifier, r.rating) from Review r where r.year = :yr and r.applicationUser.id = app.id))" +
" from ApplicationUser app where app.id in (:ids)", UserInformationExport.class)
.setParameter("yr", year)
.setParameter("ids", ids)
.getResultList();
but it throws the following error:
java.lang.UnsupportedOperationException: getDataType() is not supported by ConstructorNode!
at org.hibernate.hql.internal.ast.tree.ConstructorNode.getDataType(ConstructorNode.java:129)
at org.hibernate.hql.internal.ast.tree.QueryNode.getDataType(QueryNode.java:151) at org.hibernate.hql.internal.ast.tree.ConstructorNode.resolveConstructorArgumentTypes(ConstructorNode.java:158) at org.hibernate.hql.internal.ast.tree.ConstructorNode.prepare(ConstructorNode.java:133) at org.hibernate.hql.internal.ast.HqlSqlWalker.processConstructor(HqlSqlWalker.java:1260) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2395) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2261) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1523) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:602) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:330) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:278) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:113) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:73) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:613) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:825) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114) at jdk.internal.reflect.GeneratedMethodAccessor93.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362) at com.sun.proxy.$Proxy101.createQuery(Unknown Source) at jdk.internal.reflect.GeneratedMethodAccessor93.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:311) at com.sun.proxy.$Proxy101.createQuery(Unknown Source)
I would like to have something like this:
[
{
"userGid" : "958",
"lastName":"fad",
"firstName":"fad",
"grades" : [
{
"month": "Jan",
"monthNumber":1,
"qualifier":4,
"rating":"wqfw"
}
]
}
]