should a db connection be a singleton?

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

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

Solution 3:

What is the best way in Java to create a singleton?

Follow the design pattern creation guidelines. i.e private constructor, etc.

Should a DB connection be a singleton (being a singleton it's automatically thread-safe)?

creating a DB connection as a singleton might be a poor design choice in many scenarios. Use it only if you are sure that you don't need DB concurrency. If you have multiple users logged in at the same time, or even if your single user spawns many threads that need to access the DB, then a DB Connection pool is a better choice. You can use either apache or tomcat db connection pools. THese classes are defined for example in the package

org.apache.commons.dbcp.*;

org.apache.tomcat.dbcp.dbcp.*;

where dbcp stands for database connection pooling.

The biggest reason for using a connection pool is that on average the time it takes for the DB access (DML etc) is much smaller than the time it takes to create a connection and then close the connection. Additionally, don't forget to close your ResultSet, PreparedStatement and Connection variables after the transaction is done.

Because theoretical the DB can't be accessed by many users in the same time.

Why not? DB in most cases is meant to be used concurrently. You have these DB isolation levels - READ_COMMITTED, READ_UNCOMMITTED, SERIALIZED etc. SERIALIZED is the case where your DB becomes single user access.

Solution 4:

Singletons are a pattern - there's no explicit way to create one, you just follow the design practice.

So, if you're using a database that can handle concurrent reads/writes (i.e. MySQL), you don't need to worry so much about thread safety. If you're using a DB that's doesn't do concurrent writes well (SQLite), then a singleton should theoretically work.