aspose file tools*
The moose likes JDBC and the fly likes Optimizing Database Connection ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Optimizing Database Connection ?" Watch "Optimizing Database Connection ?" New topic
Author

Optimizing Database Connection ?

Tay Thotheolh
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
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 ?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Tay Thotheolh
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
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

Joined: Aug 07, 2008
Posts: 84
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

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.
 
subject: Optimizing Database Connection ?