Use a ResultSet twice

[Brennen smith]

I want to use a ResultSet twice. The problem is that after I loop through the result set "while (resultSet.next())" I am not a able to loop through it again. I was looking at the stored values of the ResultSet while debugging. Before I loop through positionFromFirst_ =0 and positionFromFirst_ = -1 after I run through positionFromFirst_ = 2 and positionFromFirst_ = 0. Is there a way to reset these values so that I can loop though again. I believe this is my issue...
Thanks in advance,

[Paul Clapham]

Why do you want to use the ResultSet twice?

[Koen Aerts]

Did you try the beforeFirst() method on the ResultSet instance?

[Brennen smith]

Ok, so I am querying a databse and returning 15 columns of data. I want to be able to display the first 5 in a JTable but need to retain access to the rest of the data. So before I create my DefaultTableModel which only contains only the 5 collumns that go in my Jtable. I was trying to save my resultset to a String[][]. I am sure there is an easier way, this was the best I could come up with.

DatabaseConnection db = new DatabaseConnection(); //Connects to database with embeded username and password
ResultSet resultSet = db.selectQuery(bquery); // prepquery() submits a Statement that queries database returns ResultSet for jt_result
String[][] allData = db.data(resultSet); //<<<<<<<<<<Trying to save as a string
dtm_search_model = new MyDefaultTableModel(
db.data(resultSet), //<<<<<<<<<<<This Fails
db.headings(resultSet));
return dtm_search_model;

public static Object[][] data(ResultSet resultSet){
List<Object[]> data = new ArrayList<Object[]>();
try {
int numofcols = resultSet.getMetaData().getColumnCount();
while (resultSet.next()) {
Object[] rowData = new Object[numofcols];
for (int i = 0; i < rowData.length; ++i) {
rowData[i] = resultSet.getObject(i+1);
}
data.add(rowData);
}
int the_size = data.size();
Object[][] array = new Object[the_size][data.get(0).length];
for(int i = 0; i < the_size; i++)
{
array[i] = data.get(i);
}
return array;
}
catch(Exception e)
{
throw new RuntimeException(e);
}
}//End data

[Brennen smith]

@Koen Yes I tried that but get an error:

Java.sql.SQLException: Cursor state not valid.

[Paul Clapham]

That would work if you didn't have a forward-only data set. But usually when beginners want to use a ResultSet twice, that's based on incomplete knowledge. Often they want to put the rows into an array (because they don't know about anything better) so they decide to read the thing once just to count the rows and then go through it again to put the rows into the array which they sized based on the count.

[Paul Clapham]

 

Brennen smith wrote:I was trying to save my resultset to a String[][].

So, as I suspected. If I'm not mistaken you can pass a Vector of Vectors to a table model; using a Vector doesn't require you to know the number of rows in advance.

[Koen Aerts]

Create a Statement like this:

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

Then use the executeQuery method on the Statement instance.

[Brennen smith]

I am a little confused with your exact answer...

So, set my result set to a vector and pass the vector into the TableModel?

vector v = db.data(resultSet); //<<<<<<<<<< set to vector?
dtm_search_model = new MyDefaultTableModel(
db.data(v), //<<<<<<<<<< pass the vector into the table model?
db.headings(resultSet));

[Koen Aerts]

He's saying that you only need to iterate through your ResultSet once if you use a Vector to fill it up with the rows. For instance (I haven't tested this):

ResultSet rs = db.selectQuery(bquery);
Vector<String[]> rows = new Vector<String[]>();
while (rs.next()) {
Vector<String> row = new Vector<String>();
row.addElement(rs.getString(1));
...
row.addElement(rs.getString(15));
String[] v = new String[row.size()];
v.copyInto(v);
rows.addElement(v);
}

After this piece of code, you have a Vector of String[] values; which in turn you can again copyInto a String[] array.

[Brennen smith]

Ok,I get it and will make it from here. I appreciate your help.