Servlet creating too many connections

[Sam Tilley]

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

[William Brogden]

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]

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]

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]

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]

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]

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