Apologies if I'm not giving enough information - I am new to ojdbc connections and servlets in general ~
I am responsible for maintaining a bunch of legacy code which involves several deployed servlets; some through Tomcat 5.5.2, and others through Glassfish 3.3. All connect using the ojdbc14 driver, to Oracle 10 databases. All - or so I am told - use pooled connections.
However, occasionally, the connection to the DB is dropped, the DB restarts, etc., and the servlets fail until someone notices and they are re-deployed. Not a great setup. I understand there is some way to monitor the DB connection the servlet makes, so that, in the event of a lost connection, the servlet 'knows' about it and can recover gracefully rather than throwing an SqlException. But how do I accomplish this? Is there a way to make the servlet smart enough to recover itself by reinstigating the DB conection? I've searched around for an answer, but I have not been successful. Suggestions and pointers muchly appreciated. Thank you.
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.