avoid setParameter for "order by" in JPQL

you might want to create a JPQL like this:

String sql = "select distinct s from Student s order by :orderByColumn asc"
Query query = em.createQuery(sql);
query.setParameter("orderByColumn", "name");
return query.getResultList();

 However, the "order by" parameter might not be set properly with some implementations, especially with the "distinct" keyword in the select clause.

 

So generally you would have two solutions for this:

1. use dynamic jpql, based on passed sorting requirement, for "order by"

String sql = "select distinct s from Student s";
if(sortByName) {
  sql += " order by s.name asc";
}
else if(sortByMob) {
  sql += " order by s.mobile asc";
}
Query query = em.createQuery(sql);
//query.setParameter("orderByColumn", "name");
return query.getResultList();

 

2. sorting the returned list in your application, rather than in the query.

 

One last thing to mention: The "asc/desc" cannot be set as parameters (not tested though)

 

hope this can save you a few hours ... (-;