0

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

}
Banik
  • 320
  • 4
  • 9
  • 1
    Check this.https://stackoverflow.com/questions/6567839/if-i-use-a-singleton-class-for-a-database-connection-can-one-user-close-the-con – Sambit May 17 '19 at 20:38

2 Answers2

1

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!)

Konrad Neitzel
  • 736
  • 3
  • 7
  • Thank you for your answer that explains well the problematic domain. I can clearly understand the advantages of using this techinque, however I've never used connection pools and I'm little bit worried that this would mean a lot of changes in our current code (we've implemented almost all of requirements). It's not that complex software (it's school project), however we are limited with time. I try to wrap my head around how much code we would need to change. Does it mean that if we used database queries like the one from example, it would mean drastical changes? – Banik May 17 '19 at 21:11
  • The use of connection pools is part of most jdbc drivers. As far as I know, they are active by default so it is quite likely that you already use them without knowing it – Konrad Neitzel May 17 '19 at 21:15
  • Ohh, seems that I might be a little wrong and got confused with .Net drivers where it is a simple part of the connection string (e.g. setting Pooling=true; for mysql. In java it seems that you need extra software as c3p0 (https://www.mchange.com/projects/c3p0/index.html) or Apache Commons DBCP (http://commons.apache.org/proper/commons-dbcp/) – Konrad Neitzel May 17 '19 at 21:22
  • 1
    Partly true, but using `DriverManager` like the OP is doing is considered the 'old-fashioned' way of establishing connections. The 'modern' way is using an implementation of `javax.sql.DataSource`, so switching between non-pooling and pooling would normally be a matter of switching out the `DataSource` implementation (which in most frameworks is a matter of configuration, and not necessarily code). – Mark Rotteveel May 18 '19 at 07:29
1

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.

Roger
  • 261
  • 1
  • 7