How to properly close MySql database connection

Question

In my web application I have separate classes DataAccess.java and DBUtils.java.

I have following code in DBUtils.jva

public static PreparedStatement getPreparedStatement(String sql) throws ClassNotFoundException, SQLException{
    PreparedStatement ps = null;
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/company";
    String user = "root";
    String pass = "root";

    Connection con = DriverManager.getConnection(url, user, pass);
    ps = con.prepareStatement(sql);       

    return ps;

I have following code in DataAccess.java class

public static List<Company> getAllCompanies(){
    List<Company> ls = new LinkedList<>();

    try {
        String sql = "select * from company";
        ResultSet rs = DBUtils.getPreparedStatement(sql).executeQuery();            
        while (rs.next()){
            Company cp = new Company (rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getTimestamp(9));
            ls.add(cp);
        }
        rs.close();            
    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
    }

    return ls;
}

And I am calling getAllCompanies function from a different servlet. I don't know how to properly close database connection as it is opened in one class and called from another class. My web application keeps crashing due to too many open connections to database, the error says,

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"

Could someone please advise me on how to properly close database connection in this case.

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

you are only closing the resultset

but you are not closing the connection.

in a finally block please do

con.close()

refer this for more info : Closing Database Connections in Java

edited May 23 '17 at 10:32

Community

1
1

answered Nov 23 '16 at 21:29

satish chennupati

2,602
1
18
27

score 0 · Answer 2 · answered Nov 23 '16 at 21:41

Basically, you are NOT closing the database connection objects which is creating the connection leaks. So you need to modify your DBUtil method to getConnection() as shown below.

Always close the resources (connection, preparedstatement and resultset objects) in the finally block as shown below, otherwise it will create the connection leaks and very soon you will run out of connections.

DBUtil getConnection():

public static Connection getConnection() throws ClassNotFoundException, SQLException{
    PreparedStatement ps = null;
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/company";
    String user = "root";
    String pass = "root";

    Connection con = DriverManager.getConnection(url, user, pass);
    return con;
}

getAllCompanies() code:

public static List<Company> getAllCompanies(){
    List<Company> ls = new LinkedList<>();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Connection conn = null;
    try {
        String sql = "select * from company";
         conn = DBUtils.getConnection();
         pstmt = conn.prepareStatement(sql);       
        rs = pstmt.executeQuery();            
        while (rs.next()){
            Company cp = new Company (rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getTimestamp(9));
            ls.add(cp);
        }

    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if(rs != null) {
             rs.close();
        }
        if(pstmt !=null) {
           pstmt.close();
        } 
        if(conn != null) {
           conn.close();
        }
    }
    return ls;
}

Also, I strongly suggest you to create methods like closeConnection() , closeResultSet(), closePreparedStatement() in your DBUtil class in order to avoid the boilerplate code in all methods.

Thank you so much. It makes perfect sense. What would you write in methods like closeConnection() , closeResultSet(), and closePreparedStatement(). As it only takes one line to close them and if I create these methods and DBUtils it would take one line to call them anyway. What do you recommend. — Monauwar, Nov 25 '16 at 02:50

score 0 · Answer 3 · answered Nov 25 '16 at 04:33

I have following code now in DBUtils.java class i have following methods

public static void closeResultSet(ResultSet rs) throws ClassNotFoundException, SQLException {
    if(rs != null) {
        try {
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}   

public static void closePreparedStatement(PreparedStatement ps) throws ClassNotFoundException, SQLException {
    if(ps != null) {
        try {
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}  

public static void closeConnection(Connection conn) throws ClassNotFoundException, SQLException {
    if(conn != null) {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

And in DataAccess.java class I have,

public static List<Company> getAllCompanies(){
    List<Company> ls = new LinkedList<>();
    ResultSet rs = null;
    PreparedStatement ps = null;        
    Connection conn = null;
    try {
        String sql = "select * from company";
        conn = DBUtils.getConnection();
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()){
            Company cp = new Company (rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getTimestamp(9));
            ls.add(cp);
        }
    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
    }finally {
        DBUtils.closeResultSet(rs);
        DBUtils.closePreparedStatement(ps);
        DBUtils.closeConnection(conn);
    }

I have not compiled it yet but I think it should be fine. Thanks again for the help.

How to properly close MySql database connection

3 Answers3