如何以 ArrayList 形式检索 JDBC 结果集?
我正在执行一个查询来检索大量 ID(整数)。 有没有某种方法可以简单地以 ArrayList 形式检索所有内容,而不是在 ResultSet 中迭代数百万次并将所有内容一一复制到 ArrayList 中?
我知道 ResultSet 应该被迭代,因为底层实现可能会缓存东西,但在我的情况下,我只需要立即所有 ID。 我知道我可以将 FetchSize 设置为一个很大的数字,但是我仍然必须一一检索 ID。
澄清:我想这样做的原因是性能。 分析显示,执行 ResultSet.next()、ResultSet.getInt() 和 ArrayList.add() 数百万次需要相当长的时间。 我认为数据库(我使用的是用 Java 编写的 H2)可能在内存中的某个位置有数组或列表,所以我正在寻找一种方法将其直接复制给我,而不是通过 ResultSet 迭代接口。
I'm doing a query to retrieve a large amount of IDs (integers). Instead of iterating millions of times through the ResultSet and copying everything one-by-one to an ArrayList, is there some way to simply retrieve everything as an ArrayList?
I understand that ResultSet is supposed to be iterated because the underlying implementation may be caching stuff, but in my situation I just need all the IDs straight away. I know I can set the FetchSize to a large number, but then I still have to retrieve the IDs one-by-one.
Clarification: the reason I want to do this is performance. Profiling shows me that doing ResultSet.next(), ResultSet.getInt() and ArrayList.add() millions of times takes quite some time. I figure that the database (I'm using H2, which is written in Java) probably has the array or list somewhere in memory, so I'm looking for a way to have it copied to me directly instead of through the ResultSet iterating interface.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
使用 Apache DbUtils 库,您可以轻松地将结果集作为映射列表返回。
Using the Apache DbUtils library you can easily return a ResultSet as a List of Maps.
将代码放在方法中。 调用方法非常简单......
我的脑海中浮现出:
然后将其称为:
完成。
Put the code in a method. It's very simple to call methods...
Off the top of my head:
Then just call it as:
Done.
如果您的问题是性能不佳,请在执行之前使用 Experiment with 100, 1000, 10000,.. 调整语句
。这将避免不必要的往返,这可能是您提到的缓慢的原因。
此外,如果必须多次调整内部数组的大小,ArrayList.add() 可能会很慢,因为它会创建一个新数组并将所有数据复制到那里。 尝试使用 LinkedList 代替。
If your problem is poor performance, tune the statement before executing it with
Experiment with 100, 1000, 10000,.. This will avoid unnecessary roundtrips, which may be the cause of the slowness you mentioned.
Also, ArrayList.add() may be slow if it must resize the internal array many times, as it creates a new array and copies all data to there. Try LinkedList instead.