Problem with SQL, ResultSet in java

Question

How can I iterate ResultSet ? I've tried with the following code, but i get the error java.sql.SQLException: Illegal operation on empty result set.

 while ( !rs.isLast()) {
     rs.next();
     int id = rs.getInt("person_id");
     SQL.getInstance().getSt().execute("INSERT ref_person_pub(person_id) VALUES(" + id + ")");
}

Update: I've found the problem. I have used only one statement from the SQL singleton. When the statement is closed it can't be used again.

Answer 1

As per the JDBC tutorial:

resultSet = statement.executeQuery();
while (resultSet.next()) { 
    int id = resultSet.getInt("id");
    // ...
}

The ResultSet#next() moves the cursor forward one row from its current position and returns true if the new current row is valid. Thus, the while loop will stop automatically when there are no more rows.

If it is supposed to return zero or one row instead of multiple rows, then rather use if instead:

resultSet = statement.executeQuery();
if (resultSet.next()) { 
    int id = resultSet.getInt("id");
    // ...
}

This way you have the opportunity to add an else.

Update, that said and unrelated to the actual problem, I see more potential problems in your code: first, you seem to fire multiple queries which are dependent on each other. This can be done more efficient. Are you familiar with SQL Joins? Second, aren't you leaking JDBC resources? It look like that you're acquiring a statement, but not getting a handle of it so that you can properly close it after use. Please consult the before linked JDBC tutorial for a basic explanation how to work properly with JDBC code and this article for several basic kickoff examples how to use JDBC properly. Otherwise your application may crash sooner or later when the DB runs out of resources.

Answer 2

while(rs.next()) {
   // iterate
}