Error: Data source rejected establishment of connection, message from server: "Too many connections"

Question

I created an application that writes data to the database every 5 minutes.

However after some time this error appears:

Error: Data source rejected establishment of connection, message from server: "Too many connections"

I've been searching around and tells you to close the connection to the database after every request side.

I tried this:

conexao.close();

But it gives me this error:

No operations allowed after conection closed.

I apologize if the question is not well formulated.

Thanks for the help

---------------------What I tried but didn't work---------------------------

Add

finally{ 
    if(conexao!=null)
   conexao.close();
   }

---

  Class.forName("com.mysql.jdbc.Driver");
        Connection conexao = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/bdTeste", "root", "root");
        Statement stm = conexao.createStatement();
        BufferedReader reader = new BufferedReader(new FileReader("C:/Users/RPR1BRG/Desktop/test.txt"));

        String dados[] = new String[6];
        String linha = reader.readLine();

        while (linha != null) {

            StringTokenizer st = new StringTokenizer(linha, ";\"");

            dados[0] = st.nextToken();
            dados[1] = st.nextToken(); 
            dados[2] = st.nextToken();
            dados[3] = st.nextToken();
            dados[4] = st.nextToken();
            dados[5] = st.nextToken();

             DateFormat dateFormat = new SimpleDateFormat("d-M-yy");

    PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement("replace into registos"
    + " (data_registo, hora_registo, IdSensor, Temperatura, Humidade, pt_orvalho) values (?,?,?,?,?,?)");
                    try {
                        stmt.setDate(1, new java.sql.Date(dateFormat.parse(dados[0]).getTime()));
                        stmt.setString(2, dados[1]);
                        stmt.setString(3, dados[2]);
                        stmt.setString(4, dados[3]);
                        stmt.setString(5, dados[4]);
                        stmt.setString(6, dados[5]);

                    } catch (java.text.ParseException ex) {
                        Exceptions.printStackTrace(ex);
                    }


stmt.executeUpdate();

            linha = reader.readLine();
            PrintWriter writer = new PrintWriter("C:/Users/RPR1BRG/Desktop/test.txt"); 
            writer.print("");
            writer.close();
            Verifica();
            }

    } catch (ClassNotFoundException | SQLException | IOException e) {

        System.err.println("Erro: " + e.getMessage());

    }finally{ 
    if(conexao!=null)
   conexao.close();
   }

Solution

This kind of problem arises when you are NOT properly closing the connection after usage.

Please use finally block after catch to close the connections appropriately. This is because to ensure that the connection gets closed properly even when there is an unexpected exception or error. Please note that statements inside finally block gets executed always. it allows the programmer to avoid having cleanup code accidentally bypassed by a return, continue, or break

Note: If the JVM exits while the try or catch code is being executed, then the finally block may not execute. Likewise, if the thread executing the try or catch code is interrupted or killed, the finally block may not execute even though the application as a whole continues.

As you have asked in comment, I have added the code sample to demonstrate practically!

Connection con = null
try{
 //Establishing connection to datasource
 con = DBConnection.getConnection();
 //perform DB operations
 ...
 ...
 ...
}catch(SQLException sqlEx){
 /*To catch any SQLException thrown during DB 
  *Operations and continue processing like sending alert to admin
  *that exception occurred.
  */
}finally{
 /*This block should be added to your code
  * You need to release the resources like connections
  */
 if(con!=null)
  con.close();
}

Please note that the declaration of Connection variable should be in proper scope to close it in finally block.

Hope this helps!

Comments

[strange_098]

Thank for reply. I think I know what you're saying, but that could be me explain in a more practical way please? Thank you very much.

[strange_098]

Once again thank you very much. I try what you said, but give me the same error when past some time. "No operations allowed after conection closed."

[Keerthivasan]

Please post the updated code fully which is relevant

[Keerthivasan]

Please edit your question properly to help. Its not readable.

[Keerthivasan]

Ok, Are you calling the close() method several times?

[Robert Ruxandrescu]

Excellent answer, I completely forgot about the finally block for some reason. This solved my issue.

[dirbacke]

Even if you restart your Tomcat instance, you will still have the same problem afterwords. It is better to write good code instead.