php中PDO方式实现数据库的增删改查

DSN = $dsn; $this->DBUser = $DBUser; $this->DBPwd = $DBPwd; $this->longLink = $longLink; $this->connect(); } //私有 空克隆函数 防止被克隆 private function __clone(){} //静态 实例化函数 返回一个pdo对象 static public function instance($dsn,$longLink = false){ static $singleton = array();//静态函数 用于存储实例化对象 $singIndex = md5($dsn . $DBUser . $DBPwd . $longLink); if (empty($singleton[$singIndex])) { $singleton[$singIndex] = new self($dsn,$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->DBPwd);
}
$this->pdo->query('SET NAMES UTF-8');
} catch(PDOException $e) {
die('Error:' . $e->getMessage() . '
');
}
}
}

$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); }

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,$limit = array(0,10),$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,$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,$mapData = array()) {
if(!empty($tableName) && $condition){
$sql = 'DELETE FROM ' . $tableName;
list($where,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$execRet = $pdoStatement->execute($mapData);
return $execRet;
}
}

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'] = '<';
$upConData['b'] = 30;
$upConJudge['b'] = '>';
list($upConStr,$mapUpConData) = $DB->FDField('b',200,'<','gt');
$condition = array(
'str' => $upConStr,'data' => $upConData,'judge' => $upConJudge,'link' => 'and'
);
$upData['a'] = rand(1,10);
$upData['b'] = 1;
$upData['c'] = 1.00;
$changeRows = $DB->update('a',$upData,$mapUpConData);
echo '更新行数:' . (int) $changeRows . '
';
//删除
$delVal = rand(1,10);
list($delCon,$mapDelCon) = $DB->FDField('a',$delVal);
$delRet = $DB->delete('a',$delCon,$mapDelCon);
echo '删除a=' . $delVal . ($delRet ? '成功' : '失败') . '
';

//查询
$data['a'] = '10';
$judge['a'] = '>';
$data['b'] = '400';
$judge['b'] = '<';
list($conSql,$mapConData) = $DB->FDFields($data,'and',$judge);
$mData = $DB->fetch('select * from a where ' . $conSql . ' order by a desc',$mapConData);

var_dump($mData);