can be used Singleton pattern with Mysql jdbc connection?

Question

this is the code that i used to establish jdbc connection. can i use getInstance() shown in below. MysqlConnection class object can be called in other classes using MysqlConnection.getInstance(). this code segment is working properly.but i want to know, is this code incorrect? can i use like this?

public class MysqlConnection {

//use singleton design patern 
private static MysqlConnection instance;
public static MysqlConnection getInstance(){
  if(instance == null) {
    instance = new MysqlConnection();
  }
  return instance;
}

// init database constants
private static final String DATABASE_DRIVER = "com.mysql.jdbc.Driver";
private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/storetest";
private static final String USERNAME = "root";
private static final String PASSWORD = "root";
private static final String MAX_POOL = "250";

// init connection object
private Connection connection;
// init properties object
private Properties properties;

// create properties
private Properties getProperties() {
  if(properties == null) {
    properties = new Properties();
    properties.setProperty("user", USERNAME);
    properties.setProperty("password", PASSWORD);
    properties.setProperty("MaxPooledStatements", MAX_POOL);
  }
  return properties;
}

// connect database
public Connection connect() {
    if (connection == null) {
        try {
            Class.forName(DATABASE_DRIVER);
            connection = (Connection) DriverManager.getConnection(DATABASE_URL, getProperties());
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    return connection;
}

// disconnect database
public void disconnect() {
  if(connection != null) {
    try {
      connection.close();
      connection = null;
    } catch (SQLException e) {
        e.printStackTrace();
    }
  }
}    

}

In a completely different context, *this is a broken Singleton*. You need to *synchronize* calls to `getInstance()` and `disconnect()` — TheLostMind, Oct 09 '15 at 05:21
You should use dependency injection to inject your sqlconnection to those classes that need it. A Singleton is just another global. — Philip Stuyck, Oct 09 '15 at 05:22

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

Using a Singleton for a SqlConnection object is not a good idea.
See getting db connection through singleton class

But if you still want to make it singleton, replace:

public class MysqlConnection {

    //use singleton design patern 
    private static MysqlConnection instance;
    public static MysqlConnection getInstance(){
        if(instance == null){
            instance = new MysqlConnection();
        }
        return instance;
    }

With enum :

public enum MysqlConnection {
    INSTANCE;
    public static MysqlConnection getInstance(){
        return INSTANCE;
    }

For more details see Implementing Singleton as Enum in Java

edited May 23 '17 at 11:52

Community

1
1

answered Oct 09 '15 at 05:24

Sumit Singh

23,530
8
71
99

I don't think a Singleton for a sql connection is a very good idea. – Philip Stuyck Oct 09 '15 at 05:33
@PhilipStuyck Yes you are right, Thanks :). Updated the answer. – Sumit Singh Oct 09 '15 at 05:55

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

You should use dependency injection to inject your dependencies in the classes that need them.

An example can be found here : http://www.journaldev.com/2394/dependency-injection-design-pattern-in-java-example-tutorial

A singleton is just another global and is an antipattern in the way that you are using it here. What is so bad about singletons?

edited May 23 '17 at 12:29

Community

1
1

answered Oct 09 '15 at 05:25

Philip Stuyck

6,843
3
23
36

can be used Singleton pattern with Mysql jdbc connection?

2 Answers2