Method for SELECT in java: what to return and how to close resources?

Question

I am new to Java. I'm trying to create a class containing some utility methods for SQL operations in Java 1.6 to be used for general purposes.

I have written down a selectMethod for getting the results of a SELECT on the DB.

Problem: if my selectMethod method returns a ResultSet type, then when I call the method its related resources (ResultSet, Statement) will unavoidably remain open: I cannot close them from another method because they have been created into the selectMethod... on the other hand I cannot close them inside the selectMethod, otherwise the latter wouldn't return anything.

So my point is: ==> How can I close the resources? <==

I cannot use the try-with-resource because I'm using an earlier version of Java.

Among similar questions I haven't found a "general way" to overcome this issue.

Solutions: The only two ways I know at the moment:

A) avoid creating a selectMethod that returns a ResultSet type, and only create a method in which the query is performed internally, together with other operations on the query results. Then close all the resources into the method.

Example:

public String selectMethod(String query, Connection conn) {
    Statement stmt = null;
    ResultSet rset = null;
    String myOutput = "";
    try {
        stmt = conn.PreparedStatement(query);
        rset = st.executeQuery();
        myOutput = rs.getString(2);   // the "particular task" I perform on the data retrieved
    } catch (SQLException e) {
        System.out.println(e);
    } finally {
        rset.close();
        stmt.close();
    }
    return myOutput;
}
...
...
// method call:
String myQuery = "SELECT colA FROM table_name WHERE table_id = 192837465";
String theDataINeeded = selectMethod(myQuery, myConn);
myConn.close();

Drawbacks of A): I wanted a SQL class of general use and not limited to a particular task...

B) into the selectMethod, copying the ResultSet data into a CachedRowSet and return the CachedRowSet.

Example:

public CachedRowSet selectMethod(String query, Connection conn) {
    Statement stmt = null;
    ResultSet rset = null;
    CachedRowSetImpl crset = null;
    try {
        stmt = conn.PreparedStatement(query);
        rset = st.executeQuery();
        crset = new CachedRowSetImpl();
        crset.populate(rset);
    } catch (SQLException e) {
        System.out.println(e);
    } finally {
        rset.close();
        stmt.close();
    }
    return crset;
}
...
...
// method call:
String myQuery = "SELECT colA FROM table_name WHERE table_id = 192837465";
CachedRowSetImpl theDataINeeded = new CachedRowSetImpl();
theDataINeeded = selectMethod(myQuery, myConn);
myConn.close();

Drawabacks of B): I am afraid of running out of memory when doing select with many rows. I cannot make a query with pagination with LIMIT... OFFSET... because my DB version is below Oracle 12g, and I don't want to make query manipulations to insert row_number() between ... and .... I'd like my utility to work with any kind of query.

Does anyone know other solutions?

Thanks in advance.

Answer 1

Another option would be providing result mapper to the method like below;

public interface ResultMapper<T> {

    /**
     * Extract row from the columns of the {@link ResultSet}.
     * Implementors should just get the values for the columns and not call {@link ResultSet#next()} or {@link ResultSet#close()}
     *
     * @param rs the rs
     * @return the t
     */
    T extractRow(ResultSet rs);
}

//
// see ResultMapper
// Be aware that this method returns list of type <T>
public <T> List<T> selectMethod(final String query, final Connection conn, final ResultMapper<T> resultMapper) {
        final List<T> results = new LinkedList<>();

        Statement stmt = null;
        ResultSet rset = null;
        try {

            stmt = conn.createStatement();
            rset = stmt.executeQuery(query);
            while (rset.next()) {
                results.add(resultMapper.extractRow(rset));
            }
            return results;
        } catch (final SQLException e) {
            // handle sql exceprion
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException throwables) {
                // log error while closing
            }
        }
        
        return results;
    }

Since you are new to Java, you may need to take a look at java generics and

So, based on the example you provided, we want to extract String field. You can define your result mapper like this:

public class MyResultMapper implements ResultMapper<String> {
    @Override
    public String extractRow(final ResultSet rs) {
        return rs.getString(2);   // the "particular task" I perform on the data retrieved
    }
}

Then you can just execute query like this:

String output = SqlUtils.selectMethod(sql, conn, new MyResultMapper()).stream()
                .findFirst();

Answer 2

What about creating a Result object that implements AutoClosable (or Closable – I don't remember when these were introduced to Java)?

The Statement and ResultSet objects are attributes to that Result instance, and your selectMethod() is just the factory for it. Then you can do it like this:

Connection connection = …
Result result = null;
String query = "…";
try
{
     result = selectMethod( query, connection );
     ResultSet resultSet = result.getResultSet();
     myOutput = resultSet.getString(2);   // the "particular task" I perform on the data retrieved
}
catch( …Exception e )
{
    // Handle the exception
    …
}
finally
{
    if( result != null ) result.close();
}

The class Result will roughly look like this:

public final class Result implements Closeable
{
    private final Statement m_Statement;
    private final ResultSet m_ResultSet;

    public Result( final Statement statement, final ResultSet resultSet )
    {
        m_Statement = statement;
        m_ResultSet = resultSet;
    }

    public final ResultSet getResultSet() { return m_ResultSet; }

    @Override
    public final void close() throws SQLException
    {
        m_ResultSet.close();
        m_Statement.close();
    }
}

Of course, my error handling is poor and needs improvement …

connection is not yours, and you may not close it …

And resultSet (that one inside the try-catch-finally block) is just a copy to the reference that is hold by m_ResultSet, inside the Result instance. Therefore, a call to resultSet.close() is redundant (or obsolete – or even dangerous with my poor error handling).

And your selectMethod() looks like this:

public final Result selectMethod( String query, Connection connection ) throws SQLException
{
    Statement statement = connection.PreparedStatement( query );
    ResultSet resultSet = statement.executeQuery();
    return new Result( statement, resultSet );
}

Answer 3

Basing on the answer of tquadrat, I find another way to close the resources by just defining attributes in the class SQLUtil:

public class SQLUtil {
    
    //attributes:
    Connection connection;
    ResultSet resultSet;
    Statement statement;

    public void connectToMyDB {
        // populate the connection attribute
        ...
    }
    
    public ResultSet selectMethod (String query) {
        // here I populate the statement and resultSet attribute
        ...
        return resultSet;
    }
    
    ...
    public void cleanUpRes () {
        if (resultSet != null) resultSet.close();
        if (statement != null) resultSet.close();
    }
}

Then, when I will call the selectMethod I will get the resultSet without storing it in memory. After processing it, I will close it by calling cleanUpRes().

Drawbacks I see:

1 - there is only one resultSet related to the SQLUtil object, so if I would have to handle two or more queries together, I'd have to instantiate many different SQLUtil objects... Maybe using an array of resultSet attributes could work instead of only one? Btw this is beyond the scope of the question :)

2 - The external method is responsible for closing the resources

What do you think? Thanks everybody