JAVA: MySql: too many connection

问题:

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");
            }   
      }
}

回答1:

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.

回答2:

The disconnect method may need following enhancement. In this one, we close ResultSet, Statement & Connection separately. This will save from situation where individual exception will not result into not closing the connection object.

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


        try{
           if(stat != null) stat.close();
        }
        catch(SQLException sqlEx){
            System.out.println("Error: disconnect");
        }   

        try{
            if(cn != null) cn.close();
        }
        catch(SQLException sqlEx){
            System.out.println("Error: disconnect");
        }   
  }

回答3:

There're a couple of options that I think you can try out:

• As in the comments provided, you can increase the max number of connections allowed.

• You talked about performance degradation. If this has to do with number of connection open, I would suggest that you consider reusing your connection. In this scenario using a connection pool might be of help. This might be of help in this case.

回答4:

If you're using a developer version of your database it probably has a restriction on the number of connections that can be made. So you'll need to manage them better which means that you should make sure they are closed when you've finished with them. (EDIT: just realised you're using mysql so this shouldn't be an issue)

Also, you mentioned that your debugger is going through comments - this is typically the case where your code is out of synch with your build. Clean your build (in eclipse it would be Project > clean ... > clean all projects) and that problem should disappear.

回答5:

Try this

if(cn == null){
    String driver = "com.mysql.jdbc.Driver";
    Class.forName(driver);
    dbHost = "jdbc:mysql://"+dbHost;
    cn = DriverManager.getConnection(dbHost,dbUser,dbPassword);
}