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.

Who told you that if something is a singleton it is automatically threadsafe? Also, DBs are generally accessed by many users at the same time. — Reverend Gonzo, Jun 28 '11 at 14:12
A singleton is just an object for which only one instance is created. It has no bearing on the thread-safety of an object. If the internals of that object aren't threadsafe, turning it into a singleton doesn't make it threadsafe. A Connection object is not threadsafe since you can't concurrently use it from multiple threads. By making it a singleton, you're prone to serious problems if you do try accessing that connection from multiple threads. (What you should be using is connection pooling (via either a library or ThreadLocal) to have a single connection per thread. — Reverend Gonzo, Jun 28 '11 at 15:29

Dave · Accepted Answer · 2011-06-29T02:56:12.467

36

A DB connection should not normally be a Singleton.

Two reasons:

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.
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:

edited Jun 29 '11 at 02:56

answered Jun 28 '11 at 14:10

Dave

13,518
7
42
51

Should DB connector be uploaded in **ServletContext** if the connector isn't **Singletone**? Will it be a good _OOP_ style? – gkiko Aug 10 '12 at 13:48
I generally use spring to inject a pooled Datasource into my objects. The pooled datasource can come from JNDI if the app server supports it, or by defining a datasource in spring itself. – Dave Aug 11 '12 at 14:43
3

Generally these points are very valid; however, there are some DBs, such as MongoDB, where the designers have specifically recommended the Singleton pattern, and some DBs DO guarantee thread safety. Basically, read the specs for your DB and consider the application... – jsh May 07 '13 at 19:54
I don't think its so terrible to use a singleton for global resources when you have an application without true threading e.g. python or dart application. – Anthony O Oct 19 '20 at 16:41

score 5 · Answer 2 · edited May 23 '17 at 12:16

5

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)

edited May 23 '17 at 12:16

Community

1
1

answered Jun 28 '11 at 14:04

Andreas Dolk

113,398
19
180
268

Yes , Singleton is not a good choice for Connection object. However, should one create a singleton DataSource which is backed by a db pool ? – bluelurker Aug 16 '16 at 10:49

score 4 · Answer 3 · edited Mar 28 '13 at 16:03

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.

score 3 · Answer 4 · answered Jun 28 '11 at 14:06

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.

should a db connection be a singleton?

4 Answers4

Linked

Related