使用php中的PDO方式做到数据库的增删改查及类的单例花
- PHP中文网Original
- 2016-05-23 17:10:211206browse
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() . '
');
}
}
}
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);
}
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;
}
}
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'] = '
5. [文件] test.php ~ 2KB
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);
}
}
?>
7. [文件] DPDO.php ~ 1KB
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PHP中巧用curl 并发减少获取第三方网页内容时间Next article：任意文本数据导入SQL SERVER