Error when trying to obtain records in a specific order

Question

Error when trying to obtain records in a specific order

Navigation

#1 by (1 votes)

0

I'm trying to get the records in a specific order (the order of the calendar) based on an attribute called month using jpa, but I throw an exception and I do not understand why.

This is my function:

@Query("SELECT P FROM Periodo P WHERE P.linea = :linea ORDER BY"
        + "(P.mes = 'Enero'), (P.mes = 'Febrero'),(P.mes = 'Marzo'),"
        + "(P.mes = 'Abril'),(P.mes = 'Mayo'),(P.mes = 'Junio'),"
        + "(P.mes = 'Julio'),(P.mes = 'Agosto'),(P.mes = 'Septiembre'),"
        + "(P.mes = 'Octubre'),(P.mes = 'Noviembre'),(P.mes = 'Diciembre')")
List<Periodo> findPeriodoByLinea(@Param("linea") Linea linea);

This is the exception:

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: = near line 1, column 80 [SELECT P FROM app.core.entity.Periodo P WHERE P.linea = :linea ORDER BY (P.mes = 'Enero')]
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_121]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_121]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:347) ~[spring-orm-4.3.6.RELEASE.jar:4.3.6.RELEASE]
at com.sun.proxy.$Proxy98.createQuery(Unknown Source) ~[na:na]
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
... 60 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: = near line 1, column 80 [SELECT P FROM app.core.entity.Periodo P WHERE P.linea = :linea ORDER BY (P.mes = 'Enero')]
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.analyze(QueryTranslatorImpl.java:268) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190) ~[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.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(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]
... 67 common frames omitted

java jpa

asked by gibran alexis moreno zuñiga 26.04.2017 в 23:09

source

1 answer

Failed to run Laravel 5.4 from public directory in apache virtualhost divide an array by numerical indexes

score 1 · Accepted Answer

According to the HQL documentation regarding the clause ORDER BY , what this clause expects is a series of properties of a class or component, since these are JPA entities but in your case you are passing them Boolean expressions.

Depending on your needs, I would recommend any of the following alternatives:

Create a SQL view inside your database with the data already sorted, in order to make the HQL expression with which you get it from Hibernate easier. Example:

CREATE VIEW PERIODO_ORD AS SELECT P.*, CASE WHEN P.MES = 'ENERO' THEN 1, WHEN P.MES = 'FEBRERO' THEN 2 ... "ORDENMES" FROM PERIODO P ORDER BY ORDENMES
Create a SQL function that returns an ordered numeric value for each name of the day of the month and use it in your ORDER BY clause, for example:

CREATE FUNCTION ORDEN_MES(MES IN VARCHAR) RETURN NUMBER BEGIN IF UPPER(MES) = 'ENERO' RETURN 1; ELSEIF UPPER(MES) = 'FEBRERO' RETURN 2; ... END
Obtain the unordered data and sort it by Arrays.sort(...) in the ArrayList where the stores.