How can i return ResultSet from a method while closing the connection and preparedStatement? when i am closing connection and prepared statement, it is giving an exception that 'the connection is closed'. can i skip the closing part in this case?
CodePudding user response：
RowSet
If you really want to return a ResultSet after closing a connection, use its sub-interface, RowSet.
More specifically, you will want one of the disconnected RowSet types. These include:
… as described here.
For more info, do a Web search. And see The Java Tutorials by Oracle Corp free-of-cost, Using RowSet Objects. And search Stack Overflow to learn more.
To get a RowSet object, you will need an implementation of the interfaces. In modern Java, the RowSetProvider and RowSetFactory classes are used to automatically locate the presence of an implementation. Discussed on this Question.
A JDK based on the OpenJDK codebase will likely come bundled with the open-source com.sun.rowset.CachedRowSetImpl implementation of CachedRowSet used in example code below.
Caveat: The RowSet feature in Java seems to have gone largely ignored by much of the Java community, for reasons I cannot fathom. I suspect it came too late, after many folks moved onto other paradigms, and ORMs, and such. But a RowSet does provide precisely what you asked for: A ResulSet that works after closing the Connection.
Example code
Here is a complete example app of a CachedRowSet containing data after disconnecting from database.
This code uses the H2 Database Engine. The created database is in-memory, no file storage, so it is ephemeral.
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 ( )
{
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 )
{
try (
Connection conn = dataSource.getConnection() ;
)
{
try ( Statement stmt = conn.createStatement() ; )
{
String sql =
"""
CREATE TABLE person_ (
pkey_ UUID DEFAULT random_uuid() PRIMARY KEY ,
name_ VARCHAR NOT NULL
) ;
""";
stmt.execute( sql );
}
}
catch ( SQLException e )
{
throw new RuntimeException( e );
}
}
private void populateTable ( final DataSource dataSource )
{
try (
Connection conn = dataSource.getConnection() ;
)
{
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 (
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 )
{
try (
Connection conn = dataSource.getConnection() ;
)
{
// Retrieve rows from a `ResultSet`.
String sql =
"""
SELECT *
FROM person_
;
""";
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 )
{
try (
Connection conn = dataSource.getConnection() ;
)
{
// Retrieve rows from a `ResultSet`.
String sql =
"""
SELECT *
FROM person_
;
""";
try (
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! 2022-08-07T23:26:43.721898Z
Inserting list of names: [Alice, Bob, Carol, Davis]
Person[id=8c5329fd-5abb-4a65-ad41-5e32914b166b, name=Alice]
Person[id=6ed97068-ed14-4be4-a2a0-0bf4ad113862, name=Bob]
Person[id=91b335b5-aa6f-4689-b2f4-eff2fea51d76, name=Carol]
Person[id=d600cffe-1c65-4e33-bfb1-59ac1b4de7af, name=Davis]
*****| CachedRowSet without Connection |**************
pkey = 8c5329fd-5abb-4a65-ad41-5e32914b166b | name = Alice
pkey = 6ed97068-ed14-4be4-a2a0-0bf4ad113862 | name = Bob
pkey = 91b335b5-aa6f-4689-b2f4-eff2fea51d76 | name = Carol
pkey = d600cffe-1c65-4e33-bfb1-59ac1b4de7af | name = Davis
**************************************************
Note that a RowSet object is also a ResultSet object. You can use a RowSet wherever you would have used a ResultSet, subject to the limits of its interface and class implementation.
record
Another approach is to define a Data-Transfer Object (DTO).
The new record feature in Java 16 can be ideal for this purpose of communicating data transparently and immutably. With a record, you merely declare the type and name of each member field. The compiler implicitly creates the constructor, accessors, equals & hashCode, and toString.
You write code to loop through your ResultSet rows, copying the data into record objects, and storing in a List or Set.
See example code above for a record being declared locally, and instantiated with data retrieved from database. Excerpt:
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 ) );
}
You could make a new List< Person > such as ArrayList, and add each new Person object.
CodePudding user response：
When you execute a query and obtain a ResultSet, the data has not yet transferred from the database into your JVM. Consider having a huge database, much larger than your RAM (that is usually the reason to run a database at all) and therefore you need to process the result record by record.
The pattern is to
while (rs.next()) {
// do something about the record loaded into the ResultSet now
}
rs.close();
If you close the database connection before you looped through the resultset you eliminate the chance of loading further records. The exception just indicates this situation.
On the other hand, not closing the resultset would mean you wait until it gets garbage collected. Until then it stays in memory, and the database needs to stay ready to deliver the data in case next() were called. That also means all records that may be locked stay locked until the garbage collector kicks in - which may result in performance penalties or even deadlocks.
The question would be why you need to close the connection before processing the data.