Hibernate error: Validation failed for query for method public abstract java.util.List

user_15053014 0
Viewed 1025

Here I wanted to retrieve all Orders from the Order table of a specific user, Ordered by orderDate in DESC and also those Orders having OrderStatus as True to top the list....

But I m getting an error on execution of the code , Hibernate error: Validation failed for query for method public abstract java.util.List

USER

@Entity
@Getter
@Setter
@Table(name = "Users")
public class User {

    @Id
    @Column(name = "user_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "user",cascade = CascadeType.ALL)
    private List<Orders> orders;

 
}

Orders

@Entity
@Getter
@Setter
@Table(name="Orders")
public class Orders {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "order_id")
    private int id;

    private Date orderDate;

    @OneToOne(cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    @JoinColumn(name = "order_id")
    @MapsId
    private OrderStatus orderStatus;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    @JsonIgnore
    private User user;
}

OrderStatus

@Entity
@Table(name="Order_Status")
@Getter
@Setter
public class OrderStatus {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "order_id")
    private int id;
    private boolean delivered;
    private boolean shipped;
}

Repository

public interface OrdersRepository extends JpaRepository<Orders,Integer> {

    @Query("select o from Orders o where o.user.id=:userid ORDER BY (o.orderStatus.shipped=true), o.orderDate DESC")
    List<Orders> getALlOrders(@Param("userid") int userid);

}

Error

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.shopit.now.demo.repository.OrdersRepository.getALlOrders(int)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:93) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:63) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:76) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:56) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:146) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:216) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:81) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:100) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(QueryExecutorMethodInterceptor.java:93) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[na:na]
    at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133) ~[na:na]
    at java.base/java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1056) ~[na:na]
    at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[na:na]
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:na]
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[na:na]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.mapMethodsToQuery(QueryExecutorMethodInterceptor.java:95) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$new$0(QueryExecutorMethodInterceptor.java:85) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at java.base/java.util.Optional.map(Optional.java:258) ~[na:na]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.<init>(QueryExecutorMethodInterceptor.java:85) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:303) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$6(RepositoryFactoryBeanSupport.java:326) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:230) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at org.springframework.data.util.Lazy.get(Lazy.java:114) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:329) ~[spring-data-commons-2.4.3.jar:2.4.3]
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:144) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1847) ~[spring-beans-5.3.3.jar:5.3.3]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1784) ~[spring-beans-5.3.3.jar:5.3.3]
    ... 48 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: = near line 1, column 125 [select o from com.shopit.now.demo.bean.register.modules.orders.Orders o where o.user.id=:uid ORDER BY (o.orderStatus.shipped=true ), o.orderDate DESC]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:113) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362) ~[spring-orm-5.3.3.jar:5.3.3]
    at com.sun.proxy.$Proxy92.createQuery(Unknown Source) ~[na:na]
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87) ~[spring-data-jpa-2.4.3.jar:2.4.3]
    ... 77 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: = near line 1, column 125 [select o from com.shopit.now.demo.bean.register.modules.orders.Orders o where o.user.id=:uid ORDER BY (o.orderStatus.shipped=true ), o.orderDate DESC]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:282) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
    ... 85 common frames omitted
spring-boot hibernate java
3 Answers

Why passing column value in Order by clause?

As per the logs, the problem is with o.orderStatus.shipped=true only.

Change the query by removing =true from the query or remove shipped from order by query as it doesn't make any sense by ordering by shipped column.

user_65729710

you have a mistake in your select about it tell org.hibernate.hql.internal.ast.QuerySyntaxException from your stacktrace. I'm not sure, but I guess the trouble is here o.orderStatus.shipped=true because it is a compute.

user_149270010

One problem is with (o.orderStatus.shipped=true) which is a condition. Conditions are for WHERE clause, whereas ORDER BY expect properties.

Try with With ORDERBY o.orderStatus.shipped DESC, ... as in this query.

@Query("select o from Orders o where o.user.id=:userid ORDER BY o.orderStatus.shipped DESC, o.orderDate DESC")
List<Orders> getALlOrders(@Param("userid") int userid);
user_93644790
Related