aragonthebest aragonthebest - 2 months ago 6
SQL Question

Java: SQL Query: rs.next() is false after updating a column

I have a strange problem. I have a database and I want to change the values of a column. The values are safed in an Arraylist (timelist).

In order to write the values in the right row, I have a second Arrylist (namelist). So I want to read the first row in my Database, than I check the namelist and find the name. Than i take the matching value out of the timelist and write it into the database into the column "follows_date" in the row, matching to the name.

And than I read the next row of the Database, until there are no more entries.

So the strange thing is, if I change nothing in the database, the

while(rs.next())
part works.

For example:

ResultSet rs = statement.executeQuery("SELECT username FROM users");
while(rs.next()){

// read the result set
String name = rs.getString("username");
System.out.println("username = " + name); //liest die namen
}
}


This would print me every name after name. But when I change the table, the while loop ends after that. (no error, the program just finishes)

ResultSet rs = statement.executeQuery("SELECT username FROM users");
while(rs.next()){

// read the result set
String name = rs.getString("username");
System.out.println("username = " + name); //writes the name

//look, if name is in Arraylist "namelist"). if yes, than write the matching date from "timelist" into the database.
if (namelist.contains(name)){
System.out.println("name found: "+ name);
int listIndizi = namelist.indexOf(name); //get index
Long indiziDatum = (long) timelist.get(listIndizi); //get date from same Index
System.out.println(indiziDatum); // print date so i can see it is correct (which it is)

statement.executeUpdate("UPDATE users SET follows_date ="+ indiziDatum +" WHERE username = '"+name+"'"); //updates the follows_date column

}
}


Everything works fine, except that now, the while loop doesn't continues after the first passage, but ends.

Answer

The resultSet of a statement is closed and will not return further results if you execute another statement. Create a new separate statement object for the update and everything should work as excepted.

Statement statement1 = connection.createStatement();
Statement statement2 = connection.createStatement();

ResultSet resultSet1 = statement1.executeQuery("SELECT username FROM users");
while(resultSet1.next()){
...
    statement2.executeUpdate("UPDATE users ..."));
}
Comments