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

JDBC Newbie: Understaning 'Connection'

Alton Hernandez
Ranch Hand

Joined: May 30, 2003
Posts: 443
Hi,
I have Servlets and JSPs that access a certain database. In order to centralize the code, I would instantiate one 'Connection' object inside a ServletContext listener and then place it in the servlet context as an attribute. So everytime a servlet or a jsp needs to access the db, they would just call 'servletContext.getAttribute' to get the Connection object.
Now I am wondering if it is correct to share/use only one 'Connection' object. I tested my program to see what would happen if 2 servlets use one 'Connection' at the same time and it seems it does not cause any problems.
So what are the problems if I were to use only one instance of 'Connection' object as oppose to creating a new one everytime a servlet/jsp needs to access the database?
Thanks.
Ali Gohar
Ranch Hand

Joined: Mar 18, 2004
Posts: 572
I tested my program to see what would happen if 2 servlets use one 'Connection' at the same time and it seems it does not cause any problems

I don't agree with your opinion. Do you know that resultset object is associated with a connection object and it doen't fetch all the records if there are a lot of records in your table.
So if your connection is associated with one resultset and then you want to associate with any other then the first will get lost.
So try to test using 2 jsps or servlets which brings large data from database like you have next,previous buttons at the page etc. and then see the effect.
Alton Hernandez
Ranch Hand

Joined: May 30, 2003
Posts: 443
Originally posted by Ali Gohar:

So if your connection is associated with one resultset and then you want to associate with any other then the first will get lost.


Thanks.
I'll try that.
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
So if your connection is associated with one resultset and then you want to associate with any other then the first will get lost

I think you mean that a ResultSet is associated to one Statement, right? i.e. a Statement can process only one ResultSet at a time. However, a Connection object can usually handle multiple Statement objects concurrently, therefore you can theoretically process multiple ResultSetS consurrently as long as each one is created from a different Statement.

However, the problem with using only one Connection object is that it is not very scalable and you are likely to run into problems as the number of concurrent users increases. The general practice is to create a Connection pool that holds multiple Connection objects and hands them out as needed. You can place the connection pool in your servlet context instead of a single Connection.

Check out connection pooling. I believe most, if not all, servlet containers (e.g. Tomcat) have built-in support for connection pools because they are so frequently used.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Newbie: Understaning 'Connection'