Returning a ResultSet
You should never pass a ResultSet
around through public methods. This is prone to resource leaking because you're forced to keep the statement and the connection open. Closing them would implicitly close the result set. But keeping them open would cause them to dangle around and cause the DB to run out of resources when there are too many of them open.
Map it to a collection of Javabeans like so and return it instead:
public List<Biler> list() throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Biler> bilers = new ArrayList<Biler>();
try {
connection = database.getConnection();
statement = connection.prepareStatement("SELECT id, name, value FROM Biler");
resultSet = statement.executeQuery();
while (resultSet.next()) {
Biler biler = new Biler();
biler.setId(resultSet.getLong("id"));
biler.setName(resultSet.getString("name"));
biler.setValue(resultSet.getInt("value"));
bilers.add(biler);
}
} finally {
if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
}
return bilers;
}
Or, if you're on Java 7 already, just make use of try-with-resources statement which will auto-close those resources:
public List<Biler> list() throws SQLException {
List<Biler> bilers = new ArrayList<Biler>();
try (
Connection connection = database.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT id, name, value FROM Biler");
ResultSet resultSet = statement.executeQuery();
) {
while (resultSet.next()) {
Biler biler = new Biler();
biler.setId(resultSet.getLong("id"));
biler.setName(resultSet.getString("name"));
biler.setValue(resultSet.getInt("value"));
bilers.add(biler);
}
}
return bilers;
}
By the way, you should not be declaring the Connection
, Statement
and ResultSet
as instance variables at all (major threadsafety problem!), nor be swallowing the SQLException
at that point at all (the caller will have no clue that a problem occurred), nor be closing the resources in the same try
(if e.g. result set close throws an exception, then statement and connection are still open). All those issues are fixed in the above code snippets.
How to return the Result Set in Java
When the (prepared) statement is closed, also its result sets are closed.
This is understandable as ResultSet is a heavy class, with metadata and all.
Return a more specific list.
public List<String> computeGPA(String studentId) throws SQLException {
String sql = "SELECT gpa FROM student WHERE sid=?";
try (Connection con = connect();
PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setString(1, studentId);
try (ResultSet rst = pstmt.executeQuery()) {
List<String> gpas = new ArrayList<>();
while (rst.next()) {
gpas.add(rst.getString(1));
}
return gpas;
}
}
}
The second try-with-resources is officially not needed as said, but code checkers will notice that ResultSet is Autocloseable, and might give a false positive warning.
Returning a ResultSet to another function
Your database access code should be kept entirely separate from your user-interface code. The UI should not deal with an active ResultSet
.
You need to copy the data out of the result set, or use a utility to do so for you.
CachedRowSet
A RowSet
may be the solution. This interface extends ResultSet
. See the Java Tutorials by Oracle for explanations.
You could use the CachedRowSet
interface that keeps a copy of the result set data in memory, detached from the database. Oracle provides an implementation, as might other vendors such as your JDBC driver.
A CachedRowSet
implementation is disconnected from the database. In contrast, a ResultSet
maintains a database connection (the root of your problem). To quote the Javadoc:
A CachedRowSet object is a container for rows of data that caches its rows in memory, which makes it possible to operate without always being connected to its data source. Further, it is a JavaBeans™ component and is scrollable, updatable, and serializable.
That interface is extended by a few more interfaces.
POJOs
Plain old Java objects is another option, copying every field of every row from your ResultSet
into properties of a Java object.
You can simply loop the result set while instantiating records. Or you can use any of a variety of frameworks to assist.
Records
Defining a class for such POJOs is much simpler when using the new records feature arriving in Java 16, now previewed in Java 15. The constructor, getters, toString
, and equals
& hashCode
are all synthesized by the compiler. You simply declare the properties.
A record can be declared as a stand-alone class, or as a nested class, or even locally within a method.
How can I return a ResultSet?
You use the try-with-resources statement to get the connection:
try (Connection connection = getConnection()) {
The whole point of that statement is to close the connection at the end. And closing the connection also closes its statements and result sets.
Don't return a ResultSet
. Return a List
of objects.
And please, respect the Java naming conventions, too.
Returning ResultSet without close?
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
Any way to return a ResultSet in Java?
You need to provide better context for the error. Is this, for example, a JAX-WS web service endpoint? Anyway, as stated in the trace, your error is a web service error--not a JDBC error. This error can happen for many reasons--usually related to something wrong with the way you are defining the API to your service.
You are certainly allowed to return a ResultSet
from a method even if that is a very bad idea, especially from a web service endpoint. A ResultSet
can't be serialized into a SOAP message. More generally, to return a ResultSet
betrays implementation details to the callers of the method. Why should they know you are using JDBC? Or even that you are talking to a relational (or any) database at all?
The better approach is to populate a model object relevant to your domain with the data in the ResultSet
, and that object will be serialized to SOAP via JAXB or whatever you use. Or maybe you just return some text from the database, in which case JAX-WS knows what to do.
Also, make sure you do something with SQLException
so you can trace the cause of your actual JDBC errors.
Related Topics
How to Use Java to Read from a File That Is Actively Being Written To
In Java, How to Get the Difference in Seconds Between 2 Dates
Sorting Strings That Contains Number in Java
How to Set Classpath When I Use Javax.Tools.Javacompiler Compile the Source
How to Get All Table Names from a Database
Utf-8 Text Is Garbled When Form Is Posted as Multipart/Form-Data
How to Find the Console Width with Java
Call a Method of Subclass in Java
Differencebetween Double.Parsedouble(String) and Double.Valueof(String)
Class Not Found with Ant, Ivy and Junit - Error in Build.Xml
Recursively List All Files Within a Directory Using Nio.File.Directorystream;
Passing Command Line Unicode Argument to Java Code
Why Can't You Have Multiple Interfaces in a Bounded Wildcard Generic
Having a 3Rd Party Jar Included in Maven Shaded Jar Without Adding It to Local Repository