Java: Return 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) {}
}
}

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

[thuh Freak]

what are you going to do with the resultset after this function? Strictly read-only after this function, or are you going to do some UPDATEs or INSERTs directly on it?

If you aren't doing any data changing (in the db), I would say push the result set into a generic double-dimension array (or some disconnected collection object, maybe an array of hastables or something) before you leave the function, and return that disconnected object. If you are doing manipulation on that result set, then you have to pass the statement (and/or connection) to the function (because this function shouldn't really own those objects in that case).

[Gargoyle]

It's been a very long time since I did any Java, and I didn't do any DB access at the time so the code below is probably not going to be syntactically correct, but more like a java'ish pseudo code. :D


/************************************************** **********************/
public ResultSet sqlQuery(String sql)
{
Connection conn = null;
Statement stmt = null;
ResultSet myResults = null; /* Or possibly, ResultSet myResults = new ResultSet() ??? */

try
{
conn = RDBMServices.getConnection ();
stmt = conn.createStatement();
myResults = stmt.executeQuery(sql);
}
catch (SQLException ex)
{
System.out.println("SQL Error Fetching Results: "+ex);
}

try
{
if (stmt != null) stmt.close();
if (conn!= null)
{
RDBMServices.releaseConnection(conn);
}
}
catch (SQLException e) {
System.out.println("SQL Error Closing Connection: "+e);
}

return myResults;
}

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


Generally, I always have a single return at the end of my functions which returns null or an empty object of the correct type. Or, I throw / re-throw any exceptions to be handled by the calling code.

[ast3r3x]

/************************************************** **********************/
.....
try
{
if (stmt != null) stmt.close();
if (conn!= null)
{
RDBMServices.releaseConnection(conn);
}
}
catch (SQLException e) {
System.out.println("SQL Error Closing Connection: "+e);
}

return myResults;
}

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

Does that work for you? Generally the problem I was having with that was that returning a closed ResultSet was pretty useless.

what are you going to do with the resultset after this function? Strictly read-only after this function, or are you going to do some UPDATEs or INSERTs directly on it?

If you aren't doing any data changing (in the db), I would say push the result set into a generic double-dimension array (or some disconnected collection object, maybe an array of hastables or something) before you leave the function, and return that disconnected object. If you are doing manipulation on that result set, then you have to pass the statement (and/or connection) to the function (because this function shouldn't really own those objects in that case).
Yeah, I'm looking into returning a disconnected ResultSet or CachedRowSet at the moment. I posted on google groups, and some super geek replied with a small talk idiom called "execute around" which was pretty neat. It was neat in that you basically send a function as one of the parameters and so you can do whatever you want before you close the ResultSet. It has it's place, but I don't think that is exactly what I need to do right now since need to use the ResultSet to help build an XML file.

Thanks guy, I'll let you know how it goes, I'll have 5 hours or so to play with it at work.

[euain]

Maybe I'm being daft, but doesn't closing the Statement (as in your example) also close the ResultSet - rendering it unusable.

So even when your return executes, it is returning nothing usable..

EDIT - Yup, from Java 1.5 Javadoc for the ResultSet close() method:Releases this ResultSet object's database and JDBC resources immediately
instead of waiting for this to happen when it is automatically closed.
Note: A ResultSet object is automatically closed by the Statement object that generated
it when that Statement object is closed, re-executed, or is used to retrieve the next
result from a sequence of multiple results.
A ResultSet object is also automatically closed when it is garbage collected.

[ast3r3x]

Maybe I'm being daft, but doesn't closing the Statement (as in your example) also close the ResultSet - rendering it unusable.

So even when your return executes, it is returning nothing usable..

EDIT - Yup, from Java 1.5 Javadoc for the ResultSet close() method:Releases this ResultSet object's database and JDBC resources immediately
instead of waiting for this to happen when it is automatically closed.
Note: A ResultSet object is automatically closed by the Statement object that generated
it when that Statement object is closed, re-executed, or is used to retrieve the next
result from a sequence of multiple results.
A ResultSet object is also automatically closed when it is garbage collected.

Yeah you are right, that was my problem from the beginning. From google groups, the guys gave me this "execute around" idiom…

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

------------------------------------------------------
public static renderXML()
{
                conn = RDBMServices.getConnection();
               
                stmt = conn.prepareStatement ("SELECT * FROM ALERT_PROVIDERS");
                executeQuery(stmt, xml, new ResultSetHandler()
                {
                    public String handle(ResultSet results) throws SQLException
                    {
                        String xml = "";
                        while(results.next())
                        {
                           //process results
                        }
                        System.out.println("ResultSet: "+results);
                        return xml;
                    }
                });
                    conn.close();
                    stmt.close();
}


You can see it's pretty clever, it's declares an interface for resulthandler, and then you pass up the function of how you want it processed. That way you can return whatever you want, or how I modified it, I send an xml string so that I can append to it. I think this is a great way to do it, but I'll have to post this on the sun forums, and make sure that this is ok to do since I've never seen it done like this. I haven't decided if I want to use the same connection over, or if I want to do all that up in executeQuery() so it will be even cleaner, but there will be more overhead opening and closing connections instead of using the same one.

[euain]

If opening and closing connections is a concern, consider using connection pooling. Look in the Jakarta Commons packages for dbcp (database connection pooling).. that way you can "close" your connection, but it actually gets returned to the pool.. See: Apache Jakarta (http://jakarta.apache.org/)

Unless you are accessing the database several times a second, or performance is really critical, you may be fine just making connections as you need them. (Just remember to explicitly close them, depending on you java memory size, you can easily run out of database resources before your JVM gets round to garbage collecting).

[ast3r3x]

If opening and closing connections is a concern, consider using connection pooling. Look in the Jakarta Commons packages for dbcp (database connection pooling).. that way you can "close" your connection, but it actually gets returned to the pool.. See: Apache Jakarta (http://jakarta.apache.org/)

Unless you are accessing the database several times a second, or performance is really critical, you may be fine just making connections as you need them. (Just remember to explicitly close them, depending on you java memory size, you can easily run out of database resources before your JVM gets round to garbage collecting).

I'm not sure I could do this since the software I'm using is uPortal, and I think requires using RDBMServices.getConnection(); to establish the connection. I believe that is uPortal class that so I am limited by what it can do/does. I'd change it if I could, but my boss won't let me just go running around changing classes. :\

Memory is a concern since this could be used by hundreds (maybe a thousand?) kids at a time. It isn't LIKELY, but it technically is possible. I could just use the same connection, and multiple statements, but it just isn't as clean, I'll have to check out if that is ok to do or not.

I really appreciate the help guys, I also think that using a compile language as a server language is horrible, and I'm going to try and do an independent study next semester with 2 friends and make a similar software with PHP. :D