在JDBC / iBatis 3中迭代大型结果集的最佳方法是什么？ [英] What is the best way to iterate over a large result set in JDBC/iBatis 3?

问题描述

我们试图从数据库中迭代大量的行并将它们转换为对象。行为将如下所示：

We're trying to iterate over a large number of rows from the database and convert those into objects. Behavior will be as follows:

• 结果将按序列id排序，当序列ID更改时将创建新对象。创建的对象将被发送到外部服务，有时必须等待再发送一个（这意味着下一组数据不会立即使用）。


• 我们已经投入了代码在iBatis 3中，所以iBatis解决方案对我们来说是最好的方法（我们已经尝试过使用RowBounds，但还没有看到它是如何进行迭代的。）


• 我们是喜欢平衡最小化内存使用量和减少数据库访问次数。


• 我们也对纯JDBC方法持开放态度，但我们希望该解决方案适用于不同的数据库。

• Result will be sorted by sequence id, a new object will be created when sequence id changes. The object created will be sent to an external service and will sometimes have to wait before sending another one (which means the next set of data will not be immediately used)
• We already have invested code in iBatis 3 so an iBatis solution will be the best approach for us (we've tried using RowBounds but haven't seen how it does the iteration under the hood).
• We'd like to balance minimizing memory usage and reducing number of DB trips.
• We're also open to pure JDBC approach but we'd like the solution to work on different databases.

更新1 ：

• 我们需要尽可能少地调用DB（1次调用将是理想的情况），同时还要防止应用程序使用太多内存。对于这类问题，还有其他解决方案可能是纯JDBC还是其他任何技术？

更新2

• 查询将被后端驱动，并且在给定时间只执行1个实例。

感谢并希望听到您对此的见解。

Thanks and hope to hear your insights on this.

推荐答案

看来你需要某种分页。 iBatis通过查询中的标准LIMIT / OFFSET参数（以及iBatis 3中的RowBounds）来实现这一点。

It seems you need some sort of pagination. iBatis does that through the standard LIMIT/OFFSET parameters in the query (and RowBounds in iBatis 3 ).

但似乎（如果我说得对）你也正在使用iBatis的GROUP BY功能，所以选择返回N个记录与N1 distintidx字段导致创建N1父对象，每个对象具有多个子对象（创建了总共N个子对象）。或类似的东西。

But it seems (if I get it right) that you also are using the GROUP BY feature of iBatis, so that a select returning N records with N1 distint "idx" fields result in the creation of N1 "parent" objects each one having several children objects (with a total of N children objects created). Or something like that.

不幸的是（并且可以理解）这两件事混合不好。

Unfortunately (and understandably) both things do not mix well.

我在这里看不到任何银弹，人们可以想到很多方法，每个方法都有它的缺点 - 很难在没有更多信息的情况下进行评估。

I dont' see any silver bullet here, one can think of many approaches, each has its shortcomings - hard to evaluate without more information.

如果主要对象是大的 （许多记录），每一个都将被单独处理（通过远程服务器访问）你甚至可能想要进行临时分页，每页有一个对象，在内部记住优先读取的id（类似 SELECT ... FROM ... WHERE id =（SELECT MIN（id）FROM .... WHERE id> #lastid＃））

If the main objects are "big" (many records) and each one will be processed individually (with a trip to a remote server) you might even want to do an ad-hoc pagination, with a object per page, remembering internally the previosuly read id (something like SELECT ... FROM ... WHERE id = (SELECT MIN(id) FROM .... WHERE id > #lastid# ) )

这篇关于在JDBC / iBatis 3中迭代大型结果集的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！