JDBC Connection Issue

Question

I have create a getDBConnection method in my Java application. This returns a connection object, and hence I haven't closed this connection in this method itself.

Now, I am invoking this method from various methods in my application at regular intervals, and closing them inside a try - finally block. I thought this should free up the connection after use. However, I am seeing a large number of connections opened (about 50) in the MySQL Administrator's Server Connections tab.

//Defining a method to retrieve a database connection
// PropDemo is a properties class that retrieves Database related values from a file
public Connection getDBConnection() {

    //Instantiating the Properties object
    PropDemo prop = new PropDemo();
    Connection con = null;

    // Retrieving values from the parameters.properties file
    String JdbcDriver = prop.getMessage("JdbcDriver");
    String JdbcUrlPrefix = prop.getMessage("JdbcUrlPrefix");
    String DBIP = prop.getMessage("DBIP");
    String DBName = prop.getMessage("DBName");
    String DBUser = prop.getMessage("DBUser");
    String DBPassword = prop.getMessage("DBPassword");

    try {

        // Loading and instantiating the JDBC MySQL connector driver class
        Class.forName(JdbcDriver).newInstance();
        con = DriverManager.getConnection(JdbcUrlPrefix + DBIP + "/" + DBName, DBUser, DBPassword);


          if (con.isClosed())
                    Logger.log("Connection cannot be established", "vm");

    } catch (Exception e) {
        Logger.log("Exception: " + e, "vm");
        Logger.log(Logger.stack2string(e), "vm");
    }
    return con;

}

I am also closing the associated ResultSet and Statement Objects. What could be missing here?

I am planning to replace all the Statements with PreparedStatements for efficiency and security reasons. Will that help significantly? What else can be done?

EDIT: This is just a core java application that is repeatedly quering for changes in some fields in a MySQL database through MySQL-JDBC connector. I am not using any framework like Spring or Hibernate.

Answer 1

Are you closing the connection object when you application closes as well?

Answer 2

One thing to start out with is to check to see if the Connection object has a session object within it (I check this through stepping through a debugger). If there is a session object in the Connection, then check to see if it changes during a close(). The database that I use sets the session object to be null.

Another thing: what Driver class are you creating the connection object with. If you are using the PooledConnection class, the connection would not be closed automatically.

Answer 3

Are you using your JDBC connection within a J2EE application server or with Hibernate? Both of these tend to start out with a fairly high connection pool to begin with, so you would see a large number.

Check out the details on connection pooling.

Answer 4

Pat please post your code snippet of the class which creates the DB connection.

Answer 5

You could take a Singleton approach to the problem and only create a new Connection object if the current one is null:

If (connectionObject != null){
   return connectionObject;
}else {
   //create new connection object
}

This will make sure that you only have one non-null connection at any time.

Answer 6

Your code looks sane.

That's how you're creating a new connection.

Probably the error is where you close it.

You should close it in a finally block.

Some additional questions.

1) Are you sure those 50 conections come from this program ? Maybe there are some others comming from your same office. To confirm this you would need to stop the program, and look again in your connection monitor.

2) Does your application uses many connection simultaneously? Probably its a peak when you're using 50 at the same time.

If you can post the code where you close the connection. Chances are the problem is there.

Additionally I would suggest you to use a connection pool. You can build one your self or you can see the results from this page:

http://stackoverflow.com/questions/471745/how-many-jdbc-connections-in-java