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

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.

Consider using connection pools.http://stackoverflow.com/questions/2835090/jdbc-connection-pooling — kosa, Dec 18 '13 at 19:24
Dependency Injection. Instantiate the object once, pass it to every method that needs it. — JRizz, Dec 18 '13 at 19:24

score 14 · Accepted Answer · answered Dec 18 '13 at 19:24

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.

@JosefN I can imagine several single threaded applications which can use singleton for database, for example some kind of long running job which manipulates db and need easy access to connection from different classes — Iłya Bursov, Dec 18 '13 at 19:58
ok, sorry I work on differnet kine of apps, going to clean my comment. thx — JosefN, Dec 18 '13 at 20:03

score 2 · Answer 2 · edited Oct 22 '15 at 20:08

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.

score 1 · Answer 3 · answered Dec 18 '13 at 20:01

very primitive way, you can get a Connection instance by

Connect_db.getConnection(dbName,userName,passwd);

in any class because it is static method.

public class Connect_db {   
static {
     try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        throw new IllegalArgumentException("MySQL db driver isnot on classpath");
    }
}
public static Connection getConnection(String db_name,String user_name,String password) throws SQLException
{
    return DriverManager.getConnection("jdbc:mysql://localhost/"+db_name+"?user="+user_name+"&password="+password);    
}

}

if your applicaiton is mutlithreaded and should perfom well use a pool

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

3 Answers3