If I use a singleton class for a database connection, can one user close the connection for everybody?

Question

I wrote a singleton class for obtaining a database connection.

Now my question is this: assume that there are 100 users accessing the application. If one user closes the connection, for the other 99 users will the connection be closed or not?

This is my sample program which uses a singleton class for getting a database connection:

public class GetConnection {      private GetConnection() { }      public Connection getConnection() {         Context ctx = new InitialContext();         DataSource ds = ctx.lookup("jndifordbconc");         Connection con = ds.getConnection();         return con;     }      public static  GetConnection   getInstancetoGetConnection () {         // which gives GetConnection class instance to call getConnection() on this .     } } 

Please guide me.

Answer 1

As long as you don't return the same Connection instance on getConnection() call, then there's nothing to worry about. Every caller will then get its own instance. As far now you're creating a brand new connection on every getConnection() call and thus not returning some static or instance variable. So it's safe.

However, this approach is clumsy. It doesn't need to be a singleton. A helper/utility class is also perfectly fine. Or if you want a bit more abstraction, a connection manager returned by an abstract factory. I'd only change it to obtain the datasource just once during class initialization instead of everytime in getConnection(). It's the same instance everytime anyway. Keep it cheap. Here's a basic kickoff example:

public class Database {      private static DataSource dataSource;      static {         try {             dataSource = new InitialContext().lookup("jndifordbconc");         }         catch (NamingException e) {              throw new ExceptionInInitializerError("'jndifordbconc' not found in JNDI", e);         }     }      public static Connection getConnection() {         return dataSource.getConnection();     }  } 

which is to be used as follows according the normal JDBC idiom.

public List<Entity> list() throws SQLException {     List<Entity> entities = new ArrayList<Entity>();      try (         Connection connection = Database.getConnection();         PreparedStatement statement = connection.prepareStatement("SELECT id, foo, bar FROM entity");         ResultSet resultSet = statement.executeQuery();     ) {         while (resultSet.next()) {             Entity entity = new Entity();             entity.setId(resultSet.getLong("id"));             entity.setFoo(resultSet.getString("foo"));             entity.setBar(resultSet.getString("bar"));             entities.add(entity);         }     }      return entities; } 

See also:

• Is it safe to use a static java.sql.Connection instance in a multithreaded system?