• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Optimizing Database Connection ?

 
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a web application and it accesses a mysql database. I have a class dedicated for database access called SQLmanager. In SQLManager, it has a Connection , method to get Connection and anything relating to database access.

Whenever a servlet or JSP needs to access, it would do a SQLManager sql = new SQLmanager(); Imagine what happens if I have to go to many servlets , I would have so many connections per user. My web hosting service I subscribed only give a limit of 10 connections per subscriber so I only have 10.

What can I do so that every user would only have 1 connection ? Can I use a singleton ? Is there any good samples and tutorials for Datasource and Connection pooling ?
 
author & internet detective
Posts: 40746
827
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tay,
You are correct that you would use a connection pool. The idea is to use each connection for as short a time as it is actually working and then return it to the pool.

Some databases provide drivers with connection pooling. Otherwise dbcp is an open source implementation.
 
Tay Thotheolh
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jeanne. My SQLManager contains all the database access rather than doing each access in the servlets itself. So effectively, servlets just call a SQLManager and they have all the possible methods available so that I don't need to re-code the database access within each servlet itself. I was wondering if each servlet calls a new SQLManager , I was afraid it might be a pretty expensive process and can lead to lots of connections to the database because of each new instance of the SQLManager.

What happened that caused me to post this topic is that I have a newly deployed web application using the same database accessing through a SQLManager and sometimes the pages (with Database connection needed) can get information and sometimes it can't. I have ruled out the possibility of errors in codes where the database can't get anything from the database and throw nulls... it's been made to handle that sort but the problem is , whenever I reach a page with database access , sometimes it renders , sometimes it doesn't and after a while, it can render again with the information.

The hosting doesn't have any system.out.println or print stack traces for the web application (the only logs are web server logs....not tomcat logs). So I could only do guess work that the weird rendering could be caused by the SQLManager.
[ September 09, 2008: Message edited by: Tay Thotheolh ]
 
Tay Thotheolh
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One other thing, I wrote the code portion for the Data Source and connection pooling. Other then programming portion, do I need to configure in some web.xml or anything ?
 
Jeanne Boyarsky
author & internet detective
Posts: 40746
827
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Tay Thotheolh:
The hosting doesn't have any system.out.println or print stack traces for the web application (the only logs are web server logs....not tomcat logs)


That sounds frustrating. Does the error occur when you try out the app (as opposed to other people) ? If so, you could put the logs on the screen. If not, you could store them in some static object and have a page that dumps it to the screen on demand.

Without an error message, it's hard to figure out anything.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic