How can I use one database connection object in whole application? [duplicate]

Question

I have created this class which returns connection object. I have used MySQL database.

public class Connect_db {        
    public Connection getConnection(String db_name,String user_name,String password)
    {
        Connection con=null;
        try
        {
        Class.forName("com.mysql.jdbc.Driver");
        con=DriverManager.getConnection("jdbc:mysql://localhost/"+db_name+"?user="+user_name+"&password="+password);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return con;        
    }
}  

Now all I want to do is instantiate this class once and get connection object. And I want to use this same object in entire application. Another solution will also be appreciated.

Answer 1

I suppose you need singleton pattern, here is quick example:

public class Connect_db {        
    static Connection con=null;
    public static Connection getConnection()
    {
        if (con != null) return con;
        // get db, user, pass from settings file
        return getConnection(db, user, pass);
    }

    private static Connection getConnection(String db_name,String user_name,String password)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost/"+db_name+"?user="+user_name+"&password="+password);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return con;        
    }
} 

then you will be able to use connection like this:

Connect_db.getConnection().somemethods();

but, you should think - how this will work in multi-threaded environment, when several threads are trying to make requests to database.

Answer 2

I really liked Lashane's response, I used the code to create a DataSource solution. I also redesigned it to only store DataSource and not the Connection, in case you do want to open multiple ones.

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class SignalDB {

    private static MysqlDataSource ds = null;

    public static MysqlDataSource getDataSource(String db_name) {
        if (ds == null) {
            // db variables set here
            getDataSource(db_url, db_user, db_password, db_port);
        }
        ds.setDatabaseName(db_name);
        return ds;
    }

    private static void getDataSource(String db_url, String db_user, String db_password, int db_port) {
        try {
            ds = new MysqlDataSource();
            ds.setServerName(db_url);
            ds.setUser(db_user);
            ds.setPassword(db_password);
            ds.setPort(db_port);
        } catch (Exception e) {
            System.out.println("MysqlDataSource err: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Then you could create connections using:

con = SignalDB.getDataSource("database_name").getConnection();

I added ability to connect to a different database every time, in some cases, like ours, it's what you need to do on the fly.

Hope this helps.