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

Question

I created this class that returns a connection object. I used the 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 the connection object. And I want to use the same object throughout the application. Another solution will also be appreciated.

Answer 1

I assume you need a singleton pattern, here is an 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 can use the connection as follows:

 Connect_db.getConnection().somemethods(); 

but you should think about how this will work in a multi-threaded environment when multiple threads try to execute database queries.

Answer 2

a very primitive way, you can get an instance of Connection with

Connect_db.getConnection (DBNAME, username, PASSWD);

in any class because it is a 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 application is mutlithreaded and should use the pool well

Answer 3

I really liked Lashane's answer, I used the code to create the DataSource solution. I also reworked it to store a DataSource , not Connection , if you want to open several of them.

 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 can create connections using:

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

I added the ability to connect to another database every time, in some cases, like ours, this is what you need to do on the fly.

Hope this helps.