Java 如何将 JDBC ResultSet 作为 ArrayList 检索？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/642249/
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
How can I retrieve a JDBC ResultSet as an ArrayList?
提问by Deckard
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?
我正在执行查询以检索大量 ID（整数）。不是通过 ResultSet 迭代数百万次并将所有内容一一复制到 ArrayList 中，而是有什么方法可以简单地将所有内容作为 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.
我知道 ResultSet 应该被迭代，因为底层实现可能正在缓存内容，但在我的情况下，我只需要立即获得所有 ID。我知道我可以将 FetchSize 设置为一个很大的数字，但是我仍然需要一个一个地检索 ID。
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.
澄清：我想这样做的原因是性能。分析显示，执行 ResultSet.next()、ResultSet.getInt() 和 ArrayList.add() 数百万次需要相当长的时间。我认为数据库（我使用的是用 Java 编写的 H2）可能在内存中的某个地方有数组或列表，所以我正在寻找一种方法将它直接复制给我，而不是通过 ResultSet 迭代接口.
回答by Tom Hawtin - tackline
Put the code in a method. It's very simple to call methods...
将代码放在方法中。调用方法很简单...
Off the top of my head:
在我的头顶：
public static List<Integer> readInts(
PreparedStatement statement
) throws SQLException {
ResultSet results = statement.executeQuery();
try {
assert results.getMetaData().getColumnCount() == 1;
List<Integer> ints = new ArrayList<Integer>();
while (results.next()) {
ints.add(Integer.valueOf(results.getInt(1)));
}
return ints;
} finally {
results.close();
}
}
Then just call it as:
然后只需将其称为：
List<Integer> ids = readInts(myStatemnet);
Done.
完毕。
回答by Mark
Using the Apache DbUtilslibrary you can easily return a ResultSet as a List of Maps.
使用 Apache DbUtils库，您可以轻松地将 ResultSet 作为映射列表返回。
public List query(String query) {
List result = null;
try {
QueryRunner qrun = new QueryRunner();
result = (List) qrun.query(connection, query, new MapListHandler());
} catch (Exception ex) {
ex.printStackTrace();
}
return result;
}
回答by Antonio
If your problem is poor performance, tune the statement before executing it with
如果您的问题是性能不佳，请在执行之前调整语句
java.sql.Statement.setFetchSize(int)
Experiment with 100, 1000, 10000,.. This will avoid unnecessary roundtrips, which may be the cause of the slowness you mentioned.
尝试 100, 1000, 10000,.. 这样可以避免不必要的往返，这可能是您提到的缓慢的原因。
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.
此外，如果 ArrayList.add() 必须多次调整内部数组的大小，它可能会很慢，因为它会创建一个新数组并将所有数据复制到那里。尝试使用 LinkedList。
