I am new to Java. I’m trying to create a class containing some utility methods for SQL operations in Java 1.6 to be used for general purposes.
I have written down a selectMethod
for getting the results of a SELECT
on the DB.
Problem: if my selectMethod
method returns a ResultSet
type, then when I call the method its related resources (ResultSet
, Statement
) will unavoidably remain open: I cannot close them from another method because they have been created into the selectMethod
… on the other hand I cannot close them inside the selectMethod
, otherwise the latter wouldn’t return anything.
So my point is:
==> How can I close the resources? <==
I cannot use the try-with-resource
because I’m using an earlier version of Java.
Among similar questions I haven’t found a “general way” to overcome this issue.
Solutions: The only two ways I know at the moment:
A) avoid creating a selectMethod
that returns a ResultSet
type, and only create a method in which the query is performed internally, together with other operations on the query results. Then close all the resources into the method.
Example:
public String selectMethod(String query, Connection conn) {
Statement stmt = null;
ResultSet rset = null;
String myOutput = "";
try {
stmt = conn.PreparedStatement(query);
rset = st.executeQuery();
myOutput = rs.getString(2); // the "particular task" I perform on the data retrieved
} catch (SQLException e) {
System.out.println(e);
} finally {
rset.close();
stmt.close();
}
return myOutput;
}
...
...
// method call:
String myQuery = "SELECT colA FROM table_name WHERE table_id = 192837465";
String theDataINeeded = selectMethod(myQuery, myConn);
myConn.close();
Drawbacks of A): I wanted a SQL class of general use and not limited to a particular task…
B) into the selectMethod
, copying the ResultSet
data into a CachedRowSet
and return the CachedRowSet
.
Example:
public CachedRowSet selectMethod(String query, Connection conn) {
Statement stmt = null;
ResultSet rset = null;
CachedRowSetImpl crset = null;
try {
stmt = conn.PreparedStatement(query);
rset = st.executeQuery();
crset = new CachedRowSetImpl();
crset.populate(rset);
} catch (SQLException e) {
System.out.println(e);
} finally {
rset.close();
stmt.close();
}
return crset;
}
...
...
// method call:
String myQuery = "SELECT colA FROM table_name WHERE table_id = 192837465";
CachedRowSetImpl theDataINeeded = new CachedRowSetImpl();
theDataINeeded = selectMethod(myQuery, myConn);
myConn.close();
Drawabacks of B): I am afraid of running out of memory when doing select with many rows. I cannot make a query with pagination with LIMIT... OFFSET...
because my DB version is below Oracle 12g, and I don’t want to make query manipulations to insert row_number() between ... and ...
. I’d like my utility to work with any kind of query.
Does anyone know other solutions?
Thanks in advance.