“Too many connections” Java + Msql

Question:

Hello, I have the following problem, I have a function that searches the database and returns an object. I have a "for" function that calls this method almost 300 times, due to this number of calls mysql is returning the following error.

Algo deu errado ao fazer uma ação envolvendo formulações. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

The code below. I have no idea what I can do to prevent this error from occurring as I'm closing the Statement, Resultset and the connection in each query. Could you help me with this? Thanks.

 private Formulacao retornaFormulacaoDoItem(Item item) throws Exception {
    Connection cx = br.com.projeto.Conexao.Conexao.abrir();
    try {

        String comando = "SELECT * FROM Formulacao WHERE fk_item = ?";
        PreparedStatement ps = cx.prepareStatement(comando);
        ps.setInt(1, item.getIdItem());
        ResultSet rs = ps.executeQuery();
        Formulacao f = new Formulacao();
        ArrayList list = new ArrayList();
        NutrienteDAO nutri = new NutrienteDAO();
        int i = 0;
        while (rs.next()) {
            if (rs.getString("operador") != null) {
                list.add(rs.getString("operador"));
            } else if (rs.getString("fk_nutriente") != null) {
                list.add(rs.getString("fk_nutriente"));
            } else {
                list.add(nutri.retornaUmNutriente(rs.getInt("fk_nutriente")).getQuantidade());
            }
        }
        String[] re = new String[list.size()];
        for (int j = 0; j < list.size(); j++) {
            re[j] = list.get(j).toString();
        }
        f.setCalculo(re);
        ps.close();
        rs.close();
        return f;
    } catch (Exception e) {
        throw new Exception("Algo deu errado ao retornar os dados de formula do item" + e);
    } finally {
        cx.close();
    }
}

Bank connection class.



import java.sql.Connection;
import java.sql.DriverManager;

/**
 *
 * @author jpsa
 */
public class Conexao {
    private static final String USUARIO = "root";
    private static final String SENHA = "123456";
    private static final String URL = "jdbc:mysql://localhost:3306/miner";
    private static final String DRIVER = "com.mysql.jdbc.Driver";

    // Conectar ao banco
    public static Connection abrir() throws Exception {
        // Registrar o driver
        Class.forName(DRIVER);
        // Capturar a conexão
        Connection conn = DriverManager.getConnection(URL, USUARIO, SENHA);
        // Retorna a conexao aberta
        return conn;
    }
}


Answer:

Hi,

Just looking at the method is correct. You query and close the ResultSet and PreparedStatement next. I would just put the closed() on the finally , along with the connection.

As people say in the comments… The problem is that you call this "300" times… because when we ask to close objects, this closed is not immediate. It can take a few milliseconds, and in the meantime, you keep bombarding the base asking for more connections.

The solution would be to use the same connection, so try decreasing the getConnection calls… do something like keep a single connection and use that to make the 300 calls:

public static Connection abrir() throws Exception {
    Connection conn = null;
    if (conn != null) {
      Class.forName(DRIVER);
      conn = DriverManager.getConnection(URL, USUARIO, SENHA);
    }
    return conn;
}

A test I usually do is to monitor the existing connections in the base, this usually makes it very clear what happens.

It's wrong to leave it that way… but just for testing, comment out cx.close() , even if the connection stays open all the time, you can test and confirm where the problem is… then try to leave out this step.

Scroll to Top
AllEscort