Returning ResultSet without close?

Question

I would like to have a database connection managing class which I can use for simple SQL commands like SELECT, INSERT etc. by simple calling something like this (class below):

ResultSet test = DataService.getResultSet("SELECT NOW()");
test.first();
System.out.println(test.getString(1));

This is class I've found on web:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Database object to load drivers and perform queries
 * @author Abdulsalam Umar blog.salamtura.com
 */
public class DataService {

    private static Connection con;
    private static final String Driver = "oracle.jdbc.driver.OracleDriver";
    private static final String ConnectionString = "Your database connection string";
    private static final String user = "username";
    private static final String pwd = "password";

    /**
     * create Database object
     */
    public DataService() {
    }

    /**
     * to load the database base driver
     * @return a database connection
     * @throws SQLException throws an exception if an error occurs
     */
    public static Connection loadDriver() throws SQLException {
        try {
            Class.forName(Driver);
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage());
        }
        con = DriverManager.getConnection(ConnectionString, user, pwd);
        return con;
    }

    /**
     * to get a result set of a query
     * @param query custom query
     * @return a result set of custom query
     * @throws SQLException throws an exception if an error occurs
     */
    public static ResultSet getResultSet(String query) throws SQLException {
        Connection con = loadDriver();
        ResultSet rs;
        PreparedStatement st = con.prepareStatement(query);
        rs = st.executeQuery();

        return rs;
    }

    /**
     * to run an update query such as update, delete
     * @param query custom query
     * @throws SQLException throws an exception if an error occurs
     */
    public static void runQuery(String query) throws SQLException {
        Connection con = loadDriver();
        ResultSet rs;
        PreparedStatement st = con.prepareStatement(query);
        st.executeUpdate();
    }
}

Is this way of returning ResultSet without closing it (and closing the statement) right? How can I return the ResultSet from the method?

Answer 1

Returning result set is not a good idea. So,fetch the required data and make use of collection to return the data. This answer may be useful

Answer 2

How about passing a callback that takes ResultSet as parameter and let client code do whatever needs to inside it while you make sure that everything is cleaned up afterwards.

This is pattern in used in spring JDBC ResultSetExtractor and RowMapper. Look at this answer.

Answer 3

You can't return ResultSet because it will be closed when method destroyed. But you can get raw data from ResultSet, try this:

public ArrayList<ArrayList<byte[]>> getResultQuery(String query){
    ArrayList<ArrayList<byte[]>> tableResult = new ArrayList<>();
    ArrayList<byte[]> row;
    conn = getConnection(db_url);
    try {
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(query);
        int countColumn = resultSet.getMetaData().getColumnCount();
        if (countColumn==0) return null;
        while (resultSet.next()){
            row = new ArrayList<>();
            for (int i = 0; i<countColumn; i++){
                row.add(i,resultSet.getBytes(i+1));
            }
            tableResult.add(row);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return tableResult;
}

public static Connection getConnection (String db_url){
    Connection conn = null;
    try{
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        conn = DriverManager.getConnection(db_url);
    }catch(Exception e){
        e.printStackTrace();
    }
    return conn;
}

In this, i try to return ArrayList of ArrayList<byte[]>, ArrayList<byte[]> = 1 row in ResultSet. If you want to get some value, just use row.get(i) to get value from column i+1 in ResultSet which look like a 2 dimensions Matrix