在特定服务器上时，Java.sql 连接关闭异常

Question

每当我将应用程序上传到我的生产服务器上时，我都会收到 Java.sql.Connection 关闭异常。生产服务器是 Jboss 。我也尝试过 QA、DEV 服务器，没有出现此问题。我们的应用程序是一个 Web 应用程序，每当我们遍历到特定选项卡时，就会发生此异常。

这是痕迹:

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)

我还附上了一个代码示例，这是发生错误的代码。

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;

    }

请注意，这是用于创建准备语句的函数。

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

看起来数据库在一段时间后会关闭与您的连接。确保在数据源配置文件中使用有效的检查 SQL(例如，从双中选择 1)启用 check-valid-connection-sql 属性，以便在重新调整给您之前自动验证连接。还要确保您没有保留类变量中的连接对象并在多个请求中重用它

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