PHP PDO operation MYSQL encapsulation class

/**
 * author soulence
 * Call data class file
 * modify 2015/06/12
*/
class DBConnect
{
    private $dbname = null;
    private $pdo = null;
    private $persistent = false;
    private $statement = null;
    private $lastInsID = null;
    private static $_instance = [];

    private function __construct($dbname,$attr)
    {
        $this->dbname = $dbname;
        $this->persistent = $attr;
    }

    public static function db($flag='r',$persistent=false)
    {
        if(!isset($flag)){
            $flag = 'r';
        }

        if (!class_exists('PDO'))
        {
            throw new Exception('not found PDO');
            return false;
        }
        $mysql_server = Yaf_Registry::get('mysql');
        if(!isset($mysql_server[$flag])){
            return false;
        }

        $options_arr = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.$mysql_server[$flag]['charset'],PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC);
        if($persistent === true){
            $options_arr[PDO::ATTR_PERSISTENT] = true;
        }

        try {
            $pdo = new PDO($mysql_server[$flag]['connectionString'],$mysql_server[$flag]['username'],$mysql_server[$flag]['password'],$options_arr);
        } catch (PDOException $e) {
            throw new Exception($e->getMessage());
            //exit('连接失败:'.$e->getMessage());
            return false;
        }

        if(!$pdo) {
            throw new Exception('PDO CONNECT ERROR');
            return false;
        }

