在Hibernate中,有没有比执行选择查询和计数查询更有效的分页方法?

时间:2020-03-06 15:05:18  来源:igfitidea点击:

通常,分页查询看起来像这样。有没有更好的方法而不是使用两个几乎相等的方法,其中一个执行" 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();
}

解决方案

如果我们不需要显示总页数,那么我不确定我们是否需要计数查询。包括google在内的许多网站都没有在页面结果中显示总数。相反,他们只是说" next>"。

这是在休眠状态下进行分页的方式

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

我们可以从以下位置的休眠文档中获取更多信息:http://www.hibernate.org/hib_docs/v3/reference/zh-CN/html_single/#objectstate-querying-executing-pagination
10.4.1.5和10.4.1.6部分为我们提供了更多灵活的选择。

BR
〜A

有一种方法

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

第二个SELECT返回一个数字,该数字指示如果没有LIMIT子句写入第一个SELECT,它将返回多少行。

参考:FOUND_ROWS()

我知道这个问题,以前也曾遇到过。对于初学者来说,执行相同的SELECT条件的双重查询机制确实不是最佳选择。但是,它确实有效,在我们开始进行一些重大更改之前,只是意识到这可能不值得。

但是,无论如何:

1)如果要在客户端上处理小数据,请使用结果集实现,该结果集实现可将游标设置为集合的末尾,获取其行偏移量,然后将游标重设为首先。

2)重新设计查询,以便在常规行中获得COUNT(*)作为额外的列。是的,它的每一行都包含相同的值,但它只包含1个额外的整数列。这是不正确的SQL,无法用非聚合值来表示聚合值,但它可能会起作用。

3)重新设计查询以使用一个估计的限制,与前面提到的类似。每页使用行数和一些上限。例如。只需说"显示1到10或者500或者更多"之类的内容即可。当他们浏览到"显示X在25o到260之间"时,它会稍后查询,因此我们可以通过相对于页面*行/页面的上限来更新X估算值。

MySQLPerformanceBlog.com的Baron Schwartz撰写了有关此内容的文章。我希望有解决这个问题的灵丹妙药,但没有。他介绍的选项摘要:

  • 在第一个查询中,获取并缓存所有结果。
  • 不显示所有结果。
  • 不要显示总数或者指向其他页面的中间链接。仅显示"下一个"链接。
  • 估计有多少个结果。

我们可以使用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];

我想将其包装成一些易于使用的方法就足够容易了,这样我们就可以在一行代码中包含可分页的,可数的查询。

或者,如果我们愿意在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();

显然,没有进行批处理,因此效率不高,但是仍然可以满足需求?

希望这可以帮助!

我认为解决方案取决于我们使用的数据库。例如,我们正在使用MS SQL并使用下一个查询

select 
  COUNT(Table.Column) OVER() as TotalRowsCount,
  Table.Column,
  Table.Column2
from Table ...

可以使用数据库指定的SQL更改查询的那部分。

此外,我们设置了我们希望看到的查询最大结果,例如

query.setMaxResults(pageNumber * itemsPerPage)

并获取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()
}

在此Hibernate Wiki页面上:

https://www.hibernate.org/314.html

我提供了一个完整的分页解决方案;特别是,元素的总数是通过滚动到结果集的末尾来计算的,目前该结果集已得到多个JDBC驱动程序的支持。这避免了第二个"计数"查询。