java.sql.SQLException: Connection has already been closed after first database query

Question

My configurations are : Application Server: Weblogic 10gR3 Database : Oracle 10g IDE : Netbeans Java: version 7

I am facing exception while getting connection from pool. I have used try with resources to avoid burden to wrongly closing connections and statements.

Here is the stack trace.

[INFO] 2016-06-24 19:01:08,304 org.nmdc.incentivepackage.databaseFiles.DepositMDao checkData 184 - Executing query SELECT count(*) count FROM dep_m
[INFO] 2016-06-24 19:01:08,304 org.nmdc.incentivepackage.databaseFiles.DepositMDao checkData 188 - Executing query SELECT count(*) count FROM dep_m
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.databaseFiles.DepositMDao checkData 194 - Relinquish Database connection ...
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.databaseFiles.DepositMDao checkData 206 - return 1###success###
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.controllerFiles.CheckDepositMDataController processRequest 35 - result 1###success###   .........save
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.controllerFiles.CheckDepositMDataController processRequest 46 - update deposit master table 
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.controllerFiles.GetDepositMDataController processRequest 33 - Fetching deposit master data 
[ERROR] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.databaseFiles.DepositMDao getDepositMData 239 - Exception 
java.sql.SQLException: Connection has already been closed.
at weblogic.jdbc.wrapper.PoolConnection.checkConnection(PoolConnection.java:55)
at weblogic.jdbc.wrapper.Connection.preInvocationHandler(Connection.java:92)
at weblogic.jdbc.wrapper.Connection.createStatement(Connection.java:441)
at org.nmdc.incentivepackage.databaseFiles.DepositMDao.getDepositMData(DepositMDao.java:219)
at org.nmdc.incentivepackage.controllerFiles.GetDepositMDataController.processRequest(GetDepositMDataController.java:35)
at org.nmdc.incentivepackage.controllerFiles.GetDepositMDataController.doPost(GetDepositMDataController.java:74)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:292)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:175)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3498)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(Unknown Source)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2180)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2086)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1406)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)

The DBUtility class that gives the connection object is

public class DBUtility {
  private static Connection conn;
  private static Context ctx ;
  final static Logger logger = LoggerFactory.getLogger(DBUtility.class);

  public DBUtility() {
    ctx = null;            
    conn = null;        
  }

  public static Connection getConnection() throws Exception {
    if (conn != null)
         return conn;
    else {            
           //Hashtable ht = new Hashtable();
          // ht.put(Context.INITIAL_CONTEXT_FACTORY,"weblogic.jndi.WLInitialContextFactory");
         //  ht.put(Context.PROVIDER_URL,"t3://localhost:7001"); //weblogic server        
          try {                  
            ctx = new InitialContext();
            javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup ("incentivepool");
            conn = ds.getConnection();                
          }
          catch(Exception e) {
             logger.error("Error in establishing connection...",e);
          }
   }

   return conn;
   }
}

The class that is throwing exception is

ublic class DepositMDao extends HttpServlet {

private  Connection connection = null; 
final static Logger log = LoggerFactory.getLogger(DepositMDao.class); 
private String deposit,category,period,query;
private float amount;
private String errors,status;
private int errorCount;
private Statement statement;
private ResultSet rs;
JSONObject jobject; 

public DepositMDao() throws Exception {
   // connection = DBUtility.getConnection();
    if(connection != null) {
      log.info("Received Database connection ...");
    }
    errors ="";
    status = "success";
    errorCount =0;  
    statement = null;
}  
 public String checkData() throws SQLException {
    int count=0;
    query = "SELECT count(*) count FROM dep_m" ;
    log.info("Executing query "+query);
    try ( Connection connection = DBUtility.getConnection();
        Statement statement = connection.createStatement(); 
       ) {           
        log.info("Executing query "+query);
        try (ResultSet rs = statement.executeQuery(query)) {                    
            if(rs.next()) {
                count = rs.getInt("count");
            }              
         }
        log.info("Relinquish Database connection ...");
    } catch(Exception ex) {
        errorCount++;
        errors = errors + " "+ex.getMessage();
        log.error("Exception ",ex);         
    }     

    if(errorCount == 0) {
        status = "success";          
     } else {
        status = "failure";          
     }   
     log.info("return "+count+"###"+status + "###"+errors);
     return count+"###"+status + "###"+errors;
}

public List<DepositMBean> getDepositMData() throws SQLException {

 List<DepositMBean> depositList = new ArrayList();
 DepositMBean depositBean;
 String dep14,dep11C,dep11B;
 depositBean = null;
 depositList = null;   
 query = "SELECT c_dep14,c_dep11C,c_dep11B FROM dep_m ";     
 try ( Connection connection1 = DBUtility.getConnection();
        Statement statement1 = connection1.createStatement(); 
       ) 
    {           
        log.info("Executing query "+query);
        try (ResultSet rs1 = statement1.executeQuery(query)) {
            while (rs1.next()) { 
                depositBean = new DepositMBean();
                dep14 = rs1.getString("c_dep14"); 
                dep11C = rs1.getString("c_dep11C"); 
                dep11B = rs1.getString("c_dep11B");

                depositBean.setcDep14(dep14);
                depositBean.setcDep11B(dep11B);
                depositBean.setcDep11C(dep11C);            
                depositList.add(depositBean);            
            }    
            depositBean.display();
            log.info("Relinquish Database connection ...");
        } 
    } catch(Exception ex ) {
      log.error("Exception ",ex);         
  }  
 return depositList;     
}

Pooling configuration on server is :

dunno what's wrong but you may want to lookup/initialize the dataSource only once... i'd put it in some initializer and not within a getter method. beside the check is on the connection and not the dataSource — Zeromus
Do not store the Connection as a field of your servlet or DBUtility class. The connection is reclaimed by the web container and returned to the connection pool when the web request completes. The container considers it a dangling/orphaned connection and a coding error, and it is entirely right in doing do. — Andreas

CSK CSK · Accepted Answer · 2016-06-24T14:39:42

it is becasue the connection is static object which means there is ONLY ONE connection instance can exist.

private static Connection conn

so the first time you call it works but the second time doesn't because the connection is already closed.

you need to recreate the connection if it's closed

if (conn != null && !conn.isClosed())
     return conn;

java.sql.SQLException: Connection has already been closed after first database query

1 Answers