[FIXED] PostgreSQL Close connection after method has finished

Issue

I noticed that I couldn't get access to my PostgreSQL recently. All 20 of my allowed connections were used up. I couldn't understand why as I had nothing open. Seems that things stay open even after PC is shut down or something. I'm using remote real data base working locally and also when I push live its using same database.

Here is a example method that i was wondering if needs closing after used

@RequestMapping("/users")
public String users(Model model) {
    try {
        Connection connection = getConnection();
        Statement stmt = connection.createStatement();
        String sql;
        sql = "SELECT id, first, last, email, company, city FROM cuser";
        ResultSet rs = stmt.executeQuery(sql);
        StringBuffer sb = new StringBuffer();
        List users = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt("id");
            String first = rs.getString("first");
            String last = rs.getString("last");
            String email = rs.getString("email");
            String company = rs.getString("company");
            String city = rs.getString("city");
            users.add(new User(id,first, last, email, company, city));
        }
        model.addAttribute("users", users);
        return "user";
    }catch(Exception e) {return e.toString();}
}

How should I close connections at end of this method

Thanks very much anyone who gets time answer

Solution

Ok there are several issues here.

First is that database connections really need to timeout eventually. Sometimes the timeout gets extended to something ridiculous on account of some report that takes forever or a similar issue, but it's an antipattern. You need the connections to timeout so that when there is a mistake like a stranded connection the database can recover from it.

Second, always close the connection when you're done with it. Here the easiest way is to use try-with-resources, which will make sure your resources get closed in the right order with no chance of exception-masking (where an exception getting thrown by a close causes a previous exception thrown in the try block to get lost, which is bad because it means you don't know why your code failed):

try (Connection connection = getConnection(); 
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sql)) {
    StringBuffer sb = new StringBuffer();
    List users = new ArrayList<>();
    while (rs.next()) {
        int id = rs.getInt("id");
        String first = rs.getString("first");
        String last = rs.getString("last");
        String email = rs.getString("email");
        String company = rs.getString("company");
        String city = rs.getString("city");
        users.add(new User(id,first, last, email, company, city));
    }
    model.addAttribute("users", users);
    return "user";
}

Third, consider using more Spring in your Spring Boot application. You have data access glommed into your controller, so you have no separation of concerns, and you're not taking advantage of any Spring-provided functionality like JDBC templates, with resource-closing, named parameters, data exception translation (so that the exceptions you get back from jdbc calls make sense), or transaction handling (to let you make multiple DAO calls in the same transaction). See this link for spring-jdbc documentation.

Make sure you use a connection pool, by the way; it's much faster to have a request use an existing connection from a pool than it is to make a new connection. When you get a connection from the pool it comes wrapped in an object that returns the connection to the pool and does cleanup (like rolling back any transaction in progress) when you call close on it.

Answered By - Nathan Hughes
Answer Checked By - Pedro (JavaFixing Volunteer)