CODEWITHSUNDEEP
javajdbc
BanikPyco
BanikPyco

Reputation: 820

How can I use a singleton design pattern for establishing and closing connection of JDBC?

Here is my problem: I know it's not considered as a good pracitse, however I've been told by teacher to use singleton for JDBC connection.

Argument for why was that database connection is very expensive operation so I shouldn't connect to database every time I need to do operation with my database.

But how can I close the connection if my code looks like this? What are the options? I was looking for a solution of my problem but I couldn't find one.

Thank you very much! I really appreaciate all your time and effort.

public class DbConnect {

    public static Connection con;
    public static Statement st;
    public static ResultSet rs;

    public DbConnect() throws SQLException{

       con = getDbConnection();
       st = con.createStatement();
       rs = null;
    }

    public static Connection getDbConnection(){
          try{
            Class.forName(("com.mysql.cj.jdbc.Driver"));

            con = DriverManager.getConnection( .... 
                  ....
}

public static void closeConnection() throws SQLException{
    con.close();
    st.close();
    rs.close();
}

// Example of how I use it:

public void removeCustomer(String value) throws SQLException{

        String query="DELETE FROM customer WHERE idCustomer="+value;
        pst=DbConnect.con.prepareStatement(query); // THIS
        try {
            pst.executeUpdate();
        } catch (SQLException ex) {
                Logger.getLogger(CustomerRemover.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pst.close();
        }

}

Upvotes: 0

Views: 934

Answers (2)

Roger
Roger

Reputation: 261

Your teacher is incorrect. Remember you have two things that use resources. Your application and your database. So, 1. Always use Try With Resources convention so that you resources are properly closed. I witnessed a large software company use a thread pool to make JDBC connections. The database was not reachable for about two minutes but the application kept trying to make connections creating a new thread each time. This was assentially a memory leak and brought the whole system down. Use try with resources. 2. Having one persistent connection is less expensive for the front end application, but it is exponentially more expensive for the database. The common knowledge basic thing to do is to connect, select, and close as fast as possible.

Upvotes: 1

Konrad Neitzel
Konrad Neitzel

Reputation: 760

Your teacher should look up connection pools :)

He is correct, that opening a connection to a database with authentication and all that stuff is quite expensive. But this issue is solved with connection pools. That way, the database driver takes care of keeping a connection open.

So you can simply open / close a connection as open as you want: Opening a connection will check if there is already an connection open inside the connection pool and closing a connection will just put a connection back to the connection pool.

The important thing is, that a connection can time out. So if you simply try to keep one connection open all the time, then you might get an exception when trying to use the connection. (Some drivers can reopen connections automatically but afaik most don't.)

So my suggestion is really: When you need connection, then open it, use it and close it afterwards. And make sure to use connection pooling.

But if you really want to us a singleton, then use it correctly. A Singleton class normally has a private constructor and a static method to get the instance. The instance is a static variable which is either set inside the static constructor (So it is defined like static private MyClass instance = new MyClass(); ) or the getInstance checks if the instance is null and creates a first instance if required (using synchronized in case multiple threads try to call getInstance at the same time!)

Upvotes: 1

Related Questions