zend framework DB封装类

本文介绍了如何在zend framework中创建一个数据库操作的封装类,包括连接配置、查询执行、事务处理等关键功能,旨在提高代码复用性和数据库操作的安全性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >




zend framework DB封装类



<?php
abstract class Base_DataBase_Pdo_Abstract{
	const CASE_FOLDING = 'caseFolding';
	const AUTO_QUOTE_IDENTIFIERS = 'autoQuoteIdentifiers';
	protected $_config = array();
	protected $_connection = null;
	protected $_caseFolding = PDO::CASE_NATURAL;
	protected $_autoQuoteIdentifiers = true;
	protected $_fetchMode = PDO::FETCH_ASSOC;
	protected $_transactionLevel = 0;
	protected $_curdb = null;
	
	protected function __construct($connection = 'default'){
	    global $CONFIG;
	    $this->_curdb = $connection;
		$options = array(
            self::CASE_FOLDING           => $this->_caseFolding,
            self::AUTO_QUOTE_IDENTIFIERS => $this->_autoQuoteIdentifiers
        );
        
	    $driverOptions = array();
		if (array_key_exists('options', $CONFIG->database['database'])) {
            foreach ((array) $CONFIG->database['database']['options'] as $key => $value) {
                $options[$key] = $value;
            }
        }
	    if (array_key_exists('driver_options', $CONFIG->database['database'])) {
            if (!empty($CONFIG->database['database']['driver_options'])) {
                foreach ((array) $CONFIG->database['database']['driver_options'] as $key => $value) {
                    $driverOptions[$key] = $value;
                }
            }
        }
        
        $this->_config['options'] = $options;
        $this->_config['driver_options'] = $driverOptions;
		
	    if (array_key_exists(self::CASE_FOLDING, $options)) {
        	$case = (int) $options[self::CASE_FOLDING];
        	switch ($case) {
                case PDO::CASE_LOWER:
                case PDO::CASE_UPPER:
                case PDO::CASE_NATURAL:
                    $this->_caseFolding = $case;
                    break;
                default:
                    throw new DBException('Case must be one of the following constants: '
                        . 'PDO::CASE_NATURAL, PDO::CASE_LOWER, PDO::CASE_UPPER');
            }
        }
		if (array_key_exists(self::AUTO_QUOTE_IDENTIFIERS, $options)) {
            $this->_autoQuoteIdentifiers = (bool) $options[self::AUTO_QUOTE_IDENTIFIERS];
        }
	}
	
	protected function setdatabase($config){
		if(!is_array($config)){
			throw new DBException('DB exception');
		}
		
		$this->_checkRequiredOptions($config);
		
		if (!isset($config['charset'])) {
            $config['charset'] = null;
        }
        $this->_config = array_merge($this->_config, $config);
        
        //设置字符集
        $initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
        $this->_config['driver_options'][1002] = $initCommand; // 1002 = PDO::MYSQL_ATTR_INIT_COMMAND       
	}
	
