Returning A ResultSet

[ast3r3x]

I tried breaking out this chunk of code into a function so that I can
reuse it without having to have these huge try/catch blocks repeated
if I do multiple SQL queries.

So my problem is that the 'return' doesn't run in my first try block
because I have a finally block which supersedes returning anything.
However I can't return in the finally block because either a)it
returns and never closes the stmt and conn, or b)I have nothing to
return because stmt has always been closed.

Does anyone have any ideas how to return this ResultSet and still be
able to close it?

Possible ideas, but don't think they'll work:
1)Is there a way to pass by reference so that I can give this function
a ResultSet to pass the results to so I never have to return it?

2)If I locally make a ResultSet, and never perform .close() on it,
will it be deallocated on it's own since it's a local variable?

3)If this function was static, so that every time it ran, it would set
the same variable to the new results when it returns, would that stop
multiple ResultSets from not being closed?

/
************************************************************************/
public ResultSet sqlQuery(String sql)
{
Connection conn = null;
Statement stmt = null;
try
{
conn = RDBMServices.getConnection ();

stmt = conn.createStatement();
return stmt.executeQuery(sql);
}
catch (SQLException ex)
{
System.out.println("SQL Error 1: "+ex);
}
finally
{
try
{
if (stmt != null) stmt.close();
if (conn!= null)
{
RDBMServices.releaseConnection(conn);
}
}
catch (SQLException e) {}
}
}

/
************************************************************************/

 

[Tom Hawtin]

I tried breaking out this chunk of code into a function so that I can
reuse it without having to have these huge try/catch blocks repeated
if I do multiple SQL queries.

The usual approach is to use the "Execute Around" idiom. Put the code
you want to access the ResultSet in a class (probably an inner or local
class). Pass that to a utility method that contains all the boilerplate,
and runs the passed in handler right in the middle.

http://groups.google.co.uk/group/co...round"+tackline&rnum=1&hl=en#c28770eb749709d2

http://www.jroller.com/page/tackline?entry=factoring_out_exception_handling

Tom Hawtin

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I tried breaking out this chunk of code into a function so that I can
reuse it without having to have these huge try/catch blocks repeated
if I do multiple SQL queries.

So my problem is that the 'return' doesn't run in my first try block
because I have a finally block which supersedes returning anything.
However I can't return in the finally block because either a)it
returns and never closes the stmt and conn, or b)I have nothing to
return because stmt has always been closed.

Does anyone have any ideas how to return this ResultSet and still be
able to close it?

Possible ideas, but don't think they'll work:
1)Is there a way to pass by reference so that I can give this function
a ResultSet to pass the results to so I never have to return it?

2)If I locally make a ResultSet, and never perform .close() on it,
will it be deallocated on it's own since it's a local variable?

3)If this function was static, so that every time it ran, it would set
the same variable to the new results when it returns, would that stop
multiple ResultSets from not being closed?

I do not think a result set matches your requirements.

Try look at disconnected row set.

Arne

 

[ast3r3x]

I tried this yesterday before I got to read your guys responses, I was
hoping you could tell me if this works. My understanding of java isn't
great, so I had some trouble following what you were doing in the
'execute around' idiom, but what I did kinda sounds like what you
suggested.

I created this query class...
/***************************************************************/
private class MUQuery
{
private Connection conn;
private Statement stmt;
public ResultSet rs;

public MUQuery()
{
conn = null;
stmt = null;
rs = null;
}

public void sqlQuery(String sql)
{
if(conn != null)
conn = null;

if(stmt != null)
stmt = null;

if(rs != null)
rs = null;

try
{
conn = RDBMServices.getConnection ();

stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
}
catch (SQLException ex)
{
System.out.println("SQL Error 1: "+ex);
}
}

protected void finalize() throws Throwable
{
try
{
rs.close(); // close open ResultSe
if (stmt != null) stmt.close();
if (conn!= null)
{
RDBMServices.releaseConnection(conn);
}
}
catch (SQLException e) {}
finally
{
super.finalize();
}
}
}
/***************************************************************/



Then when I actually use that, I just do the following. I think when I
do db = null that my override of finalize should run in my class, and
then all the variables/connections will be closed/deallocated properly
correct?
//////////////////////////////////////////////////////////
MUQuery db = new MUQuery();

db.sqlQuery("SELECT id, provider_name FROM ALERT_PROVIDERS");
while(db.rs.next())
{
//do stuff with db.rs.getString(x);
//do more stuff, who knows what!
}

