Closing Connection Which way ????

[sandhya menon]

Hi Friends,
My Current strucuture of coding is a Centralised Java Bean which act as Connection Object Supplier Program to all JSP which needs DB Connections.
So In my Jsp i used to hook a method of Java Bean which provides Connection from WebSphere DataSource Pool(WAS3.5.5-JDNI LookUp etc .etc).
Jsp pages creates Statement,ResultSet , executes SQL queries and in finally block closes all connection in orderly way 1st RS,then Stmt and Conn. I send this conn object back to Java Bean and make sure closed at Java Bean also.One ClosAllConn method added specifically for this in Java Bean.
Now the Q ?: is this right way ?? I have seen the
codes all SQL operation at Java Bean end including Firing of SQL and sends back the ResultSet abck to JSP and close RS in JSP. Just by closing RS will it make sure all related objects incl Conn. got closed ie send back to pool. Or should i send this RS back to JavaBean and get related Statement Obejct (RS.getStatement) and Conn obj(Stmt.getConenction) and close all there at Java end
Pls comment

[schandu999]

'Now the Q ?: is this right way ?? I have seen the
codes all SQL operation at Java Bean end including Firing of SQL and sends back the ResultSet abck to JSP and close RS in JSP. Just by closing RS will it make sure all related objects incl Conn. got closed ie send back to pool. Or should i send this RS back to JavaBean and get related Statement Obejct (RS.getStatement) and Conn obj(Stmt.getConenction) and close all there at Java end '
Hi sandya ,
by just closing the rs won't close the stmt and conn objet.
One suggetion instead of sending rs back , why don't u close u r rs,stmt,conn obj in the bean only,
and close first resultset, statement and then close your connection object in the finally
like this

}
finally
{
    try
    {
        if ( rs != null )
            rs.close
    }
    catch(Exception e)
    {
        throw new Exception("Error in closing rs");
    }
    try
    {
        if ( stmt != null )
            stmt.close();
    }
    catch(Exception e)
    {
        throw new Exception("Error in closing stmt");
    }
    try
    {
        if ( conn != null )
            conn.close
    }
    catch(Exception e)
    {
        throw new Exception("Error in closing conn");
    }
}

let me know if this workd for u or not
thanks
champak
[ Edited by Dave to format code ]
[ February 15, 2002: Message edited by: David O'Meara ]

[David O'Meara]

"champak",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements.
Thanks.
Dave

[David O'Meara]

I do more or less the same, the only difference is that I'd log the fact that they failed to close rather than throwing a new Exception.

    try
    {
        //some database stuff
    }
    finally
    {
        if (resultSet != null)
        {
            try
            {
                resultSet.close();
            } 
            catch (SQLException e) 
            {
                Logger.error("Failed to close ResultSet");
            }
        }
        if (preparedStatement != null)
        {
            try 
            { 
                preparedStatement.close(); 
            } 
            catch (SQLException e)
            {
                Logger.error("Failed to close PreparedStatement");
            }
        }
        ctx.releaseResource(connection);
    }

(note that this example returns the db connection to a pool, but it all works the same...)
DOM

[sandhya menon]

Thanks, But still..
Even if I fire all sql operation at Bean end,I have to display ResultSet values on my Browser.In my case itis a huge Reports Module (Around 25-35 diif types of reports having 1000 records pulled from Oracle.)
I can understand had the scenario is just update or insert... I could finsih off all there and there at bean level. But i my current module it RS values get dispalyed hence i may have to pass RS back to JPS from bean.
Let us say just take RS from bean to JSP then just closing RS wont work as per my friend champak. So Should i send it to bean. ??? Else if Send RS to Bean is that a copy a RS object send to JSP so that Bean RS can be closed immed. {wild thought !! pardon me )
Due to this process I have thought to just take connection from bean and create Statment and RS at JSP end and so that close all related objects.
Thanks to all entered in Discusssion pls continue with valuable suggestions !!
Regards

[sandhya menon]

David O'Meara and champak,
I am eagerly waiting for furthur discussion....
Regards

[David O'Meara]

(I wrote this huge reply then my computer blue screened. )
Basically to save trouble you need to provide separate levels of logic: JSPs for presentation, Java Beans for business, and another for persistence.
Your JSP should only perform display related duties. It should make a call to the Java Bean, which should make the data the JSP requires available (without the JSP having any knowledeg of how the data was created or where it came from)
The bean can then get a connection, perform a query, parse the results then make them available to the JSP.
There isn't really any separation here between the business and persistence layers, so preferably the bean would make a call to another component to liase between the bean and the persitent storage. We do have the advantage of maintaining the database resources in a single place thought.
So, to get back to the original question:
the JSP should call your bean without any knowledge of what the bean will do. All the JSP knows is that the bean will have the data it requires.
The bean then makes a call to the persistence layer, either by itself (not recommended but easier) or through an intermediary.
I guess what I'm trying to say is: no Database Resiources (Connections, ResultSets etc) in a JSP, and all resource manageent should happen in one place so you don't create resource leakage.
DOM
(hope this said what I originally wanted to say, it never comes out right the second time... )

[sandhya menon]

Thanks for all that valuable discussion.
At a times I pull around 9000 recs.USing Scrollable RS I could manage Pagiantion also. Not putting inot temp String array (Vector etc is not advisable to use in Web Programs i suppose ?) all
will result in re creation of my JSP. Dont u think all these are just work around and when we buy such costly Applciation Servers like WAS or Iplanet or WebLogic they should be able to manage all these things I mean workload management. Even after taking WAS we are forced to lot tunings / performacne issues etc etc is an extra specialised service I suppose
Pls comment
regards

[Jotty Tharakan]

Hi sandhya,
I am supproting david's suggestion for the design.Offcourse application server tuning is a must for good performance.But still it will ease ur job in real time scenario.
U said u have to pull more than 9000 records at a time.I think u can use cachedrowset provided by sun.It is easily managable upto 60,000 records using this object and it is serializable object.It act as a temporary table.

[sandhya menon]

So as u have suggested I f change my code as
CacheRowSet crs= new CacheRowSet();
Result rs=stmt.executeQuery(Qry);
crs.populate(rs);
rs.close();
stmt.close();
con.close();
Is rs close here ok. no since crs should act as a disconnected ResultSet so if do this in my bean i can release all DB related objects at JAva Bean end and carry CRS to JSP & work freely with CacheRowset ??
Pls advise
Regards