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

问题描述

有谁知道 HQL 有没有关键字来标识行，例如 ROWID 或 ROWNUM?

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

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

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.

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

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.

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

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

我最后的手段是使用 ROWNUM/ROWID 关键字来限制结果集.还有其他人有什么建议吗?

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

推荐答案

这是 hibernate 大放异彩的一种情况:

this is one situation where hibernate shines:

hql 查询的典型解决方案.

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 会将其转换为数据库根据其 sql 方言理解的模式.在 oracle 上，它将创建一个 ROWNUM < 的子选择.X.在 postgresql 上它会发出一个 LIMIT/OFFSET在 msSQL 服务器上，它会发出一个 TOP..

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

据我所知，使用纯"hql 无法实现这一点.

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

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