Make ResultSet as a result of a generic function QueryRunner in Java and JDBC

Question

I have a function QueryRunner that takes a query, runs it and returns the result.

public static ResultSet QueryGetter(String query) {
    Connection connection = null;
    ResultSet rslt = null;
    try {
        Class.forName(driverName);
        connection = DriverManager.getConnection(url + ":" + port + "/" + dbName, 
                                                 username, password);
        Statement statement = connection.createStatement();
        rslt = statement.executeQuery(query);
    } catch (SQLException e) {
        System.out.println(e);
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(QueriesRunner.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            connection.close();
        } catch (SQLException ex) {
            System.out.println(ex);
        }
    }
    return rslt;

However, when I use it to run a query, I get "Operation not allowed after ResultSet closed". It is because I close the connection.

How can I modify the return signature of my function to make it work?

I tried using a List, but the thing is that I have to distinguish between int and String by using rslt.getString().

I don't want to do that. I want to return the content of the ResultSet regardless of the type of the object in the SQL table (int, date, String ...). I will then process the content later, in another function.

Answer 1

You can't access the ResultSet if you already closed the connection. So you need to pass your converter as parameter of the QueryGetter method.

You need to define a Converter interface (you can also define generics to have a better code)

public interface Converter {
    public Object from(ResultSet rs);
}

and change the QueryGetter method to get this Converter as a parameter and retrieving a List of objects instead of a ResultSet. Something like:

  // Pass the converter as parameter
   // I also changed the name of the method in lowercase that is better practice
   public static List<Object> queryGetter(String query, Converter converter) {
        // Creates the list
        List list = new ArrayList(); 
        Connection connection = null;
        ResultSet rslt = null;
        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url + ":" + port + "/" + dbName, 
                                                     username, password);
            Statement statement = connection.createStatement();
            rslt = statement.executeQuery(query);
            // Loop through the result set
            while (rslt.next()) {
                // Convert the current record to an object
                Object record = convert.from(rslt);
                // Add it to the list
                list.add(record);
            }
        } catch (SQLException e) {
            System.out.println(e);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(QueriesRunner.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                connection.close();
            } catch (SQLException ex) {
                System.out.println(ex);
            }
        }
        return list;   // Return a list
    }

Note that this is a very similar approach that has been used by spring jdbc using JdbcTemplate and NamedParameterJdbcTemplate classes, for example in the method query where Convert is named RowMapper in spring jdbc, but does pratically the same.

Query given SQL to create a prepared statement from SQL, mapping each row to a Java object via a RowMapper.

So my tip is: if this is not only for studying purpose don't reinvent the wheel and use existing code.