Java.sql Connection closed Exception when on a particular server

Question

I get Java.sql.Connection closed Exception whenever i upload my application on My production Server.The production server is Jboss . I have also tried on QA,DEV server where this problem does not occur. Ours is a web application and whenever we traverse to a particular tab this exception occurs.

This is the Trace :

2011-11-11 14:32:12,983 ERROR [STDERR] java.sql.SQLException: Closed Connection 2011-11-11 14:32:12,984 ERROR [STDERR] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) 2011-11-11 14:32:12,984 ERROR [STDERR] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) 2011-11-11 14:32:12,984 ERROR [STDERR] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) 2011-11-11 14:32:12,984 ERROR [STDERR] at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:897) 2011-11-11 14:32:12,984 ERROR [STDERR] at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:816) 2011-11-11 14:32:12,984 ERROR [STDERR] at com.amadeus.mis.usermanagement.MisDb.prepareStatement(MisDb.java:72) 2011-11-11 14:32:12,984 ERROR [STDERR] at com.amadeus.mis.usermanagement.MisDb.getCompId(MisDb.java:1002) 2011-11-11 14:32:12,984 ERROR [STDERR] at com.amadeus.mis.usermanagement.MisDb.loadUnLinkedUsers(MisDb.java:1033) 2011-11-11 14:32:12,984 ERROR [STDERR] at org.apache.jsp.LinkUser_jsp._jspService(LinkUser_jsp.java:445) 2011-11-11 14:32:12,985 ERROR [STDERR] at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) 2011-11-11 14:32:12,985 ERROR [STDERR] at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) 2011-11-11 14:32:12,985 ERROR [STDERR] at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:387) 2011-11-11 14:32:12,985 ERROR [STDERR] at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320) 2011-11-11 14:32:12,985 ERROR [STDERR] at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266) 2011-11-11 14:32:12,985 ERROR [STDERR] at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

I have also attached a code sample,This is the code where the error does occur.

public List<String>loadLinkedUsers(String uname)
    throws Exception
    {

        List<String> userList = new ArrayList<String>();

        String[]temp=uname.split("_");
        String myschema=temp[0]+"_"+temp[1];
        if(!getUserType(uname).equalsIgnoreCase("RC"))
        {
        PreparedStatement stmt = null;

         stmt = prepareStatement("select res_user_id from"+" "+myschema+".res_user where login_id = ? and res_user_id !='Administrator'");
         stmt.setString(1,uname);

        try {

            ResultSet rs = stmt.executeQuery();
            while(rs.next())
            {

                userList.add(rs.getString(1));

            }
            rs.close();
        } catch (Exception e) {
            System.out.println("Exception"+e.getMessage());
        }
        stmt.close();
        }
        return userList;

    }

    public  int getCompId(String userName)
    {
        PreparedStatement stmt;
        int temp=0;
        try{
        stmt = prepareStatement("Select comp_id from mis.users where login_id = ?");
        stmt.setString(1,userName);
        ResultSet rs=stmt.executeQuery();
        while(rs.next())
        {
            temp=rs.getInt(1);
        }

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return temp;
    }
    /**
     * Load the information about users that are not Linked to 
     * the user who is logged in.
     * (login_id, user_email, user_type_id from table mis.users)
     * @param login
     * @param users Vector that is filled with user information of type User.
     *              The previous data is not cleared. 
     * @throws SQLException
     */
    public List<String> loadUnLinkedUsers(String uname)
    throws Exception
    {

        List<String>userList = new ArrayList<String>();

        String[]temp=uname.split("_");
        String myschema=temp[0]+"_"+temp[1];
        int comp_id=getCompId(uname);

        if(!getUserType(uname).equalsIgnoreCase("RC"))
        {
        PreparedStatement stmt1=null;
        String query1="select res_user_id from"+" "+myschema+".res_user where login_id=? and res_user_id='Administrator'";
        stmt1 = prepareStatement(query1);
        stmt1.setString(1,uname);
        try {

            ResultSet rs1 = stmt1.executeQuery();
             /*if(!rs1.next())
             {
                userList.add("Administrator");
             }*/

        } catch (Exception e) {
            System.out.println("Exception"+e.getMessage());
        }

        PreparedStatement stmt = null;
        String query="SELECT u.login_id FROM mis.users u LEFT JOIN mis.user_type t ON u.user_type_id = t.user_type_id  WHERE comp_id = ? minus select res_user_id from"+" "+myschema+".res_user where login_id =?";
        stmt = prepareStatement(query);
        stmt.setInt(1,comp_id);
        stmt.setString(2,uname);

        try {

            ResultSet rs = stmt.executeQuery();
            while(rs.next())
            {
                userList.add(rs.getString(1));

            }
            rs.close();
        } catch (Exception e) {
            System.out.println("Exception"+e.getMessage());
        }
        stmt.close();
        }
        return userList;

    }

Please note that this is the function used to create Prepared Statements.

protected Connection m_Connection;

    /**
     * Prepare a statement from the current connection
     * @param strSql
     * @return Prepared statement
     * @throws SQLException
     */
    protected PreparedStatement prepareStatement(String strSql) throws SQLException
    {
        logger.debug("SQL=  " + strSql);
        return m_Connection.prepareStatement(strSql);
    } 

Answer 1

Kindly ensure you have the correct apache derby jars in your built path. You can get the derby.jat and the derbyclient.jar in the lib folder of the extracted zip derby file.

Answer 2

I finally did resolve the issue by replacing the classes12.jar and also using initial context instead driver manager.

Answer 3

It is not clear where what the scope of m_Connection is, whether it is reused, how often is reused or whether multiple threads could access the same connection concurrently, which may lead to problems.

If the m_Connection is kept open and intended to be reused by individual requests, what may happen is that the database driver possibly automatically closes it after a period of inactivity.

My question is, would you consider using a JNDI DataSource to look up your connections rather than caching the connection in m_Connection?

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
Connection conn = ds.getConnection();

try {
    ... DB logic ...
} finally {
    conn.close();
}

That way the business of creating and managing connections is delegated to the application server which will pool the connections, so you won't actually open and close a new connection every time you make a call.

Other possible issues with the code include:

• Not closing all ResultSets
• Not closing the PreparedStatements in a finally block (or in case of getCompId not closing it at all)

Answer 4

Looks like the database is closing the connection on you after some time. Make sure you enable check-valid-connection-sql property with a valid check SQL (e.g. select 1 from dual) in your datasource config file so the connection is validated automatically before retuning to you. Also make sure you are not holding on to the connection object in a class variable and reusing it across multiple requests

<check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>