HQL - 用于分页的行标识符 [英] HQL - row identifier for pagination

问题描述

有谁知道HQL是否有关键字来标识ROWID或ROWNUM等行？

我想用HQL实现分页，但我无法使用.setMaxResult（）或.setFirstResult（）因为我不直接使用会话对象，因此不使用Query对象，而只是将我的查询创建为字符串并使用.find（）方法。

我在查询中尝试使用LIMIT和OFFSET，但HQL似乎忽略了这些关键字，并且无论如何都将整个结果返回给我。

我也无法使用Hibernate标准，因为它不支持出现在查询中的HAVING子句。

我最后的手段是使用ROWNUM / ROWID关键字限制结果集。是否有其他人有任何其他建议？

解决方案

这是hibernate闪耀的一种情况：

使用hql查询的典型解决方案。

  int elementsPerBlock = 10; 
 int page = 2; 
 
 return getSession（）。createQuery（SomeItems order by id asc）
 .setFirstResult（elementsPerBlock *（page-1）+ 1）
 .setMaxResults（elementsPerBlock） 
 .list（）; 
  

hibernate会将它转换为数据库根据其sql语言理解的模式。在oracle上
它将用ROWNUM创建一个子选择< X.
on postgresql它会在msSQL服务器上发出一个LIMIT / OFFSET
它会发出一个TOP ..

据我所知，它不是可能用纯的hql实现这一点。

Does anyone know if HQL has a keyword to identify rows such as ROWID or ROWNUM?

I would like to implement pagination with HQL but I am not able to use .setMaxResult() or .setFirstResult() because I don't work with the session object directly and therefore don't use the Query object but simply create my query as a string and use the .find() method.

I tried using LIMIT and OFFSET in my query, but HQL seems to be ignoring these keywords and is returning the whole result to me no matter what.

I'm also not able to use Hibernate criteria because it does not have support for the "HAVING" clause that appears in my query.

My last resort is to restrict the result set using the ROWNUM/ROWID keyword. Does anyone else have any other suggestions?

解决方案

this is one situation where hibernate shines:

typical solution with hql query.

int elementsPerBlock = 10;
int page = 2;

return  getSession().createQuery("from SomeItems order by id asc")
            .setFirstResult(elementsPerBlock * (page-1) + 1 )
            .setMaxResults(elementsPerBlock)
            .list();

hibernate will translate this to a pattern that is understood by the database according to its sql dialect. on oracle it will create a subselect with ROWNUM < X. on postgresql it will issue a LIMIT / OFFSET on msSQL server it will issue a TOP..

to my knowledge it is not possible to achieve this with "pure" hql.

这篇关于HQL - 用于分页的行标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！