Connections Pool opens connections but does not close after!

Question

I am facing some problems with my postgresql connection pool with java, in which it is called by my Daos methods quietly, but does not close after the method runs, And that really gets in the way of me doing some research and the bank is full of connections!

I’ll send my pool and one of the Daos:

package util;
import java.sql.Connection;
import java.sql.SQLException;
import org.postgresql.ds.PGConnectionPoolDataSource;

public class ConnectionFactory {
public static Connection connection;
private static javax.sql.ConnectionPoolDataSource dataSource;

private static void createConnectionPool(){        

    PGConnectionPoolDataSource pool = new PGConnectionPoolDataSource();
    pool.setUrl("jdbc:postgresql://localhost:5432/comercio_bd");
    pool.setUser("postgres");
    pool.setPassword("");
    pool.setPortNumber(5432);
    pool.setDatabaseName("comercio_bd");
    dataSource = pool;
}


public static Connection getConnection() throws SQLException{        

    if(dataSource == null){
        createConnectionPool();
    }

    if(connection == null || connection.isClosed()){
        //connection = DriverManager.getConnection("jdbc:postgresql://localhost/alura", "postgres", "postgres");            
        connection = dataSource.getPooledConnection().getConnection();
        //connection.setAutoCommit(false);
    }

    return connection;

}


public void Close(){



} }

I’ll send the User DAO:

    package DAO;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException; 
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Vector;
    import javax.swing.JOptionPane;
    import model.Funcionario;
    import model.Usuario;
    import util.ConnectionFactory;
    import view.TelaDeCadastro;
    import view.TelaPesquisas;
public class UsuarioDAO {
private Connection con = ConnectionFactory.connection;

public UsuarioDAO() throws Exception {

    try {
        con = ConnectionFactory.getConnection();
    } catch (Exception e) {
        throw new Exception(e.getMessage());
    }

}

public void Salvar(Usuario u) throws Exception {

    PreparedStatement ps = null;

    if (u == null) {
        throw new Exception("Erro: Usuario não pode ser nulo!");
    }

    try {

        try {

            String sql = "insert into tbl_usuario (id_usuario, login_usuario, senha_usuario, fk_funcionario, flag_ativo)"
                    + "values (NEXTVAL('sequencia_usuario'),?,?,?,1)";

            //con = ConnectionFactory.getInstance().getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, u.getLoginUsuario());
            ps.setString(2, u.getSenhaUsuario());
            ps.setInt(3, u.getFuncionarioUsuario().getIdFuncionário());

            ps.executeUpdate();

            ps.close();

        } catch (Exception e) {
            throw new Exception("Erro ao inserir os dados!" + e.getMessage());
        }
    } finally {
       try { con.close(); } catch (SQLException e) {JOptionPane.showMessageDialog(null, "Erro ao fachar conexão:"+e.getMessage(), "Ops!", JOptionPane.ERROR_MESSAGE);}
    }

}

public ArrayList<Usuario> ConsultaU() throws Exception {
    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<Usuario> listaU = new ArrayList<>();
    try {

        try {
            //con = ConnectionFactory.getInstance().getConnection();
            TelaPesquisas tela = new TelaPesquisas();

            String sql = "SELECT usu.login_usuario, usu.senha_usuario, func.nome_funcionario, func.sobrenome_funcionario "
                    + "FROM tbl_usuario usu "
                    + "INNER JOIN tbl_funcionario func ON "
                    + "usu.fk_funcionario = func.id_funcionario "
                    + "WHERE usu.flag_ativo = 1 "
                    + "ORDER BY usu.login_usuario";

            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Usuario u = new Usuario();
                Funcionario f = new Funcionario();

                u.setLoginUsuario(rs.getString("login_usuario"));
                u.setSenhaUsuario(rs.getString("senha_usuario"));

                f.setNomeFuncionario(rs.getString("nome_funcionario"));
                f.setSobrenomeFuncionario(rs.getString("sobrenome_funcionario"));

                u.setFuncionarioUsuario(f);

                listaU.add(u);
            }
             ps.close();
             rs.close();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Erro ao buscar os dados dos Usuários!" + e.toString(), "Ops!", JOptionPane.ERROR_MESSAGE);
        }
    } finally {
        try { con.close(); } catch (SQLException e) {JOptionPane.showMessageDialog(null, "Erro ao fachar conexão:"+e.getMessage(), "Ops!", JOptionPane.ERROR_MESSAGE);}
    }
    return listaU;
}

public ArrayList<Usuario> ListarUsuarioNome(String login) throws Exception {

    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<Usuario> ListaU = new ArrayList<>();

    try {

        try {
            String sql = "SELECT usu.login_usuario, usu.senha_usuario, func.nome_funcionario, func.sobrenome_funcionario "
                    + "FROM tbl_usuario usu "
                    + "INNER JOIN tbl_funcionario func ON "
                    + "usu.fk_funcionario = func.id_funcionario "
                    + "WHERE usu.flag_ativo = 1 and usu.login_usuario LIKE '%" + login + "%' "
                    + "ORDER BY func.nome_funcionario";
            //con = ConnectionFactory.getInstance().getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Usuario u = new Usuario();
                Funcionario f = new Funcionario();

                u.setLoginUsuario(rs.getString("login_usuario"));
                u.setSenhaUsuario(rs.getString("senha_usuario"));

                f.setNomeFuncionario(rs.getString("nome_funcionario"));
                f.setSobrenomeFuncionario(rs.getString("sobrenome_funcionario"));

                u.setFuncionarioUsuario(f);

                ListaU.add(u);
            }
            ps.close();
            rs.close();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Erro ao buscar os dados dos Usuários!" + e.toString(), "Ops!", JOptionPane.ERROR_MESSAGE);
        }
    } finally {
        try { con.close(); } catch (SQLException e) {JOptionPane.showMessageDialog(null, "Erro ao fachar conexão:"+e.getMessage(), "Ops!", JOptionPane.ERROR_MESSAGE);}
        System.out.println("Conexão ConsultaUsuariologin Fechada");
    }
    return ListaU;

}

