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 = 'mysql'; // 数据库地址 private $host = 'localhost'; // 数据库默认名称, 设置为静态是因为有切换数据库的需求 private static $db = 'sakila'; // 数据库用户名 private $username = 'root'; // 数据库密码 private $password = ''; // 当前数据库连接 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, ]; }
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;"
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()); } }
setDatabase - Switch database
public function setDatabase($db) { self::$db = $db; return $this; }
_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]; }
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));
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)
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); }
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 ); } }
So do you understand _( :3 ∠)
// 与read不同的地方在于, read返回数据, update返回boolean(true/false) public function update($sql, $bindings) { $statement = $this->connection->prepare($sql); $this->bindValues($statement, $bindings); return $statement->execute(); }
// 与update一样, 分开是因为方便日后维护制定 public function delete($sql, $bindings) { $statement = $this->connection->prepare($sql); $this->bindValues($statement, $bindings); return $statement->execute(); }
// 返回最新的自增ID, 如果有 public function create($sql, $bindings) { $statement = $this->connection->prepare($sql); $this->bindValues($statement, $bindings); $statement->execute(); return $this->lastInsertId(); }
Add id, if any
// pdo自带,只是稍微封装 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(); }
Complete code
<?php class Connector { // 数据库地址前缀,常见的有mysql,slqlsrv,odbc等等等 private $driver = 'mysql'; // 数据库地址 private $host = 'localhost'; // 数据库默认名称, 设置为静态是因为有切换数据库的需求 private static $db = 'sakila'; // 数据库用户名 private $username = 'root'; // 数据库密码 private $password = ''; // 当前数据库连接 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(); } }
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!