How to write your own database package (2)-Mysql Tutorial-php.cn

Connector.php

Responsible for communicating with the database, adding, deleting, modifying and reading (CRUD)

First, create a Connector class, and set the attribute

<?php
class Connector {
    // 数据库地址前缀，常见的有mysql，slqlsrv，odbc等等等
    private $driver = &#39;mysql&#39;;
    // 数据库地址
    private $host = &#39;localhost&#39;;
    // 数据库默认名称, 设置为静态是因为有切换数据库的需求
    private static $db = &#39;sakila&#39;;
    // 数据库用户名
    private $username = &#39;root&#39;;
    // 数据库密码
    private $password = &#39;&#39;;
    // 当前数据库连接
    protected $connection;
    // 数据库连接箱，切换已存在的数据库连接不需要重新通信，从这里取即可
    protected static $container = [];

    // PDO默认属性配置，具体请自行查看文档
    protected $options = [
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
        PDO::ATTR_STRINGIFY_FETCHES => false,
    ];
}

Copy after login

The above code should be understandable with comments , so I won’t explain it further and go directly to the function

buildConnectString - is to generate a DSN connection string, very straightforward

  protected function buildConnectString() {
      return "$this->driver:host=$this->host;dbname=".self::$db;
  }
      // "mysql:host=localhost;dbname=sakila;"

Copy after login

connect - Connect to the database

  public function connect() {
      try {
          // 连接数据库，生成pdo实例， 将之赋予$connection，并存入$container之中
          self::$container[self::$db] = $this->connection = new PDO($this->buildConnectString(), $this->username, $this->password, $this->options);
          // 返回数据库连接
          return $this->connection;
      } catch (Exception $e) {
          // 若是失败, 返回原因
          // 还记得dd()吗?这个辅助函数还会一直用上
          dd($e->getMessage());
      }
  }

Copy after login

setDatabase - Switch database

  public function setDatabase($db) {
      self::$db = $db;
      return $this;
  }

Copy after login

_construct - What is the first step after generating an instance

  function construct() {
      // 如果从未连接过该数据库, 那就新建连接
      if(empty(self::$container[self::$db])) $this->connect();
      // 反之, 从$container中提取, 无需再次通信
      $this->connection = self::$container[self::$db];
  }

Copy after login

The next two functional expressions are used together. You may be confused by looking at them alone. Step by step with the exampleDebugging

$a = new Connector();

$bindValues = [
    'PENELOPE',
    'GUINESS'
];

dd($a->read('select * from actor where first_name = ? and last_name = ?', $bindValues));

Copy after login

Return value

array (size=1)
  0 => 
    object(stdClass)[4]
      public 'actor_id' => string '1' (length=1)
      public 'first_name' => string 'PENELOPE' (length=8)
      public 'last_name' => string 'GUINESS' (length=7)
      public 'last_update' => string '2006-02-15 04:34:33' (length=19)

Copy after login

read - read data

  public function read($sql, $bindings) {
      // 将sql语句放入预处理函数
      // $sql = select * from actor where first_name = ? and last_name = ?
      $statement = $this->connection->prepare($sql);
      // 将附带参数带入pdo实例
      // $bindings = ['PENELOPE', 'GUINESS']
      $this->bindValues($statement, $bindings);
      // 执行
      $statement->execute();
      // 返回所有合法数据, 以Object对象为数据类型
      return $statement->fetchAll(PDO::FETCH_OBJ);
  }

Copy after login

bindValues - bring the attached parameters into the pdo instance

      // 从例子中可以看出, 我用在预处理的变量为?, 这是因为pdo的局限性, 有兴趣可以在评论区讨论这个问题
  public function bindValues($statement, $bindings) {
      // $bindings = ['PENELOPE', 'GUINESS']
      // 依次循环每一个参数
      foreach ($bindings as $key => $value) {
          // $key = 0/1
          // $value = 'PENELOPE'/'GUINESS'
          $statement->bindValue(
              // 如果是字符串类型, 那就直接使用, 反之是数字, 将其+1
              // 这里是数值, 因此返回1/2
              is_string($key) ? $key : $key + 1,
              // 直接放入值
              // 'PENELOPE'/'GUINESS'
              $value,
              // 这里直白不多说
              // PDO::PARAM_STR/PDO::PARAM_STR
              is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR
          );
      }
  }

Copy after login

So do you understand _( :3　∠)

#update - Rewrite data

  // 与read不同的地方在于, read返回数据, update返回boolean(true/false)
  public function update($sql, $bindings) {
      $statement = $this->connection->prepare($sql);
      $this->bindValues($statement, $bindings);
      return $statement->execute();
  }

Copy after login

delete - Delete data

// 与update一样, 分开是因为方便日后维护制定
  public function delete($sql, $bindings) {
      $statement = $this->connection->prepare($sql);
      $this->bindValues($statement, $bindings);
      return $statement->execute();
  }

Copy after login

create - Add data

// 返回最新的自增ID, 如果有
  public function create($sql, $bindings) {
      $statement = $this->connection->prepare($sql);
      $this->bindValues($statement, $bindings);
      $statement->execute();
      return $this->lastInsertId();
  }

Copy after login

lastInsertId - Return

