How to put a resultSet into an Array to use multiple times

I have a resultSet that I am pulling from my database:

      String selStmt = "SELECT code_id, internal_code, representation, pos, decode(substr(internal_code, 5, 1), 'Q', 2, 1) as sorter, "
                 + "       to_char(term_start, 'MM-DD-YYYY') as sDate "
                 + "  FROM table1, terms WHERE code_id = 'SEARCH.TERMS' AND internal_code = terms.terms_id (+) ORDER BY 5, 4 ";

  stmt = conn.prepareStatement(selStmt);
  result = stmt.executeQuery();

What I want to do now is put these results into an array, I know that I can use a while loop to loop and get the results, but I can only use those once, I need to continue to use them throug开发者_C百科hout the rest of the page. I figured this could be done using an array, but if there is an easier way, let me know, also if more information is needed, please let me know.

Since you do not know the number of rows beforehand I would actually opt for an ArrayList. You can then insert custom objects as you like (build from your resultset).

There is no DataSet (.NET) equivalent for Java that I know of. Hence, consider something like the following:

ArrayList<Integer> data = new ArrayList<Integer>();

ResultSet rs = ....;

// For each record in the result...    
while (rs.next()) {
  // Want more values? Create a custom Type representing the Row!
  // The following is just an example taking the first column (as an int).
  // This would be done manually for each column... and possibly loadeded
  // in a custom object which is then added to the list... blah blah.
  // (Alternatively each row could be represented as Object[] or List<Object>
  //  at the expense of losing static typing.)
  int id = rs.getInt(0);
  data.add(id);
}

// Then later, if you *really* want an array...
// (Java is such a backwards language and lacks a trivial way
//  to go to int[] from Integer[] but I digress...)
Integer[] array = data.toArray(new Integer[0]);

Happy coding

You could just store the data in a 2D array. But a more OO friendly way would be to create an object to represent what a row of data represents and create a List of these objects from the ResultSet.

To pull the data, just loop through the ResultSet like you have been then for each loop create a new instance of your custom object and add it to the list.

I once used the below code to solve a similar challenge:

Vector rows = new Vector();
Vector nrow;
int cnt = 0;

while(result.next())
{
    nrow=new Vector();
    cnt+=1;
    nrow.addElement(String.valueOf(cnt));
    for(int i=1;i<=3;i++) //replace 3 with the length of the columns
    {
        nrow.addElement(result.getObject(i));
    }
    rows.addElement(nrow);
}

Now you can loop through rows and make use of the data as you like. Each object returned on each iteration contains an entire row data.

The standard way is to loop over the ResultSet once and (possibly) store each record in an array -which you can later loop over and over as you like. That's what most answers here suggest, and that's the correct way.

The relevant point is this: you'd (apparently) like to keep the "raw" records that a ResultSet returns in each next() call (so that you can store each record on an ArrayList and treat it as a scrollable ResultSet or sort of). I think can't be done, and I strongly believe that, in most cases, that's a bad idea. The ResultSet belongs to the JDBC layer, which is a rather low-level layer, and which has its problems and pitfalls. In general, the ResultSet should be consummed and closed as quickly as you can, and should not leak to upper layers. By "consummed" I mean to loop it over and build for each curson position your own Java objects (using the rs.getXXX() methods) not related to the JDBC api. That's what most people and frameworks (eg iBatis) do, and, in most cases, doing otherwise is bad practice.

For example, I've once seen some pseudo-dao implemented as (pseudo code) :

   public ResultSet getUsers() { 
      conn = openConnection();
      stmt = conn.prepareStatement(...);
      result = stmt.executeQuery();
      stmt.close();
      conn.close();
      return result;
   }

and the caller would then loop over the resultset. This is horrid, can fail, as the resultset may try to get the data from the db (but the connection has been closed!).

If you really want to do something like this, you should look into the RowSet class. But I don't think it's much used.

An array is probably the way to do it. Just initialize it before your while-loop, fill it during your while-loop, and use it afterwards :)

Another possibility is to use a HashMap to store your rows if you need to constantly reference them throughout the page. For the index use the primary key from the db.

This way you can quickly reference specific rows as needed. If you just need to loop over the result set every time than an arraylist as has been said should work pretty well here.