Java - cannot use ResultSet after closing the connection

Question

I have a problem closing my MySQL connection.

I get an error message:

java.sql.SQLException: operation not allowed after closing ResultSet

My code is:

public static ResultSet sqlquery (String query) { ResultSet rs=null; Connection connection=null; Statement st=null; try{ Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("databaseadress","username","password"); st = connection.createStatement(); rs = st.executeQuery(query); }catch(SQLException e){System.out.println("SQL error: " + e);} catch(Exception e){System.out.println("Error: " + e);} finally { try{ if(rs != null) rs.close(); if(st!= null) st.close(); if(connection != null) connection.close(); }catch(SQLException e){System.out.println("SQL error : " + e);} } return rs; } 

Answer 1

This is how JDBC works. In your code, you have closed ResultSet and Connection , after which ResultSet no longer used. If you want it to be used, you must leave it (and Connection ) open.

However, if you return a ResultSet , you must reorganize your code so that the calling method provides Connection .

Answer 2

JDBC does not return all query results to a ResultSet, because there may be too many of them to get all of them with impatience. Instead, it gives you something that you can use to get results, but that disappears when the connection closes. Therefore, when you return it from your method after closing the database connection, nothing else can use it.

Instead, you can use this method to populate an object or collection of objects and pass that populated object.

If you change your code to pass to rowMapper (which takes the result set and passes the object filled with the current line in the result set), and use this to populate the container object that you pass back, then you will have something reusable, like what you wrote, but which really works, because it does not depend on whether the connection was opened after the call ended.

Here is your sample code rewritten to use rowmapper, get rid of unnecessary exceptions and fix an error that will prevent the connection from closing in some cases:

 public static List<T> sqlquery (String query, RowMapper<T> rowMapper) throws SQLException { Connection connection=null; Statement st=null; ResultSet rs=null; // don't need Class.forName anymore with type4 driver connection = DriverManager.getConnection("databaseadress","username","password"); st = connection.createStatement(); rs = st.executeQuery(query); List<T> list = new ArrayList<T>(); while (rs.next()) { list.add(rowMapper.mapRow(rs)); } // don't let exception thrown on close of // statement or resultset prevent the // connection from getting closed if(rs != null) try {rs.close()} catch (SQLException e){log.info(e);} if(st!= null) try {st.close()} catch (SQLException e){log.info(e);} if(connection != null) try {connection.close()} catch (SQLException e){log.info(e);} return list; } 

If you do not catch each exception that is thrown at closing separately, as shown above, you risk not closing the connection if either the operator or resultSet throws an exception when closing.

This is similar to what spring-jdbc does, it defines RowMapper as:

 public interface RowMapper { T mapRow(ResultSet, int rowNum) throws SQLException; } 

The next step is to parameterize your queries so that you do not have to surround quotation marks in quotation marks or worry about SQL injection. See this answer for an example of how spring-jdbc works. The long-term answer here is that it would be better to adopt spring-jdbc than to invent it in parts.

Answer 3

Once the connection is closed, you can no longer use any resources (instructions, prepared statements, result sets), all of them are automatically closed. So you need to do all your processing while resources are open.

Try filling out and returning the DTO, so you can have the data you need without supporting the connection.