PDO基础操作类

花了点时间写了个基础的PDO操作类,仅供需要的童鞋参考,也欢迎板砖来拍。
特性:预处理,字符转义,事务操作,ping

话不多说,上代码

<?php
    
    /**
     * PDO操作类
     *
     * User: xiangqian
     * Date: 17/1/2
     * Time: 下午5:19
     */
    namespace Lib\Model;
    
    use \Conf\MysqlConf;
    
    class PdoModel
    {
        public $dbName;     //数据库名称
        private $dbConfig;  //数据库配置
    
        private static $_instance;
        private static $dbh;
    
        /**
         * 构造方法
         *
         * PdoModel constructor.
         * @param $dbName
         */
        private function __construct($dbName)
        {
            $this->dbName = $dbName;
            $this->dbConfig = MysqlConf::$dbConfig;
    
            $dsn = "mysql:host={$this->dbConfig['host']};dbname={$this->dbName}";
            self::$dbh = new \PDO($dsn, $this->dbConfig['username'], $this->dbConfig['password'], $this->dbConfig['options']);
        }
    
        /**
         * mysql连接是否可用
         *
         * @return bool
         */
        public static function ping()
        {
            try {
                self::$dbh->getAttribute(\PDO::ATTR_SERVER_INFO);
            } catch (\PDOException $e) {
                if (strpos($e->getMessage(), 'MySQL server has gone away') !== false) {
                    return false;
                }
            }
            return true;
        }
    
        /**
         * 重置连接
         */
        public static function resetConnect()
        {
            self::$_instance = null;
        }
    
        /**
         * 不允许克隆
         */
        public function __clone()
        {
            trigger_error('Not allowed to be cloned', E_USER_ERROR);
        }
    
        /**
         * 获取实例对象
         *
         * @param string $dbName
         * @return PdoModel
         */
        public static function getInstance($dbName = 'testdb')
        {
            if (!isset(self::$_instance) || !(self::$_instance instanceof self)) {
                self::$_instance = new self($dbName);
            }
            return self::$_instance;
        }
    
        /**
         * 查询单条数据
         *
         * @param string $tableName
         * @param array $where
         * @param array $fields
         * @return array|mixed
         */
        public function getRow($tableName = '', array $where, $fields = [])
        {
            $fields = self::_parseFields($fields);
            try {
                $parseWhere = self::_parseWhere($where);
                $sql = "SELECT {$fields} FROM `{$tableName}` WHERE {$parseWhere['whereStr']} LIMIT 1";
                $stmt = self::$dbh->prepare($sql);
                $stmt->execute($parseWhere['bindArr']);
                return $stmt->fetch();
            } catch (\PDOException $e) {
                return [];
            }
        }
    
        /**
         * 查询列表数据数据 常规查询
         *
         * @param string $tableName
         * @param array $where
         * @param array $fields
         * @param string $order
         * @param int $skip
         * @param int $limit
         * @return array
         */
        public function getList($tableName = '', array $where, $fields = [], $order = 'id asc', $skip = 0, $limit = 20)
        {
            $fields = self::_parseFields($fields);
            try {
                $parseWhere = self::_parseWhere($where);
                $sql = "SELECT {$fields} FROM `{$tableName}` WHERE {$parseWhere['whereStr']} ORDER BY {$order} LIMIT {$skip}, {$limit}";
                $stmt = self::$dbh->prepare($sql);
                $stmt->execute($parseWhere['bindArr']);
                return $stmt->fetchAll();
            } catch (\PDOException $e) {
                return [];
            }
        }
    
        /**
         * 查询 自己写复杂sql
         *
         * @param $sql
         * @param bool $one
         * @return array|mixed
         */
        public function query($sql, $one = false)
        {
            $sql = addslashes($sql);
            try {
                $stmt = self::$dbh->query($sql);
                return $one ? $stmt->fetch() : $stmt->fetchAll();
            } catch (\PDOException $e) {
                return [];
            }
        }
    
        /**
         * 删除数据
         *
         * @param string $tableName
         * @param array $where
         * @return int
         */
        public function delete($tableName = '', array $where)
        {
            try {
                $parseWhere = self::_parseWhere($where);
                $sql = "DELETE FROM `{$tableName}` WHERE {$parseWhere['whereStr']}";
                $stmt = self::$dbh->prepare($sql);
                $stmt->execute($parseWhere['bindArr']);
                return $stmt->rowCount();
            } catch (\PDOException $e) {
                return 0;
            }
        }
    
        /**
         * 更新数据
         *
         * @param $tableName
         * @param array $where
         * @param array $update
         * @return int
         */
        public function update($tableName, array $where, array $update)
        {
            try {
                $parseUpdate = self::_parseUpdate($update);
                $parseWhere = self::_parseWhere($where);
                $sql = "UPDATE `{$tableName}` SET {$parseUpdate['whereStr']} WHERE {$parseWhere['whereStr']}";
                $stmt = self::$dbh->prepare($sql);
                $bindArr = array_merge($parseUpdate['bindArr'], $parseWhere['bindArr']);
                $stmt->execute($bindArr);
                return $stmt->rowCount();
            } catch (\PDOException $e) {
                return 0;
            }
        }
    
        /**
         * 插入语句
         *
         * @param $tableName
         * @param array $arr
         * @return int
         */
        public function insert($tableName, array $arr)
        {
            $parseInsert = self::_parseInsert($arr);
            try {
                $sql = "INSERT INTO `{$tableName}` ({$parseInsert['keyStr']}) VALUES ({$parseInsert['bindKeyStr']})";
                $stmt = self::$dbh->prepare($sql);
                $re = $stmt->execute($parseInsert['bindArr']);
                return $re ? self::$dbh->lastInsertId() : 0;
            } catch (\PDOException $e) {
                return 0;
            }
        }
    
        /**
         * 事务操作
         *
         * @param array $tranStr
         * @return bool
         */
        public function excuteTransaction(array $tranStr)
        {
            $re = false;
            try {
                self::$dbh->beginTransaction();
                foreach ($tranStr as $stateStr) {
                    self::$dbh->exec($stateStr);
                }
                $re = self::$dbh->commit();
            } catch (\PDOException $e) {
                self::$dbh->rollBack();
            }
            return $re;
        }
    
        /**
         * 解析where
         *
         * @param array $where
         * @return array
         * @throws \Exception
         */
        private static function _parseWhere(array $where)
        {
            if (!is_array($where) || empty($where)) {
                throw new \Exception('分析where语句失败, where参数不能为空');
            }
            $whereStr = '';
            $bindArr = [];
            foreach ($where as $k => $item) {
                $bindKey = ':w_' . $k;
                $whereStr .= "`{$k}` {$item['operate']} $bindKey AND ";
                $bindArr[$bindKey] = $item['value'];
            }
            return [
                'whereStr' => rtrim($whereStr, 'AND '),
                'bindArr' => $bindArr
            ];
        }
    
        /**
         * 解析update
         *
         * @param array $update
         * @return array
         * @throws \Exception
         */
        private static function _parseUpdate(array $update)
        {
            if (!is_array($update) || empty($update)) {
                throw new \Exception('分析update语句失败, update参数不能为空');
            }
            $whereStr = '';
            $bindArr = [];
            foreach ($update as $k => $value) {
                $bindKey = ':k_' . $k;
                $whereStr .= "`{$k}` = $bindKey AND ";
                $bindArr[$bindKey] = $value;
            }
            return [
                'whereStr' => rtrim($whereStr, 'AND '),
                'bindArr' => $bindArr
            ];
        }
    
        /**
         * 解析插入语句
         *
         * @param array $arr
         * @return array
         * @throws \Exception
         */
        private static function _parseInsert(array $arr)
        {
            if (!is_array($arr) || empty($arr)) {
                throw new \Exception('分析insert语句失败, arr参数不能为空');
            }
            $separator = ', ';
            $keyStr = '';
            $bindKeyStr = '';
            $bindArr = [];
            foreach ($arr as $k => $value) {
                $bindKey = ':k_' . $k;
                $keyStr .= "`{$k}` {$separator}";
                $bindKeyStr .= $bindKey . $separator;
                $bindArr[$bindKey] = $value;
            }
            return [
                'keyStr' => rtrim($keyStr, $separator),
                'bindKeyStr' => rtrim($bindKeyStr, $separator),
                'bindArr' => $bindArr
            ];
        }
    
        /**
         * 解析fields字段
         *
         * @param array $fields
         * @return string
         * @throws \Exception
         */
        private static function _parseFields(array $fields)
        {
            $fieldStr = '';
            if (empty($fields) || !is_array($fields)) {
                return '*';
            }
            foreach ($fields as $field) {
                if (!is_string($field)) {
                    throw new \Exception("field必须是字符串,field=" . json_encode($field), '-1');
                }
                $fieldStr .= "`{$field}`, ";
            }
            return rtrim($fieldStr, ', ');
        }
    }