java 如何保存 SQL Query 中的 resultSet 以便多次使用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11217155/
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 to save the resultSet from SQL Query in order to be used several times?
提问by Willian Fuks
I have this issue: first, I execute a SQL query using Java and then the result is stored into a resultSet() called rs. The query is as follows:
我有这个问题：首先，我使用 Java 执行 SQL 查询，然后将结果存储到名为 rs 的 resultSet() 中。查询如下：
"select ad_id, publisher_id, date, impressions, clicks from table"
Then I read all the data using:
然后我使用以下方法读取所有数据：
while(rs.next()){
(...)
}
The problem is that the code we are running needs to re-use all information from rs several times (running stochastic gradient so there are several iterations) but when the it starts a new iteration (going from iteration 1 to 2) rs now is null and we have to execute the SQL query once again which takes quite a long time...
问题是我们正在运行的代码需要多次重用来自 rs 的所有信息（运行随机梯度所以有几次迭代）但是当它开始新的迭代时（从迭代 1 到 2） rs 现在为空我们必须再次执行 SQL 查询，这需要很长时间......
Is there some "direct" way of saving the result from this query into a variable? We considered creating two ArrayLists to save the Integers numbers (ad_id, publisher_id...) and one for the date field and then iterate over those lists, but we thought maybe there′s a better and easier way of doing so
是否有一些“直接”的方法可以将此查询的结果保存到变量中？我们考虑过创建两个 ArrayList 来保存整数数字（ad_id、publisher_id...）和一个用于日期字段，然后迭代这些列表，但我们认为也许有更好、更简单的方法来做到这一点
In python all we had to do is something like:
在 python 中，我们所要做的就是：
data = cursor.fetchall()
and the variable "data" could be used several times, maybe there′s something similar in Java (I′m still learning Java so I′m not sure)
并且变量“data”可以多次使用，也许Java中有类似的东西（我还在学习Java所以我不确定）
Thanks for any help
谢谢你的帮助
回答by sperumal
In Java, ResultSet object is a cursor that moves forward only and you can iterate only once. So you have store the results into temporary variable (like list), if you would like to use it many times.
在 Java 中，ResultSet 对象是一个只能向前移动且只能迭代一次的游标。因此，如果您想多次使用它，您已将结果存储到临时变量（如列表）中。
Its better to follow some object oriented approach to store the results, instead of using loosely coupled arrays or key value pairs in a HashMap.
最好遵循一些面向对象的方法来存储结果，而不是在 HashMap 中使用松散耦合的数组或键值对。
First you should encapsulate your data into Java Object, for example creating a class like this
首先你应该将你的数据封装到Java Object中，例如创建一个这样的类
public class ClickInfo {
private int ad_id;
private int publisher_id;
private Date date;
private int impressions;
private int clicks;
// Create getter and setter functions for the instance variabless
}
Read the database and create ClickInfoobjects and store it in an Array.
读取数据库并创建ClickInfo对象并将其存储在数组中。
List<ClickInfo> list = new ArrayList<ClickInfo>();
while (rs.next()) {
ClickInfo clickInfo = new ClickInfo();
clickInfo.setAdId(rs.getInt("ad_id"));
...
list.add(clickInfo);
}
Now, you can iterate through the list any number of times to access the data
现在，您可以遍历列表任意次数以访问数据
for (ClickInfo cInfo : list)
System.out.println(cInfo.getAdId());
EDIT: Above example stores the result in an ArrayList, ClickInfo objects can be stored in any data structure that makes sense for your application.
编辑：上面的示例将结果存储在 ArrayList 中，ClickInfo 对象可以存储在对您的应用程序有意义的任何数据结构中。
回答by quux00
If your JDBC ResultSet is scrollable(introduced in JDBC v2 I think), you can reuse the ResultSet itself by calling ResultSet#beforeFirst, rather than having to copy the data out of it. Example:
如果您的 JDBC ResultSet 是可滚动的（我认为是在 JDBC v2 中引入的），您可以通过调用 来重用 ResultSet 本身ResultSet#beforeFirst，而不必从中复制数据。例子：
Statement stmt = dbconx.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
// do stuff
}
// when finished you can reuse the ResultSet by calling beforeFirst()
rs.beforeFirst();
while (rs.next()) {
// do more stuff
}
rs.beforeFirst();
// etc.
I have gotten this to work with the MySQL JDBC driver. I tried it on the PostgreSQL driver and that one does not support Scrollable ResultSets, so if it's not supported by your driver, you'll need to cache the results in some Collection as the other answers here describe.
我已经让它与 MySQL JDBC 驱动程序一起使用。我在 PostgreSQL 驱动程序上尝试过它，但该驱动程序不支持 Scrollable ResultSets，因此如果您的驱动程序不支持它，则您需要将结果缓存在某些集合中，如此处的其他答案所述。
If it is scrollable, you can also look at the absoluteand relativemethods in the link above to jump to arbitrary points in the ResultSet, which may be useful in some scenarios.
如果是可滚动的，还可以查看上面链接中的absolute和relative方法，跳转到ResultSet中的任意点，在某些场景下可能会有用。
Update:As pointed out by Will in the comment below - you may need to additional params in your createStatementto make the ResultSet scrollable. I've added that as the first line of code above.
更新：正如 Will 在下面的评论中指出的那样 - 您可能需要额外的参数createStatement才能使 ResultSet 可滚动。我已将其添加为上面的第一行代码。
回答by Quinma
Your best option is to do exactly what you said in saving it to a multi-dimensional list.
您最好的选择是完全按照您所说的将其保存到多维列表中。
It is a pretty basic function:
这是一个非常基本的功能：
Data member:
数据成员：
List<List> multiDimensionalList = new ArrayList();
Function:
功能：
while(rs.next()){
//create temporary list
List tempList = new ArrayList();
//add results to temporary list
tempList.add(rs.getInt("ad_id"));
...
//add temporary list to your multidimensionallist
multiDimensionalList.add(tempList);
}
