This content has been marked as final. Show 3 replies
You should either write the servlet to obtain, use, and close a JDBC connection every time it is invoked,
or use a connection pooling system that will test and keep a stock of healthy connections for you to re-use
as needed. If a connection fails halfway through executing the servlet after having been tested or newly
made at the beginning of the servelt then you will still have to handle the exception and re-do your work
with a new connection if you can.
I don't know the answer to why its doing that, but here's some ideas (not necessarily good ideas):
Does each function that uses a connection get a connection from the datasource, and instantiates connection/preparedStatement/resultSet in a try/catch/finally block and close them in the finally block in reverse order they were instantiated? Are the connection/preparedStatement/resultSet declared as local variables within the function? Is there only one datasource instantiated for the entire application that stays around for the duration of the application (until the last person logs off)? Does all your exception catch statements log an error message rather than just consume the error and not tell you if anything is wrong?
You may consider putting System.out.println() just before the execution of each sql, writing out the sql statement before it runs. This way, you know the last sql statement that was run before it hanged. See if its always the same sql statement. Also, servlets are thread safe, but the code within it may not be. Look at the code for such situations as a servlet using a class member variables rather than all local variables.
Perhaps you can write a simple project that queries the database over and over to see if the connections are dropped. If it does, then you problem may be outside of the code such as:
Does your jdbc version match the database version?
Does the version of Java you are using match the version that the original developers created the code with?
If all the servlets get their connections a a JDBC lookup of a resource defined in context.xml you are in luck, you only have to change the definition to use a connection pool. Otherwise you are up for recoding in each servlet ideally to so that, and making sure each connection is obtained for each use and closed after each use rather than retained across uses.