too many connection error in tomcat 7 with Mysql

Question

I'm getting the below "too many connection " error, even though, I'm sure I have closed connection in my code.

I'm using tomcat 7 with MySQL, i didn't configure connection pooling, unless tomcat using connection pooling by default.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1128)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2336)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2369)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2153)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor42.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
    at java.sql.DriverManager.getConnection(DriverManager.java:579)
    at java.sql.DriverManager.getConnection(DriverManager.java:243)

in my code I close the connection like this.

 private void insertIntoDB()
    {  
        /**
         * THIS JUST a WORKAROUND  ... REMOVE later
         */
        if(token==null || token.equals(""))
        {
            return; 
        }
        //System.out.println("Inserting values in Mysql database table!"); 
        Connection connect = null;
        PreparedStatement preparedStatement = null;

        try{ 
            // This will load the MySQL driver, each DB has its own driver
                        Class.forName("com.mysql.jdbc.Driver");
                        // Setup the connection with the DB
                        connect = DriverManager
                                .getConnection("jdbc:mysql://127.0.0.1:3306/dbname?"
                                        + "user=dbuser&password=xxxxxxxx");




                preparedStatement = connect
                        .prepareStatement("INSERT INTO location_V2_android  (udid, token, lat , lng,update_time) VALUES "
                                                      +"          (?,               ?,        ?,  ?,   NOW() )"
                                        );

                preparedStatement.setString(1, udid);
                preparedStatement.setString(2,  token);
                preparedStatement.setDouble(3,lat);
                preparedStatement.setDouble(4, lng);

                preparedStatement.executeUpdate();

                //preparedStatement.close();

            } 
            catch (SQLException s)
            {
                System.out.println("SQL statement is not executed! UpdateLocations V2 updateUserLocationinDB"+ s.toString()); 
            } 
         catch (Exception e)
            {  e.printStackTrace();  } 
           finally {
               // Close database resources
               try {
                   if (preparedStatement != null) {
                       preparedStatement.close();
                   }
               } catch (SQLException e) {
                   System.out.println("Cleanup failed to close Statement.");
               }

               try {
                   if (connect != null) {
                       connect.close();
                   }
               } catch (SQLException e) {
                   System.out.println("Cleanup failed to close Connection.");
               }

               }



    }

Answer 1

At first I would "cache" the preparedStatement in the code and not always build it new.

An other problem could be that the Driver with the JDBC-Connection-Pool of Tomcat are caching to much.

Try to add the parameter-setting "cachePrepStmts=false" to the connection-url. Caching should be done in your code afterwards.