java.sql.SQLException: Statement has already been closed

Question

public class CpiDaoBase {

protected Connection con = null;

public void test(){
cpiDAOBase.openDbConnection();
ps = new cpiDAOBase().con.prepareStatement(INSERT_CARRIER);
ps.executeQuery();
... blah blah blah
 }

 public void openDbConnection() throws CpiSystemException
    {
        try {
            if (con == null || con.isClosed()) {
                con = CpiDataSource.getNonTxConnection();
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            new CpiSystemException("SQLException caused by con.isClosed(): " + e.getMessage());
        }
    }   
}

public class CpiDataSource {
    public static Connection getNonTxConnection() throws CpiSystemException {
    try {
        if (nonTxDs == null) 
        {
            if(log.isDebugEnabled()){
                log.debug("nonTxDs is null");
            }
            init();
        }
        return nonTxDs.getConnection();
    } catch (NamingException e) 
    {
        log.error("cpiPoolDataSource is not available !" + e);
        throw new CpiSystemException("cpiPoolDataSource is not available !");
    } catch (SQLException e) 
    {
        log.error("Failed to get connection from datasource !" + e);
        throw new CpiSystemException("Failed to get connection from datasource !");
    } catch (Exception e) 
    {
        log.error("Exception \n" + e);
        throw new CpiSystemException(e.getMessage());
    }
}
}

I am getting below Exception:

java.sql.SQLException: Statement has already been closed] [[ACTIVE] ExecuteThread: '8' for queue: 'weblogic.kernel.Default (self-tuning)'] [2014-10-28 05:54:17,918] [ERROR] [com.uprr.app.cpi.dao.CpiCustomerPipelinePreferencesDao:104] [SQL Exception : java.sql.SQLException: executeQuery, Exception = null] [[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)'] [2014-10-28 05:54:17,918] [ERROR] [com.uprr.app.cpi.web.action.PatternSelectionAction:112] [Got SQLException when accessing the CPI_CUST_PILN_PREF table : executeQuery, Exception = null] [[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)']

My doubt is simple but i am confused:

From the above code i am using local connection object or global ? Please suggest.

Elliott Frisch Elliott Frisch · Accepted Answer · 2014-10-28T14:08:26

You aren't using the Connection you open. This,

cpiDAOBase.openDbConnection();
ps = new cpiDAOBase().con.prepareStatement(INSERT_CARRIER);

Should be

cpiDAOBase.openDbConnection();
ps = cpiDAOBase.con.prepareStatement(INSERT_CARRIER);

Because the instance you create with new cpiDAOBase() doesn't have an openDbConnection.

java.sql.SQLException: Statement has already been closed

1 Answers