This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Soft Skills and have John Sonmez on-line!
See this thread for details.
The moose likes Servlets and the fly likes Servlet creating too many connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "Servlet creating too many connections" Watch "Servlet creating too many connections" New topic
Author

Servlet creating too many connections

Sam Tilley
Ranch Hand

Joined: Dec 05, 2001
Posts: 160
Hi, i have a big problem that i don't seem to be able to sort out. I have servlet A which accesses servlet B which then accesses a database, servlet B then passes the info back to Servlet A for output (done so servlet B is only used for database access, i know though that i only needed to access a class.)
The application will be used by quite a few people at once on our intranet. My problem is that i seem to be opening too many connections to the database and not closing them, hence leaving too many sleeping and clogging the system up. Servlet A creates an instance of B, in my constructor of B i have my connection, A then accesses a method of B and then i close the connection.
However i still seem to have random connections open all over the place, is the best way to just open up a connection in init for all to use, open up a connection in each method each time and then close it or use some form of connection pooling

Thanks

Sam


Sam Tilley SCJP, SCWCD
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12835
    
    5
Yours is a classic case that can make use of connection pooling. The latest SDK even includes connection pool related classes in javax.sql.
Bill
Sam Tilley
Ranch Hand

Joined: Dec 05, 2001
Posts: 160
Thanks William, i created a connection pooling class correctly and successfully set it up to open and close connections correctly.
However even with this when i look at user access on my sql database using sp_who it shows up a load of connections sitting there in sleep mode, even though they should be closed and even when i set the max no. of connections to 1. Every time i refresh or open it up again i initiates another few which can only be stopped by restarting Tomcat or deleting them. I am sure the connection pool is working correctly but now think that it might be something in Tomcat and maybe i need to do some connection pooling there instead.
Thanks
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12835
    
    5
You should be able to specify to the Connection Pool the maximumn number of connections to create. Sure sounds like something is not working right in your configuration.
Sam Tilley
Ranch Hand

Joined: Dec 05, 2001
Posts: 160
I did, i set both the initial no. of connections and max no. of connections to 1, i then also put in println code to tell me every time a connection is made and closed and from that it looks to have worked. The code was from Core Servlets and JavaServer pages by Marty Hall.
This is why i think it might be Tomcat holding sleeping connections or something but cant seem to work out what
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12835
    
    5
The bare Tomcat framework does not use any database connections. Are there other applications installed in your system that might be using the DB?
Bill
Sam Tilley
Ranch Hand

Joined: Dec 05, 2001
Posts: 160
Cheers for getting back to me Will, no i dont have any other applications running on this server and remain perplexed. I got the idea for connection pooling in Tomcat from usergroups in Google using Jakarta Tomcat page. But i havent yet worked out how to use it not being used to Tomcat.
The servlet seems to be creating new connections every time i refresh as well, despite the code saying that they are being freed and despite only 1 connection being available at a time. This sp_who on sql is driving me crazy as to why there are 4 sleeping threads being created each time.
Thanks for the help Will, i feel we are almost there,
i have a couple of other ideas such as i am now creating my ConnectionPool object in my constructor of my database access class, as this doesnt seem to get instantiated when my webapplication is started up (even though it is a servlet by design) i know this because rather basically i put test statements in the init() (where the connectionpool object should be formed) and nothing ever happened. So a new connectionpool object is started every time the class is used (however this should still not open a connection until i use my getConnection() method.
Sorry if this is getting confusing
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Servlet creating too many connections