Hibernate ScrollableResults 不返回整组结果

发布于 2024-08-27 08:00:51 字数 1100 浏览 8 评论 0原文

我们运行的一些查询有超过 100,000 个结果，需要很长时间才能加载它们然后将它们发送到客户端。所以我使用 ScrollableResults 来提供分页结果功能。但我们的结果大约为 50k（结果数量从来不完全相同）。

我使用 Oracle9i 数据库，使用 Oracle 10 驱动程序，并且 Hibernate 配置为使用 Oracle9 方言。我尝试使用最新的 JDBC 驱动程序 (ojdbc6.jar)，问题再次出现。

我们还遵循了一些建议并添加了一个排序子句，但问题再次出现。

下面是一个代码片段，说明了我们所做的事情：

final int pageSize = 50;
Criteria crit = sess.createCriteria(ABC.class);
crit.add(Restrictions.eq("property", value));
crit.setFetchSize(pageSize);
crit.addOrder(Order.asc("property"));
ScrollableResults sr = crit.scroll();
...
...
ArrayList page = new ArrayList(pageSize);
do{
  for (Object entry : page)
    sess.evict(entry); //to avoid having our memory just explode out of proportion
  page.clear();
  for (int i =0 ; i < pageSize && ! metLastRow;  i++){
    if (sr.next())
      page.add(sr.get(0));
    else 
      metLastRow = true;
  }
  metLastRow = metLastRow?metLastRow:sr.isLast();

  sendToClient(page);
}while(!metLastRow);

那么，为什么我得到的结果集告诉我它是在最后，而它应该有更多的结果呢？

原文

Some of the queries we run have 100'000+ results and it takes forever to load them and then send them to the client. So I'm using ScrollableResults to have a paged results feature. But we're topping at roughly 50k results (never exactly the same amount of results).

I'm on an Oracle9i database, using the Oracle 10 drivers and Hibernate is configured to use the Oracle9 dialect. I tried with the latest JDBC driver (ojdbc6.jar) and the problem was reproduced.

We also followed some advice and added an ordering clause, but the problem was reproduced.

Here is a code snippet that illustrates what we do:

final int pageSize = 50;
Criteria crit = sess.createCriteria(ABC.class);
crit.add(Restrictions.eq("property", value));
crit.setFetchSize(pageSize);
crit.addOrder(Order.asc("property"));
ScrollableResults sr = crit.scroll();
...
...
ArrayList page = new ArrayList(pageSize);
do{
  for (Object entry : page)
    sess.evict(entry); //to avoid having our memory just explode out of proportion
  page.clear();
  for (int i =0 ; i < pageSize && ! metLastRow;  i++){
    if (sr.next())
      page.add(sr.get(0));
    else 
      metLastRow = true;
  }
  metLastRow = metLastRow?metLastRow:sr.isLast();

  sendToClient(page);
}while(!metLastRow);

So, why is it that I get the result set to tell me its at the end when it should be having so much more results?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

世界等同你 2024-09-03 08:00:51

您的代码片段缺少重要部分，例如 resultSet 和 page 的定义。但我还是想知道，这条线不应该

if (resultSet.next())

是这样吗

if (sr.next())

？

附带说明一下，据我所知，从持久性上下文中清理多余的对象可以简单地通过调用来实现，

session.flush();
session.clear();

而不是循环遍历对象集合以分别逐出每个对象。（当然，这要求查询在其自己的独立会话中执行。）

更新：好的，下一轮猜测:-)

您是否可以实际检查哪些行发送到客户端并进行比较直接针对数据库的等效 SQL 查询的结果？最好知道这段代码是否检索（并将达到一定限制的所有行发送给客户端，或者仅从整个结果集中发送一些行（例如每第二行），或者......这可以对根有一些启发。

您可以尝试的另一件事是

crit.setFirstResults(0).setMaxResults(200000);

Your code snippet is missing important pieces, like the definitions of resultSet and page. But I wonder anyway, shouldn't the line

if (resultSet.next())

be rather

if (sr.next())

As a side note, AFAIK cleaning up superfluous objects from the persistence context could be achieved simply by calling

session.flush();
session.clear();

instead of looping through the collection of object to evict each separately. (Of course, this requires that the query is executed in its own independent session.)

Update: OK, next round of guesses :-)

Can you actually check what rows are sent to the client and compare that against the result of the equivalent SQL query directly against the DB? It would be good to know whether this code retrieves (and sends to the client all rows up to a certain limit, or only some rows (like every 2nd) from the whole resultset, or ... that could shed some light on the root cause.

Another thing you could try is

crit.setFirstResults(0).setMaxResults(200000);

回复收藏 0 原文

平安喜乐 2024-09-03 08:00:51

由于我对基于 List 实例的大型项目代码遇到了同样的问题，
我编写了一个非常有限的 List 实现，仅支持迭代器来浏览 ScrollableResults，而无需重构所有服务实现和方法原型。

这个实现可以在我的 IterableListScrollableResults.java Gist 中找到，

它还会定期从会话中刷新 Hibernate 实体。这是一种使用它的方法，例如，当使用 for 循环将数据库中的所有非存档实体导出为文本文件时：

Criteria criteria = getCurrentSession().createCriteria(LargeVolumeEntity.class);
criteria.add(Restrictions.eq("archived", Boolean.FALSE));
criteria.setReadOnly(true);
criteria.setCacheable(false);
List<E> result = new IterableListScrollableResults<E>(getCurrentSession(),
        criteria.scroll(ScrollMode.FORWARD_ONLY));
for(E entity : result) {
    dumpEntity(file, entity);
}

希望它可以有所帮助

As I had the same issue with a large project code based on List<E> instances,
I wrote a really limited List implementation with only iterator support to browse a ScrollableResults without refactoring all services implementations and method prototypes.

This implementation is available in my IterableListScrollableResults.java Gist

It also regularly flushes Hibernate entities from session. Here is a way to use it, for instance when exporting all non archived entities from DB as a text file with a for loop:

Criteria criteria = getCurrentSession().createCriteria(LargeVolumeEntity.class);
criteria.add(Restrictions.eq("archived", Boolean.FALSE));
criteria.setReadOnly(true);
criteria.setCacheable(false);
List<E> result = new IterableListScrollableResults<E>(getCurrentSession(),
        criteria.scroll(ScrollMode.FORWARD_ONLY));
for(E entity : result) {
    dumpEntity(file, entity);
}

With the hope it may help

回复收藏 0 原文

~没有更多了~