java - 没有可用的缓冲区空间，已达到最大连接数

Question

package Simple;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.text.*;

public class CurrentProg {
    //connecting to the database

  private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
  private static final String DB_CONNECTION ="jdbc:mysql://localhost:3306/db?autoReconnect=true"; 
  private static final String DB_USER = "root";
  private static final String DB_PASSWORD = "root";
  static Connection dbConnection = null;
  static Statement statement = null;

  static int total=1;

  //Searching between startdate and enddate

  public static java.util.LinkedList searchBetweenDates(java.util.Date startDate, java.util.Date endDate) {

        java.util.Date begin = new Date(startDate.getTime());
        java.util.LinkedList list = new java.util.LinkedList();
        list.add(new Date(begin.getTime()));
        java.util.Date end = new Date(endDate.getTime());
        endDate.setTime(endDate.getTime() + 24*3600*1000);
        Calendar cal = Calendar.getInstance();
        cal.setTime(begin);
       dbConnection = getDBConnection();

        while(begin.compareTo(endDate)<0){
        begin = new Date(begin.getTime() + 86400000);
        list.add(new Date(begin.getTime()));        
        Timestamp timestamp = new Timestamp(new Date().getTime());

        //For a single day calculation: 24hours*60mins=1440 /2 (2 mins time difference as per the requirement) = 720 

                for (int j = 0; j < 720; j++) {
                cal.add(Calendar.MINUTE, 2);            
                timestamp = new Timestamp(cal.getTime().getTime());
                String S = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timestamp);               
                String[] parts = S.split(" ");
                String date=parts[0];
                String time=parts[1];
                cal.getTime().toString();

           // To create data loop into a List
            List<String> records = new ArrayList<String>();
            StringBuffer record = new StringBuffer();
            for (int i = 1; i <= total; i++) {
            records = new ArrayList<String>(total);         

            int a2 = 220 + j % 31; // 230 - 244 by 1
            String wString = Integer.toString(a2);
            String a = String.valueOf(a2);   
            double b2 = 0.00 + j % 3.7  ; // 1.3 - 3.9 by 0.1 
            String aString = Double.toString(b2);
            String b = String.valueOf(b2);
            b = b.substring(0, Math.min(b.length(), 5));


          record.delete(0, record.length());
          record.append(a + "," + b  +  ",'"+  date + "', '"+  time + "'");
          record.append("\t\t");      
          record.append("\n");
          records.add(record.toString()); 

          //Insert Query
          String insertTableSQL = "INSERT INTO cmd1"
                  + "(a, b, date, time) " + "VALUES"
                  + "("+record.toString()+")";
                  System.out.println("insertTableSQL - " + insertTableSQL);             //  Statement.executeUpdate(insertTableSQL);

          try {

                  statement = dbConnection.createStatement();
                  statement.executeUpdate(insertTableSQL);
                  System.out.println("Record is inserted into Db table!");
                  } catch (SQLException e) {
                  System.out.println(e.getMessage());
                  } 
      try {

            //  dbConnection = getDBConnection();
              statement = dbConnection.createStatement();
              statement.executeUpdate(insertTableSQL);
              System.out.println("Record is inserted into Db table!");
              } catch (SQLException e) {
              System.out.println(e.getMessage());
              } 
        finally {
        //  httpPost.releaseConnection()
            try{
                 if(statement!=null)
                    statement.close();
              }
             finally{
             }

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

              }

              }                                            
         }
        }
        }
        return list;
        }

  @SuppressWarnings("unused")
public static void main(String[] args) throws Exception {

      //To enter startDate and enddate
    //  EntityManagerFactory.getCache().evictAll;


    SimpleDateFormat  startDate=new java.text.SimpleDateFormat("yyyy-MM-dd");
    SimpleDateFormat  endDate=new java.text.SimpleDateFormat("yyyy-MM-dd");

                java.util.LinkedList hitList = searchBetweenDates(
                startDate.parse("2016-01-01"),
                endDate.parse("2016-03-01"));
                String[] combo = new String[hitList.size()];
                for(int i=0; i<hitList.size(); i++)
                combo[i] = new java.text.SimpleDateFormat("yyyy-MM-dd").format(((java.util.Date)hitList.get(i)));

  }

private static void insertRecordIntodb() {
    //
  }
  private static Connection getDBConnection() {
    Connection dbConnection = null;
    try {
      Class.forName(DB_DRIVER);
    } catch (ClassNotFoundException e) {
      System.out.println(e.getMessage());
    }
    try {
      dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
      return dbConnection;
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
    return dbConnection;
  }  
} 

