Rumah > php教程 > PHP源码 > 使用php中的PDO方式做到数据库的增删改查及类的单例花

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

PHP中文网
Lepaskan: 2016-05-23 17:10:21
asal
1277 orang telah melayarinya

跳至

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() . '
');
		}
	}
}
Salin selepas log masuk

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);
	}
Salin selepas log masuk

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;
		}
	}
Salin selepas log masuk

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'] = '
Salin selepas log masuk

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

Salin selepas log masuk
Salin selepas log masuk

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);
	}
}
?>
Salin selepas log masuk

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

Salin selepas log masuk
Salin selepas log masuk

                           

       

Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Artikel terbaru oleh pengarang
Cadangan popular
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan