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?
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.
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.