SQL connections dangling: Where am I not correctly closing up connections correctly?

Question

I am building a basic java application to load some files into a mysql database. I am able to load the files up and populate my tables without any problems. However after speaking to someone who reviewed my code, I am apparently not correctly closing my connections and wasting resources. Where am I not closing up the connections? Have I done this incorrectly?

I am using the try-with-resources construct within my DbSinger class to execute prepared statements to my database, which should automatically close the connection so long as the AutoCloseable interface is implemented, which it is in the parent class of Db. The close() method however is never reached. The DbSinger is instantiated inside my main() and then runs it's single method populateSingers() with an ArrayList of Singer objects.

Connection Class

public class SQLConnection {
    private static final String servername = "localhost";
    private static final int port = 3306;
    private static final String user = "ng_user";
    private static final String pass = "ng";
    private static final String db = "ng_music";
    private static final String connectionString = "jdbc:mysql://" + servername + ":" + port + "/" + db;

    public Connection provide() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            return DriverManager.getConnection(connectionString, user, pass);

        }
        catch (SQLException | ClassNotFoundException e) {
            throw new SQLConnectionException(e);
        }
    }

    public class SQLConnectionException extends RuntimeException {
        SQLConnectionException(Exception e) {super(e);}
    }
}

Abstract parent class

public abstract class Db implements AutoCloseable{
    private Connection connection;

    Db() {
        SQLConnection sqlC = new SQLConnection();
        this.connection = sqlC.provide();
    }

    @Override
    public synchronized void close() throws SQLException {
        if(connection != null) {
            connection.close();
            connection = null;
            System.out.println("Connection closed");
        }
    }
    Connection getConnection() {
        return connection;

    }
    boolean checkIfPopulated(String query){
        try {
            PreparedStatement ps = getConnection().prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            return !rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
}

Concrete class to execute queries to database for singers table

public class DbSinger extends Db {
    public DbSinger() {
        super();
    }

    public void populateSingers(ArrayList<Singer> singers) {
        String populateSingersQuery = "insert into ng_singers(name, dob, sex) values(?,?,?)";
        if(!checkIfPopulated("select * from ng_singers")){
            System.out.println("Singer Table is already populated");
            return;
        }
        try (PreparedStatement ps = getConnection().prepareStatement(populateSingersQuery)) {
            for (Singer s : singers) {
                ps.setString(1, s.getName());
                ps.setDate(2, java.sql.Date.valueOf(s.getDob()));
                ps.setString(3, s.getSex());
                ps.addBatch();
            }
            ps.executeBatch();
            System.out.println("Singers added to table");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

My code is able to execute is able to run fine and does what it needs to, but I want to understand why and where I am not closing connections, and to understand how I can resolve this. Or at least understand if I am approaching this wrong.

Answer 1

In your case, you need to instantiate DBSinger class in try-with-resources statement to close the underlying connection.

Instead of doing:

DbSinger dbSinger = new DbSinger();

You need to do:

try (DbSinger dbSinger = new DbSinger()) {
// Your other code
}

This way the close() method you are overriding in your Db class will be called automatically.

Also, close the preparedStatement you created in your checkIfPopulated method by:

try (PreparedStatement ps = getConnection().prepareStatement(query)) {
// Other codes
}