Database Pooling design

[webster5u]

Hi, i am developing the JSP/Servlet system now. Let we discuss database pooling, how you normally do that? My developing application is open/close database object every single class instance.

My design is every time, we need access database then we invoked a database class object, execute SQL and close it. It is logically consume much of the computing process in server.

But my friend suggest that open a database object like a global database connection pooling. This object will open when user login and close when user logout. I feel this consume much memory resource to handle large amount user.

Who's what's the weakness for either design? Your recommendation is much appreciate.

[stephen84s]

Rather than making your own why dont you just try some of the freely available Database Connection Pooling libraries.
Look here for a list of them, For my apps I normally used the DBPool library.
Alternatively if you are using Tomcat you can check this article

So if you use a standard pool of database connections, whenever you wish to query the database, just checkout a connection from the pool, fire your query, process the resultset if any and return the connection back to the pool.
So you neither will have the problem of continuously opening and closing connections nor the problem for one user occupying one DB connection for his entire session.
Also you can read the following references to know more about the importance of Connection pooling : 1 , 2

[webster5u]

stephen84s, thank for you comment.
It is really useful information.
Thank a lot.