"Too Many Connections" Java + Msql

Question

Hello, I have 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 amount 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 is below. I have no idea what I have to do to prevent this error from occurring, since I am closing Statement, Resultset and the connection in each query. Could you help me with that? Thank you.

 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();
    }
}

Connection class with seat.

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 1

Hello,

Looking only at the method, it is correct. You make the query and close the ResultSet and PreparedStatement then. I would just put the closed() in the finally, along with the connection.

As the staff says in the comments.. The problem is you call it "300" times.. pq when we ask to do a closed on the objects, this closed is not immediate. It may take a few milliseconds, and in the middle, you keep bombing the base for more connections.

The solution would be to use the same connection, so try decreasing the calls to getConnection... do something like maintain a single connection and use this 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 at the base, this usually makes it very clear what happens.

It is wrong to leave it so.. but just for testing, comment on the cx.close(), Even if the connection is open all the time, you can test and confirm where the problem is.. then try to leave out of the for that step.