This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Connection Pool with JSPs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection Pool with JSPs" Watch "Connection Pool with JSPs" New topic
Author

Connection Pool with JSPs

shilpa kulkarni
Ranch Hand

Joined: Jun 07, 2000
Posts: 87
Hi all,
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.
Thanks,
Shilpa.
Amit Agrawal
Ranch Hand

Joined: Aug 23, 2001
Posts: 282
Its good that at least you know your options. Anyway, first of all I would advice you not to take connection to jsps when you have jsp->bean model.
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).
regards,
amit.
shilpa kulkarni
Ranch Hand

Joined: Jun 07, 2000
Posts: 87
Hi Amit,
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.
Any ideas?
Thanks for ur help.
Shilpa.
Amit Agrawal
Ranch Hand

Joined: Aug 23, 2001
Posts: 282
well, in that case (if you are calling too many methods from one jsp) you may take the connection to jsp itself (and pass the connection to bean when you call a method).
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.
regards,
amit.
(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).

shilpa kulkarni
Ranch Hand

Joined: Jun 07, 2000
Posts: 87
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.
Regards,
Shilpa.
P.S.
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.
scottie zman
Ranch Hand

Joined: Aug 08, 2001
Posts: 40
intersting topic. How do I create a connection and pass it to a bean? What I'd like to do is have a global include that activates a connection in a dbBean and then throughout the page hands it off to whatever other bean needs to hit the database. each bean would use that one connection to do it's thing (request scope) and then at the end of the page I'd put another include that will close the connection.
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...
thanks
Zman
Amit Agrawal
Ranch Hand

Joined: Aug 23, 2001
Posts: 282
just a word of caution:
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.

shilpa kulkarni
Ranch Hand

Joined: Jun 07, 2000
Posts: 87
Yes Amit, will take care abt the scope of the page.
Zman, that sounds like a good idea too, having an include or something at the end of the page which specifically closes the connection.
Thanks to both of u'll,
Shilpa.
 
 
subject: Connection Pool with JSPs