avoid setParameter for "order by" in JPQL
程序员文章站
2024-01-11 18:59:04
...
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 ... (-;