java - 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/