All the examples given up to this point in this chapter have closed the Connection object after the servlet services the request. However, you might ask the following question: If the servlet is called more than once and it opens a Connection object with the same arguments (url, user, and password) each time, wouldn’t it better to leave the Connection object open for future use; that is, for when the servlet is called again?
In fact, opening a Connection object is one of the most expensive operations in database programming. Being able to save CPU cycles by keeping the Connection object is very tempting. In many reference books, however, including those of non-Java web programming, you are always told to close the Connection object as soon as it’s no longer needed. The recommendation is so intense that some people have the impression that keeping the connection open is forbidden. Why?
Actually, an open connection is not taboo. Whether to leave the Connection object open really depends on the application you are building. First, however, you should consider that it is true that the response time will be faster if you can access the database without having to open a Connection object. For example, you can make the object variable of the Connection object class level so that it can be accessed from anywhere in the servlet and put the code that opens the Connection object in the init method. This way, the Connection object will be opened when the servlet first initializes. On subsequent requests, the Connection is already open, so the user will experience faster response.
A servlet can be accessed by multiple users at the same time, however. The bad news is that the underlying architecture of the Connection object will allow only one query to be processed at a time. As a result, only one user can use the connection at a time. The others will have to queue. Considering that modern databases allow multiple connections, this is really a waste.
The conclusion is that if your servlet is accessed only by a single user at a time, leaving the Connection object open makes the response time faster. If you can’t guarantee that there will not be simultaneous accesses to the servlet, however, response time will start to decrease because the second and subsequent users have to wait until the first user gets serviced.
That’s why, except in some rare cases, you should always close your Connection object. Rather than having the Connection object open, a technique called connection pooling is implemented by the driver to manage the Connection objects to a database so that multiple connections can be pooled. A connection can be used and, when finished, returned to the pool to be used by another request.