时间戳

首页 > 解决方案 > jdbc连接池中的数据源不返回连接

jsp - jdbc连接池中的数据源不返回连接

问题描述

我有一个 jsp 网络应用程序。我正在使用 jdbc 连接池来使用位于服务器端的 mysql 数据库。我的 Web 应用程序有时会挂起，当我检查调试时发现它挂在 ds.getConnection(); 它不返回任何异常/错误。浏览器将简单地处于加载状态。我已经包含了我的 DBConnection 类，我用它来从数据源创建数据源和 getconnection。我正在调用 getConnection(); 从池中获取连接的方法。我打电话给 closeConnection(); 所有jsp中的方法最终阻塞以关闭连接。所以我假设一旦我关闭连接，连接将返回到池中

public class DBConnection {

public static Connection con = null;
public static String user_id = null;
public static DataSource ds = null;

public static Connection getConnection() {
    try {
        con = ds.getConnection();
        con.setAutoCommit(false);
    } catch (SQLException ex) {
        Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
    }
    return con;
}

static {
    con = establishConnection();
}

public static void closeConnection() {
    if (con != null) {
        try {
            if (!con.isClosed()) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
}

上下文.xml

<Context path="/KnestAdmin">
     <Resource name="dbconn" auth="Container" type="javax.sql.DataSource"
            maxActive="100" maxIdle="30" maxWait="10000"
            removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
            username="root" password="" driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mobitute_lms_data?useEncoding=true&amp;characterEncoding=UTF-8"/>
</Context>

web.xml

<resource-env-ref>
    <description>DB Connection</description>
    <resource-env-ref-name>dbconn</resource-env-ref-name>
    <resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
    <res-auth>Container</res-auth>
</resource-env-ref>

显示我如何使用连接的示例 servlet

public class Category extends HttpServlet {

    Connection connection = null;
    Statement statement = null;
    IST ist;
    PrintWriter out;

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        out = response.getWriter();
    }

    public void connectToServer() throws SQLException, NamingException {
        connection = DBConnection.getConnection();
        statement = connection.createStatement();
        ist = new IST();
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
        try {
            connectToServer();
            switch (request.getParameter("mode")) {
                case "checkAvailability": {
                    String category_name = request.getParameter("category_name");
                    String sql = "Select category_name from course_category where category_name='" + category_name + "'";
                    ResultSet resultset_val = statement.executeQuery(sql);
                    if (resultset_val.next()) {
                        out.print("exist");
                    }
                    break;
                }
                case "checkAvailability1": {
                    String category_id = request.getParameter("category_id");
                    String category_name = request.getParameter("category_name");
                    String sql = "Select category_name from course_category where category_name='" + category_name + "' and category_id!='" + category_id + "'";
                    ResultSet resultset_val = statement.executeQuery(sql);
                    if (resultset_val.next()) {
                        out.print("exist");
                    }
                    break;
                }
                case "checkwarning": {
                    String category_id = request.getParameter("category_id");
                    String warning = "";
                    String sql_query = "select * from course where category_id='" + category_id + "' and status='Active'";
                    ResultSet resultset_val = statement.executeQuery(sql_query);
                    if (resultset_val.next()) {
                        warning = "yes";
                    }
                    String sql_query1 = "select * from assessment where category_id='" + category_id + "' and status='Active'";
                    ResultSet resultset_val1 = statement.executeQuery(sql_query1);
                    if (resultset_val1.next()) {
                        warning = "yes";
                    }
                    if (warning.equals("yes")) {
                        throw new Exception();
                    }
                    break;
                }
                case "active_inactive": {
                    String category_id = request.getParameter("category_id");
                    String status = request.getParameter("status");
                    String sql = "Update course_category set status='" + status + "',last_updated_user='" + request.getSession(false).getAttribute("log_user_id") + "',last_updated_ts='" + ist.getLastUpdatedts() + "' where category_id='" + category_id + "'";
                    int i = statement.executeUpdate(sql);
                    if (i > 0) {
                    }
                    connection.commit();
                    break;
                }
                default:
                    break;
            }
        } catch (Exception ex) {
            Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
            try {
                response.setStatus(400);
                if (!connection.isClosed()) {
                    connection.rollback();
                }
            } catch (SQLException ex1) {
                Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex1);
            }
        } finally {
            try {
                out.flush();
                out.close();
                if (!statement.isClosed()) {
                    statement.close();
                }
                DBConnection.closeConnection();
                connection=null;
            } catch (SQLException ex) {
                Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
        try {
            connectToServer();
            switch (request.getParameter("mode")) {
                case "add": {
                    String category_name = request.getParameter("category_name");
                    String category_desc = request.getParameter("category_desc");
                    String status = request.getParameter("status");
                    String sql = "INSERT INTO course_category(category_name,category_desc,status,created_user,created_ts,last_updated_user,last_updated_ts) VALUES ('" + category_name + "',  '" + category_desc + "','" + status + "','" + request.getSession(false).getAttribute("log_user_id") + "','" + ist.getLastUpdatedts() + "','" + request.getSession(false).getAttribute("log_user_id") + "','" + ist.getLastUpdatedts() + "')";
                    statement.executeUpdate(sql);
                    connection.commit();
                    break;
                }
                case "edit": {
                    String category_id = request.getParameter("category_id");
                    String category_name = request.getParameter("category_name");
                    String category_desc = request.getParameter("category_desc");
                    String sql = "update course_category set category_name='" + category_name + "',category_desc='" + category_desc + "',last_updated_user='" + request.getSession(false).getAttribute("log_user_id") + "',last_updated_ts='" + ist.getLastUpdatedts() + "' where category_id='" + category_id + "'";
                    statement.executeUpdate(sql);
                    connection.commit();
                    break;
                }
                default:
                    break;
            }
        } catch (Exception ex) {
            try {
                Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
                if (ex instanceof SQLIntegrityConstraintViolationException) {
                    response.setStatus(1);
                }else{
                    response.setStatus(2);
                }
                if (!connection.isClosed()) {
                    connection.rollback();
                }
            } catch (SQLException ex1) {
                Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex1);
            }
        } finally {
            try {
                out.flush();
                out.close();
                if (!statement.isClosed()) {
                    statement.close();
                }
                DBConnection.closeConnection();
                connection=null;
            } catch (SQLException ex) {
                Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    @Override
    public String getServletInfo() {
        return "Short description";
    }
}

标签： jspservletsjdbcjndi

解决方案

你忘了关闭连接

使用 finally 语句关闭池连接。以下 finally 块将出现在适用于使用池连接的代码的 try/catch 块之后：

try {
     Connection con = 
  ds.getConnection(username, password);
     // ... code to use the pooled
     // connection con
 } catch (Exception ex {
     // ... code to handle exceptions
 } finally {
     if (con != null) con.close();
 }

您的数据源已达到定义的最大连接数。

推荐阅读