CODEWITHSUNDEEP
javamysql
Spedwards
Spedwards

Reputation: 4492

How to return ResultSet in SQL Wrapper class

So I'm creating a simple MySQL wrapper class in Java just for my personal ease of use though I have a question.

In every example of MySQL in Java, the ResultSet is closed, followed by the Statement, then the Connection. Is the order of this important and if not, does the ResultSet have to be closed?

I want to return the ResultSet though if I can't do that, I have to find some other way to return the query's result.

Upvotes: 1

Views: 864

Answers (2)

Narendra Pathai
Narendra Pathai

Reputation: 41945

Is order important?

Yes in general it is a good practice to close resources in reverse order . Also in Java 7 try-with-resources, the order is reverse i.e. the resource opened last is closed first.

Passing result set to other components?

You can use CachedRowSet for that, it is a result set which is disconnected from datasource and is safe to pass to other components.

Quoting Oracle javadocs:

A CachedRowSet object is a disconnected rowset, which means that it makes use of a connection to its data source only briefly. It connects to its data source while it is reading data to populate itself with rows and again while it is propagating changes back to its underlying data source. The rest of the time, a CachedRowSet object is disconnected, including while its data is being modified. Being disconnected makes a RowSet object much leaner and therefore much easier to pass to another component. For example, a disconnected RowSet object can be serialized and passed over the wire to a thin client such as a personal digital assistant (PDA).

EDIT:

Based on comment of @JoD. You should consider the data size and the problem you are trying to solve before you choose any option.

If you are handling a query, maybe as part of an ETL job, that needs to handle 4 GB of memory, do you really want to have that 4GB completely in memory before you start any further processing? CachedRowSet is very good given certain situation, and is definitely worth mentioning here. But we should consider this in the proper context

Upvotes: 2

Elliott Frisch
Elliott Frisch

Reputation: 201447

It's a good practice to close resources in the reverse of the order they are opened. The Statement.close() Javadoc says (in part)

Note: When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

Instead of returning a ResultSet, it's common to implement a ValueObject and return a Collection of your ValueObject type. This is usually a Plain Old Java Object (or POJO), JavaBean or Data Transfer Object (DTO).

Upvotes: 1

Related Questions