Java – Can’t use ResultSet after connection close

by

JDBC doesn’t bring back all the results of a query in a ResultSet, because there may be too many of them to fetch them all eagerly. Instead it gives you something you can use to retrieve the results, but which goes away when the connection closes. So when you pass it back from your method after closing the database connection, nothing else can use it.

What you can do instead is to have this method use the resultSet to populate an object or a collection of objects, and pass that populated object back.

If you change your code to pass in a rowMapper (that takes a resultSet and passes back an object populated with the current row in the resultset), and use that to populate a container object that you pass back, then you’ll have something as reusable as what what you’ve written, but which actually works because it doesn’t depend on having the connection held open after the call is finished.

Here’s your example code rewritten to use the rowmapper, get rid of some unnecessary exception-catching, and fix a bug that will prevent the connection from getting closed 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 don’t catch each exception thrown on close individually, as shown above, you risk failing to close the connection if either the statement or the resultSet throw an exception on close.

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

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

The next step is going to be parameterizing your queries so you don’t have to
surround parameter values in quotes or worry about sql injection. See this answer for an example of how spring-jdbc handles this. The long term answer here is, it would be better to adopt spring-jdbc or something similar to it than to reinvent it piecemeal.

More Related Contents:

  1. Hours and seconds are lost when storing a date in the database [closed]
  2. Simulate Result set closing failed [closed]
  3. Multiple queries executed in java in single statement
  4. Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
  5. Using setDate in PreparedStatement
  6. Efficient way to do batch INSERTS with JDBC
  7. How to execute IN() SQL queries with Spring’s JDBCTemplate effectively?
  8. How do I get the size of a java.sql.ResultSet?
  9. How can I get the SQL of a PreparedStatement?
  10. What is the best approach using JDBC for parameterizing an IN clause? [duplicate]
  11. How does a PreparedStatement avoid or prevent SQL injection?
  12. Variable column names using prepared statements
  13. MySQL and JDBC with rewriteBatchedStatements=true
  14. java.sql.SQLException: Exhausted Resultset
  15. How to enable logging for SQL statements when using JDBC
  16. Getting output from dbms_output.get_lines using JDBC
  17. handling DATETIME values 0000-00-00 00:00:00 in JDBC
  18. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  19. JDBC ResultSet get columns with table alias
  20. Timezones in SQL DATE vs java.sql.Date
  21. Queries returning multiple result sets
  22. Getting an exception ORA-00942: table or view does not exist – when inserting into an existing table
  23. dll missing in JDBC
  24. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  25. Inserting null to an Integer column using JDBC
  26. How to use prepared statement for select query in Java?
  27. MS SQL Exception: Incorrect syntax near ‘@P0’
  28. MyBatis executing multiple sql statements in one go, is that possible?
  29. Database independence through JDBC in java
  30. Searching between dates in SQL with JDBC?

Leave a Comment