RE: db connections and Abusive refresh

[Jay Gardner]

If you are not going to use MVC is there any reason why you can't do your
database accesses at the top of your JSP and the put the results in
variables to be output below in the page? Sorry, if I don't understand your
question, but it sounded like you were holding a database connection open
until a scriplet closes it at the bottom of the page.

--JG

-----Original Message-----
From: Jacob Kjome [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 1:31 PM
To: Tomcat Users List
Subject: Re: db connections and Abusive refresh

Hello Laurent,

Yes,

Here is an example.  Take notice of how connections, result sets, and
prepared statements are guaranteed to be closed in the "finally"
blocks:


public Site getSite( int _id ) throws SQLException
    {
        Connection conn = null;
        Site newSite    = null;

        try {
            conn = openConn();
            newSite = getSite(_id, conn);
            closeConn(conn);
            return newSite;
        }
        catch(SQLException e) {
            System.out.println(MSG_ERROR_CONNECTION + e.getMessage());
            throw e;
        }
        finally {
            if (conn != null)  try { closeConn(conn); } catch(Exception e2)
{}
        }
    }

    //  Get a single site by id
    public Site getSite( int _id, Connection _conn ) throws SQLException
    {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Site newSite = null;

        String query = "SELECT id, url, description, created, updated,
deleted " +
                       "FROM site WHERE id = ?";

        try {
            pstmt = _conn.prepareStatement(query);
            pstmt.setInt(1,_id);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                newSite = Site.fromResults(rs);
                if (debug) System.out.println("\n" + newSite);
            }
            rs.close();
            pstmt.close();
            return newSite;
        }
        catch(SQLException e) {
            System.out.println(MSG_ERROR_RESULTSET + e.getMessage());
            throw e;
        }
        finally {
            if (rs != null)    try { rs.close(); }    catch(Exception e2) {}
            if (pstmt != null) try { pstmt.close(); } catch(Exception e3) {}
        }
    }


You can also use Syncronization if it is still a problem.

Jake

Wednesday, May 01, 2002, 2:23:17 PM, you wrote:


LFP> Hi all,

LFP> We are experiencing a few problems with our DB connection code and an
LFP> abusive usage of the F5/refresh function on the client side.

LFP> Each of our jsp actually connects to the db at the top and disconnects
LFP> at the bottom. When someone uses the refresh in the browser it leaves
LFP> connections hanging/sleeping in MySQL. Is there anyway to avoid this?

LFP> Thanks for the help,
LFP> Laurent




LFP> --
LFP> To unsubscribe:   <mailto:[EMAIL PROTECTED]>
LFP> For additional commands: <mailto:[EMAIL PROTECTED]>
LFP> Troubles with the list: <mailto:[EMAIL PROTECTED]>



--
Best regards,
 Jacob                            mailto:[EMAIL PROTECTED]


--
To unsubscribe:   <mailto:[EMAIL PROTECTED]>
For additional commands: <mailto:[EMAIL PROTECTED]>
Troubles with the list: <mailto:[EMAIL PROTECTED]>


--
To unsubscribe:   <mailto:[EMAIL PROTECTED]>
For additional commands: <mailto:[EMAIL PROTECTED]>
Troubles with the list: <mailto:[EMAIL PROTECTED]>