使用JDBC的Java – 连接太多了？

[weixin_38063515]

我正在为酒吧写一个库存补货系统作为我的最后一年项目.我可以从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); } } 如果有人可以提供帮助,将不胜感激.

[weixin_38103537]

当您有太多打开的连接时,会出现此异常.这是可配置的,但在您的情况下,问题出在您的代码中. 你发布的代码很奇怪(至少).您要么不关闭连接,除非您得到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(); } 通过将关闭置于最后,您可以确保连接以正确的流程或异常关闭(为清楚起见,我省略了try-catch). 因此,通过这种方式修改代码以关闭连接. 为了获得更好的性能,您应该研究连接池