This is the first time I am posting at JavaRanch.
We are currently using jsps and beans. I want to know which is the best way to get a pooled connection to use in the beans.
One option is to get a new connection from the pool for each method and release it back to the pool at the end of the method.
Another option is to create one static connection per bean and use it in all the static and non-static methods. When and how would this connection then be closed and returned to the pool?
Yet another option is to create a connection when a user logs in (all users have to login to use the application) and store it in session. In each jsp, read it from session and pass it to each method call on any bean. This connection would be closed only when user logs out.
Or, get a new connection from the pool in each jsp page and pass it to each method call on any bean in that page.....??
What should one consider when deciding between using any of these or other strategies?
Any help / pointers will be appreciated.
And always borrow and return connections from/to connection pool. which(connection pool) should be able to grow and shrink depending upon requirements.
Although, opinion may vary about where should one take and release connection, My personal experience/opinion is that one should borrow and return connection in the methods itself (until and unless its a transaction across methods).
BUT, take care that you don't return Result Set from these methods to jsps, instead return vector/arraylist (or any other disconnected data) or see if you can switch to RowSets (which is the best option).
Right now I am doing the exact same thing as you suggest - connection per method and return array/vector as necessary.
But, it seems to be a bit slow in returning connections to the pool. So, sometimes when a method calls other methods before returning, which in turn open their own connection, there are a lot of open connections at a given instant. Now, multiply this with the number of users logged in and we can get a high number of open connections very soon.
Pooling in itself is working fine - the connections grow and shrink exactly as expected. But sometimes, the peak number of open connections is way too high. This led me to think that there should be some other way to get and release connections.
Thanks for ur help.
but before that, plz lemme know wat is the scope of your jsp pages (page,session..) and how active normally the user is (is it highly active or ..) in its one session.
look, simple factor is a connection should not be blocked with a user for long especially when he is idle in between its two calls. If you believe that user is highly active (and will always be) than there is no harm taking connection to jsp or keeping even one connection per session.
but then, no of connections become directly proportional to no of users at any given time so you need be careful about session-time-out handling to make sure that user doesn't seat idle blocking a connection.
(if it doesn�t violate ur cos policies can you lemme know wat exactly is ur application so that I can have some better idea).
Originally posted by Amit Agrawal*:
look, simple factor is a connection should not be blocked with a user for long especially when he is idle in between its two calls.
Exactly my thoughts!
User may be idle for some stretches, and the connection is bound to be idle for some quite time.
I like the idea of having it in the jsp, and taking care of the scope of the page....
Thank you very much for ur suggestion.
The application is an in-house call center type of software where reps are logged in for most of the day, it helps them to have some information regarding our customers before they make the call.
Right now I have it so that all the beans access dbBean and each time they make a query they create a new connection. Which of course is not very efficient...
if the user session doesn't expire for the day long, avoid making jsp scope as session. I think you better decide about handling of connection on per page (jsp) basis depending upon its usage rather than defining a common strategy for the whole application.
Originally posted by shilpa kulkarni:
where reps are logged in for most of the day, it helps them to have some information regarding our customers before they make the call.