Connection Pool opens connections but does not close later!

Question

Connection Pool opens connections but does not close later!

Navigation

#1 by (0 votes)

0

I'm experiencing some problems with my postgresql connection pool with java, where it's called by my DAO methods quietly, but it does not close after the method is executed, and it gets in the way when I do 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 him to pass a sout to check if it was finally arriving, and it was, follows an image:

ItexecutesFinallythatclosestheconnection,butdoesnotreturnthesametothepool,itfollowsanimageofmycommandselect*frompg_stat_actvity:

java postgresql

asked by anonymous 28.03.2018 / 19:51

1 answer

REDIRECT APP ADDRESS FOR AZURE Remove information from within an array with angle

score 0 · Answer 1

I managed to solve it! I had to completely change my Pool, but now it is no longer generating a connection on top of the other! Here's 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 changed the pool to DBCP, it was simple and fast, nor does it need an xml file to configure, it does everything by itself practically! To add in 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());
    }

}}