Single database instance

Question

Single database instance

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I have a controller and need to access 2 or more tables from my database, eg:

 $userModel = loadModel('userModel');
   $userModel->setTable('users');
   $userModel->getAllUser();

   $outroModel = loadModel('outroModel');
   $outroModel->setTable('outros');
   $outroModel->getAllOutros();

Being each one of these object, classes that extend the Model, that makes the connection with the bank, Ex User:

class userModel extends Model {

    private $table;

    function __construct() {
        parent::__construct();
    }

    public function setTable($table) {
        $this -> table = $table;
    }

    public function getTable() {
        return $this -> table;
    }

    public function getAllUsers() {

        $this -> setSql("SELECT * FROM {$this->getTable()}");
        $getAll = $this -> getAll();
        return $getAll;

    }

Model:

abstract class Model {

    protected $db;
    protected $sql;

    function __construct() {

        $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);

        $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME;
        try {
            $this -> db = new PDO($dsn, DB_USER, DB_PASS, $options);
            return $this -> db;

        } catch (exception $e) {
            echo $e -> getMessage();
        }

    }
....

Doing this, am I opening more than one connection to the bank? if yes, what is the best way to do it?

php pdo mvc

asked by anonymous 23.05.2014 / 20:43

2 answers

0

Just include the finally and close the connection inside.

23.05.2014 / 21:34

How to work with more than one dependency in Service class? Problem passing Polled Bean to controller and insert into DB using Java with Spring mvc

score 2 · Accepted Answer

You end up creating two instance (or more) of the PDO class, which in case one would be sufficient for the bank operations. It has a template and I will share it with Injection of Dependencies and the DAL layer.

Interfaces

interface ConnectionInterface {
    public function Close();
    public function Connection();
}
interface ClienteInterface {
    public function setId($value);
    public function setNome($value);
    public function getId();
    public function getNome();
}
interface FornecedorInterface {
    public function setId($value);
    public function setRazaoSocial($value);
    public function getId();
    public function getRazaoSocial();
}

Implementing these Interfaces

class Connection implements ConnectionInterface {
    private $db;
    public function _construct(){
        $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);
        $dsn = 'mysql:host=localhost;dbname=dbtest';
        $this->db = new PDO($dsn, 'root', 'senha', $options);
    }
    public function Close(){
        unset($this->db);
    }
    public function Connection(){
        return $this->db;
    }
}
class Cliente implements ClienteInterface {
    private $id;
    private $nome;
    public function __construct($id = 0, $nome = ''){
        $this->id = $id;
        $this->nome = $nome;
    }
    public function setId($value){
        $this->id = $value;
    }
    public function setNome($value){
        $this->nome = $value;
    }
    public function getId(){
        return $this->id;
    }
    public function getNome(){
        return $this->nome;
    }
}

class Fornecedor implements FornecedorInterface {
    private $id;
    private $razaoSocial;
    public function __construct($id = 0, $razaoSocial = ''){
        $this->id = $id;
        $this->razaoSocial = $razaoSocial;
    }
    public function setId($value){
        $this->id = $value;
    }
    public function setRazaoSocial($value){
        $this->razaoSocial = $value;
    }
    public function getId(){
        return $this->id;
    }
    public function getRazaoSocial(){
        return $this->razaoSocial;
    }
}

DAL

class DalCliente {
    private $Connection;
    public function _construct(ConnectionInterface $Connection){
        $this->Connection = $Connection;            
    }
    public function Insert(ClienteInterface $cliente){
        $sts = $this->Connection->prepare('INSERT INTO tbcliente(nome) values(?)');
        $sts->bindValue(1, $cliente->getNome(),PDO::PARAM_STR);
        $sts->execute();
        $cliente->setId($this->Connection->lastInsertId());         
        return $cliente;
    }
    public function Edit(ClienteInterface $cliente){
        $sts = $this->Connection->prepare('UPDATE tbcliente SET nome=? WHERE id=?');
        $sts->bindValue(1, $cliente->getNome(),PDO::PARAM_STR);
        $sts->bindValue(2, $cliente->getId(),PDO::PARAM_INT);
        $sts->execute();
    }
}

class DalFornecedor {
    private $Connection;
    public function _construct(ConnectionInterface $Connection){
        $this->Connection = $Connection;            
    }
    public function Insert(FornecedorInterface $fornecedor){
        $sts = $this->Connection->prepare('INSERT INTO tbfornecedor(nome) values(?)');
        $sts->bindValue(1, $fornecedor->getRazaoSocial(),PDO::PARAM_STR);
        $sts->execute();
        $cliente->setId($this->Connection->lastInsertId());         
        return $fornecedor;
    }
    public function Edit(FornecedorInterface $fornecedor){
        $sts = $this->Connection->prepare('UPDATE tbfornecedor SET razaosocial=? WHERE id=?');
        $sts->bindValue(1, $fornecedor->getRazaoSocial(),PDO::PARAM_STR);
        $sts->bindValue(2, $fornecedor->getId(),PDO::PARAM_INT);
        $sts->execute();
    }
}

How to use

Let's say that in a code block, or in a controller you have to open two different DAL SQL, do:

$connection    = new Connection();
$dalcliente    = new DalCliente($connection);
$dalfornecedor = new DalFornecedor($connection);

Insert operation

$fornecedor = new Fornecedor(0, "Fornecedor 1");
$fornecedor = $dalfornecedor->Insert($fornecedor); // inserindo fornecedor

At this point you are injecting a Class that is responsible for connecting to two other classes, eliminating duplicate instances, improving performance, standardizing your software to facilitate new implementations and fixes.

Organization of folders

Connection Folder: Connection.php

DAL Folder: DalCliente.php and DalFornecedor.php

Folder Interfaces: ConnectionInterface.php , ClienteInterface.php , and FornecedorInterface.php

Short Folder: Cliente.php and Fornecedor.php

To call !!!

Create the files in the root and give an include what you need:

include 'Interfaces/ConnectionInterface.php';
include 'Connection/Connection.php';

include 'Interfaces/ClienteInterface.php';
include 'Interfaces/FornecedorInterface.php';
include 'Poco/Cliente.php';
include 'Poco/Fornecedor.php';

include 'Dal/DalCliente.php';
include 'Dal/DalFornecedor.php';