欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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 ... (-;

相关标签: JPQL order by