Java 有没有比执行 select 和 count 查询更有效的在 Hibernate 中进行分页的方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/168084/
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
Is there a more efficient way of making pagination in Hibernate than executing select and count queries?
提问by serg
Usually pagination queries look like this. Is there a better way instead of making two almost equal methods, one of which executing "select *..." and the other one "count *..."?
通常分页查询看起来像这样。有没有更好的方法来代替两个几乎相等的方法，其中一个执行“select * ...”，另一个执行“count * ...”？
public List<Cat> findCats(String name, int offset, int limit) {
Query q = session.createQuery("from Cat where name=:name");
q.setString("name", name);
if (offset > 0) {
q.setFirstResult(offset);
}
if (limit > 0) {
q.setMaxResults(limit);
}
return q.list();
}
public Long countCats(String name) {
Query q = session.createQuery("select count(*) from Cat where name=:name");
q.setString("name", name);
return (Long) q.uniqueResult();
}
回答by Kyle Dyer
If you don't need to display the total number of pages then I'm not sure you need the count query. Lots of sites including google don't show the total on the paged results. Instead they just say "next>".
如果您不需要显示总页数，那么我不确定您是否需要计数查询。包括谷歌在内的许多网站不会在分页结果中显示总数。相反，他们只是说“下一个>”。
回答by anjanb
here's the way pagination is done in hibernate
这是在休眠中完成分页的方式
Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();
you can get more info from hibernate docs at : http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#objectstate-querying-executing-pagination10.4.1.5 and 10.4.1.6 section give you more flexbile options.
你可以从休眠文档中获得更多信息：http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#objectstate-querying-executing-pagination 10.4.1.5 和 10.4.1.6 部分给你更灵活的选择。
BR,
~A
BR，
~A
回答by michal kralik
There is a way
有一种方法
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.
第二个 SELECT 返回一个数字，指示如果第一个 SELECT 在没有 LIMIT 子句的情况下写入，它将返回多少行。
Reference: FOUND_ROWS()
参考：FOUND_ROWS()
回答by Josh
I know this problem and have faced it before. For starters, the double query mechanism where it does the same SELECT conditions is indeed not optimal. But, it works, and before you go off and do some giant change, just realize it might not be worth it.
我知道这个问题，以前也遇到过。对于初学者来说，它执行相同 SELECT 条件的双重查询机制确实不是最佳的。但是，它有效，在你开始做一些巨大的改变之前，意识到它可能不值得。
But, anyways:
但是，无论如何：
1) If you are dealing with small data on the client side, use a result set implementation that lets you set the cursor to the end of the set, get its row offset, then reset the cursor to before first.
1）如果您在客户端处理小数据，请使用结果集实现，让您将游标设置到集合的末尾，获取其行偏移量，然后首先将游标重置为之前。
2) Redesign the query so that you get COUNT(*) as an extra column in the normal rows. Yes, it contains the same value for every row, but it only involves 1 extra column that is an integer. This is improper SQL to represent an aggregated value with non aggregated values, but it may work.
2) 重新设计查询，以便将 COUNT(*) 作为普通行中的额外列。是的，它的每一行都包含相同的值，但它只涉及 1 个额外的整数列。这是用非聚合值表示聚合值的不当 SQL，但它可能有效。
3) Redesign the query to use an estimated limit, similar to what was being mentioned. Use rows per page and some upper limit. E.g. just say something like "Showing 1 to 10 of 500 or more". When they browse to "Showing 25o to 260 of X", its a later query so you can just update the X estimate by making the upper bound relative to page * rows/page.
3) 重新设计查询以使用估计的限制，类似于所提到的。使用每页行数和一些上限。例如，只需说“显示 500 个或更多中的 1 到 10 个”之类的内容。当他们浏览到“Showing 25o to 260 of X”时，这是一个稍后的查询，因此您可以通过使上限相对于页面 * 行/页面来更新 X 估计值。
回答by Eric R. Rath
Baron Schwartz at MySQLPerformanceBlog.com authored a postabout this. I wish there was a magic bullet for this problem, but there isn't. Summary of the options he presented:
巴伦·施瓦茨在MySQLPerformanceBlog.com撰写了后这个问题。我希望这个问题有灵丹妙药，但没有。他提出的选项摘要：
- On the first query, fetch and cache all the results.
- Don't show all results.
- Don't show the total count or the intermediate links to other pages. Show only the "next" link.
- Estimate how many results there are.
- 在第一次查询时，获取并缓存所有结果。
- 不要显示所有结果。
- 不要显示总数或到其他页面的中间链接。仅显示“下一个”链接。
- 估计有多少结果。
回答by tobinharris
You can use MultiQueryto execute both queries in a single database call, which is much more efficient. You can also generate the count query, so you don't have to write it each time. Here's the general idea ...
您可以使用MultiQuery在单个数据库调用中执行这两个查询，这样效率更高。您还可以生成计数查询，因此您不必每次都编写它。这是一般的想法......
var hql = "from Item where i.Age > :age"
var countHql = "select count(*) " + hql;
IMultiQuery multiQuery = _session.CreateMultiQuery()
.Add(s.CreateQuery(hql)
.SetInt32("age", 50).SetFirstResult(10))
.Add(s.CreateQuery(countHql)
.SetInt32("age", 50));
var results = multiQuery.List();
var items = (IList<Item>) results[0];
var count = (long)((IList<Item>) results[1])[0];
I imagine it would be easy enough to wrap this up into some easy-to-use method so you can have paginateable, countable queries in a single line of code.
我想把它包装成一些易于使用的方法会很容易，这样你就可以在一行代码中进行可分页、可数的查询。
As an alternative, if you're willing to test the work-in-progress Linq for NHibernate in nhcontrib, you might find you can do something like this:
作为替代方案，如果您愿意在nhcontrib 中为 NHibernate 测试正在进行的 Linq ，您可能会发现您可以执行以下操作：
var itemSpec = (from i in Item where i.Age > age);
var count = itemSpec.Count();
var list = itemSpec.Skip(10).Take(10).AsList();
Obviously there's no batching going on, so that's not as efficient, but it may still suite your needs?
显然没有批处理，所以效率不高，但它仍然可以满足您的需求？
Hope this helps!
希望这可以帮助！
回答by ruslan
I think the solution depends on database you are using. For example, we are using MS SQL and using next query
我认为解决方案取决于您使用的数据库。例如，我们正在使用 MS SQL 并使用下一个查询
select
COUNT(Table.Column) OVER() as TotalRowsCount,
Table.Column,
Table.Column2
from Table ...
That part of query can be changed with database specified SQL.
可以使用数据库指定的 SQL 更改查询的那部分。
Also we set the query max result we are expecting to see, e.g.
我们还设置了我们期望看到的查询最大结果，例如
query.setMaxResults(pageNumber * itemsPerPage)
And gets the ScrollableResults instance as result of query execution:
并获取 ScrollableResults 实例作为查询执行的结果：
ScrollableResults result = null;
try {
result = query.scroll();
int totalRowsNumber = result.getInteger(0);
int from = // calculate the index of row to get for the expected page if any
/*
* Reading data form page and using Transformers.ALIAS_TO_ENTITY_MAP
* to make life easier.
*/
}
finally {
if (result != null)
result.close()
}
回答by Pietro Polsinelli
At this Hibernate wiki page:
在这个 Hibernate wiki 页面上：
https://www.hibernate.org/314.html
https://www.hibernate.org/314.html
I present a complete pagination solution; in particular, the total number of elements is computed by scrolling to the end of the resultset, which is supported by now by several JDBC drivers. This avoids the second "count" query.
我提出了一个完整的分页解决方案；特别是，元素总数是通过滚动到结果集的末尾来计算的，现在有几个 JDBC 驱动程序支持。这避免了第二个“计数”查询。
回答by randomThought
I found a way to do paging in hibernate without doing a select count (*) over a large dataset size. Look at the solution that I posted for my answer here.
我找到了一种在 hibernate 中进行分页的方法，而无需对大型数据集进行选择计数 (*)。看看我在这里发布的答案的解决方案。
processing a large number of database entries with paging slows down with time
you can perform paging one at a time without knowing how many pages you will need originally
您可以一次执行一个分页，而无需知道您最初需要多少页
回答by kommradHomer
Here is a solutionby Dr Richard Kennard (mind the bug fix in the blog comment!), using Hibernate Interceptors
这是Richard Kennard 博士的解决方案（注意博客评论中的错误修复！），使用Hibernate Interceptors
For summary, you bind your sessionFactory to your interceptor class, so that your interceptor can give you the number of found rows later.
总之，您将 sessionFactory 绑定到拦截器类，以便拦截器可以稍后为您提供找到的行数。
You can find the code on the solution link. And below is an example usage.
您可以在解决方案链接上找到代码。下面是一个示例用法。
SessionFactory sessionFactory = ((org.hibernate.Session) mEntityManager.getDelegate()).getSessionFactory();
MySQLCalcFoundRowsInterceptor foundRowsInterceptor = new MySQLCalcFoundRowsInterceptor( sessionFactory );
Session session = sessionFactory.openSession( foundRowsInterceptor );
try {
org.hibernate.Query query = session.createQuery( ... ) // Note: JPA-QL, not createNativeQuery!
query.setFirstResult( ... );
query.setMaxResults( ... );
List entities = query.list();
long foundRows = foundRowsInterceptor.getFoundRows();
...
} finally {
// Disconnect() is good practice, but close() causes problems. Note, however, that
// disconnect could lead to lazy-loading problems if the returned list of entities has
// lazy relations
session.disconnect();
}
