JDBC Query excecution

Question

I am facing an issue while executing queries.I use the same resultSet and statement for excecuting all the queries.Now I face an intermittent SQlException saying that connection is already closed.Now we have to either have separate resultSet for each query or have lock like structure.Can anyone tell which is better.I think introducing locks will slow down the process.Am I right?

Update: To be more clear.The error may happen because the finally block gets called before all the queries get executed and the connection gets closed and exception will be thrown.

This is the exception I get

java.sql.SQLException: Connection has already been closed. at weblogic.jdbc.wrapper.PoolConnection.checkConnection(PoolConnection.java:81) at weblogic.jdbc.wrapper.ResultSet.preInvocationHandler(ResultSet.java:68) at weblogic.jdbc.wrapper.ResultSet_com_informix_jdbc_IfxResultSet.next(Unknown Source) at com.test.test.execute(test.java:76)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:413) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:225) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1858) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:459) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1077) at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:465) at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:348) at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:7047) at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321) at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121) at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3902) at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2773) at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224) at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)

Sample code:

ResultSet rst=null; 
Statement stmt=null; 
Connection con=DBConnection.getConnection();
 stmt=con.createStatement();
 rst=stmt.executeQuery("select * from dual");
 while(rst.next())
 { : ://Some code } 
rst=stmt.executeQuery("select * from doctor where degree="BM");
 while(rst.next())
 { //blah blah } 
finally
 { 
//close con,rst and stmt 
} 

Answer 1

you are not reusing the resultset, you are leaking resultsets. rst=stmt.executeQuery... generates a new resultset and the previous resultset is never closed :(

Answer 2

It appears that the code in question has issues in multi-threaded environment.

DBConnection.getConnection() is probably returning the same connection to all threads. When multiple threads are processing multiple requests, the first thread that finishes execution of the method will close the connection, leaving all other threads high and sundry.

I'm speculating here, but is appears that the connection object returned by DBConnection is an instance member of the DBConnection object, and that would qualify as a bad practice for a connection manager in a multi-threaded environment.

A code fix would avoid the usage of instance members for Connection, Statement (and the like), and the ResultSet objects.

Answer 3

I'm not sure what's going on without knowing more about your code. Is it threaded ? Is the underlying database going down (or are you losing connectivity to it).

One thing I would do is to implement connection pooling (via Apache DBCP, say). This framework will maintain a pool of connections to your database and validate these connections before handing them out to you. You would ask for a new connection each time you make a query (or perhaps set of queries) but because they're pooled this shouldn't be a major oeverhead.

Answer 4

Unless your connection to the database has really been closed I think you did something more like this:

try {
    return resultSet.getBoolean("SUCCESS");
} finally {
    resultSet.close();
}

This code will actually close the connection before your result set is being evaluated, resulting in the exception you show.