I need a criteria query for the following SQL query.
SELECT w.weight_id, w.weight, zc.charge
FROM weight w
LEFT OUTER JOIN zone_charge zc ON w.weight_id=zc.weight_id
AND zc.zone_id=? <-------
ORDER BY w.weight ASC
The corresponding JPQL query would be like,
SELECT w.weightId, w.weight, zc.charge
FROM Weight w
LEFT JOIN w.zoneChargeSet zc
WITH zc.zone.zoneId=:id <-------
ORDER BY w.weight
I can't reproduce the same with criteria especially WITH zc.zone.zoneId=:id.
The following criteria query uses the where clause.
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple>criteriaQuery=criteriaBuilder.createTupleQuery();
Root<Weight> root = criteriaQuery.from(entityManager.getMetamodel().entity(Weight.class));
SetJoin<Weight, ZoneCharge> join = root.join(Weight_.zoneChargeSet, JoinType.LEFT);
ParameterExpression<Long>parameterExpression=criteriaBuilder.parameter(Long.class);
criteriaQuery.where(criteriaBuilder.equal(join.get(ZoneCharge_.zoneTable).get(ZoneTable_.zoneId), parameterExpression));
criteriaQuery.multiselect(root.get(Weight_.weightId), root.get(Weight_.weight), join.get(ZoneCharge_.charge));
criteriaQuery.orderBy(criteriaBuilder.asc(root.get(Weight_.weight)));
TypedQuery<Tuple> typedQuery = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, 1L);
List<Tuple> list = typedQuery.getResultList();
How could it be modified so that it corresponds to ...LEFT OUTER JOIN zone_charge zc ON w.weight_id=zc.weight_id
AND zc.zone_id=?
This generates the following SQL query.
SELECT weight0_.weight_id AS col_0_0_,
weight0_.weight AS col_1_0_,
zonecharge1_.charge AS col_2_0_
FROM social_networking.weight weight0_
LEFT OUTER JOIN social_networking.zone_charge zonecharge1_
ON weight0_.weight_id = zonecharge1_.weight_id
WHERE zonecharge1_.zone_id =?
ORDER BY weight0_.weight ASC
The with operator is a hibernate-specific extension to JPQL. You won't find support for it in the criteria API.
This can be done along with the criteria API provided by JPA 2.1 using the new feature join ON clause. Accordingly, the criteria query given in the question can be rewritten as follows.
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple>criteriaQuery=criteriaBuilder.createQuery(Tuple.class);
Root<Weight> root = criteriaQuery.from(entityManager.getMetamodel().entity(Weight.class));
ListJoin<Weight, ZoneCharge> join = root.join(Weight_.zoneChargeList, JoinType.LEFT);
criteriaQuery.multiselect(root.get(Weight_.weightId), root.get(Weight_.weight), join.get(ZoneCharge_.charge));
ParameterExpression<Long>parameterExpression=criteriaBuilder.parameter(Long.class);
join.on(criteriaBuilder.equal(join.get(ZoneCharge_.zoneTable).get(ZoneTable_.zoneId), parameterExpression));
criteriaQuery.orderBy(criteriaBuilder.asc(root.get(Weight_.weight)));
List<Tuple> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, 1L).getResultList();
It produces the following desired SQL query.
SELECT weight0_.weight_id AS col_0_0_,
weight0_.weight AS col_1_0_,
zonecharge1_.charge AS col_2_0_
FROM social_networking.weight weight0_
LEFT OUTER JOIN social_networking.zone_charge zonecharge1_
ON weight0_.weight_id = zonecharge1_.weight_id
AND ( zonecharge1_.zone_id =? )
ORDER BY weight0_.weight ASC
It should be worth mentioning that this query succeeds on Hibernate (4.3.5 final) but the same query unexpectedly fails on EclipseLink (2.5.1) with the following exception.
java.lang.IllegalArgumentException: No parameter with name : Parameter[name=null] was found within the query: ReportQuery(referenceClass=Weight ).
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.setParameter(EJBQueryImpl.java:548)
at admin.beans.ZoneChargeBean.getZoneChargeList(ZoneChargeBean.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1081)
at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1153)
at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:4695)
at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:630)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:582)
at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:46)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:883)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:582)
at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doCall(SystemInterceptorProxy.java:163)
at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:140)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:883)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:369)
at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:4667)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:4655)
at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:212)
... 72 more
ParamterExpression must be removed for it to work as follows.
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple>criteriaQuery=criteriaBuilder.createQuery(Tuple.class);
Root<Weight> root = criteriaQuery.from(entityManager.getMetamodel().entity(Weight.class));
ListJoin<Weight, ZoneCharge> join = root.join(Weight_.zoneChargeList, JoinType.LEFT);
criteriaQuery.multiselect(root.get(Weight_.weightId), root.get(Weight_.weight), join.get(ZoneCharge_.charge));
join.on(criteriaBuilder.equal(join.get(ZoneCharge_.zoneTable).get(ZoneTable_.zoneId), 1L));
criteriaQuery.orderBy(criteriaBuilder.asc(root.get(Weight_.weight)));
List<Tuple> list = entityManager.createQuery(criteriaQuery).getResultList();
It should be an oversight in EclipseLink 2.5.1.