public ArrayList<Usuario> ListarUsuarioCod(int cod) throws Exception {

    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<Usuario> ListaU = new ArrayList<>();

    try {

        try {
            String sql = "SELECT usu.id_usuario, usu.login_usuario, usu.senha_usuario, func.nome_funcionario, func.sobrenome_funcionario "
                    + "FROM tbl_usuario usu "
                    + "INNER JOIN tbl_funcionario func ON "
                    + "usu.fk_funcionario = func.id_funcionario "
                    + "WHERE usu.flag_ativo = 1 and usu.id_usuario = " + cod
                    + "ORDER BY func.nome_funcionario";
            //con = ConnectionFactory.getInstance().getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Usuario u = new Usuario();
                Funcionario f = new Funcionario();

                u.setLoginUsuario(rs.getString("login_usuario"));
                u.setSenhaUsuario(rs.getString("senha_usuario"));

                f.setNomeFuncionario(rs.getString("nome_funcionario"));
                f.setSobrenomeFuncionario(rs.getString("sobrenome_funcionario"));

                u.setFuncionarioUsuario(f);

                ListaU.add(u);
            }
            ps.close();
            rs.close();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Erro ao buscar os dados dos Usuários!" + e.toString(), "Ops!", JOptionPane.ERROR_MESSAGE);
        }
    } finally {
        try { con.close(); } catch (SQLException e) {JOptionPane.showMessageDialog(null, "Erro ao fachar conexão:"+e.getMessage(), "Ops!", JOptionPane.ERROR_MESSAGE);}
        System.out.println("Conexão ConsultaUsuarioCod Fechada");
    }
    return ListaU;

}

public ArrayList<Usuario> ListarUsuarioFunc(String func) throws Exception {

    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<Usuario> ListaU = new ArrayList<>();

    try {

        try {
            String sql = "SELECT usu.login_usuario, usu.senha_usuario, func.nome_funcionario, func.sobrenome_funcionario "
                    + "FROM tbl_usuario usu "
                    + "INNER JOIN tbl_funcionario func ON "
                    + "usu.fk_funcionario = func.id_funcionario "
                    + "WHERE usu.flag_ativo = 1 and func.nome_funcionario LIKE '%" + func + "%'"
                    + "ORDER BY func.nome_funcionario";
            //con = ConnectionFactory.getInstance().getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Usuario u = new Usuario();
                Funcionario f = new Funcionario();

                u.setLoginUsuario(rs.getString("login_usuario"));
                u.setSenhaUsuario(rs.getString("senha_usuario"));

                f.setNomeFuncionario(rs.getString("nome_funcionario"));
                f.setSobrenomeFuncionario(rs.getString("sobrenome_funcionario"));

                u.setFuncionarioUsuario(f);

                ListaU.add(u);
            }
            ps.close();
            rs.close();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Erro ao buscar os dados dos Usuários!" + e.toString(), "Ops!", JOptionPane.ERROR_MESSAGE);
        }
    } finally {
        try { con.close(); } catch (SQLException e) {JOptionPane.showMessageDialog(null, "Erro ao fachar conexão:"+e.getMessage(), "Ops!", JOptionPane.ERROR_MESSAGE);}
    }
    return ListaU;

}}

I put it to him to pass a Sout to check if it was arriving in Finally, and was, follows an image:

It runs Finally which closes the connection, but does not return it to the pool, follows a picture of my command select * from pg_stat_actvity:

Answer 1

I was able to solve it! I had to totally change my pool, but now it’s not generating a connection on top of another one! Follow the new code:

package util;
import java.sql.Connection;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class ConnectionFactory {

static final BasicDataSource bds = new BasicDataSource();
static Connection conn;

static{

    bds.setDriverClassName("org.postgresql.Driver");
    bds.setUrl("jdbc:postgresql://localhost:5432/comercio_bd");
    bds.setUsername("postgres");
    bds.setPassword("");
    bds.setMaxIdle(20);

}

public static DataSource getDataSource() {
    return bds;
}}

I switched from pool to DBCP, it was simple and fast, not even need xml file to configure, it does everything alone practically! To add to the DAO, just do so:

package DAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import model.Funcionario;
import model.Usuario;
import util.ConnectionFactory;
import view.TelaPesquisas;
public class UsuarioDAO {

private Connection con;
// no construtor da classe:
public UsuarioDAO() throws Exception {

    try {
        con = ConnectionFactory.getDataSource().getConnection();
    } catch (Exception e) {
        throw new Exception(e.getMessage());
    }

}}