Java 你如何在 JPQL 或 HQL 中进行限制查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1239723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you do a limit query in JPQL or HQL?
提问by stevedbrown
In Hibernate 3, is there a way to do the equivalent of the following MySQL limit in HQL?
在 Hibernate 3 中，有没有办法在 HQL 中执行以下等效的 MySQL 限制？
select * from a_table order by a_table_column desc limit 0, 20;
I don't want to use setMaxResults if possible. This definitely was possible in the older version of Hibernate/HQL, but it seems to have disappeared.
如果可能，我不想使用 setMaxResults。这在旧版本的 Hibernate/HQL 中肯定是可能的，但它似乎已经消失了。
采纳答案by skaffman
This was postedon the Hibernate forum a few years back when asked about why this worked in Hibernate 2 but not in Hibernate 3:
几年前，当被问及为什么这在 Hibernate 2 中有效但在 Hibernate 3 中无效时，有人将其发布在 Hibernate 论坛上：
Limit was nevera supported clause in HQL. You are meant to use setMaxResults().
限制从来不是HQL 中受支持的子句。您打算使用 setMaxResults()。
So if it worked in Hibernate 2, it seems that was by coincidence, rather than by design. I thinkthis was because the Hibernate 2 HQL parser would replace the bits of the query that it recognised as HQL, and leave the rest as it was, so you could sneak in some native SQL. Hibernate 3, however, has a proper AST HQL Parser, and it's a lot less forgiving.
所以如果它在 Hibernate 2 中工作，似乎是巧合，而不是设计。我认为这是因为 Hibernate 2 HQL 解析器会替换它识别为 HQL 的查询部分，而其余部分保持原样，因此您可以潜入一些本机 SQL。然而，Hibernate 3 有一个合适的 AST HQL 解析器，它的宽容度要低得多。
I think Query.setMaxResults()really is your only option.
我认为Query.setMaxResults()真的是你唯一的选择。
回答by pjp
If you don't want to use setMaxResults()on the Queryobject then you could always revert back to using normal SQL.
如果您不想setMaxResults()在Query对象上使用，那么您始终可以恢复使用普通 SQL。
回答by Lluis Martinez
If you don't want to use setMaxResults, you can also use Query.scroll instead of list, and fetch the rows you desire. Useful for paging for instance.
如果您不想使用 setMaxResults，您也可以使用 Query.scroll 而不是 list，并获取您想要的行。例如用于分页。
回答by Xingsheng
My observation is that even you have limit in the HQL (hibernate 3.x), it will be either causing parsing error or just ignored. (if you have order by + desc/asc before limit, it will be ignored, if you don't have desc/asc before limit, it will cause parsing error)
我的观察是，即使您在 HQL (hibernate 3.x) 中有限制，它也会导致解析错误或被忽略。（如果limit之前有order by + desc/asc，会被忽略，如果limit之前没有desc/asc，会导致解析错误）
回答by Dilawar
String hql = "select userName from AccountInfo order by points desc 5";
String hql = "select userName from AccountInfo order by points desc 5";
This worked for me without using setmaxResults();
这对我有用而不使用 setmaxResults();
Just provide the max value in the last (in this case 5) without using the keyword limit.
:P
只需提供最后一个（在本例中为 5）的最大值，而无需使用关键字limit。:P
回答by Jessu
// SQL: SELECT * FROM table LIMIT start, maxRows;
Query q = session.createQuery("FROM table");
q.setFirstResult(start);
q.setMaxResults(maxRows);
回答by biancardi
If can manage a limit in this mode
如果可以在此模式下管理限制
public List<ExampleModel> listExampleModel() {
return listExampleModel(null, null);
}
public List<ExampleModel> listExampleModel(Integer first, Integer count) {
Query tmp = getSession().createQuery("from ExampleModel");
if (first != null)
tmp.setFirstResult(first);
if (count != null)
tmp.setMaxResults(count);
return (List<ExampleModel>)tmp.list();
}
This is a really simple code to handle a limit or a list.
这是处理限制或列表的非常简单的代码。
回答by Deep Chand Jaipur
Criteria criteria=curdSession.createCriteria(DTOCLASS.class).addOrder(Order.desc("feild_name"));
criteria.setMaxResults(3);
List<DTOCLASS> users = (List<DTOCLASS>) criteria.list();
for (DTOCLASS user : users) {
System.out.println(user.getStart());
}
回答by swatisinghi
You need to write a native query, refer this.
您需要编写本机查询，请参阅此。
@Query(value =
"SELECT * FROM user_metric UM WHERE UM.user_id = :userId AND UM.metric_id = :metricId LIMIT :limit", nativeQuery = true)
List<UserMetricValue> findTopNByUserIdAndMetricId(
@Param("userId") String userId, @Param("metricId") Long metricId,
@Param("limit") int limit);
回答by Leena Varshney
@Query(nativeQuery = true,
value = "select from otp u where u.email =:email order by u.dateTime desc limit 1")
public List<otp> findOtp(@Param("email") String email);
