使用JDBC的Java-连接过多? [英] Java using JDBC - Too many connections?
问题描述
我正在为酒吧编写库存补充系统，作为我的最后一年的项目.我可以从MYSQL数据库检索信息，也可以一次滚动浏览一个结果.
I am writing a stock replenishment system for a bar as my final year project. I can retrieve information from a MYSQL database and I can scroll through one result at a time.
我正在尝试根据所选类别更改结果.我设法使用组合框来实现此目的，但是在类别之间移动时出现以下错误:
I'm trying to change the results depending on a selected category. I've managed to use a combo box to acheive this but I get the following error when moving between categories:
线程"main"中的异常com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:数据源拒绝建立连接，来自服务器的消息:连接太多"
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
两个单独文件的代码如下:
The code for the two separate files are as follows:
-
RetrieveStockQuery
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);
}
}
如果有人可以提供帮助，将不胜感激.
If anyone can help it would be appreciated.
推荐答案
打开的连接过多时，会出现此异常.
这是可配置的，但是在您的情况下，问题出在您的代码中.
You get this exception when you have too many open connections.
This is configurable but in your case the problem is in your code.
您发布的代码很奇怪(最少).
除非得到SQLException，否则不要关闭连接，或者在处理结果集的过程中关闭连接！
The code you posted is weird (the least).
You either don't close the connection unless you get an SQLException or you close it during processing of a result set!
无论如何，一旦完成，您应该重构代码以关闭连接和结果集等.
示例:
In any case you should refactor your code to close connections and result set etc as soon as you are finished.
Example:
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).
By putting the close in a finally you are sure that the connection is closed either in correct flow or in exception (I have omitted try-catch for clarity).
因此，以这种方式修改代码以关闭连接.
So modify the code this way to close the connections.
为了获得更好的性能，您应该研究连接池
For better performance you should look into connection pooling
这篇关于使用JDBC的Java-连接过多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
