java - tomcat 7 与 Mysql 的连接错误太多

Question

我收到下面的“太多连接”错误，尽管我确信我已经在我的代码中关闭了连接。

我在 MySQL 中使用 tomcat 7，我没有配置连接池，除非 tomcat 默认使用连接池。

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)

在我的代码中，我像这样关闭连接。

 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

起初我会在代码中“缓存”preparedStatement，并不总是新建它。

另一个问题可能是带有 Tomcat 的 JDBC-Connection-Pool 的驱动程序缓存过多。

尝试将参数设置 "cachePrepStmts=false" 添加到连接 url。之后应在您的代码中进行缓存。