Multiple DB connection class

Question

How do you create multiple DB connections using Singleton pattern? Or maybe there's better approach, to share the same class but multiple connections?

Answer 1

Connection Pooling.

In case of java:

http://java.sun.com/developer/onlineTraining/Programming/JDCBook/Code/JDCConnectionPool.java

This example just shows the way, you can implement it in still better way.

Answer 2

How about using a Factory pattern to return the same instance for each connection, e.g.

ConnectionFactory::getInstance(ConnectionFactory::DEVELOPMENT);

Returns a Connection instance for a connection to the development database.

Instantiation of the Connection should only be performed by the ConnectionFactory, which can keep references to those instances in a static array, keyed by the connection type. This avoid the singleton pattern, but ensures you only maintain a single instance of each Connection.

Answer 3

How about dropping the Singleton pattern if what you want is multiple? Lately, Singleton has become an anti-pattern so you should probably drop it even if you need a single connection. Not to mention that extending Singleton in PHP is pretty hard for the moment.

Answer 4

As others have said, drop the singleton, then, I'd probably do something like this:

interface Connection_Interface
{
    public function connect();
    public function disconnect();
    public function exec($sql);
    // etc...
}

class Connection implements Connection_Interface
{
    public function __construct($host, $username, $password, $database);
    public function connect();
    public function disconnect();
    public function exec($sql);
    // etc...
}

Then, another class that takes multiple connections:

class Connection_Multiple implements Connection_Interface
{
    protected $_connections = array();        

    public function __construct();
    public function add(Connection $connection);
    public function connect();
    public function disconnect();
    public function exec($sql)
    {
        // decide here which connection you want to use, then...
        return $connection->exec($sql);
    }
    // etc...
}

As both the single connection and the multiple connection classes implement the same interface you can use either in exactly the same way.

Answer 5

I've come up with this solution:

class Database {
    private static $instances = array();

    public static function getInstance($connection='default') {
     if (!array_key_exists($connection,self::$instances)) {
      self::$instances[$connection] = new Database($connection);
     }
     return self::$instances[$connection];
    }

    private function __construct($connection) {
     $this->credentials = // getting credentials from config by $connection...
     $this->connect(); // connect using credentials
    }
}

$DB1 = Database::getInstance('development');
$DB2 = Database::getInstance('production');

Seems like works for me. Is this a Singleton pattern or something mixed?