JAVA：MySql：太多连接 [英] JAVA: MySql: too many connection

问题描述

我认为我的应用程序是诅咒，调试去了它想要的，我不知道为什么。
逐行调试似乎也分析了注释的行。
我认为问题是在我的Connection方法，我看到一个显着的性能减速，在第三（或第四个nvm）连接，我得到这个错误：

  com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException：太多连接
  

我确信我每次访问数据库时都关闭连接（在执行过程中，try语句中的finally语句）。

这是我的连接类：

  
 
 import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.rmi.RemoteException; import java.sql。*; import java.util.Properties; 
 
 public class ConnectionDB {
 public static ResultSet rs = null; 
 public static Statement stat = null; 
 public static Connection cn = null; 
 
 public static void connect（）throws RemoteException {
 String dbHost =，dbUser =，dbPassword =; 
属性prop = new Properties（）; 
 try 
 {
 // carico il file：
 prop.load（new FileInputStream（*** / config.properties））; 
 // Leggo leproprietàdel文件：
 dbHost = prop.getProperty（host）; 
 dbUser = prop.getProperty（user）; 
 dbPassword = prop.getProperty（password）; 
} catch（FileNotFoundException fe）{
 System.out.println（config file not found）; 
} catch（IOException ex）{
 System.out.println（Error reading config file）; 
} 
 try 
 {
 String driver =com.mysql.jdbc.Driver; 
 Class.forName（driver）; 
 dbHost =jdbc：mysql：//+ dbHost; 
 cn = DriverManager.getConnection（dbHost，dbUser，dbPassword）; 
 stat = cn.createStatement（）; 
} catch（SQLException e）{
 System.out.println（无法连接到DB）; 
} 
 catch（ClassNotFoundException cln）{
 System.out.println（Error using JDBC driver）; 
} 
} 
 
 public static void disconnect（）throws RemoteException {
 try {
 if（rs！= null）rs.close 
 if（stat！= null）stat.close（）; 
 if（cn！= null）cn.close（）; 
} 
 catch（SQLException sqlEx）{
 System.out.println（Error：disconnect）; 
} 
} 
} 
  

解决方案>

通常用户可用的连接数量有限。另外，建立连接是昂贵的操作。因此，如果遇到性能问题，您应该开始使用连接池，讨论连接池

关于你的代码，确保你总是在finally块中释放连接。此外，我们不知道你运行什么样的查询。打印您的查询，并尝试在mysql中手动运行它们，并查看问题是否是您的SQL，而不是Java。此外，你永远不会关闭FileInputStream意味着你可以运行文件处理程序。

如果你在循环中运行相同的语句，考虑使用PreparedStatements和批量更新和事务。

如果您想要分析查询的效果，可以使用 JAMon（Java应用程序监视器）

建议增加连接数建议腹泻患者多吃一些食物。

I think that my application is cursed, debug goes where it wants and I don't know why. Line by line debugging seems to analyze also the commented rows. I think that the problems are on my Connection method, I see a significant performance slowdown, and at the third (or fourth nvm) connection I get this error:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

I'm sure that I close the connection each time I've access to the DB (with finally statement out of the try catch in the implementation).

Here's my connection class:

package Connection;

import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.rmi.RemoteException; import java.sql.*; import java.util.Properties;

public class ConnectionDB {         
    public static ResultSet rs = null;  
    public static Statement stat = null;    
    public static Connection cn = null;

    public static void connect() throws RemoteException     {
            String dbHost="",dbUser="",dbPassword="";
            Properties prop=new Properties();
            try
            {
                //carico il file:
                prop.load(new FileInputStream("***/config.properties"));
                //Leggo le proprietà del file:
                dbHost=prop.getProperty("host");
                dbUser=prop.getProperty("user");
                dbPassword=prop.getProperty("password");
            }catch(FileNotFoundException fe){
                System.out.println("config file not found");
            }catch(IOException ex){
                System.out.println("Error reading config file");
            }
            try 
            {
                String driver = "com.mysql.jdbc.Driver";
                Class.forName(driver);
                dbHost = "jdbc:mysql://"+dbHost;
                cn = DriverManager.getConnection(dbHost,dbUser,dbPassword);
                stat = cn.createStatement();
            }catch(SQLException e){
                System.out.println("Can't connect to the DB");
            }
            catch(ClassNotFoundException cln){
                System.out.println("Error using JDBC driver");
            }   
    }   

    public static void disconnect() throws RemoteException  {
            try{
                if(rs != null) rs.close();
                if(stat != null) stat.close();
                if(cn != null) cn.close();
            }
            catch(SQLException sqlEx){
                System.out.println("Error: disconnect");
            }   
      }
}

解决方案

Normally there is limited number of connections available for user. Additionally, establishing connections is costly operation. Therefore, if you experience performance problems you should start using connection pool, discussion about connection pools

Regarding your code, make sure you always release connections in finally block. Also we don't know what kind of queries you run. Print your queries and try running them manually in mysql and see if the problems is your SQLs, not Java. Also you never close FileInputStream meaning you can run out of file handlers.

In case you run the same statement in a loop consider using PreparedStatements and batch updates and transactions. If you use transactions, make sure you don't have much data in uncommitted state.

If you want to analyze performance of your queries you can use JAMon (Java Application Monitor)

Suggestion to increase number of connections is similar to advising a person with diarrhea to eat more food.

这篇关于JAVA：MySql：太多连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！