Re: [Proxool-developer] "get connection from pool on create statement" AND "return connection to pool when statement is closed"

[Bill H. <bi...@lo...>]

Hi Mark,

> we designed our JSP application 4 years ago such that it uses 1 
> connection object per user session.  now, we are having a problem 
> because the JSP application is hitting the maximum connections our 
> database can support.  this would not be a problem if each JSP gets a 
> connection from the pool and closes it afterwards.  we now have more 
> than 500 JSP pages.

I think you're right to want to change from that approach ;)

> now, im thinking that it would be nice if the connection is returned 
> to the pool everytime a statement is closed.  and a connection is 
> retrieved from the pool if a statement is created.  do you think this 
> is a worthwhile addition to proxool ?  or is this already supported?  
> or are there alternatives?

That would mean some code changes. And it would be deviating from JDBC 
standards, wouldn't it? Closing a statement should not close the 
connection. Can I ask why you want to do that?

Why not just write a wrapper in your own application. You ask it for a 
statement and it goes off and gets a connection for you and creates the 
statement on your behalf. Then you hand the statement back to the 
wrapper and it closes the statement and then the conneciton.

But if I was looking at some code that said:

    Statement s = MyWrapper.getStatement(sql);
    s.execute();
    s.close();

I would wonder about what connection it was using.

In order for me to understand your problem, please explain what is 
inappropriate with:

    Connection c = getConnection(); // Either using a wrapper or JDBC direct
    Statement s = c.prepareStatement(sql);
    s.execute();
    s.close();
    c.close();

(You would do smarter exception handling than that of course).

Cheers,
Bill