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.
