php对mysql pdo多数据库连接类简易封装

<?php

/**
 * pdo处理mysql数据
 * Class Mysql
 */
class MysqlConnection
{
    const PARAM_PREFIX = ':qp';

    private $pdo;

    private $dsn;

    private function __construct($dbConfig)
    {
        try{
            $host = $dbConfig['host'];
            $port = $dbConfig['port'];
            $user = $dbConfig['user'];
            $pass = $dbConfig['pass'];
            $database = $dbConfig['database'];
            $charset = $dbConfig['charset'];

            $this->dsn = "mysql:host=$host;port=$port;dbname=$database;charset=$charset";
            $this->pdo = new \PDO($this->dsn, $user, $pass);
        }catch (\PDOException $e){
            die($e->getMessage());
        }
    }

    /**
     * @param $dbConfig
     * @return MysqlConnection
     */
    public static function connect($dbConfig)
    {
        return new self($dbConfig);
    }

    /**
     * @param $sql
     * @param array $params
     * @return int
     */
    public function execute($sql, $params = [])
    {
        try{
            $statement = $this->prepare($sql);
            $statement->execute($params);
            $affectRows = $statement->rowCount();
        }catch (\Exception $e){
            die($e->getMessage());
        }

        return $affectRows;
    }

    /**
     * 准备要执行的sql语句
     * @param $sql
     * @return \PDOStatement
     */
    protected function prepare($sql)
    {
        return $this->pdo->prepare($sql);
    }

    /**
     * 查询并返回所有目标数据
     * @param $sql
     * @param array $params
     * @return array
     */
    public function queryAll($sql, $params = [])
    {
        return $this->_query($sql, $params)->fetchAll();
    }

    /**
     * 查询并返回一条数据
     * @param $sql
     * @param array $params
     * @return mixed
     */
    public function queryOne($sql, $params = [])
    {
        return $this->_query($sql, $params)->fetch();
    }

    /**
     * 查询并返回一列数据，指某一列字段值
     * @param $sql
     * @param array $params
     * @return array
     */
    public function queryColumn($sql, $params = [])
    {
        return $this->_query($sql, $params)->fetchAll(\PDO::FETCH_COLUMN);
    }

    /**
     * 查询并返回某个值，默认首行首列字段值
     * @param $sql
     * @param array $params
     * @return string
     */
    public function queryScalar($sql, $params = [])
    {
        return $this->_query($sql, $params)->fetchColumn();
    }

    /**
     * 执行预处理语句
     * @param $sql
     * @param array $params
     * @return null|\PDOStatement
     */
    private function _query($sql, $params = [])
    {
        $statement = null;
        try{
            $statement = $this->prepare($sql);
            $statement->setFetchMode(\PDO::FETCH_ASSOC);
            $statement->execute($params);
        }catch (\PDOException $e) {
            die($e->getMessage());
        }

        return $statement;
    }

    /**
     * 配合execute()作数据插入使用，若执行insert()成功则默认返回此函数值，无需再次调用
     * @return string
     */
    public function getInsertId()
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * @param $table
     * @param array $columns
     * @return int|string
     */
    public function insert($table, $columns = [])
    {
        $sql = "INSERT INTO " . $this->quoteTableName($table);
        $params = [];
        $fields = [];
        $placeholders = [];
        foreach ($columns as $field => $value)
        {
            $fields[] = $this->quoteColumnName($field);
            $phName = self::PARAM_PREFIX . count($params);
            $placeholders[] = $phName;
            $params[$phName] = $value;
        }

        $sql .= ' (' . implode(',', $fields) . ') VALUES (' . implode(',', $placeholders) . ')';

        $affectRows = $this->execute($sql, $params);
        $id = $this->getInsertId();
        if(empty($id))
        {
            return $affectRows;
        }else{
            return $id;
        }
    }

    /**
     * @param $table
     * @param array $columns
     * @param null|string|array $condition
     * @return int
     */
    public function update($table, $columns = [], $condition = null)
    {
        $sql = "UPDATE " . $this->quoteTableName($table) . " SET ";
        $params = [];
        $fields = [];
        foreach ($columns as $field => $value)
        {
            $phName = self::PARAM_PREFIX . count($params);
            $fields[] = $this->quoteColumnName($field) . '=' . $phName;
            $params[$phName] = $value;
        }

        $sql .= implode(',', $fields);
        if(!is_null($condition))
        {
            $sql .= ' WHERE ' . $this->buildCondition($condition, $params);
        }

        return $this->execute($sql, $params);
    }

    /**
     * @param $table
     * @param null $condition
     * @return int
     */
    public function delete($table, $condition = null)
    {
        $sql = "DELETE FROM " . $this->quoteTableName($table);
        $params = [];
        if(!is_null($condition))
        {
            $sql .= ' WHERE ' . $this->buildCondition($condition, $params);
        }

        return $this->execute($sql, $params);
    }

    /**
     * 构建条件语句
     * @param $condition
     * @param $params
     * @return string
     */
    protected function buildCondition($condition, &$params)
    {
        try{
            if(is_string($condition))
            {
                return $condition;
            }elseif(is_array($condition)){
                $fields = [];
                foreach ($condition as $field => $value)
                {
                    $phName = self::PARAM_PREFIX . count($params);
                    $fields[] = $this->quoteColumnName($field) . '=' . $phName;
                    $params[$phName] = $value;
                }

                return implode(' AND ', $fields);
            }else{
                throw new \Exception('不是有效的条件语句！');
            }
        }catch(\Exception $e){
            die($e->getMessage());
        }
    }

    /**
     * @param $name
     * @return string
     */
    public function quoteTableName($name)
    {
        return strpos($name, '`') !== false ? $name : "`$name`";
    }

    /**
     * @param $name
     * @return string
     */
    public function quoteColumnName($name)
    {
        return strpos($name, '`') !== false || $name === '*' ? $name : "`$name`";
    }

    /**
     * @return \PDO
     */
    public function getHandler()
    {
        return $this->pdo;
    }

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

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

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

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

}



$userConf = [
    'host' => '192.168.31.196',
    'port' => 3306,
    'user' => 'root',
    'pass' => '123456',
    'database' => 'user',
    'charset' => 'utf8',
];

$goodsConf = $userConf;
$goodsConf['database'] = 'goods';

$userConn = MysqlConnection::connect($userConf);
$goodsConn = MysqlConnection::connect($goodsConf);


//$insertUser = "insert into tbl_user (name,mobile) values ('Jerry', '18200001111')";
//$insertGoods = "insert into tbl_goods (name,price) values ('pear', 20.50)";
//var_dump($userConn->execute($insertUser));
//var_dump($goodsConn->execute($insertGoods));


//$s1 = 'select * from tbl_user';
//$s2 = 'select * from tbl_goods';
//$r1 = $userConn->queryAll($s1);
//$r2 = $goodsConn->queryAll($s2);
//print_r($r1); echo '<br/>';
//print_r($r2); echo '<br/>';

//$i1 = $userConn->insert('tbl_user', ['name' => 'lisi', 'mobile' => '13100001111']);
//$u1 = $userConn->update('tbl_user', ['mobile' => '13200001111'], ['name' => 'zhangsan']);

//var_dump($u1);