db封装

我就是我 2022-11-29 03:30 117阅读 0赞

# db封装 #

> 以下`Connection`类封装支持以下几个特性

*  1.参数绑定防止sql注入
 *  2.读写分离
 *  3.多主多从，多节点负载均衡
 *  4.故障自动摘除及自动恢复

> 代码实现

<?php
    class Connection
    { 
        /** * @var array * @datetime 2020/7/5 10:20 AM * @author roach * @email jhq0113@163.com */
        public $masters = [];
    
        /** * @var array * @datetime 2020/7/5 10:20 AM * @author roach * @email jhq0113@163.com */
        public $slaves = [];
    
        /** * @var \PDO * @datetime 2020/7/5 10:21 AM * @author roach * @email jhq0113@163.com */
        protected $_master;
    
        /** * @var \PDO * @datetime 2020/7/5 10:21 AM * @author roach * @email jhq0113@163.com */
        protected $_slave;
    
        /** * @var string * @datetime 2020/7/6 1:32 下午 * @author roach * @email jhq0113@163.com */
        protected $_driver;
    
        /** * @return string * @datetime 2020/7/6 1:49 下午 * @author roach * @email jhq0113@163.com */
        public function getDriver()
        { 
            if(is_null($this->_driver)) { 
                $this->_driver = ucfirst(substr($this->masters[0]['dsn'], 0, strpos($this->masters[0]['dsn'], ':')));
            }
            return $this->_driver;
        }
    
        /** * @param array $configs * @return \PDO * @datetime 2020/7/5 12:19 PM * @author roach * @email jhq0113@163.com */
        protected function _select($configs = [])
        { 
            shuffle($configs);
    
            foreach ($configs as $config) { 
                try { 
                    if(!isset($config['options'])) { 
                        $config['options'] = [];
                    }
                    $config['options'][ \PDO::ATTR_ERRMODE ] = \PDO::ERRMODE_EXCEPTION;
    
                    $pdo = new \PDO($config['dsn'], $config['username'], $config['password'], $config['options']);
                    return $pdo;
                }catch (\Throwable $throwable) { 
                    continue;
                }
            }
        }
    
        /** * @return \PDO * @throws Exception * @datetime 2020/7/5 10:30 AM * @author roach * @email jhq0113@163.com */
        protected function _master()
        { 
            if(is_null($this->_master)) { 
                $this->_master = $this->_select($this->masters);
                if(is_null($this->_master)) { 
                    throw new Exception('没有可用的master数据库了');
                }
            }
    
            return $this->_master;
        }
    
        /** * @return \PDO * @throws Exception * @datetime 2020/7/5 10:31 AM * @author roach * @email jhq0113@163.com */
        protected function _slave()
        { 
            if(is_null($this->_slave)) { 
                if(empty($this->slaves)) { 
                    $this->slaves = $this->masters;
                }
    
                $this->_slave = $this->_select($this->slaves);
                if(is_null($this->_slave)) { 
                    throw new Exception('没有可用的slave数据库了');
                }
            }
    
            return $this->_slave;
        }
    
        /** * @param bool $useMaster * @param string $sql * @return bool|\PDOStatement * @throws Exception * @datetime 2020/7/5 12:12 PM * @author roach * @email jhq0113@163.com */
        protected function _createCommand($useMaster, $sql)
        { 
            try { 
                if($useMaster) { 
                    $pdo = $this->_master();
                    return $pdo->prepare($sql);
                } else { 
                    $pdo = $this->_slave();
                    return $pdo->prepare($sql);
                }
            }catch (\Throwable $throwable) { 
                //连接断了，原因可能是超时、mysql宕机等，会重新选择一个数据库，仅重新选择一次
                if($useMaster) { 
                    $this->_master = null;
                    $pdo = $this->_master();
                    return $pdo->prepare($sql);
                } else { 
                    $this->_slave = null;
                    $pdo = $this->_slave();
                    return $pdo->prepare($sql);
                }
            }
        }
    
        /** * @return string * @datetime 2020/7/5 10:42 AM * @author roach * @email jhq0113@163.com */
        public function lastInsertId()
        { 
            return $this->_master->lastInsertId();
        }
    
        /** * @param string $sql * @param array $params * @param bool $useMaster * @return array * @throws Exception * @datetime 2020/7/5 12:13 PM * @author roach * @email jhq0113@163.com */
        public function queryAll($sql, $params = [], $useMaster = false)
        { 
            $stmt = $this->_createCommand($useMaster, $sql);
            $stmt->execute($params);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            $stmt->closeCursor();
    
            return $rows;
        }
    
        /** * @param $sql * @param array $params * @return int * @throws Exception * @throws \ReflectionException * @datetime 2020/7/5 12:13 PM * @author roach * @email jhq0113@163.com */
        public function execute($sql, $params = [])
        { 
            $stmt = $this->_createCommand(true, $sql);
            $stmt->execute($params);
    
            return $stmt->rowCount();
        }
    
        /** * @return bool * @throws Exception * @datetime 2020/7/5 10:44 AM * @author roach * @email jhq0113@163.com */
        public function begin()
        { 
            return $this->_master()->beginTransaction();
        }
    
        /** * @return bool * @throws Exception * @datetime 2020/7/5 10:45 AM * @author roach * @email jhq0113@163.com */
        public function rollback()
        { 
            return $this->_master()->rollBack();
        }
    
        /** * @return bool * @throws Exception * @datetime 2020/7/5 10:48 AM * @author roach * @email jhq0113@163.com */
        public function commit()
        { 
            return $this->_master()->commit();
        }
    
        /** * @param callable $handler * @return bool * @throws Exception * @datetime 2020/7/5 10:50 AM * @author roach * @email jhq0113@163.com */
        public function transaction(callable $handler)
        { 
            $result = $this->begin();
            if(!$result) { 
                return false;
            }
    
            $result = call_user_func($handler, $this);
            if($result) { 
               return $this->commit();
            }
    
            $this->rollback();
            return false;
        }
    }

