Retrieve data from ResultSet before closing the Connections

Question

I've created a method to fetch details from Database which returns an object of ResultSet by executing a callableStatement. I want to close the Connection & CallableStatement. For performing this I'm using try-with-resource. But the problem arise here, as I'm return the ResultSet from my method, it also gets closed and throws an exception while fetching data from it:

com.microsoft.sqlserver.jdbc.SQLServerException: The result set is closed.

As per my knowledge, ResultSet will automatically get closed when either all data is fetched from ResultSet or if the CallableStatement will get closed. In my case second thing is happening as I'm using try-with-resource.

This is how my code looks like.

public ResultSet getUSStates() throws SQLException {
    try(
            Connection inGlobalConnnections = getConnection();
            CallableStatement csmt = inGlobalConnnections.prepareCall("{call mst.USP_GetUSStates()}");) {

        csmt.execute();
        return csmt.getResultSet();
    } catch (Exception e) {
        _log.error(e, e);
    }

    return null;
}

What should I do to fetch records and close the connections afterwards?

NOTE: I'm using multiple methods like this to fetch details from database. So, please suggest way where I have to perform minimal changes in my code.

Answer 1

As the comments suggest, a ResultSet should generally be processed immediately, not handed off.

I suggest either:

• Transform the ResultSet into a RowSet.
• Process the database rows into a collection of Java objects. Often a good time for Java records.

javax.sql.RowSet

Instead, use the sub-interface of ResultSet that is meant for handing off data directly from the database: RowSet. In your case, CachedRowSet sounds appropriate.

See tutorial by Oracle. And see article by Michael Good on Baeldung.

The catch is finding a concrete implementation of that interface. You may find one bundled with JDKs based on OpenJDK. But as I recall, that implementation is very old (like dating back to the Sun Microsystems days), and has not been maintained/updated.

Row sets always seemed like a great idea to me. But apparently they have never received the attention they deserve.

For more on obtaining implementations of RowSet, see these Questions: here and here.

Here is a full example app, using H2 Database Engine.

Notice how, in modern Java, RowSetProvider uses the Java Service Provider Interface (SPI) to locate and load an implementation at runtime. (Well, I think that is what’s going on here.) See: What the reason to use RowSetProvider to obtain RowSetFactory?.

package work.basil.example;

import org.h2.jdbcx.JdbcDataSource;

import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
import java.sql.*;
import java.time.Instant;
import java.util.List;
import java.util.UUID;

/**
 * Example of using a disconnected RowSet, specifically a CachedRowSet,
 * to keep a ResultSet even after disconnecting from database.
 */
public class App
{
    public static void main ( String[] args )
    {
        System.out.println ( "Hello World! " + Instant.now ( ) );
        App app = new App ( );
        app.demo ( );
    }

    private void demo ( )
    {
        DataSource dataSource = this.getDataSource ( );
        this.createDatabase ( dataSource );
        this.populateTable ( dataSource );
        this.dumpTable ( dataSource );
        CachedRowSet rowSet = this.fetchRowSet ( dataSource );
        this.reportRowSet ( rowSet );
    }

    private DataSource getDataSource ( )
    {
        org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource ( ); // The `javax.sql.DataSource` interface implemented by `org.h2.jdbcx.JdbcDataSource`.
        ds.setURL ( "jdbc:h2:mem:" + "RowSetExDb" + ";DB_CLOSE_DELAY=-1" );  // Set delay to -1 to keep in-memory database even after last connection closed.
        ds.setUser ( "scott" );
        ds.setPassword ( "tiger" );
        ds.setDescription ( "Dummy database for demo showing how to use a RowSet after closing connection." );
        return ds; // Generalizing from the concrete class to the interface.
    }

    private void createDatabase ( final DataSource dataSource )
    {
        String sql = """
                CREATE TABLE person_ ( 
                    pkey_ UUID DEFAULT random_uuid() PRIMARY KEY , 
                    name_ VARCHAR NOT NULL 
                ) ;
                """;
        try (
                Connection conn = dataSource.getConnection ( ) ;
                Statement stmt = conn.createStatement ( ) ;
        )
        {
            stmt.execute ( sql );
        }
        catch ( SQLException e ) { throw new RuntimeException ( e ); }
    }