事务期间服务器连接失败。由于底层异常:“java.net.SocketException:java.net.SocketException:没有可用的缓冲区空间(达到最大连接数？):连接”。

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: java.net.SocketException: No buffer space available (maximum connections reached?): connect

STACKTRACE:

java.net.SocketException: java.net.SocketException: No buffer space available (maximum connections reached?): connect
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:276)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2717)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1509)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at Simple.CurrentProg.getDBConnection(CurrentProg.java:186)
    at Simple.CurrentProg.searchBetweenDates(CurrentProg.java:126)
    at Simple.CurrentProg.main(CurrentProg.java:164)


** END NESTED EXCEPTION **


Attempted reconnect 3 times. Giving up.
Exception in thread "main" java.lang.NullPointerException
    at Simple.CurrentProg.searchBetweenDates(CurrentProg.java:137)
    at Simple.CurrentProg.main(CurrentProg.java:164)

这里我尝试将java程序与数据库连接，但是当我尝试插入大数据(例如1个月)时，它只能获取16000条记录，不超过我希望数据应该按照给定的日期范围插入什么我应该做什么才能得到那个。在堆栈跟踪中，它显示异常，因为没有达到可用的最大连接缓冲区空间。提前致谢

Answer 1

这是修改后的代码。我删除了很多不必要的代码:

public static LinkedList<Date> searchBetweenDates(Date startDate, Date endDate) throws SQLException {
    Date begin = new Date(startDate.getTime());
    LinkedList<Date> list = new LinkedList<Date>();

    list.add(new Date(begin.getTime()));

    endDate.setTime(endDate.getTime() + 24 * 3600 * 1000);
    Calendar cal = Calendar.getInstance();
    cal.setTime(begin);

    dbConnection = getDBConnection();
    PreparedStatement ps = dbConnection.prepareStatement("INSERT INTO cmd1(aaaa, bbbb, datee, timee) VALUES(?, ?, ?, ?)");
    while (begin.compareTo(endDate) < 0) {
        begin = new Date(begin.getTime() + 86400000);
        list.add(new Date(begin.getTime()));
        Timestamp timestamp = new Timestamp(new Date().getTime());

        // For a single day calculation: 24hours*60mins=1440 /2 (2 mins time
        // difference as per the requirement) = 720

        for (int j = 0; j < 720; j++) {
            cal.add(Calendar.MINUTE, 2);
            timestamp = new Timestamp(cal.getTime().getTime());
            String S = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timestamp);
            String[] parts = S.split(" ");
            String date = parts[0];
            String time = parts[1];
            cal.getTime().toString();

            // To create data loop into a List
            for (int i = 1; i <= total; i++) {

                int a2 = 220 + j % 31; // 230 - 244 by 1
                String a = String.valueOf(a2);
                double b2 = 0.00 + j % 3.7; // 1.3 - 3.9 by 0.1
                String b = String.valueOf(b2);
                b = b.substring(0, Math.min(b.length(), 5));

                ps.setString(1, a);
                ps.setString(2, b);
                ps.setString(3, date);
                ps.setString(4, time);

                ps.execute();
            }
        }
    }
    if (ps != null)
        ps.close();
    if (dbConnection != null)
        dbConnection.close();

    return list;
}

我改变了什么:

• 我删除了您的 try/catch，因为我想让代码简短，以便我可以轻松地在此处进行编辑。但你应该正确处理异常。 永远不要接受异常。我的意思是以下内容不行:

  } catch (SQLException e) {
      System.out.println(e.getMessage());
  } 
  

这是一个非常糟糕的想法，因为在这种情况下你不会看到问题的真正原因；至少要执行e.printStackTrace()，尽管在​​实际项目中不推荐这样做。

• 我将 Statement 对象替换为 PreparedStatement，因为它效率更高。
• 删除了包名称并改为放置 import 语句，因为没有必要这样做，除非您有来自不同包的具有相同名称的不同类。
• 我更改了列名称，因为我的数据库不想接受它们。像 a 这样的列名非常糟糕。选择描述性名称，以便几个月后您可以理解它是什么。不要使用像 date 这样的列名，因为它们是我所知道的某些数据库系统的保留字。
• 不要在循环中创建连接等数据库资源，除非绝对需要!否则，你的程序将会耗尽资源，因为这些资源非常昂贵!这正是您现在所经历的。

希望对您有帮助，否则请给我评论。