sharing a JDBC Connection object between other objects

Question

I have created a Database class which uses a static connection object in order to be used in common between instances of itself. my question is that is there any problem with this approach or not?

class Database {
    private static Connection connection = null;

    public Database() {
        if(connection == null){
            ...
            connection = DriverManager.getConnection(...);
            ...
        }
    }
}

Answer 1

If you are going to have many (hundreds) of queries per second then implementing a connection pool is the way to go. See the answer to this question for more details. However, if you a Java novice (we all were one day!) then I don't imagine you will be needing this requirement, and probably will struggle to implement it.

Instead, the simple pattern of creating a new connection if required, and then closing it when finished will be the best way to go forward for you. Below is a modified version of your Database class which I think is a good way to move forward.

class Database {
    private Connection con = null;
    private final String connectionString;

    public Database(String connectionString) {
        this.connectionString = connectionString;
    }

    public void connect() throws SQLException {
        if (con != null // if the connection exists
             && !con.isClosed() // and has not been closed 
             && con.isValid(0)) { // and appears to be functioning (with a test timeout of 0ms)
             return; // skip connection creation
        }

        // create the connection
        con = DriverManager.getConnection(connectionString);        
    }

    public void testFunction() {
        try {
            connect();
            // .. do some stuff with the connection ..
        } catch (Exception e) {
            // log or otherwise deal with the error
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                System.err.println("Failed to close connection: " + e.toString());
            }
        }

    }
}

Some things to note about this solution:

• It is not very efficient - creating a new connection always takes more time than using an existing one
• This class if not thread safe - if you need this requirement, I recommend using a thread pool. However, if you create a new instance of this class per thread then it will be thread safe (as there is not static connection to worry about!)
• It does do the job - certainly for simple cases. I use the model for a relatively low volume database which has approx 50-100 connections made/closed per minute and it does not add a noticeable lag
• It is very robust - nothing is safer than opening and closing a connection per query. You are guaranteed to be able to handle a connection failure per query, and the connection will always be closed (unless it already has been).

Disclaimer The solution above is not a particularly amazing solution. However, I believe it is simple to implement and a good way for a Java novice to get to know the ropes before jumping into external libraries.

Answer 2

There is nothing wrong with creating an object to manage your connections, however, connections should be opened and closed and can be used in multi-threaded environments, so having a static connection is not a good idea. For a method that needs a connection, get a connection use it, close it. Even if you are not using it in a multi-threaded environment, the connection can time-out, then you need to constantly check if the connection is up and available, instead of just saying, get me a connection, use the connection, close it.