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.