Database Poller : MySQLNonTransientConnectionExcep

问题:

I have written a WebService which is responsible to poll DB every 3 seconds and send the Latest Data

Unfortunately i am getting this exception in my console after some time .

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.<init>(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)

This is my program

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<Orders> newOrdersList = new ArrayList<Orders>();  
        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;
    }

}

回答1:

Note: this has been answered numerous times.

You need to close your jdbc Connection, PreparedStatement, and ResultSet objects. This is most easily achieved by adding a finally block to the end of your try / catch blocks wrapping your calls to getConnection and prepareStatement:

try {
    connection = DriverManager.getConnection(...)
    ...
} catch ...
...
} finally {
    // Each of these needs wrapped in a try / catch block
    if(resultSet != null) { resultSet.close() }
    if(preparedStatement != null) { preparedStatement.close() }
    if(connection != null) { connection.close() }
}

When working with JDBC, I'll usually write helper methods to handle the closing tasks. By placing them in a finally, you ensure they are always executed (whether you get an exception or return from within the try block.

回答2:

You can make use of try with resources feature that will take care of closing the connection

try(Connection con=DriverManager.getConnection()){
//sql queries
}
catch(SQLException ex){
//error handling code
}