CODEWITHSUNDEEP
javaresultset
Willian Fuks
Willian Fuks

Reputation: 11787

How to save the resultSet from SQL Query in order to be used several times?

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:

"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...

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

In python all we had to do is something like:

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)

Thanks for any help

Upvotes: 1

Views: 20639

Answers (3)

quux00
quux00

Reputation: 14604

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:

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.

If it is scrollable, you can also look at the absolute and relative methods in the link above to jump to arbitrary points in the ResultSet, which may be useful in some scenarios.

Update: As pointed out by Will in the comment below - you may need to additional params in your createStatement to make the ResultSet scrollable. I've added that as the first line of code above.

Upvotes: 2

sperumal
sperumal

Reputation: 1519

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.

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.

First you should encapsulate your data into Java Object, for example creating a class like this

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 ClickInfo objects and store it in an Array.

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.

Upvotes: 2

Quinma
Quinma

Reputation: 1476

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);
}

Upvotes: 1

Related Questions