	protected function _checkRequiredOptions($config){
		if (! array_key_exists('dbname', $config)) {
            throw new DBException("Configuration array must have a key for 'dbname' that names the database instance");
        }
		if (! array_key_exists('password', $config)) {
            throw new DBException("Configuration array must have a key for 'password' that names the database instance");
        }
		if (! array_key_exists('password', $config)) {
            throw new DBException("Configuration array must have a key for 'password' that names the database instance");
        }
	}
	protected function _dsn()
    {
        $dsn = $this->_config;
        unset($dsn['username']);
        unset($dsn['password']);
        unset($dsn['options']);
        unset($dsn['charset']);
        unset($dsn['persistent']);
        unset($dsn['driver_options']);
        foreach ($dsn as $key => $val) {
            $dsn[$key] = "$key=$val";
        }
        $c=$this->_pdoType . ':' . implode(';', $dsn);
        return $this->_pdoType . ':' . implode(';', $dsn);
    }
    protected function _connect(){
   	    global $CONFIG;	
    	if(isset($this->_connection)){
    		return;
    	}
    	$this->setdatabase($CONFIG->database[$this->_curdb]); 
    	$dsn = $this->_dsn();
    	if(!extension_loaded('pdo')){
    		new DBException('The PDO extension is required for this adapter but the extension is not loaded');
    	}
    	
    	//$drivers = PDO::getAvailableDrivers(); $dricers 共5个 sqlite,mysql,pgsql,sqlite2,oci
    	if (!in_array($this->_pdoType, PDO::getAvailableDrivers())) {
    		throw new DBException('The ' . $this->_pdoType . ' driver is not currently installed');
    	}
    	if (isset($this->_config['persistent']) && ($this->_config['persistent'] == true)) {
            $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true;
        }
    	if (isset($this->_config['timeout']) ) {
            $this->_config['driver_options'][PDO::ATTR_TIMEOUT] = $this->_config['timeout'];
        }
    	try {
            $this->_connection = new PDO(
                $dsn,
                $this->_config['username'],
                $this->_config['password'],
                $this->_config['driver_options']
            );
            $this->_connection->setAttribute(PDO::ATTR_CASE, $this->_caseFolding);
            $this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            throw new DBException($e->getMessage());
        }
    }
    protected function _beginTransaction()
    {
        $this->_connect();
        $this->_connection->beginTransaction();
    }
    protected function _commit()
    {
        $this->_connect();
        $this->_connection->commit();
    }
    protected function _rollBack() {
        $this->_connect();
        $this->_connection->rollBack();
    }
	public function getConnection()
    {
        $this->_connect();
        return $this->_connection;
    }
	public function getConfig()
    {
        return $this->_config;
    }
	public function beginTransaction()
    {
        if ($this->_transactionLevel===0) {
          $this->_connect();
          $this->_beginTransaction();
        }
        $this->_transactionLevel++;
        return $this;
    }
	public function commit()
    {
    	if ($this->_transactionLevel===1) {
           $this->_connect();
           $this->_commit();
    	}
    	$this->_transactionLevel--;
        return $this;
    }
	public function rollBack()
    {
    	if ($this->_transactionLevel===1) {
          $this->_connect();
          $this->_rollBack();
    	}
        $this->_transactionLevel--;
        return $this;
    }
	public function prepare($sql)
    {
        $this->_connect();
        $stmt = $this->_connection->prepare($sql);
        $stmt->setFetchMode($this->_fetchMode);
        return $stmt;
    }
    public function query($sql, $bind = array()){
    	if (!is_array($bind)) {
            $bind = array($bind);
        }
        $this->_connect();
        $stmt = $this->prepare($sql);
        $stmt->execute($bind);
        $stmt->setFetchMode($this->_fetchMode);
        return $stmt;
	}
	//如果插入的记录中有一个或多个是时间的字段,可以将列名的小写放进数组做为第三个参数
	//如果有一个时间字段,且名字是daytime可以省去第三个参数。
	public function insert($table, array $bind,$datecol = array('daytime'))
    {
        $cols = array();
        $vals = array();
        foreach ($bind as $col => $val) {
            $cols[] = $this->_quoteIdentifier($col, true);
       /*     if(in_array($tempcol, array('create_time','update_time','check_time'))){
            	 $vals[] = time();
            	 unset($bind[$col]);
            }elseif(in_array($tempcol, array('start_time','end_time'))){
            	 $vals[] = trim($val);
            }else{
            	 $vals[] = '?';
            }*/
             $vals[] = '?';
             //$bind[$col] = addcslashes($val, "\n\r\\'\"\032");
        }
        $sql = "INSERT INTO "
             . $this->_quoteIdentifier($table, true)
             . ' (' . implode(', ', $cols) . ') '
             . 'VALUES (' . implode(', ', $vals) . ')';
        $stmt = $this->query($sql, array_values($bind));
        $result = $stmt->rowCount();
        return $result;
    }
	public function update($table, array $bind, $where = '')
    {
        $set = array();
        $i = 0;
        foreach ($bind as $col => $val) {
            $set[] = $this->_quoteIdentifier($col, true) . ' = ?' ;
        }
        $sql = "UPDATE "
             . $this->_quoteIdentifier($table, true)
             . ' SET ' . implode(', ', $set)
             . (($where) ? " WHERE $where" : '');
        $stmt = $this->query($sql, array_values($bind));
        $result = $stmt->rowCount();
        return $result;
    }
	public function delete($table, $where = '')
    {
       // $where = $this->_whereExpr($where); 
        $sql = "DELETE FROM "
             . $this->_quoteIdentifier($table, true)
             . (($where) ? " WHERE $where" : '');
        $stmt = $this->query($sql);
        $result = $stmt->rowCount();
        return $result;
    }
	public function fetchAll($sql, $bind = array(), $fetchMode = null)
    {
        if ($fetchMode === null) {
            $fetchMode = $this->_fetchMode;
        }
        $stmt = $this->query($sql, $bind);
        
        $result = $stmt->fetchAll($fetchMode);
        return $result;
    }
	public function fetchAssoc($sql, $bind = array())
    {
        $stmt = $this->query($sql, $bind);
        $data = array();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $tmp = array_values(array_slice($row, 0, 1));
            $data[$tmp[0]] = $row;
        }
        return $data;
    }
	public function fetchOne($sql, $bind = array())
    {
        $stmt = $this->query($sql, $bind);
        $result = $stmt->fetchColumn(0);
        return $result;
    }
    public function fetchPairs($sql, $bind = array())
    {
        $stmt = $this->query($sql, $bind);
        $data = array();
        while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) {
            $data[$row[0]] = $row[1];
        }
        return $data;
    }
    public function fetchCol($sql, $bind = array())
    {
        $stmt = $this->query($sql, $bind);
        $result = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
        return $result;
    }
    public function fetchRow($sql, $bind = array(), $fetchMode = null)
    {
        if ($fetchMode === null) {
            $fetchMode = $this->_fetchMode;
        }
        $stmt = $this->query($sql, $bind);
        $result = $stmt->fetch($fetchMode);
        return $result;
    }
	public function exec($sql)
    {
        try {
            $affected = $this->getConnection()->exec($sql);            
            if ($affected === false) {
                $errorInfo = $this->getConnection()->errorInfo();
                throw new DBException($errorInfo[2]);
            }
            return $affected;
        } catch (PDOException $e) {
            throw new DBException($e->getMessage());
        }
    }
	protected function _quoteIdentifier($value, $auto=false)
    {
        if ($auto === false || $this->_autoQuoteIdentifiers === true) {
            $q = $this->getQuoteIdentifierSymbol();
            return ($q . str_replace("$q", "$q$q", $value) . $q);
        }
        return $value;
    }
	public function getQuoteIdentifierSymbol()
    {
        return '"';
    }
	public function foldCase($key)
    {
        switch ($this->_caseFolding) {
            case PDO::CASE_LOWER:
                $value = strtolower((string) $key);
                break;
            case PDO::CASE_UPPER:
                $value = strtoupper((string) $key);
                break;
            case PDO::CASE_NATURAL:
            default:
                $value = (string) $key;
        }
        return $value;
    }
	public function setFetchMode($mode)
    {
        if (!extension_loaded('pdo')) {
            throw new DBException('The PDO extension is required for this adapter but the extension is not loaded');
        }
        switch ($mode) {
            case PDO::FETCH_LAZY:
            case PDO::FETCH_ASSOC:
            case PDO::FETCH_NUM:
            case PDO::FETCH_BOTH:
            case PDO::FETCH_NAMED:
            case PDO::FETCH_OBJ:
                $this->_fetchMode = $mode;
                break;
            default:
                throw new DBException("Invalid fetch mode '$mode' specified");
                break;
        }
    }
	public function isConnected()
    {
        return ((bool) ($this->_connection instanceof PDO));
    }
	public function lastInsertId($tableName = null, $primaryKey = null)
    {
        $this->_connect();
        return $this->_connection->lastInsertId();
    }
    public function closeConnection()
    {
        $this->_connection = null;
        $this->_dbobject['slave'] = null;
        $this->_dbobject['master'] = null;
    }
	protected function _quote($value)
    {
        if (is_int($value) || is_float($value)) {
            return $value;
        }
        $this->_connect();
        return $this->_connection->quote($value);
    }
	public function getServerVersion()
    {
        $this->_connect();
        try {
            $version = $this->_connection->getAttribute(PDO::ATTR_SERVER_VERSION);
        } catch (PDOException $e) {
            return null;
        }
        $matches = null;
        if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches)) {
            return $matches[1];
        } else {
            return null;
        }
    }
    
    public function getEdt(){
        return $this->ismaster.'-'.$this->_curtarget;
    }
    
    abstract public function listTables();
    abstract public function describeTable($tableName, $schemaName = null);
    abstract public function limit($sql, $count, $offset = 0);
}


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值