I am trying to execute a MySql query in JPQL.
Here is my MySql Query
select * from test.employee order by DEG != 'm2',DEG;
where test
is schema name, employee
is table name and DEG
contains strings.
I wrote this in JPQL and got exception.
EntityManagerFactory emFactory = Persistence.createEntityManagerFactory("DBConnectionPU");
EntityManager em = emFactory.createEntityManager();
Query q = em.createQuery("SELECT em FROM Employee em ORDER BY em.deg != 'm2', em.deg");
List<Employee> list = q.getResultList();
Exception :
Exception Description: Syntax error parsing [SELECT em FROM Employee em ORDER BY em.deg != 'm2', em.deg].
[36, 50] The order by item is not a valid expression.
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1605)
at dbconnection.DBConnection.main(DBConnection.java:39)
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [SELECT em FROM Employee em ORDER BY em.deg != 'm2', em.deg].
[36, 50] The order by item is not a valid expression.
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:334)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1603)
... 1 more
I tried this using criteria query and it works fine. Whats wrong with it in straight JPQL query? Am I missing something ?
Try changing em.deg != 'm2'
to case when
:
SELECT em
FROM Employee em
ORDER BY CASE WHEN em.deg != 'm2' THEN 1 ELSE 0 END, em.deg