title: Database Poller : MySQLNonTransientConnectionException: Too many connections
body: <p>I have written a WebService which is responsible to poll DB every 3 seconds and send the Latest Data</p>
  
  <p>Unfortunately i am getting this exception in my console after some time . </p>
  
  <pre><code>Connection Failed! Check output console
  com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Too many connections
          at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921)
          at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
          at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)
          at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)
          at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247)
          at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
          at com.mysql.jdbc.Connection.&lt;init&gt;(Connection.java:1555)
          at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
          at java.sql.DriverManager.getConnection(DriverManager.java:579)
          at java.sql.DriverManager.getConnection(DriverManager.java:221)
          at com.serviceees.DBPollerService.getUpdates(DBPollerService.java:47)
          at sun.reflect.GeneratedMethodAccessor51.invoke(Unknown Source)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:601)
          at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:168)
          at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:70)
          at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:279)
          at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:86)
          at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:136)
          at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:74)
          at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1357)
          at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1289)
          at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1239)
          at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1229)
          at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:420)
          at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:497)
          at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:684)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
          at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
          at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
          at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
          at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
          at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
          at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:291)
          at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:877)
          at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:594)
          at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1675)
          at java.lang.Thread.run(Thread.java:722)
  </code></pre>
  
  <p><strong>This is my program</strong> </p>
  
  <pre><code>package com.serviceees;
  
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  
  import javax.ws.rs.GET;
  import javax.ws.rs.Path;
  import javax.ws.rs.Produces;
  
  import com.google.gson.Gson;
  
  import dto.Orders;
  
  @Path("/updates")
  public class DBPollerService {
      @GET
      @Produces("application/json")
      public String getUpdates() {
          String clientResponse = "Error";
          ArrayList&lt;Orders&gt; newOrdersList = new ArrayList&lt;Orders&gt;();  
          Gson gson = new Gson();
          try {
  
              try {
                  Class.forName("com.mysql.jdbc.Driver");
              } catch (ClassNotFoundException e) {
                  e.printStackTrace();
              }
              Connection connection = null;
              PreparedStatement preparedStatement = null;
              String selectSQL = "SELECT *\r\n" + 
              "FROM Orders\r\n" + 
              "INNER JOIN (\r\n" + 
              "    SELECT id, updated_at AS modified from Orders\r\n" + 
              "    UNION\r\n" + 
              "    SELECT id, created_at from Orders \r\n" + 
              ") AS whatever ON whatever.id = Orders.id\r\n" + 
              "ORDER BY whatever.modified DESC;\r\n" + 
              "";
              try {
                  connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "", "");
                  preparedStatement = connection.prepareStatement(selectSQL);
                  while (true) {
  
                      try {
  
  
                          ResultSet rs = preparedStatement.executeQuery(selectSQL);
                          while (rs.next()) {
                              Orders order = new Orders();
                              order.setId(rs.getInt(1));
                              order.setVendorName(rs.getString(2));
                              order.setItem(rs.getString(3));
                              newOrdersList.add(order);
                          }
                          clientResponse =    "jsonCallback(["+gson.toJson(newOrdersList)+"])";
                          Thread.sleep(3000);
                      //  System.out.println("clientResponse"+clientResponse);
                          return clientResponse;
                      } catch (InterruptedException e) {
                          e.printStackTrace();
                      }
                  }
  
              } catch (SQLException e) {
              //  System.out.println("Connection Failed! Check output console");
                  e.printStackTrace();
  
              }
  
          } catch (Exception e) {
              e.printStackTrace();
          }
      //  clientResponse =    gson.toJson(newOrdersList); 
  
          return clientResponse;
      }
  
  }
  </code></pre>