Given a table named rating in the MySQL database.
+
I need to get the row number from a group of rows in this table. In Oracle, the row_number() window function is a salvation.
SELECT row_num FROM (SELECT row_number() over (PARTITION BY prod_id ORDER BY rating_id DESC) AS prod_id, rating_id, row_num FROM rating) WHERE rating_id=? AND prod_id = ?;
However, JPA and MySQL do not support window functions. Thus, the following different SQL statement (self join based on prod_id ) can be used.
SELECT a.prod_id, a.rating_id, count(*) as row_number FROM rating a INNER JOIN rating b ON a.prod_id = b.prod_id AND a.rating_id <= b.rating_id GROUP BY a.prod_id, a.rating_id ORDER BY a.prod_id, a.rating_id DESC
The operator returns the next result set.
+
The line number associated with a particular product group can be obtained from the specified result set based on rating_id (primary key) using an additional condition in the connection. Now the query can be completed in the following way.
SELECT a.prod_id, a.rating_id, count(*) as row_number FROM rating a INNER JOIN rating b ON a.prod_id = b.prod_id AND a.rating_id <= b.rating_id AND a.rating_id=? GROUP BY a.prod_id, a.rating_id ORDER BY a.prod_id, a.rating_id DESC
As you can see, the row number of a particular product group can now be selected from the third column ( row_number ) of the result set (which will always be only one row).
A completed request to JPA still remains troublesome. The following criteria query is not sufficient to create the above SQL statement.
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery(); Root<Rating> root = criteriaQuery.from(entityManager.getMetamodel().entity(Rating.class)); criteriaQuery.multiselect(root.get(Rating_.product).get(Product_.prodId), root.get(Rating_.ratingId), criteriaBuilder.count(root)); Join<Rating, Rating> join = root.join(Rating_.rating, JoinType.INNER).on(criteriaBuilder.equal(root, rating)); criteriaQuery.where(criteriaBuilder.lessThanOrEqualTo(root.get(Rating_.ratingId), join.get(Rating_.ratingId))); criteriaQuery.groupBy(root.get(Rating_.product).get(Product_.prodId), root.get(Rating_.ratingId)); criteriaQuery.orderBy(criteriaBuilder.desc(root.get(Rating_.product).get(Product_.prodId)), criteriaBuilder.desc(root.get(Rating_.ratingId))); List<Tuple> list = entityManager.createQuery(criteriaQuery).getResultList(); Long rowNumber = list == null || list.isEmpty() ? 0L : list.get(0).get(2, Long.class);
JPQL matching the above query criteria:
SELECT a.product.prodId, a.ratingId, COUNT(a) AS row_number FROM Rating a INNER JOIN a.rating b ON a.ratingId=:ratingId WHERE a.ratingId <= b.ratingId GROUP BY a.product.prodId, a.ratingId ORDER BY a.product.prodId, a.ratingId DESC
Both of these queries generate the following SQL statement.
select rating0_.`prod_id` as col_0_0_, rating0_.`rating_id` as col_1_0_, count(rating0_.`rating_id`) as col_2_0_ from `jboss_projectdb`.`rating` rating0_ inner join `jboss_projectdb`.`rating` rating1_ on rating0_.`prod_id`=rating1_.`rating_id` and ( rating0_.`rating_id`=? ) where rating0_.`rating_id`<=rating1_.`rating_id` group by rating0_.`prod_id` , rating0_.`rating_id` order by rating0_.`prod_id` desc, rating0_.`rating_id` desc
Pay attention to the join condition in the generated statement.
on rating0_.`prod_id`=rating1_.`rating_id`
However should be
on rating0_.`prod_id`=rating1_.`prod_id`
So, the question is how to independently join the rating table based on prod_id (which is a foreign key)?
The self join relationship in the rating object is defined as follows.
@ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "prod_id", insertable = false, updatable = false) private Rating rating; @OneToMany(fetch = FetchType.LAZY, mappedBy = "rating") private List<Rating> ratingList;
I am not interested in using the following very specific MySQL statement to get the row number from a specific row group.
SELECT row_num FROM (SELECT @row_num := @row_num + 1 AS row_num, tbl.rating_id FROM rating tbl, (SELECT @row_num := 0) t WHERE tbl.prod_id=? ORDER BY tbl.rating_id DESC) t WHERE rating_id = ?
Update:
If the ratio changes as follows,
@JoinColumn(name = "prod_id", referencedColumnName = "prod_id", insertable = false, updatable = false) @ManyToOne(fetch = FetchType.LAZY) private Rating rating; @JoinColumn(name = "prod_id", referencedColumnName = "prod_id") @OneToMany(fetch = FetchType.LAZY) private List<Rating> ratingList;
Sleep mode throws the following exception.
16:43:52,609 WARNING [javax.enterprise.resource.webcontainer.jsf.lifecycle] (default task-18) javax.persistence.PersistenceException: org.hibernate.HibernateException: More than one row with the given identifier was found: entity.Rating[ratingId=null], for class: entity.Rating: service.app.exception.impl.DatabaseException: javax.persistence.PersistenceException: org.hibernate.HibernateException: More than one row with the given identifier was found: entity.Rating[ratingId=null], for class: entity.Rating at service.ejb.interceptors.ExceptionInterceptor.handle(ExceptionInterceptor.java:32) at sun.reflect.GeneratedMethodAccessor398.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.jboss.as.ee.component.ManagedReferenceLifecycleMethodInterceptor.processInvocation(ManagedReferenceLifecycleMethodInterceptor.java:89) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437) at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:64) at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:52) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:275) at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:327) at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:239) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:43) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.security.AuthorizationInterceptor.processInvocation(AuthorizationInterceptor.java:138) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:100) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:66) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:54) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356) at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:636) at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:61) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356) at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:195) at org.jboss.as.ee.component.ViewDescription$1.processInvocation(ViewDescription.java:185) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:73) at shareable.bean.ShareableService$$$view330.findRatingById(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.jboss.weld.util.reflection.Reflections.invokeAndUnwrap(Reflections.java:436) at org.jboss.weld.bean.proxy.EnterpriseBeanProxyMethodHandler.invoke(EnterpriseBeanProxyMethodHandler.java:127) at org.jboss.weld.bean.proxy.EnterpriseTargetBeanInstance.invoke(EnterpriseTargetBeanInstance.java:56) at org.jboss.weld.bean.proxy.InjectionPointPropagatingEnterpriseTargetBeanInstance.invoke(InjectionPointPropagatingEnterpriseTargetBeanInstance.java:67) at org.jboss.weld.bean.proxy.ProxyMethodHandler.invoke(ProxyMethodHandler.java:100) at shareable.bean.ShareableService$614900122$Proxy$_$$_Weld$EnterpriseProxy$.findRatingById(Unknown Source) at converter.RatingConverter.getAsObject(RatingConverter.java:41) at com.sun.faces.renderkit.html_basic.HtmlBasicInputRenderer.getConvertedValue(HtmlBasicInputRenderer.java:171) at javax.faces.component.UIViewParameter.getConvertedValue(UIViewParameter.java:437) at javax.faces.component.UIInput.validate(UIInput.java:975) at javax.faces.component.UIInput.executeValidate(UIInput.java:1248) at javax.faces.component.UIInput.processValidators(UIInput.java:712) at javax.faces.component.UIViewParameter.processValidators(UIViewParameter.java:278) at org.omnifaces.component.input.ViewParam.processValidators(ViewParam.java:120) at javax.faces.component.UIComponentBase.processValidators(UIComponentBase.java:1261) at javax.faces.component.UIComponentBase.processValidators(UIComponentBase.java:1261) at javax.faces.component.UIViewRoot.processValidators(UIViewRoot.java:1195) at com.sun.faces.lifecycle.ProcessValidationsPhase.execute(ProcessValidationsPhase.java:76) at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101) at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198) at javax.faces.webapp.FacesServlet.service(FacesServlet.java:658) at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:85) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129) at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:78) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at io.undertow.websockets.jsr.JsrWebSocketFilter.doFilter(JsrWebSocketFilter.java:129) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at org.omnifaces.facesviews.FacesViewsForwardingFilter.filterExtensionLess(FacesViewsForwardingFilter.java:128) at org.omnifaces.facesviews.FacesViewsForwardingFilter.doFilter(FacesViewsForwardingFilter.java:89) at org.omnifaces.filter.HttpFilter.doFilter(HttpFilter.java:108) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at filter.NoCacheFilter.doFilter(NoCacheFilter.java:33) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at org.omnifaces.filter.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:122) at org.omnifaces.filter.HttpFilter.doFilter(HttpFilter.java:108) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84) at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62) at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36) at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131) at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57) at io.undertow.server.handlers.DisableCacheHandler.handleRequest(DisableCacheHandler.java:33) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.security.handlers.AuthenticationConstraintHandler.handleRequest(AuthenticationConstraintHandler.java:51) at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46) at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64) at io.undertow.servlet.handlers.security.ServletSecurityConstraintHandler.handleRequest(ServletSecurityConstraintHandler.java:56) at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60) at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77) at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest(NotificationReceiverHandler.java:50) at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:284) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:263) at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81) at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:174) at io.undertow.server.Connectors.executeRootHandler(Connectors.java:202) at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:793) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: javax.persistence.PersistenceException: org.hibernate.HibernateException: More than one row with the given identifier was found: entity.Rating[ratingId=null], for class: entity.Rating at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492) at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:50) at shareable.bean.ShareableBean.findRatingById(ShareableBean.java:472) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437) at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:82) at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93) at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63) at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340) at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437) at service.ejb.interceptors.ExceptionInterceptor.handle(ExceptionInterceptor.java:22) ... 133 more Caused by: org.hibernate.HibernateException: More than one row with the given identifier was found: entity.Rating[ratingId=null], for class: entity.Rating at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:86) at org.hibernate.loader.entity.EntityLoader.loadByUniqueKey(EntityLoader.java:143) at org.hibernate.persister.entity.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:2146) at org.hibernate.type.EntityType.loadByUniqueKey(EntityType.java:685) at org.hibernate.type.EntityType.resolve(EntityType.java:427) at org.hibernate.engine.internal.TwoPhaseLoad.doInitializeEntity(TwoPhaseLoad.java:151) at org.hibernate.engine.internal.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:125) at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:1132) at org.hibernate.loader.Loader.processResultSet(Loader.java:992) at org.hibernate.loader.Loader.doQuery(Loader.java:930) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:306) at org.hibernate.loader.Loader.loadEntity(Loader.java:2197) at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:60) at org.hibernate.loader.entity.EntityLoader.loadByUniqueKey(EntityLoader.java:143) at org.hibernate.persister.entity.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:2146) at org.hibernate.type.EntityType.loadByUniqueKey(EntityType.java:685) at org.hibernate.type.EntityType.resolve(EntityType.java:427) at org.hibernate.engine.internal.TwoPhaseLoad.doInitializeEntity(TwoPhaseLoad.java:151) at org.hibernate.engine.internal.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:125) at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:1132) at org.hibernate.loader.Loader.processResultSet(Loader.java:992) at org.hibernate.loader.Loader.doQuery(Loader.java:930) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) at org.hibernate.loader.Loader.doList(Loader.java:2610) at org.hibernate.loader.Loader.doList(Loader.java:2593) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2422) at org.hibernate.loader.Loader.list(Loader.java:2417) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) ... 148 more
criteriaQuery.multiselect(...).distinct(true); Does not help. Thus, I do not think that there can be a way around the problem.