使用 JDBC 的 Java - 连接太多？

Question

作为我最后一年的项目，我正在为一家酒吧编写一个库存补货系统。我可以从 MYSQL 数据库中检索信息，并且可以一次滚动浏览一个结果。

我正在尝试根据所选类别更改结果。我已经设法使用组合框来实现此目的，但在类别之间移动时出现以下错误:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"

两个独立文件的代码如下:

• RetrieveStockQuery 中的 SQL 查询

  public JComboBox getComboBox() throws SQLException {
    con = SQLConnect.getConnection();
    combo = new JComboBox();
    combo.removeAllItems();
    try {
      stat = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
      rs = stat.executeQuery("SELECT categoryName FROM Category");
  
      while (rs.next()) {
        combo.addItem(rs.getString("categoryName"));
        categoryName = rs.getString("categoryName"); 
      }
    }
    catch (SQLException sqle) {
      System.out.println(sqle);
      stat.close();
      con.close();
    }
    return combo;
  }
  
  //----------------------------------------------------------------
  
  public void retrieveStock() throws SQLException { 
  
    con = SQLConnect.getConnection();
    stockGUI = new ViewStockGUI(); // I THINK THIS IS WHAT IS CAUSING THE ERROR   
  
    String viewStock = "SELECT * FROM Stock where categoryName = '" + "'" + stockGUI.selected + "'";
    System.out.println(viewStock);
  
    try {
      stat = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
      rs = stat.executeQuery(viewStock);
  
      while(rs.next()){
        stockID = rs.getInt("stockID");
        stockName = rs.getString("stockName");
        stockDescription = rs.getString("stockDescription");
        stockPrice = rs.getFloat("stockPrice");
        stockQuantity = rs.getInt("stockQuantity");
        categoryName = rs.getString("categoryName");  
  
        ID = Integer.toString(stockID);
        price = Float.toString(stockPrice);
        quantity = Double.toString(stockQuantity);
        stat.close();
        con.close();
  
        System.out.println( "Stock ID: " + stockID + " Stock Name: " + stockName + " Stock Description: " + stockDescription + " Stock Price: " + stockPrice + " Stock Quantity:" + stockQuantity + " Category: " + categoryName);
      }    
    }  
    catch (SQLException err) {
      System.out.println(err.getMessage());   
    }
  }
  

