• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC Newbie: Understaning 'Connection'

 
Ranch Hand
Posts: 443
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 572
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 443
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic