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/comp.lang.java.programmer/browse_frm/thread/b86
faadaafc7e8b7/c28770eb749709d2?lnk=st&q=%22execute+around%22+tackline&rnum=1&hl=
en#c28770eb749709d2

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

Tom Hawtin

I do not think a result set matches your requirements.

Try look at disconnected row set.

Arne

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;
//////////////////////////////////////////////////////////