Sharing a JDBC Connection Object Between Other Objects

Question

I created a database class that uses a static connection object to share between instances of itself. my question is, are there any problems with this approach or not?

class Database { private static Connection connection = null; public Database() { if(connection == null){ ... connection = DriverManager.getConnection(...); ... } } } 

Answer 1

If you have many (hundreds) of requests per second, then implementing a connection pool is the way to go. See the answer to this for more details. However, if you are a Java newbie (we were all one day!), Then I don’t think you will need this requirement, and it will probably struggle to implement it.

Instead, a simple template for creating a new connection, if necessary, and then closing it upon completion will be the best way forward for you. Below is a modified version of your Database class, which I think is a good way 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 points regarding this solution:

• This is not very effective - it takes longer to create a new connection 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 for the stream, then it will be thread safe (since there is no static connection for concern!)
• This does the job - of course, for simple cases. I use a model for a relatively low volume database, which has about 50-100 connections made / closed per minute and does not add a noticeable delay.
• It is very reliable - nothing is safer than opening and closing a connection for each request. You are guaranteed to be able to handle connection failure for each request, and the connection will always be closed (if it was not already).

Disclaimer The decision above is not a particularly surprising solution. However, I find this easy to implement and a good way for a Java beginner to get to know the ropes before moving on to external libraries.

Answer 2

There is nothing wrong with creating an object to manage your connections, however, connections must be open 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, connect it, close it. Even if you do not use it in a multi-threaded environment, the connection can be a timeout, then you need to constantly check whether the connection is established and available, instead of just talking, connect me, use the connection, close it.