Add id, if any

  // pdo自带,只是稍微封装
  public function lastInsertId() {
      $id = $this->connection->lastInsertId();
      return empty($id) ? null : $id;
  }

Copy after login

Too advanced and complex SQL statements may not be encapsulated, so we have prepared two functions that can directly communicate with the database using RAW query

  public function exec($sql) {
      return $this->connection->exec($sql);
  }

Copy after login

  public function query($sql) {
      $q = $this->connection->query($sql);
      return $q->fetchAll(PDO::FETCH_OBJ);
  }

Copy after login

        public function beginTransaction() {
        $this->connection->beginTransaction();
        return $this;
    }

    public function rollBack() {
        $this->connection->rollBack();
        return $this;
    }

    public function commit() {
        $this->connection->commit();
        return $this;
    }

    public function inTransaction() {
        return $this->connection->inTransaction();
    }

Copy after login

Complete code

<?php
class Connector {
    // 数据库地址前缀，常见的有mysql，slqlsrv，odbc等等等
    private $driver = &#39;mysql&#39;;
    // 数据库地址
    private $host = &#39;localhost&#39;;
    // 数据库默认名称, 设置为静态是因为有切换数据库的需求
    private static $db = &#39;sakila&#39;;
    // 数据库用户名
    private $username = &#39;root&#39;;
    // 数据库密码
    private $password = &#39;&#39;;
    // 当前数据库连接
    protected $connection;
    // 数据库连接箱，切换已存在的数据库连接不需要重新通信，从这里取即可
    protected static $container = [];

    // PDO默认属性配置，具体请自行查看文档
    protected $options = [
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
        PDO::ATTR_STRINGIFY_FETCHES => false,
    ];

    function construct() {
        // 如果从未连接过该数据库, 那就新建连接
        if(empty(self::$container[self::$db])) $this->connect();
        // 反之, 从$container中提取, 无需再次通信
        $this->connection = self::$container[self::$db];
    }

    // 生成DSN连接串
    protected function buildConnectString() {
        return "$this->driver:host=$this->host;dbname=".self::$db;
    }

    // 连接数据库
    public function connect() {
        try {
            // 连接数据库，生成pdo实例， 将之赋予$connection，并存入$container之中
            self::$container[self::$db] = $this->connection = new PDO($this->buildConnectString(), $this->username, $this->password, $this->options);

            // 返回数据库连接
            return $this->connection;
        } catch (Exception $e) {
            // 若是失败, 返回原因
            dd($e->getMessage());
        }
    }

    // 切换数据库
    public function setDatabase($db) {
        self::$db = $db;
        return $this;
    }

    // 读取数据
    public function read($sql, $bindings) {
        // 将sql语句放入预处理函数
        $statement = $this->connection->prepare($sql);
        // 将附带参数带入pdo实例
        $this->bindValues($statement, $bindings);
        // 执行
        $statement->execute();
        // 返回所有合法数据, 以Object对象为数据类型
        return $statement->fetchAll(PDO::FETCH_OBJ);
    }

    // 将附带参数带入pdo实例
    public function bindValues($statement, $bindings) {
        // 依次循环每一个参数
        foreach ($bindings as $key => $value) {
            $statement->bindValue(
                // 如果是字符串类型, 那就直接使用, 反之是数字, 将其+1
                is_string($key) ? $key : $key + 1,
                // 直接放入值
                $value,
                // 这里直白不多说
                is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR
            );
        }
    }

    // 改写数据
    public function update($sql, $bindings) {
        // 与read不同的地方在于, read返回数据, update返回boolean(true/false)
        $statement = $this->connection->prepare($sql);
        $this->bindValues($statement, $bindings);
        return $statement->execute();
    }

    // 删除数据
    public function delete($sql, $bindings) {
        $statement = $this->connection->prepare($sql);
        $this->bindValues($statement, $bindings);
        return $statement->execute();
    }

    // 增加数据
    public function create($sql, $bindings) {
        $statement = $this->connection->prepare($sql);
        $this->bindValues($statement, $bindings);
        $statement->execute();
        return $this->lastInsertId();
    }

    // 返回新增id, 如果有
    public function lastInsertId() {
        $id = $this->connection->lastInsertId();
        return empty($id) ? null : $id;
    }

    // 适用于增删改
    public function exec($sql) {
        return $this->connection->exec($sql);
    }

    // 适用于读
    public function query($sql) {
        $q = $this->connection->query($sql);
        return $q->fetchAll(PDO::FETCH_OBJ);
    }

    public function beginTransaction() {
        $this->connection->beginTransaction();
        return $this;
    }

    public function rollBack() {
        $this->connection->rollBack();
        return $this;
    }

    public function commit() {
        $this->connection->commit();
        return $this;
    }

    public function inTransaction() {
        return $this->connection->inTransaction();
    }
}

Copy after login

Questions in this issue

1.) Due to the characteristics of PHP itself, by default all code classes will be destructed after running, and pdo will automatically break connection, so I did not disconnect() and let pdo disconnect. I wonder if this is a bad practice?

2.) The two functions of adding and rewriting data do not support adding multiple sets of data at one time. This is a one-time addition. The reason is that I deleted it because I thought it was too cumbersome. If you are interested, you can provide a solution

The above is the detailed content of How to write your own database package (2). For more information, please follow other related articles on the PHP Chinese website!