Tri Nguyen Tri Nguyen - 24 days ago 6
Java Question

Java,MySQL - Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed

I'm trying to get the first element of a

ResultSet
. Why am I getting this error

public ResultSet executeSearch(String q){
openConnection();
ResultSet resultSet = null;
try{
Statement statement = connection.createStatement();
resultSet = statement.executeQuery(q);
closeConnection();
return resultSet;
}
catch (Exception e){
JOptionPane.showMessageDialog(null, e.getMessage());
}
finally {
closeConnection();
}
return resultSet;
}

public int getAddressID(String city) throws SQLException{
String q = "SELECT PK_ADDRESS_ID FROM tbl_addresses WHERE city =" + "\'"+ city + "\';";
System.out.println(q);
ResultSet rs = executeSearch(q);
int i = 0;
if (rs.next()){
i = rs.getInt(1);
};
return i;
}

Answer

You get this error because you try to read your ResultSet after closing it which is forbidden.

You need to close your connection (and your ResultSet) in your method getAddressID instead, as next:

public ResultSet executeSearch(String q){
    openConnection();
    try {
        return connection.createStatement().executeQuery(q);
    } catch (Exception e){
        JOptionPane.showMessageDialog(null, e.getMessage());
    }
    return null;
}

public int getAddressID(String city) throws SQLException{
    ...
    try (ResultSet rs = executeSearch(q)) {
        ...
    } finally {
         closeConnection();
    }
    return i;
}