MySql的连接太多 [英] MySql too many connections
问题描述
我不想提出一个在网络上被广泛问到的问题，但我似乎无法解决.
I hate to bring up a question which is widely asked on the web, but I cant seem to solve it.
我前一段时间开始了一个项目，经过一个月的测试，我遇到了连接过多"错误.我调查了一下，并通过增加max_connections来解决"了它.这样就行了.
I started a project a while back and after a month of testing, I hit a "Too many connections" error. I looked into it, and "Solved" it by increasing the max_connections. This then worked.
从那时起，越来越多的人开始使用它，并且它再次流行.当我是该站点上的唯一用户时，我键入"show processlist"，它显示大约50个仍处于打开状态的连接(在命令中说"Sleep").现在，我还不足以推测为什么它们会被打开，但是在我的代码中，我三重检查了一下，打开的每个连接都被关闭了.
Since then more and more people started to use it, and it hit again. When I am the only user on the site, i type "show processlist" and it comes up with about 50 connections which are still open (saying "Sleep" in the command). Now, I dont know enough to speculate why these are open, but in my code I tripple checked and every connection I open, I close.
即.
public int getSiteIdFromName(String name, String company)throws DataAccessException,java.sql.SQLException{
Connection conn = this.getSession().connection();
Statement smt = conn.createStatement();
ResultSet rs=null;
String query="SELECT id FROM site WHERE name='"+name+"' and company_id='"+company+"'";
rs=smt.executeQuery(query);
rs.next();
int id=rs.getInt("id");
rs.close();
smt.close();
conn.close();
return id;
}
每次我在网站上执行其他操作时，都会打开另一个连接，但并不会关闭它们. 我的代码有问题吗?如果没有，那可能是什么问题?
Every time I do something else on the site, another load of connections are opened and not closed. Is there something wrong with my code? and if not, what could be the problem?
推荐答案
使用您的方法，如果在调用conn.close()之前抛出任何异常，连接将永远不会关闭.您需要在try块中获取它(以及语句和结果集)，然后在finally块中关闭它. finally中的任何代码将始终执行，无论是否引发异常.这样，您可以确保昂贵的资源将被关闭.
With your approach, the connection will never be closed if any exception is been thrown before the conn.close() is called. You need to acquire it (and the statement and resultset) in a try block and close it in the finally block. Any code in finally will always be executed regardless of an exception is been thrown or not. With this you can ensure that the expensive resources will be closed.
这里是一个重写:
public int getSiteIdFromName(String name, String company) throws DataAccessException, java.sql.SQLException {
Connection conn = null;
Statement smt = null;
ResultSet rs = null;
int id = 0;
try {
conn = this.getSession().connection();
smt = conn.createStatement();
String query = "SELECT id FROM site WHERE name='" + name + "' and company_id='" + company + "'";
rs = smt.executeQuery(query);
rs.next();
id = rs.getInt("id");
} finally {
if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
if (smt != null) try { smt.close(); } catch (SQLException logOrIgnore) {}
if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
}
return id;
}
也就是说，该代码对 SQL注入
That said, this code is sensitive to SQL injection attacks. Use a PreparedStatement instead of Statement.
另请参见:
- Sun Exceptions tutorial: The Finally block
- Sun JDBC tutorial: introduction
- Sun JDBC tutorial: how to use PreparedStatement
- DAO tutorial: how to use basic JDBC code properly
这篇关于MySql的连接太多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