    private void populateTable ( final DataSource dataSource )
    {
        String sql = """
                INSERT INTO person_ ( name_ ) 
                VALUES ( ? ) 
                ;
                """;

        List < String > names = List.of ( "Alice" , "Bob" , "Carol" , "Davis" ); // Insert a row for each of these names.
        System.out.println ( "Inserting list of names: " + names );
        try (
                Connection conn = dataSource.getConnection ( ) ;
                PreparedStatement ps = conn.prepareStatement ( sql ) ;
        )
        {
            for ( String name : names )
            {
                ps.setString ( 1 , name );
                ps.executeUpdate ( );
            }
        }
        catch ( SQLException e ) { throw new RuntimeException ( e ); }
    }

    private void dumpTable ( final DataSource dataSource )
    {
        // Retrieve rows from a `ResultSet`.
        String sql = """
                SELECT * 
                FROM person_ 
                ;
                """;
        try (
                Connection conn = dataSource.getConnection ( ) ;
        )
        {
            try (
                    Statement stmt = conn.createStatement ( ) ;
                    ResultSet rs = stmt.executeQuery ( sql ) ;
            )
            {
                record Person( UUID id ,
                               String name )
                {
                }
                while ( rs.next ( ) )
                {
                    UUID pkey = rs.getObject ( "pkey_" , UUID.class );
                    String name = rs.getString ( "name_" );
                    System.out.println ( new Person ( pkey , name ) );
                }
            }
        }
        catch ( SQLException e ) { throw new RuntimeException ( e ); }
    }

    private CachedRowSet fetchRowSet ( final DataSource dataSource )
    {
        // Retrieve rows from a `ResultSet`.
        String sql =
                """
                        SELECT * 
                        FROM person_ 
                        ;
                        """;
        try (
                Connection conn = dataSource.getConnection ( ) ;
                Statement stmt = conn.createStatement ( ) ;
                ResultSet rs = stmt.executeQuery ( sql ) ;
        )
        {
            CachedRowSet crs = RowSetProvider.newFactory ( ).createCachedRowSet ( );
            crs.populate ( rs );
            return crs;
        }
        catch ( SQLException e ) { throw new RuntimeException ( e ); }
    }

    private void reportRowSet ( final CachedRowSet cachedRowSet )
    {
        try
        {
            System.out.println ( "*****|  CachedRowSet without Connection  |**************" );
            while ( cachedRowSet.next ( ) )
            {
                String pkey = cachedRowSet.getString ( "pkey_" );  // Using `String` rather than `UUID` as the default implementation of CachedRowSet does not support accessing as UUID object.
                String name = cachedRowSet.getString ( "name_" );

                String message = "pkey = " + pkey + " | name = " + name;
                System.out.println ( message );
            }
            System.out.println ( "**************************************************" );
        }
        catch ( SQLException e ) { throw new RuntimeException ( e ); }
    }
}

When run:

Hello World! 2023-08-29T22:47:30.159431Z
Inserting list of names: [Alice, Bob, Carol, Davis]
Person[id=241a0ba7-6763-4576-8e94-2c39770fe9e5, name=Alice]
Person[id=8a658d90-a834-410c-b94b-60f9629cbc57, name=Bob]
Person[id=b8736f53-3e9c-4cdf-bd1c-67c8c6ae72ab, name=Carol]
Person[id=93c7ca9f-1db1-408f-a0fd-d4b7f2dbe2f4, name=Davis]
*****|  CachedRowSet without Connection  |**************
pkey = 241a0ba7-6763-4576-8e94-2c39770fe9e5 | name = Alice
pkey = 8a658d90-a834-410c-b94b-60f9629cbc57 | name = Bob
pkey = b8736f53-3e9c-4cdf-bd1c-67c8c6ae72ab | name = Carol
pkey = 93c7ca9f-1db1-408f-a0fd-d4b7f2dbe2f4 | name = Davis
**************************************************

record

Or cache the data yourself by writing a record class, populate a collection of those objects from your ResultSet, and hand off the collection.