Getting Db Connection Through Singleton Class

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

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;
}

getting db connection through singleton class

Your Singleton is still off.

As far as the singleton pattern goes, please see Jon Skeet's very good and detailed description here : http://www.yoda.arachsys.com/csharp/singleton.html

Using a Singleton for a SqlConnection object is a really, really bad idea. There is no reason to do this whatsoever.

If you are attempting to avoid a performance hit of "new SqlConnection()" or "connection.Open()" be advised that there really is no performance hit there because of the connection pooling going on behind the scenes. Connection Pooling handles the opening/closing of the expensive connections. Not the SqlConnection object.

You won't be able to open multiple SqlDataReaders/Commands with the connection at the same time and will run into thread blocking issues if you are trying to share the same connection object with multiple threads.

The Singleton pattern is the most over used and abused pattern and there are many side effects of the singleton that you may not be aware of. Very good talk about the dangers of singletons here http://www.youtube.com/watch?v=-FRm3VPhseI

should a db connection be a singleton?

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:

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

How to use Singleton class for to get connections multiple?

If you want to use multiple connections efficiently you might be after a connection pool:

In software engineering, a connection pool is a cache of database
connections maintained so that the connections can be reused when
future requests to the database are required. Connection pools are
used to enhance the performance of executing commands on a database.

Having a Singleton which will return many connections defies the purpose of the Singleton, whose task is to provide the same instance whenever it is called.

I recommend you take a look at this previous SO thread where various connection pool libraries are discussed.

Implementing Singleton DB Connection in Java

the connection which you used for the query, is not the connection which you defined in dbConnection class, do this:

public static void main (String[] args) throws SQLException {
    dbConnection connect = dbConnection.getInstance();
    conn=connect.getConnection();
    authorities_check();
}

How to use singleton class for database connection in windows application

To use a singleton directly, you'd have to inherit from the connection object, which isn't possible with an OleDbConnection (as it is sealed). So you'd have to write a class, with a public property that exposes the connection itself, and make that wrapper class a singleton.

However, it the only goal is to have one instance of the connection, you could use a 'Lazy' construction, which is thread safe (For that matter, even in the singleton, I'd use Lazy or LazyInitializer to be prepared for threading)

public static class Data    
{
    public static readonly Lazy<OleDbConnection> Connection = new Lazy<OleDbConnection>(CreateConnection); //(using System.Threading)

    static OleDbConnection CreateConnection()
    {
        var conn = new OleDbConnection("YourConnectionString");
        //etc
        conn.Open();
        return conn;
    }
}

getting the connection would be something like:

var conn = Data.Connection.Value;

Then again, if you would incorporate a class that encapsulates all calls to the connection, that would be a perfect case for a singleton

* edit ** An example using a single connection, with reset*

    public static class Data   
{
    static OleDbConnection conn;
    public static OleDbConnection Connection
    {
        get
        {
            if (conn == null)
                LazyInitializer.EnsureInitialized(ref conn, CreateConnection);
            return conn;
        }
    }

    static OleDbConnection CreateConnection()
    {
        if (strDataFilePath == null)
            throw new Exception("Datafile paths is not set");
        //build connection, using strDataFilePath
        var conn = new OleDbConnection("YourConnectionStringWithDataFilePath"); 
        //other settings

        //open
        conn.Open();
        return conn;
    }

    static string strDataFilePath;

    public static string DataFilePath
    {
        get { return strDataFilePath; }
        set
        {
            if(strDataFilePath==value)return;
            strDataFilePath = value;
            if(conn!=null){
                conn.Close(); //NB, no checks were added if the connection is being used, but if the value is only set on startup or idle moments, this should be ok for the example.
                conn.Dispose();
                conn=null; //conn is reset, and (re)created the next time Connection is called
            }
        }
    }
}

And initializing:

Data.DataFilePath = ".....";

Using the connection:

var conn = Data.Connection; //Connection is created and opened on first call

Singleton design pattern is preferred to get a DB Connection

I can see nothing good coming from this class. If you want it that way (and I'm not in favor of it) you gained no benefit over a bunch of static methods. What would the singleton instance be good for? How would you put the SqlConnection variable inside a using block?

I think a singleton is a way overused pattern for people that want global variables, but read a book that mentioned the fact globals are bad. Now they found another book stating that "Singleton" is a pattern and patterns are "good" and they finally have an excuse to have a global.

Being a global variable is a side effect of the singleton. If you are using it for the side effect, it's not a good use of the pattern. I would say it's an abuse.

Singletons are almost impossible to unit test. This one is not even thread save and does not have any use over a non-singleton. So from my point of view... delete the class.

How many people use the application is irrelevant. Each will have it's own process and it's own singleton, as bad as that may be.

DB-Connections Class as a Singleton in Python

Normally, you have some kind of object representing the thing that uses a database (e.g., an instance of MyWebServer), and you make the database connection a member of that object.

If you instead have all your logic inside some kind of function, make the connection local to that function. (This isn't too common in many other languages, but in Python, there are often good ways to wrap up multi-stage stateful work in a single generator function.)

If you have all the database stuff spread out all over the place, then just use a global variable instead of a singleton. Yes, globals are bad, but singletons are just as bad, and more complicated. There are a few cases where they're useful, but very rare. (That's not necessarily true for other languages, but it is for Python.) And the way to get rid of the global is to rethink you design. There's a good chance you're effectively using a module as a (singleton) object, and if you think it through, you can probably come up with a good class or function to wrap it up in.

Obviously just moving all of your globals into class attributes and @classmethods is just giving you globals under a different namespace. But moving them into instance attributes and methods is a different story. That gives you an object you can pass around—and, if necessary, an object you can have 2 of (or maybe even 0 under some circumstances), attach a lock to, serialize, etc.

In many types of applications, you're still going to end up with a single instance of something—every Qt GUI app has exactly one MyQApplication, nearly every web server has exactly one MyWebServer, etc. No matter what you call it, that's effectively a singleton or global. And if you want to, you can just move everything into attributes of that god object.

But just because you can do so doesn't mean you should. You've still got function parameters, local variables, globals in each module, other (non-megalithic) classes with their own instance attributes, etc., and you should use whatever is appropriate for each value.

For example, say your MyWebServer creates a new ClientConnection instance for each new client that connects to you. You could make the connections write MyWebServer.instance.db.execute whenever they want to execute a SQL query… but you could also just pass self.db to the ClientConnection constructor, and each connection then just does self.db.execute. So, which one is better? Well, if you do it the latter way, it makes your code a lot easier to extend and refactor. If you want to load-balance across 4 databases, you only need to change code in one place (where the MyWebServer initializes each ClientConnection) instead of 100 (every time the ClientConnection accesses the database). If you want to convert your monolithic web app into a WSGI container, you don't have to change any of the ClientConnection code except maybe the constructor. And so on.