Given a table named rating in a MySQL database.
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| rating_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| prod_id | bigint(20) unsigned | YES | MUL | NULL | |
| rating_num | int(10) unsigned | YES | | NULL | |
| ip_address | varchar(45) | YES | | NULL | |
| row_version | bigint(20) unsigned | NO | | 0 | |
+-------------+---------------------+------+-----+---------+----------------+
I need to fetch a row number from within a group of rows in this table. In Oracle, the row_number() window function is the rescue.
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 = ?;
Neither JPA nor MySQL however, supports window functions. Therefore, the following different SQL statement can be used (self join based on prod_id).
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 statement returns the following result set.
+---------+-----------+------------+
| prod_id | rating_id | row_number |
+---------+-----------+------------+
| 7 | 16 | 1 |
| 7 | 3 | 2 |
+---------+-----------+------------+
| 8 | 8 | 1 |
| 8 | 1 | 2 |
+---------+-----------+------------+
| 9 | 15 | 1 |
| 9 | 14 | 2 |
| 9 | 5 | 3 |
+---------+-----------+------------+
| 10 | 11 | 1 |
| 10 | 10 | 2 |
| 10 | 9 | 3 |
| 10 | 7 | 4 |
| 10 | 6 | 5 |
| 10 | 2 | 6 |
+---------+-----------+------------+
| 16 | 13 | 1 |
| 16 | 12 | 2 |
| 16 | 4 | 3 |
+---------+-----------+------------+
A row number associated with a particular group of products can be retrieved from the above result set based on rating_id (primary key) using an additional condition in the join. The query can now be completed fully as follows.
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 obvious, a row number of a specific group of products can now be selected from the third column (row_number) of the result set (which will always be one row only).
The finalized query in JPA is nevertheless still troublesome. The following criteria query is insufficient to generate 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);
The JPQL corresponding to the above criteria query :
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 the above 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
Notice the join condition in the generated statement.
on rating0_.`prod_id`=rating1_.`rating_id`
It should however be
on rating0_.`prod_id`=rating1_.`prod_id`
So, the question being is how to self join the rating table based on prod_id (which is a foreign key)?
The self join relationship in the Rating entity has been 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 MySQL specific statement to fetch a row number from inside a specific group of rows.
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 relationship is changed like the following,
@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;
Hibernate 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. So, I don't think there may exist a way to get around the problem.