        return $pdo;
    }

    /**
 * Get the operation database object
 * @param string $dbname Who is the corresponding database?
 * @param bool $attr Whether the connection is long
 * Return false indicates that the given database does not exist
*/
    public static function getInstance($dbname = 'r',$attr = false)
    {
        $mysql_server = Yaf_Registry::get('mysql');
        if(!isset($mysql_server[$dbname])){
            return false;
        }
        $key = md5(md5($dbname.$attr,true));
        if (!isset(self::$_instance[$key]) || !is_object(self::$_instance[$key]))
            self::$_instance[$key] = new self($dbname,$attr);
        return self::$_instance[$key];
    }

    private function getConnect(){
        $this->pdo = self::db($this->dbname,$this->persistent);
    }

    /**
 * Query operation
 * @param string $sql SQL statement to execute the query
 * @param array $data The conditional format of the query is [':id'=>$id,':name'=>$name]( Recommended) or [1=>$id,2=>$name]
 * @param bool $one Whether to return a piece of content, the default is no
*/
    public function query($sql, $data = [], $one = false)
    {
        if (!is_array($data) || empty($sql) || !is_string($sql))
            return false;

        $this->free();

        return $this->queryCommon($data,$sql,$one);
    }

    /**
 * Common methods for internal queries
*/
    private function queryCommon($data,$sql,$one)
    {
        $this->pdoExec($data,$sql);

        if ($one){
            return $this->statement->fetch(PDO::FETCH_ASSOC);
        }else{
            return $this->statement->fetchAll(PDO::FETCH_ASSOC);
        }
    }

    /**
 * Query operation of multiple SQL statements
 * @param array $arr_sql The sql statement array format for executing the query is [$sql1,$sql2]
 * @param array $arr_data The conditional format corresponding to the query $arr_sql is [[' :id'=>$id,':name'=>$name],[':id'=>$id,':name'=>$name]] (recommended) or [[1 =>$id,2=>$name],[1=>$id,2=>$name]]
 * @param bool $one Whether to return a piece of content. The default is no. If set to true, then Each sql only returns one piece of data
*/
    public function queryes($arr_sql, $arr_data = [], $one = false)
    {
        if(!is_array($arr_sql) || empty($arr_sql) || !is_array($arr_data))
            return false;

        $this->free();

        $res = [];$i = 0;
        foreach ($arr_sql as $val) {
            if(!isset($arr_data[$i]))
                $arr_data[$i] = [];
            elseif(!is_array($arr_data[$i]))
                throw new Exception('Error where queryes sql:'.$val.' where:'.$arr_data[$i]);

            $res[] = $this->queryCommon($arr_data[$i],$val,$one);
            $i++;
        }
        return $res;
    }

    /**
 * Paging encapsulation
 *
 * @param string $sql
 * @param int $page indicates which page to start from
 * @param int $pageSize indicates how many items per page
 * @param array $data query conditions
*/
    public function limitQuery($sql, $page=0, $pageSize=20, $data = [])
    {
        $page = intval($page);
        if ($page             return [];
        }
        $pageSize = intval($pageSize);
        if ($pageSize > 0) { // pageSize 为0时表示取所有数据
            $sql .= ' LIMIT ' . $pageSize;
            if ($page > 0) {
                $start_limit = ($page - 1) * $pageSize;
                $sql .= ' OFFSET ' . $start_limit;
            }
        }
        return $this->query($sql, $data);
    }

    /**
 * This is used for adding, deleting and modifying operations using transaction operations
 * @param string $sql The sql statement to execute the query
 * @param array $data The conditional format of the query is [':id'=>$id,' :name'=>$name] (recommended) or [1=>$id,2=>$name]
 * @param bool $Transaction Whether the transaction operation defaults to No
*/
    public function executeDDL($sql, $data = [],$Transaction = false){
        if (!is_array($data) || !is_string($sql))
            return false;

        $this->free();

        if($Transaction)
            $this->pdo->beginTransaction();//开启事务
        try{
            $this->execRes($data,$sql);
            if($Transaction)
                $this->pdo->commit();//事务提交
            return $this->lastInsID;
        } catch (Exception $e) {
            if($Transaction)
                $this->pdo->rollBack();//事务回滚
            throw new Exception('Error DDLExecute '.$e->getMessage());
            return false;
        }
    }

    /**
 * This is used to perform add, delete and modify operations using transaction operations
 * It executes multiple items
 * @param array $arr_sql The array of SQL statements that need to be executed
 * @param array $arr_data The conditions of the SQL statement corresponding to the array
 * @param bool $Transaction Whether the transaction operation defaults to No
*/
    public function executeDDLes($arr_sql, $arr_data = [],$Transaction = false){

        if(!is_array($arr_sql) || empty($arr_sql) || !is_array($arr_data))
            return false;

        $res = [];

        $this->free();

        if($Transaction)
            $this->pdo->beginTransaction();//开启事务
        try{
            $i = 0;
            foreach($arr_sql as $val){
                if(!isset($arr_data[$i]))
                    $arr_data[$i] = [];
                elseif(!is_array($arr_data[$i])){
                    if($Transaction)
                        $this->pdo->rollBack();//事务回滚
                    throw new Exception('Error where DDLExecutees sql:'.$val.' where:'.$arr_data[$i]);
                }

                $this->execRes($arr_data[$i],$val);
                $res[] = $this->lastInsID;
                $i++;
            }

            if($Transaction)
                $this->pdo->commit();//事务提交

            return $res;
        } catch (Exception $e) {
            if($Transaction)
                $this->pdo->rollBack();//事务回滚
            throw new Exception('Error DDLExecutees array_sql:'.json_encode($arr_sql).' '.$e->getMessage());
            return false;
        }
        return $res;
    }

    /**
 * This method is used to calculate the number of items returned by the query. Note that it only supports SELECT COUNT(*) FROM TABLE... or SELECT COUNT(0) FROM TABLE...
 * @param string $sql The sql statement of the query
 * @param array $data Condition of SQL statement
*/
    public function countRows($sql,$data = []){
        if (!is_array($data) || empty($sql) || !is_string($sql))
            return false;
        $this->free();

        $res = $this->pdoExec($data,$sql);

        if($res == false)
            return false;

        return $this->statement->fetchColumn();
    }

    /**
 * This method is used to calculate the number of items returned by the query. It is used to execute multiple SQL statements.
 * @param string $sql The sql statement of the query
 * @param array $data The conditions of the SQL statement
*/
    public function countRowses($arr_sql,$arr_data = []){

        if(!is_array($arr_sql) || empty($arr_sql) || !is_array($arr_data))
            return false;

        $res = [];

        $this->free();
        $i = 0;
        foreach ($arr_sql as $val) {
            if(!isset($arr_data[$i]))
                $arr_data[$i] = [];
            elseif(!is_array($arr_data[$i]))
                throw new Exception('Error where CountRowses sql:'.$val.' where:'.$arr_data[$i]);

            $res1 = $this->pdoExec($arr_data[$i],$val);

            if($res1 == false)
                $res[] = false;
            else
                $res[] = $this->statement->fetchColumn();
        }

        return $res;
    }

    /**
 * Here is another method because there will be many needs in the project to open transactions and then perform operations and finally submit
 * @param bool $Transaction Whether to perform transaction operations, the default is no
*/
    public function getDB($Transaction=false)
    {
        $this->Transaction = $Transaction;
        $this->getConnect();
        if($Transaction === true)
            $this->pdo->beginTransaction();//开启事务
        return $this;
    }

    /**
 * This method can be executed multiple times. It is used to execute DDL statements.
 * Note that it needs to be used together with getDB and sQCommit and cannot be used alone.
 * If the transaction is not enabled, the sQCommit method does not need to be called.
 * @param string $sql query sql statement
 * @param array $data Conditions of SQL statement
*/
    public function execSq($sql,$data = [])
    {
        if($this->checkParams($sql,$data) === false)
            return false;

        try{
            $this->execRes($data,$sql);
            return $this->lastInsID;
        } catch (Exception $e) {
            if(isset($this->Transaction) && $this->Transaction === true)
                $this->pdo->rollBack();//事务回滚
            throw new Exception('Error execSq'.$e->getMessage());
            return false;
        } finally {
            if (!empty($this->statement))
            {
                $this->statement->closeCursor();
                unset($this->statement);
            }
        }
    }

    /**
 * The method of executing the query requires passing a connection database object
 * @param string $sql The sql statement to execute the query
 * @param array $data The conditional format of the query is [':id'=>$id,': name'=>$name] (recommended) or [1=>$id,2=>$name]
 * @param bool $one Whether to return a piece of content, the default is no
*/
    public function querySq($sql,$data = [],$one = false)
    {
        if($this->checkParams($sql,$data) === false)
            return false;

        return $this->pdoExecSq($sql,$data,[1,$one]);
    }

    /**
 * Paging encapsulation
 *
 * @param string $sql
 * @param int $page indicates which page to start from
 * @param int $pageSize indicates how many items per page
 * @param array $data query conditions
*/
    public function limitQuerySq($sql, $page=0, $pageSize=20, $data = [])
    {
        $page = intval($page);
        if ($page             return [];
        }
        $pageSize = intval($pageSize);
        if ($pageSize > 0) { // pageSize 为0时表示取所有数据
            $sql .= ' LIMIT ' . $pageSize;
            if ($page > 0) {
                $start_limit = ($page - 1) * $pageSize;
                $sql .= ' OFFSET ' . $start_limit;
            }
        }
        return $this->querySq($sql, $data);
    }

    /**
 * This method is used to calculate the number of items returned by the query. Note that it only supports SELECT COUNT(*) FROM TABLE... or SELECT COUNT(0) FROM TABLE...
 * @param string $sql The sql statement of the query
 * @param array $data Condition of SQL statement
*/
    public function countRowsSq($sql,$data = []){
        if($this->checkParams($sql,$data) === false)
            return false;
        return $this->pdoExecSq($sql,$data,[2]);
    }

    /**
 * Here is another method. This is the final commit operation. If the transaction is not started, this method does not need to be called at the end
*/
    public function sQCommit()
    {
        if(empty($this->pdo) || !is_object($this->pdo))
            return false;
        if(isset($this->Transaction) && $this->Transaction === true)
            $this->pdo->commit();//提交事务
        unset($this->pdo);
    }

    /**
 * Internal calling method
*/
    public function checkParams($sql,$data)
    {
        if (empty($this->pdo) || !is_object($this->pdo) || !is_array($data) || empty($sql) || !is_string($sql))
            return false;

        return true;
    }

    /**
 * Internal calling method
*/
    private function pdoExecSq($sql,$data,$select = []){
        try{
            $res = $this->pdoExec($data,$sql);
            if(empty($select))
                return $res;
            else{
                if($select[0] === 1){
                    if($select[1] === true)
                        return $this->statement->fetch(PDO::FETCH_ASSOC);
                    else
                        return $this->statement->fetchAll(PDO::FETCH_ASSOC);
                }elseif($select[0] === 2)
                    return $this->statement->fetchColumn();
                else
                    return false;
            }
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
            return false;
        } finally {
            if (!empty($this->statement))
            {
                $this->statement->closeCursor();
                unset($this->statement);
            }
        }
    }

    /**
 * Internal calling method
*/
    private function execRes($data,$sql){

        $res = $this->pdoExec($data,$sql);

        $in_id = $this->pdo->lastInsertId();

        if (preg_match("/^s*(INSERTs+INTO|REPLACEs+INTO)s+/i", $sql) && !empty($in_id))
            $this->lastInsID = $in_id;
        else
            $this->lastInsID = $res;
    }

    /**
 * Internal calling method used to directly execute SQL statements
*/
    private function pdoExec($data,$sql){
        $this->statement = $this->pdo->prepare($sql);
        if (false === $this->statement)
            return false;
        if (!empty($data))
        {
            foreach ($data as $k => $v)
            {
                $this->statement->bindValue($k, $v);
            }
        }
        $res = $this->statement->execute();
        if (!$res)
        {
            throw new Exception('sql:'.$sql.'where:'.json_encode($data).'error:'.json_encode($this->statement->errorInfo()));
        }else{
            return $res;
        }
    }

    /**
 * Internal calling method used to release
*/
    private function free()
    {
        if (is_null($this->pdo))
            $this->getConnect();

        if (!empty($this->statement))
        {
            $this->statement->closeCursor();
            $this->statement = null;
        }
    }
}
?>

PHP, PDO, MYSQL