should a db connection be a singleton?

Question

What is the best way in Java to create a singleton? Should a DB connection be a singleton (being a singleton it's automatically thread-safe)? Because theoretical the DB can't be accessed by many users in the same time.

Answer 1

A DB connection should not normally be a Singleton.

Two reasons:

1. many DB drivers are not thread safe. Using a singleton means that if you have many threads, they will all share the same connection. The singleton pattern does not give you thread saftey. It merely allows many threads to easily share a "global" instance.
2. Personally, I think Singleton often leads to bad design: See this post (by somebody else) http://tech.puredanger.com/2007/07/03/pattern-hate-singleton/

Instead of doing this consider a database pool. The pool is shared (and could be a singleton if you wanted). When you need to do database work your code does this:

getConnectioFromPool();  doWork() closeConnection() // releases back to pool 

Sample Pool Libraries:

• http://commons.apache.org/dbcp/
• http://jolbox.com/

Answer 2

The best way to create a singleton (as of today) is the enum singleton pattern (Java enum singleton)

I doubt, that a Singleton is necessary or of any value for a database connection. You probably want some lazy creation: a connection is created upon first demand and cached, further requests will be fullfilled with the cached instance:

public ConnectionProvider {   private Connection conn;    public static Connection getConnection() {     if (conn == null || conn.isClosed()) {       conn = magicallyCreateNewConnection();     }     return conn;   } } 

(not thread safe - synchronize, if needed)