Returning A ResultSet

Discussion in 'Java' started by ast3r3x@gmail.com, Apr 10, 2007.

  1. Guest

    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) {}
    }
    }

    /
    ************************************************************************/
    , Apr 10, 2007
    #1
    1. Advertising

  2. Tom Hawtin Guest

    wrote:
    > 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
    Tom Hawtin, Apr 10, 2007
    #2
    1. Advertising

  3. wrote:
    > 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
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=, Apr 11, 2007
    #3
  4. Guest

    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;
    //////////////////////////////////////////////////////////
    , Apr 11, 2007
    #4
  5. Tom Hawtin Guest

    wrote:


    > 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
    Tom Hawtin, Apr 11, 2007
    #5
  6. Guest

    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();
    }
    }
    -----------------------------------------------------------------
    , Apr 16, 2007
    #6
  7. Guest

    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);
    }

    -----------------------------------------------------------------
    , Apr 16, 2007
    #7
  8. Guest

    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;
    }
    });
    , Apr 16, 2007
    #8
  9. Lew Guest

    wrote:
    > 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.

    --
    Lew
    Lew, Apr 17, 2007
    #9
  10. Tom Hawtin Guest

    Lew wrote:
    > wrote:
    >>
    >> 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
    Tom Hawtin, Apr 17, 2007
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matrix
    Replies:
    0
    Views:
    558
    Matrix
    Apr 1, 2004
  2. matrix ¿ìÀÖÄñ

    How i know the size of "resultset"?

    matrix ¿ìÀÖÄñ, Apr 3, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    3,413
    matrix ¿ìÀÖÄñ
    Apr 3, 2004
  3. Biebel
    Replies:
    3
    Views:
    5,253
    Sudsy
    Nov 21, 2003
  4. qazmlp
    Replies:
    2
    Views:
    1,775
    GaryM
    Feb 9, 2004
  5. Replies:
    11
    Views:
    648
    Christos Georgiou
    May 2, 2006
Loading...

Share This Page