• 我的 ViewStockGUI 类

    public class ViewStockGUI extends JPanel {
  
      private static final long serialVersionUID = 1L;
      final JFrame viewFrame;
      ViewStockQuery stockQuery;
      ViewStockQuery stockName;
      JComboBox comboGUI;
      String selected;
      JComboBox combo;
  
      public ViewStockGUI() throws SQLException {
  
        final ViewStockQuery stock = new ViewStockQuery();
  
        comboGUI = stock.getComboBox();
        stock.retrieveStock();
        viewFrame = new JFrame("View Stock");
  
        JPanel p = new JPanel();
        p.setBorder (new TitledBorder(new LineBorder(Color.black, 1, true)));
        p.setPreferredSize(new Dimension(500,400));
  
        JPanel p2 = new JPanel();
        p2.setBorder (new TitledBorder(new LineBorder(Color.black, 1, true)));
        p2.setPreferredSize(new Dimension(500, 50));
  
        JPanel p3 = new JPanel();
        JPanel p4 = new JPanel();
        JPanel p5 = new JPanel();
        JPanel p6 = new JPanel();
  
        Box box = Box.createVerticalBox();        
        Box box2 = Box.createHorizontalBox();
        Box box3 = Box.createHorizontalBox();
        Box box4 = Box.createHorizontalBox();
  
        final JTextField textfieldStockName;
        final JTextField textfieldStockID;
        final JTextField textfieldStockDescription;
        final JTextField textfieldStockPrice;
        final JTextField textfieldStockQuantity;
        final JTextField textfieldStockCategory;
  
        final JLabel stockName = new JLabel("Name:");
        JLabel stockID = new JLabel("ID:");
        JLabel stockDescription = new JLabel("Description:");
        JLabel stockPrice = new JLabel("Price:");
        JLabel stockQuantity = new JLabel("Quantity:");
        JLabel categoryName = new JLabel("Category:");
  
        box.add(Box.createVerticalGlue());
        box.add(stockName);
        box.add(textfieldStockName = new JTextField(""));
        textfieldStockName.setText(stock.getStockName());
        textfieldStockName.setEditable(false);
  
        box.add(stockID);
        box.add(textfieldStockID = new JTextField(""));
        textfieldStockID.setText(stock.getStockID());
        textfieldStockID.setEditable(false);
  
        box.add(stockDescription);
        box.add(textfieldStockDescription = new JTextField(""));
        textfieldStockDescription.setText(stock.getStockDescription());
        textfieldStockDescription.setEditable(false);
  
        box.add(stockPrice);
        box.add(textfieldStockPrice = new JTextField(""));
        textfieldStockPrice.setText(stock.getStockPrice());
        textfieldStockPrice.setEditable(false);
  
        box.add(stockQuantity);
        box.add(textfieldStockQuantity = new JTextField(""));
        textfieldStockQuantity.setText(stock.getStockQuantity());
        textfieldStockQuantity.setEditable(false);
  
        box.add(categoryName);
        box.add(textfieldStockCategory = new JTextField(""));
        textfieldStockCategory.setText(stock.getStockCategory());
        textfieldStockCategory.setEditable(false);
        box.add(Box.createVerticalGlue());
  
        JButton next = new JButton("Next");
        next.addActionListener(new ActionListener() {
          public void actionPerformed(ActionEvent e) {
            stock.doNext();
            textfieldStockName.setText(stock.getStockName());
            textfieldStockID.setText(stock.getStockID());
            textfieldStockDescription.setText(stock.getStockDescription());
            textfieldStockPrice.setText(stock.getStockPrice());
            textfieldStockQuantity.setText(stock.getStockQuantity());
            textfieldStockCategory.setText(stock.getStockCategory());
          }
        }); 
  
        JButton previous = new JButton("Previous");
        previous.addActionListener(new ActionListener() {
          public void actionPerformed(ActionEvent e) {
            stock.doPrevious();
            textfieldStockName.setText(stock.getStockName());
            textfieldStockID.setText(stock.getStockID());
            textfieldStockDescription.setText(stock.getStockDescription());
            textfieldStockPrice.setText(stock.getStockPrice());
            textfieldStockQuantity.setText(stock.getStockQuantity());
            textfieldStockCategory.setText(stock.getStockCategory());
          }
        }); 
  
        final Counter counter = new Counter();
        final JLabel text = new JLabel(counter.getValue1());
  
        JButton plus = new JButton("+");
        plus.addActionListener(new ActionListener() {
          public void actionPerformed(ActionEvent e) {
            counter.increment();
            text.setText(counter.getValue1());
          }
        }); 
  
      JButton minus = new JButton("-");
      minus.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
          counter.decrease();
          text.setText(counter.getValue1());
        }  
      });
  
      JButton update = new JButton("Update");
      update.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
          try {
            stock.updateStock(counter.getValue1());
          } 
          catch (SQLException e1) {
            e1.printStackTrace();
          }
          finally { // doesn't update yet; will work on this later
            textfieldStockQuantity.setText(stock.getStockQuantity());
          }
        }
      }); 
  
      comboGUI.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
          try {
            combo = (JComboBox) e.getSource();
            selected = (String)combo.getSelectedItem();
            textfieldStockName.setText(stock.getStockName());
            textfieldStockID.setText(stock.getStockID());
            textfieldStockDescription.setText(stock.getStockDescription());
            textfieldStockPrice.setText(stock.getStockPrice());
            textfieldStockQuantity.setText(stock.getStockQuantity());
            textfieldStockCategory.setText(stock.getStockCategory());
            stockQuery.con.close();
          }
          catch (SQLException e1) {
            e1.printStackTrace();
          }
        }
      }); 
  
  
      box.add(comboGUI);      
      box2.add(previous);
      box2.add(next);
      box3.add(minus);
      box3.add(text);
      box3.add(plus);
      box4.add(update);
  
      p.add(box2);
      p.add(box);
      p.add(box3);
      p.add(box4);
  
      this.add(p, BorderLayout.SOUTH);
    }
  }
  

如果有人能提供帮助，我们将不胜感激。

Answer 1

当你有太多打开的连接时，你会得到这个异常。
这是可配置的，但在您的情况下，问题出在您的代码中。

您发布的代码很奇怪(至少)。
除非收到 SQLException 否则不要关闭连接，或者在处理结果集期间关闭它!

无论如何，您应该重构代码以在完成后立即关闭连接和结果集等。
示例:

try { 

     stat = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ); 
     rs = stat.executeQuery("SELECT categoryName FROM Category"); 

  while (rs.next()) { 
      combo.addItem(rs.getString("categoryName")); 
      categoryName = rs.getString("categoryName");  


  } 
} catch (SQLException sqle) { 
  System.out.println(sqle);   
} 
finally{
 if(stat != null) stat.close(); 
 if(con != null)  con.close(); 
}

通过将关闭放在 finally 中，您可以确保连接以正确的流程或异常关闭(为清楚起见，我省略了 try-catch)。

所以这样修改代码，关闭连接。

为了更好的性能，你应该考虑连接池