db = null;
//////////////////////////////////////////////////////////

 

[Tom Hawtin]

Then when I actually use that, I just do the following. I think when I
do db = null that my override of finalize should run in my class, and
then all the variables/connections will be closed/deallocated properly
correct?

No. The finalize method is not called deterministically. This makes it
pretty useless.

As an example of Execute Around, you want your code looking like:

executeQuery(someStatement, new ResultSetHandler() {
public void handle(ResultSet results) throws SQLException {
... do stuff with results ....
}
});

Where ResultSetHandler is a simple interface.

public interface ResultSetHandler {
void handle(ResultSet results) throws SQLException;
}

And executeQueue is just a static method with the boilerplate. Close you
resources, wrap thrown exceptions, iterate over rows - do whatever you
like. I'll just close the result set.

static void executeQuery(
PreparedStatement statement, ResultSetHandler handler
) throws SQLException {
ResultSet results = statement.executeQuery();
try {
handler.handle(results);
} finally {
results.close();
}
}

(Disclaimer: Not so much as compiled this code.)

Tom Hawtin

 

[ast3r3x]

Thanks for the help Tom, I really appreciate it. I changed yours a
little since I wanted to append to an XML file if there were results.
This is what I got...

A database call now looks like this...

-----------------------------------------------------------------
try
{
conn = RDBMServices.getConnection();

stmt = conn.prepareStatement("SELECT * FROM ALERT_PROVIDERS");
executeQuery(stmt, xml, new ResultSetHandler()
{
public String handle(ResultSet results, String xml) throws
SQLException
{
while(results.next())
{
xml+="\n\t<provider>"+
"\n\t\t<name>"+results.getString("provider_name")+"</
name>"+
"\n\t\t<value>"+results.getString("id")+"</value>"+
"\n\t</provider>";
}
System.out.println("ResultSet: "+results);
}
});
}
catch(SQLException e)
{
System.out.println("SQLException: "+e);
}
-----------------------------------------------------------------

Which is a lot cleaner, and a little shorter than it was originally.
Then I changed a little of the interface and executeQuery function...

-----------------------------------------------------------------
public interface ResultSetHandler
{
void handle(ResultSet results, String xml) throws SQLException;
}
-----------------------------------------------------------------


-----------------------------------------------------------------
static void executeQuery(PreparedStatement statement, String xml,
ResultSetHandler handler) throws SQLException
{
ResultSet results = statement.executeQuery();
try
{
handler.handle(results, xml);
}
catch(SQLException e){}
finally
{
results.close();
}
}
-----------------------------------------------------------------

 

[ast3r3x]

The above block of code should have looked like it does below. I
forgot to take out the specific processing, and close the connection
and the statement.
-----------------------------------------------------------------
try
{
conn = RDBMServices.getConnection();

stmt = conn.prepareStatement("SELECT ...");
executeQuery(stmt, xml, new ResultSetHandler()
{
public String handle(ResultSet results, String xml) throws
SQLException
{
while(results.next())
{
/*********************
// process results
*********************/
}
}
});
stmt.close();
conn.close();
}
catch(SQLException e)
{
System.out.println("SQLException: "+e);
}

-----------------------------------------------------------------

 

[ast3r3x]

Ok, I realized that wouldn't work with a String since they're
immutable, so I just have executeQuery returning a string and then I
append that.

xml+=executeQuery(stmt, new ResultSetHandler()
{
public String handle(ResultSet results) throws SQLException
{
String xmlAddon = "";
while(results.next())
{
/******************
// process results
******************/
}
return xmlAddon;
}
});

 

[Lew]

Ok, I realized that wouldn't work with a String since they're
immutable, so I just have executeQuery returning a string and then I
append that.

xml+=executeQuery(stmt, new ResultSetHandler()
{
public String handle(ResultSet results) throws SQLException
{
String xmlAddon = "";
while(results.next())
{
/******************
// process results
******************/
}
return xmlAddon;
}
});

If the part you don't show includes String concatenation you're probably
better off constructing the String result with a StringBuilder.

 

[Tom Hawtin]

xml+=executeQuery(stmt, new ResultSetHandler()
{
public String handle(ResultSet results) throws SQLException
{
String xmlAddon = "";
[...]

If the part you don't show includes String concatenation you're probably
better off constructing the String result with a StringBuilder.

If xml is declared as a *final* StringBuilder, then there is no need for
xmlAddon. I think it was a bad idea to force final in this situation
(perhaps Java SE 7 will do better).

Tom Hawtin