Heim > php教程 > PHP源码 > Hauptteil

使用php中的PDO方式做到数据库的增删改查及类的单例花

PHP中文网
Freigeben: 2016-05-23 17:10:21
Original
1207 Leute haben es durchsucht

跳至

class DPDO{
	private $DSN;
	private $DBUser;
	private $DBPwd;
	private $longLink;
	private $pdo;
	//私有构造函数 防止被直接实例化
	private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) {
		$this->DSN = $dsn;
		$this->DBUser = $DBUser;
		$this->DBPwd = $DBPwd;
		$this->longLink = $longLink;
		$this->connect();
	}
	//私有 空克隆函数 防止被克隆
	private function __clone(){}
	//静态 实例化函数 返回一个pdo对象
	static public function instance($dsn, $DBUser, $DBPwd, $longLink = false){
		static $singleton = array();//静态函数 用于存储实例化对象
		$singIndex = md5($dsn . $DBUser . $DBPwd . $longLink);
		if (empty($singleton[$singIndex])) {
			$singleton[$singIndex] = new self($dsn, $DBUser, $DBPwd, $longLink = false);
		}
		return $singleton[$singIndex]->pdo;
	}
	
	private function connect(){
		try{
			if($this->longLink){
				$this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd, array(PDO::ATTR_PERSISTENT => true));
			}else{
				$this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd);
			}
			$this->pdo->query('SET NAMES UTF-8');
		} catch(PDOException $e) {
			die('Error:' . $e->getMessage() . '
');
		}
	}
}
Nach dem Login kopieren

2. [代码]用于处理字段映射,使用pdo的字段映射,可以有效避免sql注入

//字段关联数组处理, 主要用于写入和更新数据、同and 或 or 的查询条件,产生sql语句和映射字段的数组
	public function FDFields($data, $link = ',', $judge = array(), $aliasTable = ''){
		$sql = '';
		$mapData = array();
		foreach($data as $key => $value) {
			$mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key;
			$sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link;
			$mapData[$mapIndex] = $value;
		}
		$sql = trim($sql, $link);
		return array($sql, $mapData);
	}
	//用于处理单个字段处理
	public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') {
		$mapIndex = ':' . $preMap . $aliasTable . $field;
		$sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex;
		$mapData[$mapIndex] = $value;
		return array($sql, $mapData);
	}
	//使用刚方法可以便捷产生查询条件及对应数据数组
	public function FDCondition($condition, $mapData) {
		if(is_string($condition)) {
				$where = $condition;
		} else if (is_array($condition)) {
			if($condition['str']) {
				if (is_string($condition['str'])) {
					$where = $condition['str'];
				} else {
					return false;
				}
			}
			if(is_array($condition['data'])) {
				$link = $condition['link'] ? $condition['link'] : 'and';
				list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge']);
				if ($conSql) {
					$where .= ($where ? ' ' . $link : '') . $conSql;
					$mapData = array_merge($mapData, $mapConData);
				}
			}
		}
		return array($where, $mapData);
	}
Nach dem Login kopieren

3. [代码]增删改查的具体实现

public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
		if ($sql) {
			$sql .= ' limit 1';
			$pdoStatement = $this->pdo->prepare($sql, $preType);
			$pdoStatement->execute($searchData);
			return $data = $pdoStatement->fetch($dataMode);
		} else {
			return false;
		}
	}
	
	public function fetchAll($sql, $searchData = array(), $limit = array(0, 10), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
		if ($sql) {
			$sql .= ' limit ' . (int) $limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);
			$pdoStatement = $this->pdo->prepare($sql, $preType);
			$pdoStatement->execute($searchData);
			return $data = $pdoStatement->fetchAll($dataMode);
		} else {
			return false;
		}
	}
	
	public function insert($tableName, $data, $returnInsertId = false, $replace = false) {
		if(!empty($tableName) && count($data) > 0){
			$sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO ';
			list($setSql, $mapData) = $this->FDFields($data);
			$sql .= $tableName . ' set ' . $setSql;
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet ? ($returnInsertId ? $this->pdo->lastInsertId() : $execRet) : false;
		} else {
			return false;
		}
	}
	
	public function update($tableName, $data, $condition, $mapData = array(), $returnRowCount = true) {
		if(!empty($tableName) && count($data) > 0) {
			$sql = 'UPDATE ' . $tableName . ' SET ';
			list($setSql, $mapSetData) = $this->FDFields($data);
			$sql .= $setSql;
			$mapData = array_merge($mapData, $mapSetData);
			list($where, $mapData) = $this->FDCondition($condition, $mapData);
			$sql .= $where ? ' WHERE ' . $where : '';
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet ? ($returnRowCount ? $pdoStatement->rowCount() : $execRet) : false;
		} else {
			return false;
		}
	}
	
	public function delete($tableName, $condition, $mapData = array()) {
		if(!empty($tableName) && $condition){
			$sql = 'DELETE FROM ' . $tableName;
			list($where, $mapData) = $this->FDCondition($condition, $mapData);
			$sql .= $where ? ' WHERE ' . $where : '';
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet;
		}
	}
