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.