> 使用

<?php
    $connection = new Connection([
        'masters' => [
            [
                'dsn'      => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
                'username' => 'browser',
                'password' => 'browser.360'
            ]
        ],
        'slaves' => [
            [
                'dsn'      => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
                'username' => 'browser',
                'password' => 'browser.360'
            ],
            [
                'dsn'      => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
                'username' => 'browser',
                'password' => 'browser.360'
            ],
        ]
    ]);
    
    $rows = $connection->execute('INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(?, ?, ?)', [
        uniqid('u'), uniqid('t'), time()
    ]);
    
    $userId = $connection->lastInsertId();
    
    echo '插入的用户id为：'.$userId.';受影响行数为：'.$rows.PHP_EOL;
    
    $rows = $connection->execute('UPDATE `t_user` SET add_time=? WHERE id=?', [
        time()+1, $userId
    ]);
    
    echo '修改的受影响行数为：'.$rows.PHP_EOL;
    
    $users = $connection->queryAll('SELECT * FROM `t_user` WHERE id=?', [
        $userId
    ]);
    
    echo '插入和修改的用户信息为：'.json_encode($users[0], JSON_UNESCAPED_UNICODE).PHP_EOL;
    
    //强制读主库
    $users = $connection->queryAll('SELECT * FROM `t_user` LIMIT 5', [], true);
    echo '主库读取到的数据：'.json_encode($users, JSON_UNESCAPED_UNICODE).PHP_EOL;
    
    //使用事务
    try { 
        $connection->begin();
        $rows = $connection->execute('INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(?, ?, ?)', [
            uniqid('u'), uniqid('t'), time()
        ]);
        if($rows < 1) { 
            throw new \Exception('插入失败');
        }
    
        $userId = $connection->lastInsertId();
        if($userId % 2 !== 0) { 
            throw new \Exception('用户id只能为偶数');
        }
    
        $connection->commit();
        echo '提交事务成功'.PHP_EOL;
    }catch (\Exception $exception) { 
        $connection->rollback();
        echo $exception->getMessage().PHP_EOL;
    }

> 如果您需要一个orm封装，可以通过以下指令安装

composer require jhq0113/roach-orm

> `jhq0113/roach-orm`源码及文档地址

*  [https://github.com/jhq0113/roach-orm][https_github.com_jhq0113_roach-orm]

### [学习更多内容: https://404.360tryst.com][https_404.360tryst.com] ###

### [我的视频课程: https://edu.csdn.net/course/detail/9933][https_edu.csdn.net_course_detail_9933] ###

[https_github.com_jhq0113_roach-orm]: https://github.com/jhq0113/roach-orm
[https_404.360tryst.com]: https://404.360tryst.com
[https_edu.csdn.net_course_detail_9933]: https://edu.csdn.net/course/detail/9933