It looks like you are closing the database connection inside your loop, before you are done with the result set.
You can't close the database connection if you are still using statements or resultsets associated with the database connection. (Well, you can do it, but closing the connection will also close the resultset, and you get the kind of behavior you observe.)
The normative pattern is to use try/catch/finally blocks, and to close the resultset(s), statements(s) and connection in the finally block(s).
For example:
ConnectionDTB cdtb = null;
ResultSet rs = null;
try {
cdtb = new ConnectionDTB();
rs = cdtb.retrieveData("select svcode from listSV");
while(rs.next()){
// whatever processing you need to do on each row, but
// do NOT close the result set here
// do NOT close the database connection here!
}
} catch (SQLException ex) {
// anything you want to do if an exception is thrown
} finally {
if (rs != null) {
try { rs.close(); } catch (SQLException e) { /*ignore*/ }
}
if (cdtb != null) {
try { cdtb.close(); } catch (SQLException e) { /*ignore*/ }
}
}
That finally block could be simplified, removing the unnecessary try/catch. This would be fine too:
} finally {
if (rs != null) {
rs.close();
}
if (cdtb != null) {
cdtb.close();
}
}
The important thing here is to do the closing in the "finally" block, so that's going to be run even if an exception occurs. And with this pattern, there is one "close" of the database connection, it's not multiple calls scattered through your code.
Some other notes:
It looks as if your code means to "add" a row if one doesn't already exist. This is a lot of overhead, pulling back every value from the database and inspecting it.
It would be much more efficient to ask the database if such a row exists. To ask the database whether a row like that exists, use a statement like this:
SELECT svcode FROM listSV WHERE svcode = ?
Prepare the statement, bind a value to the placeholder (the value you are looking for), and execute the statement, and check if a row is returned or not.
If you are performing this check to see whether a row needs to be added to the listSV table, you could actually use a single INSERT ... SELECT statement to conditionally insert a row.
