Datasource in jdbc connection pooling not returning connection

Question

I have a jsp web application. I'm using jdbc connection pooling to use mysql database located at the server side. My web application hangs sometimes, when i checked debugging i found that it hangs at ds.getConnection(); It does not return any exception/error. Browser will be simply in loading state. I have included my DBConnection class which i use to create dadasource and getconnection from datasource.I'm calling getConnection(); method to get connection from pool. I have called closeConnection(); method in all jsp finally block to close connection. So i assume that the connection will be returned to pool once i close the connection

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);
        }
    }
}
}

Context.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>

Example servlet to show how i use the connection

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";
    }
}

Please show actual code. Just having `if (con != null) con.close()` somewhere in no way guarantees proper closing of connections when exceptions occur. — Mark Rotteveel, Jan 17 '19 at 16:15
@Mark Rotteveel I have edited my question with proper code please check. — Vinod, Jan 18 '19 at 05:04

score 0 · Answer 1 · answered Jan 17 '19 at 11:11

0

You forgot to close the connection

Use a finally statement to close a pooled connection. The following finally block would appear after the try/catch block that applies to the code in which the pooled connection was used:
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();
 }

Your datasource reached the maximum connection defined.

answered Jan 17 '19 at 11:11

Ori Marko

56,308
23
131
233

All statements and connections are closed properly.I already have this code @user7294900 – Vinod Jan 17 '19 at 11:16
It's probably a leak.see https://stackoverflow.com/questions/4087559/dbcp-tomcat-connection-pool-leak – Ori Marko Jan 17 '19 at 11:22
Even if i increase max connection this happens.i checked whole app connections is closed properly. I'm only accessing one page of the web app @user7294900 – Vinod Jan 17 '19 at 11:37
@Vinod actually the link advice to lower maxActive – Ori Marko Jan 17 '19 at 11:40
Better yet, use try-with-resources, it avoids the null checking boiler plate (in addition, the example you show won't even compile). – Mark Rotteveel Jan 17 '19 at 16:14
@user7294900 I have edited my question with proper code please check. – Vinod Jan 18 '19 at 05:04

Mark Rotteveel · Answer 2 · 2019-01-18T11:45:10.913

0

Your code seems to be one big race condition. Only one servlet instance is used for multiple requests. As a result, on concurrent requests, your current code can and will leak connections.

When concurrent requests are executed, each of them will create a connection and assign it to the same instance variable, so one or more connections will be lost and remain open. The use of that DBConnection.getConnection/DBConnection.closeConnection suggests that you are potentially leaking connections there as well.

Please stop using fields to keep your connection and statement, and make these local variables instead. Also consider using try-with-resources to properly close connections, and consider using a DataSource directly instead of using that (probably unnecessary) abstraction of DBConnection.

edited Jan 18 '19 at 11:45

answered Jan 18 '19 at 09:13

Mark Rotteveel

100,966
191
140
197

Whenever i call getConnection() method. the datasource will return a connection from pool because i have a line con = ds.getConnection(); So in case of concurrent users accessing web app. Each user will have their own instance of static class. isn't it? And each time when i close the connection that connection will be closed. Can you clearly explain me the problem? – Vinod Jan 18 '19 at 11:07
@Vinod No they won't. They will all share the same class, so probably your code is leaking connections all over the place. – Mark Rotteveel Jan 18 '19 at 11:40

Datasource in jdbc connection pooling not returning connection

2 Answers2

Linked

Related