Nach dem Login kopieren

4. [代码]测试文件

header("Content-type: text/html; charset=utf-8");
define('APP_DIR', dirname(__FILE__));

if (function_exists('spl_autoload_register')) {
	spl_autoload_register('autoClass');
} else {
	function __auto_load($className){
		autoClass($className);
	}
}

function autoClass($className){
	try{
		require_once APP_DIR.'/class/'.$className.'.php';
	} catch (Exception $e) {
		die('Error:' . $e->getMessage() . '
');
	}
}
$DB = new DB();
//插入
$inData['a'] = rand(1, 100);
$inData['b'] = rand(1, 1000);
$inData['c'] = rand(1,200) . '.' . rand(1,100);
$ret = $DB->insert('a', $inData);
echo '插入' . ($ret ? '成功' : '失败') . '
';
//更新
$upConData['a'] = 100;
$upConJudge['a'] = '
Nach dem Login kopieren

5. [文件] test.php ~ 2KB

Nach dem Login kopieren
Nach dem Login kopieren

6. [文件] DB.php ~ 4KB

debugDumpParams() 可以查看sql语句错误
 */
class DB {
	protected $pdo;
	
	public function __construct($dbType = 'mysql'){
		if($dbType == 'mysql') {
			$dsn = "mysql:host=localhost;port=3306;dbname=test";
		}
		$this->pdo = DPDO::instance($dsn, 'root', '');
	}
	
	public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
		if ($sql) {
			$sql .= ' limit 1';
			$pdoStatement = $this->pdo->prepare($sql, $preType);
			$pdoStatement->execute($searchData);
			return $data = $pdoStatement->fetch($dataMode);
		} else {
			return false;
		}
	}
	
	public function fetchAll($sql, $searchData = array(), $limit = array(0, 10), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
		if ($sql) {
			$sql .= ' limit ' . (int) $limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);
			$pdoStatement = $this->pdo->prepare($sql, $preType);
			$pdoStatement->execute($searchData);
			return $data = $pdoStatement->fetchAll($dataMode);
		} else {
			return false;
		}
	}
	
	public function insert($tableName, $data, $returnInsertId = false, $replace = false) {
		if(!empty($tableName) && count($data) > 0){
			$sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO ';
			list($setSql, $mapData) = $this->FDFields($data);
			$sql .= $tableName . ' set ' . $setSql;
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet ? ($returnInsertId ? $this->pdo->lastInsertId() : $execRet) : false;
		} else {
			return false;
		}
	}
	
	public function update($tableName, $data, $condition, $mapData = array(), $returnRowCount = true) {
		if(!empty($tableName) && count($data) > 0) {
			$sql = 'UPDATE ' . $tableName . ' SET ';
			list($setSql, $mapSetData) = $this->FDFields($data);
			$sql .= $setSql;
			$mapData = array_merge($mapData, $mapSetData);
			list($where, $mapData) = $this->FDCondition($condition, $mapData);
			$sql .= $where ? ' WHERE ' . $where : '';
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet ? ($returnRowCount ? $pdoStatement->rowCount() : $execRet) : false;
		} else {
			return false;
		}
	}
	
	public function delete($tableName, $condition, $mapData = array()) {
		if(!empty($tableName) && $condition){
			$sql = 'DELETE FROM ' . $tableName;
			list($where, $mapData) = $this->FDCondition($condition, $mapData);
			$sql .= $where ? ' WHERE ' . $where : '';
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet;
		}
	}
	
	
	//字段关联数组处理
	public function FDFields($data, $link = ',', $judge = array(), $aliasTable = ''){
		$sql = '';
		$mapData = array();
		foreach($data as $key => $value) {
			$mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key;
			$sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link;
			$mapData[$mapIndex] = $value;
		}
		$sql = trim($sql, $link);
		return array($sql, $mapData);
	}
	
	public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') {
		$mapIndex = ':' . $preMap . $aliasTable . $field;
		$sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex;
		$mapData[$mapIndex] = $value;
		return array($sql, $mapData);
	}
	
	public function FDCondition($condition, $mapData) {
		if(is_string($condition)) {
				$where = $condition;
		} else if (is_array($condition)) {
			if($condition['str']) {
				if (is_string($condition['str'])) {
					$where = $condition['str'];
				} else {
					return false;
				}
			}
			if(is_array($condition['data'])) {
				$link = $condition['link'] ? $condition['link'] : 'and';
				list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge']);
				if ($conSql) {
					$where .= ($where ? ' ' . $link : '') . $conSql;
					$mapData = array_merge($mapData, $mapConData);
				}
			}
		}
		return array($where, $mapData);
	}
}
?>
Nach dem Login kopieren

7. [文件] DPDO.php ~ 1KB

Nach dem Login kopieren
Nach dem Login kopieren

                           

       

Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Empfehlungen
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage