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.
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.
Illegal operation on empty result set when calling next() on a freshly acquired resultset. So your problem lies somewhere else. Either you're accessing the wrong resultset or you're misinterpreting the exception and/or the code line where it's been caused.
getSt (I suggest using full words) returns the same Statement then it isn't a leak. My JDBC is very rusty, are you allowed to carry on using an iterator whilst executing another statement on the Statement (bad analogy: mutating a collection whilst iterating over it)? Anyway, use a PreparedStatement rather than dynamic SQL. Edit: Second line of Statement API docs: "By default, only one ResultSet object per Statement object can be open at the same time. "Statement correctly somehow when it is finished with its tasks. I honestly also don't see proper ways to do it in the given code. Some nasty conditional checks in the getInstance() call maybe? No, I wouldn't do it. As per your edit: that would have thrown a different SQLException on next() and then only when another ResultSet is been acquired afterwards.while(rs.next()) {
// iterate
}
Here's how you should typically iterate through a ResultSet in Java:
try
{
while (rs.next())
{
int id = rs.getInt("person_id");
//Perform your operations here, such as inserting data into the database
}
} catch (SQLException e)
{
// Handle any SQLException that might occur during iteration
}
Always check if the ResultSet has rows using rs.next() before attempting to retrieve data.
Ensure proper error handling by catching and handling any SQLException that might occur during the iteration or when closing resources.
