avoid setParameter for "order by" in JPQL

最新推荐文章于 2021-03-04 20:32:05 发布

最新推荐文章于 2021-03-04 20:32:05 发布 · 166 阅读

文章标签：

#JPQL order by

JEE 专栏收录该内容

40 篇文章

订阅专栏

本文介绍两种实现JPQL查询中动态排序的方法：一是通过构建动态JPQL语句来实现，二是获取查询结果后在应用程序中进行排序。此外还讨论了ORDER BY子句中asc/desc关键字的设置问题。

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