spring boot JPA IN clause not working

1
votes

I have below API in CrudRepository

public static final String getVendorOrder ="select new com.meha.ps.order.dto.VendorOrder(orderItem.vendorItemId"
        + ",order.orderId"
        + ",order.createTime"
        + ",order.modTime "
        + ",org.orgId"
        + ",org.orgType"
        + ") from Order order inner join order.OrderItem orderItem on order.orderId=orderItem.orderId"
        + " inner join Org org on org.orgId=orderItem.vendorId"
        + " where orderItem.vendorItemId in :vendorItemIds";

@Query(getVendorOrder)
List<VendorOrder> findVendorOrder(@Param("vendorItemIds") List<Integer> vendorItemIds);

On startup getting below error:

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: order near line 1, column 193 [select new com.meha.ps.order.dto.VendorOrder(orderItem.vendorItemId,order.orderId,order.createTime,order.modTime ,org.orgId,org.orgType) from com.meha.ps.order.entities.Order order inner join order.OrderItem orderItem on order.orderId=orderItem.orderId inner join Org org on org.orgId=orderItem.vendorId where orderItem.vendorItemId in :vendorItemIds]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1679) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1608) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:294) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_65]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_65]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_65]
at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_65]
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:347) ~[spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at com.sun.proxy.$Proxy114.createQuery(Unknown Source) ~[na:na]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86) ~[spring-data-jpa-1.10.6.RELEASE.jar:na]
... 95 common frames omitted

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: order near line 1, column 193 [select new com.meha.ps.order.dto.VendorOrder(orderItem.vendorItemId,order.orderId,order.createTime,order.modTime ,org.orgId,org.orgType) from com.meha.ps.order.entities.Order order inner join order.OrderItem orderItem on order.orderId=orderItem.orderId inner join Org org on org.orgId=orderItem.vendorId where orderItem.vendorItemId in :vendorItemIds] at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:288) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:115) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:76) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final] ... 102 common frames omitted

1
spring-boot
add the impl of those 3 entities.. can be only the mapping of dependencies between them – Maciej Kowalski
You should not put new keyword in the query. Remove that object creation part from query and try again. select orderItem.vendorItemId" + ",order.orderId" + ",order.createTime" + ",order.modTime " + ",org.orgId" + ",org.orgType" + "from Order order inner join order.OrderItem orderItem on order.orderId=orderItem.orderId" + " inner join Org org on org.orgId=orderItem.vendorId" + " where orderItem.vendorItemId in :vendorItemIds – Vijendra Kumar Kulhade
@Maciej Kowalski, There is mapping between Order and OrderItem of oneToMany but there is no mapping between Org and OrderItem but OrderItem stores orgId from Org, can we write hibernate query in such scenario? – Santosh choudhary

1 Answers

0
votes

If there is no direct mapping between some of the entities then i would suggest using the old style join all the way (the rest of the query seems alright):

select new com.meha.ps.order.dto.VendorOrder(orderItem.vendorItemId"
        + ",order.orderId"
        + ",order.createTime"
        + ",order.modTime "
        + ",org.orgId"
        + ",org.orgType"
        + ") from Order order, OrderItem orderItem, Org org       
           where order.orderId=orderItem.orderId"
        + " and org.orgId=orderItem.vendorId"
        + " and orderItem.vendorItemId in :vendorItemIds";