PHP單例模式編寫的PDO類的程序

2024-05-04 21:49:46

字體：大中小

來源：轉載

供稿：網友

下面的代碼是用此前一個名為MyPDO的類改寫的,引入了單例模式來保證在全局調用中不會重復實例化這個類,降低系統資源的浪費,用php大部分操作都是和各種數據庫打交道,包括mysql,redis,memcache等各種關系型和非關系型數據庫,所以一個應用中會存在大量連接數據庫的操作,如果不用單例模式,那每次都要new操作,但是每次new都會消耗大量的內存資源和系統資源,而且每次打開和關閉數據庫連接都是對數據庫的一種極大考驗和浪費,代碼如下:

<?php

class MyPDO

{

    protected static $_instance = null;

    protected $dbName = '';

    protected $dsn;

    protected $dbh;



    /**

     * 構造

     *

     * @return MyPDO

     */

    private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)

    {

        try {

            $this->dsn = 'mysql:host='.$dbHost.';dbname='.$dbName;

            $this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd);

            $this->dbh->exec('SET character_set_connection='.$dbCharset.', character_set_results='.$dbCharset.', character_set_client=binary');

        } catch (PDOException $e) {

            $this->outputError($e->getMessage());

        }

    }



    /**

     * 防止克隆

     *

     */

    private function __clone() {}



    /**

     * Singleton instance

     *

     * @return Object

     */

    public static function getInstance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)

    {

        if (self::$_instance === null) {

            self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset);

        }

        return self::$_instance;

    }



    /**

     * Query 查詢

     *

     * @param String $strSql SQL語句

     * @param String $queryMode 查詢方式(All or Row)

     * @param Boolean $debug

     * @return Array

     */

    public function query($strSql, $queryMode = 'All', $debug = false)

    {

        if ($debug === true) $this->debug($strSql);

        $recordset = $this->dbh->query($strSql);

        $this->getPDOError();

        if ($recordset) {

            $recordset->setFetchMode(PDO::FETCH_ASSOC);

            if ($queryMode == 'All') {

                $result = $recordset->fetchAll();

            } elseif ($queryMode == 'Row') {

                $result = $recordset->fetch();

            }

        } else {

            $result = null;

        }

        return $result;

    }



    /**

     * Update 更新

     *

     * @param String $table 表名

     * @param Array $arrayDataValue 字段與值

     * @param String $where 條件

     * @param Boolean $debug

     * @return Int

     */

    public function update($table, $arrayDataValue, $where = '', $debug = false)

    {

        $this->checkFields($table, $arrayDataValue);

        if ($where) {

            $strSql = '';

            foreach ($arrayDataValue as $key => $value) {

                $strSql .= ", `$key`='$value'";

            }

            $strSql = substr($strSql, 1);

            $strSql = "UPDATE `$table` SET $strSql WHERE $where";

        } else {

            $strSql = "REPLACE INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";

        }

        if ($debug === true) $this->debug($strSql);

        $result = $this->dbh->exec($strSql);

        $this->getPDOError();

        return $result;

    }



    /**

     * Insert 插入

     *

     * @param String $table 表名

     * @param Array $arrayDataValue 字段與值

     * @param Boolean $debug

     * @return Int

     */

    public function insert($table, $arrayDataValue, $debug = false)

    {

        $this->checkFields($table, $arrayDataValue);

        $strSql = "INSERT INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";

        if ($debug === true) $this->debug($strSql);

        $result = $this->dbh->exec($strSql);

        $this->getPDOError();

        return $result;

    }



    /**

     * Replace 覆蓋方式插入

     *

     * @param String $table 表名

     * @param Array $arrayDataValue 字段與值

     * @param Boolean $debug

     * @return Int

     */

    public function replace($table, $arrayDataValue, $debug = false)

    {

        $this->checkFields($table, $arrayDataValue);

        $strSql = "REPLACE INTO `$table`(`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";

        if ($debug === true) $this->debug($strSql);

        $result = $this->dbh->exec($strSql);

        $this->getPDOError();

        return $result;

    }



    /**

     * Delete 刪除

     *

     * @param String $table 表名

     * @param String $where 條件

     * @param Boolean $debug

     * @return Int

     */

    public function delete($table, $where = '', $debug = false)

    {

        if ($where == '') {

            $this->outputError("'WHERE' is Null");

        } else {

            $strSql = "DELETE FROM `$table` WHERE $where";

            if ($debug === true) $this->debug($strSql);

            $result = $this->dbh->exec($strSql);

            $this->getPDOError();

            return $result;

        }

    }



    /**

     * execSql 執行SQL語句

     *

     * @param String $strSql

     * @param Boolean $debug

     * @return Int

     */

    public function execSql($strSql, $debug = false)

    {

        if ($debug === true) $this->debug($strSql);

        $result = $this->dbh->exec($strSql);

        $this->getPDOError();

        return $result;

    }



    /**

     * 獲取字段最大值

     *

     * @param string $table 表名

     * @param string $field_name 字段名

     * @param string $where 條件

     */

    public function getMaxValue($table, $field_name, $where = '', $debug = false)

    {

        $strSql = "SELECT MAX(".$field_name.") AS MAX_VALUE FROM $table";

        if ($where != '') $strSql .= " WHERE $where";

        if ($debug === true) $this->debug($strSql);

        $arrTemp = $this->query($strSql, 'Row');

        $maxValue = $arrTemp["MAX_VALUE"];

        if ($maxValue == "" || $maxValue == null) {

            $maxValue = 0;

        }

        return $maxValue;

    }



    /**

     * 獲取指定列的數量

     *

     * @param string $table

     * @param string $field_name

     * @param string $where

     * @param bool $debug

     * @return int

     */

    public function getCount($table, $field_name, $where = '', $debug = false)

    {

        $strSql = "SELECT COUNT($field_name) AS NUM FROM $table";

        if ($where != '') $strSql .= " WHERE $where";

        if ($debug === true) $this->debug($strSql);

        $arrTemp = $this->query($strSql, 'Row');

        return $arrTemp['NUM'];

    }



    /**

     * 獲取表引擎

     *

     * @param String $dbName 庫名

     * @param String $tableName 表名

     * @param Boolean $debug

     * @return String

     */

    public function getTableEngine($dbName, $tableName)

    {

        $strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name='".$tableName."'";

        $arrayTableInfo = $this->query($strSql);

        $this->getPDOError();

        return $arrayTableInfo[0]['Engine'];

    }



    /**

     * beginTransaction 事務開始

     */

    private function beginTransaction()

    {

        $this->dbh->beginTransaction();

    }



    /**

     * commit 事務提交

     */

    private function commit()

    {

        $this->dbh->commit();

    }



    /**

     * rollback 事務回滾

     */

    private function rollback()

    {

        $this->dbh->rollback();

    }



    /**

     * transaction 通過事務處理多條SQL語句

     * 調用前需通過getTableEngine判斷表引擎是否支持事務

     *

     * @param array $arraySql

     * @return Boolean

     */

    public function execTransaction($arraySql)

    {

        $retval = 1;

        $this->beginTransaction();

        foreach ($arraySql as $strSql) {

            if ($this->execSql($strSql) == 0) $retval = 0;

        }

        if ($retval == 0) {

            $this->rollback();

            return false;

        } else {

            $this->commit();

            return true;

        }

    }

    /**

     * checkFields 檢查指定字段是否在指定數據表中存在

     *

     * @param String $table

     * @param array $arrayField

     */

    private function checkFields($table, $arrayFields)

    {

        $fields = $this->getFields($table);

        foreach ($arrayFields as $key => $value) {

            if (!in_array($key, $fields)) {

                $this->outputError("Unknown column `$key` in field list.");

            }

        }

    }



    /**

     * getFields 獲取指定數據表中的全部字段名

     *

     * @param String $table 表名

     * @return array

     */

    private function getFields($table)

    {

        $fields = array();

        $recordset = $this->dbh->query("SHOW COLUMNS FROM $table");

        $this->getPDOError();

        $recordset->setFetchMode(PDO::FETCH_ASSOC);

        $result = $recordset->fetchAll();

        foreach ($result as $rows) {

            $fields[] = $rows['Field'];

        }

        return $fields;

    }



    /**

     * getPDOError 捕獲PDO錯誤信息

     */

    private function getPDOError()

    {

        if ($this->dbh->errorCode() != '00000') {

            $arrayError = $this->dbh->errorInfo();

            $this->outputError($arrayError[2]);

        }

    }



    /**

     * debug

     *

     * @param mixed $debuginfo

     */

    private function debug($debuginfo)

    {

        var_dump($debuginfo);

        exit();

    }



    /**

     * 輸出錯誤信息

     *

     * @param String $strErrMsg

     */

    private function outputError($strErrMsg)

    {

        throw new Exception('MySQL Error: '.$strErrMsg);

    }  //開源軟件:Vevb.com



    /**

     * destruct 關閉數據庫連接

     */

    public function destruct()

    {

        $this->dbh = null;

    }

}

?>