Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Retrieving a row number from within a specific group of rows using a self join in JPA

Tags:

hibernate

jpa

self-join

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.

like image 592
Tiny Avatar asked Mar 12 '16 19:03

Tiny

1 Answers

Hibernate does everything exactly as what your mappings tell it to do. The construct FROM Rating a INNER JOIN a.rating b means join primary key of one rating with the foreign key (referencing that PK) of another.

To achieve what you want with your mappings you have to explicitly construct the join condition, so the from and where clauses of your JPQL could look like:

SELECT ...
FROM Rating a, Rating b
WHERE a.rating.id = b.rating.id
  AND a.ratingId = :ratingId
  AND a.ratingId <= b.ratingId
GROUP BY ...
ORDER BY ...

rating.id is translated to prod_id column.

like image 62
Dragan Bozanovic Avatar answered Oct 12 '22 07:10

Dragan Bozanovic
Sign in to Comment

Related questions

Spring nested @Transactional method and rollback
Hibernate - Orderding criteria by formula property
Storing Java Double Infinity and NaN values to MS SQL 2008 database
Hibernate override @transient
Generate "on delete cascade" using Ebean
Sort table records in special order
org.hibernate.MappingException: Repeated column in mapping for collection using @JoinTable
Querying Hibernate cache
No matching bean of type [org.hibernate.SessionFactory] found for dependency
Hibernate 3.x vs 4.x performance perspective
Hibernate session ID
How to convert joda DateTime in jpa native query
How is flush: true different from manually flushing the currentSession?
Failover @Type for h2?
Disable logging with hibernate from maven
Multi tenancy app Java Spring Hibernate Mysql OAuth2 Spring Securit
How to set tablespace for @Entity?
Unit test for Spring boot Hibernate JPA based DAOs
Hibernate: Details of Proxy Implementation (Lazy Fetching)
Spring - Tutorials vs Real Life

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!