when to close the database
dear all
i have tried to write a few jsp, they all connect to database using jdbc. So i have a question that when i create the 1st and connect to database, next 2nd, 3rd ... pages to work with database, last page should close the database. However, if user just open 1st, then 2nd and so on, but he/she doesn't go to the last page, then my database haven't close, what happen to my system? as well as when or what page should i code the closing database.
thanks
yan
[505 byte] By [
chungyan5] at [2007-9-26 4:22:10]
When using servlets, EJB's or any other multithreaded server, it is generally best to leverage JDBC connection pools. These allow you to allocate and close the pooled connection at the end of each page while the pool manages the underlying JDBC connection keeping it open until the server is idle or exits. This resolves a number of performance, threading and usage issues you would otherwise face.
Chuck
Presuming you don't have a application server or another method to control connection pools, then you should open the connect, gather your results (in an ArrayList), and then close the connection before returning the results.
> Opening a connection and closing it for each servlet
> request can be bad idea. Many (most?) JDBC drivers
> incur substantial performance hits when a new
> connection is required.
And your point is?
Do you want to addres performance? Let us - performance criteria in order of importance.
-Most critical: Requirements
-Design
-Environment
-Least important: Code
Based on the description of the algorithm being used, one can only hope that the system is not a high rate system, because it is probably going to be in trouble. And it doesn't matter how the connections are handled.
And your proposed solution also has the presumption that a connection pooling solution exists (or can be implemented.) The original post didn't suggest that was available and it is certainly possible to run jsp without connection pooling being available.
So if you want to address performance criteria in the original post then you might want to start by asking about the requirements for the system.
If you want to address performance from the coding level then you should at least ask about the platform (server, application software, general connectivity, etc.) At least that way you can address the alternatives of whether connection pooling is available (and how to solve that) or if available how to implement it.
And before addressing performance problems in code one should NEVER guess about the bottlenecks. Performance should always be measured using a performance tool and adjustments made only once that was done. So if performance in code is the direction you want to take, you should verify that a profiling/performance tool was used to arrive at the problem area. Let me emphasis that there is no point in using connection pools until you have profiled the application and identified that connections are a bottle neck. (That of course presumes that you dealing with bottlenecks in the code phase rather than the design or requirements phase. But that would seem to be a given in this discussion.)
My experience is that for most server-side systems, database or other backend access performance is almost always a critical issue. I generally build systems for access by many thousands of concurrent users, so perhaps my experience is a bit too tilted in that direction.
Since the OP gave no real information about his system, I assumed that performance is an issue - otherwise he would have opened and closed the DB connection each for each JSP page shown.
My post in response to yours was to merely point out the potential performance problem of that solution. If it works for the OP, then so be it. In my world, as tilted as it is, it would be unacceptable.
Chuck
