how to run Resultset.next() twice?

Question

I have this method to get a string of rows and print them.

Additionally, I have to do while(Resultset.next()) two times. The first one is to get the number of rows and the second one is to print the string. But when the method runs the first time Resultset.next() the method skips the second Resultset.next().

public static String[] gett() throws ClassNotFoundException, SQLException{

    // this for get conneced to the database .......................

    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","hr","111"); 
    Statement st = conn.createStatement();
    ResultSet re = st.executeQuery("select location_id from DEPARTMENTS");

    // Ok , now i have the ResultSet ...

    // the num_row it's counter to get number of rows
    int num_row = 0;

    // this Arrar to store String values
    String[] n = new String[num_row];

    // this is the first ResultSet.next , and it's work ..!
    // also , this ResultSet.next work to get number on rows and store the number on 'num_row' 
    while(re.next())
        num_row++;

    // NOW , this is the secound 'ResultSet.next()' , and it's doesn't WORK !!!!
    while(re.next()) {
        System.out.println(re.getString("location_id"));
    }
}

The problem is, the first Resultset.next() works fine, but the second does not work!

Could someone explain why? And how can I make it work?

note: I know, there is another way to do it in just one Resultset.next() BUT I want to do it twice ;)

Answer 1

you could initialize your Statement as the following

conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

Due to this you can move the cursor around in the Statement.

Now you could either loop through it.

while(re.next())
    num_row++;
re.beforeFirst();

but this is quite unneccessary and the optimal solution would be to just jump to the end of the set and return the row

num_row = 0;
if(re.last()) {
   num_row = rs.getRow();
   re.beforeFirst();
}