proxool-developer

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

[Mark <ma...@te...>]

hi proxool developers,

i am interested in using proxool as a solution to easily supporting =
database connection pooling without changes in our JSP application.  i =
need advice from you guys on a requirement because it seems that there =
is no way of doing it given the current proxool version after reading =
through the documents and mailing list archive.

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.

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?

i don't mind contributing code to proxool to achieve this.  i just want =
to know alternative solutions for our problem.  ill also appreciate if =
you can point me to another project offering this kind of feature.

thanks in advance,

mark

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