php中PDO方式实现数据库的增删改查
需要开启php的pdo支持，php5.1以上版本支持
实现数据库连接单例化，有三要素静态变量、静态实例化方法、私有构造函数DPDO.php
classDPDO{
private$DSN;
private$DBUser;
private$DBPwd;
private$longLink;
private$pdo;
//私有构造函数防止被直接实例化
privatefunction__construct($dsn,$DBUser,$DBPwd,$longLink=false){
$this->DSN=$dsn;
$this->DBUser=$DBUser;
$this->DBPwd=$DBPwd;
$this->longLink=$longLink;
$this->connect();
}
//私有空克隆函数防止被克隆
privatefunction__clone(){}
//静态实例化函数返回一个pdo对象
staticpublicfunctioninstance($dsn,$DBUser,$DBPwd,$longLink=false){
static$singleton=array();//静态函数用于存储实例化对象
$singIndex=md5($dsn.$DBUser.$DBPwd.$longLink);
if(empty($singleton[$singIndex])){
$singleton[$singIndex]=newself($dsn,$DBUser,$DBPwd,$longLink=false);
}
return$singleton[$singIndex]->pdo;
}
privatefunctionconnect(){
try{
if($this->longLink){
$this->pdo=newPDO($this->DSN,$this->DBUser,$this->DBPwd,array(PDO::ATTR_PERSISTENT=>true));
}else{
$this->pdo=newPDO($this->DSN,$this->DBUser,$this->DBPwd);
}
$this->pdo->query('SETNAMESUTF-8');
}catch(PDOException$e){
die('Error:'.$e->getMessage().'<br/>');
}
}
}
用于处理字段映射，使用pdo的字段映射，可以有效避免sql注入
//字段关联数组处理，主要用于写入和更新数据、同and或or的查询条件，产生sql语句和映射字段的数组
publicfunctionFDFields($data,$link=',',$judge=array(),$aliasTable=''){
$sql='';
$mapData=array();
foreach($dataas$key=>$value){
$mapIndex=':'.($link!=','?'c':'').$aliasTable.$key;
$sql.=''.($aliasTable?$aliasTable.'.':'').'`'.$key.'`'.($judge[$key]?$judge[$key]:'=').''.$mapIndex.''.$link;
$mapData[$mapIndex]=$value;
}
$sql=trim($sql,$link);
returnarray($sql,$mapData);
}
//用于处理单个字段处理
publicfunctionFDField($field,$value,$judge='=',$preMap='cn',$aliasTable=''){
$mapIndex=':'.$preMap.$aliasTable.$field;
$sql=''.($aliasTable?$aliasTable.'.':'').'`'.$field.'`'.$judge.$mapIndex;
$mapData[$mapIndex]=$value;
returnarray($sql,$mapData);
}
//使用刚方法可以便捷产生查询条件及对应数据数组
publicfunctionFDCondition($condition,$mapData){
if(is_string($condition)){
$where=$condition;
}elseif(is_array($condition)){
if($condition['str']){
if(is_string($condition['str'])){
$where=$condition['str'];
}else{
returnfalse;
}
}
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);
}
}
}
returnarray($where,$mapData);
}
增删改查的具体实现DB.php
publicfunctionfetch($sql,$searchData=array(),$dataMode=PDO::FETCH_ASSOC,$preType=array(PDO::ATTR_CURSOR=>PDO::CURSOR_FWDONLY)){
if($sql){
$sql.='limit1';
$pdoStatement=$this->pdo->prepare($sql,$preType);
$pdoStatement->execute($searchData);
return$data=$pdoStatement->fetch($dataMode);
}else{
returnfalse;
}
}
publicfunctionfetchAll($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{
returnfalse;
}
}
publicfunctioninsert($tableName,$data,$returnInsertId=false,$replace=false){
if(!empty($tableName)&&count($data)>0){
$sql=$replace?'REPLACEINTO':'INSERTINTO';
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{
returnfalse;
}
}
publicfunctionupdate($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{
returnfalse;
}
}
publicfunctiondelete($tableName,$condition,$mapData=array()){
if(!empty($tableName)&&$condition){
$sql='DELETEFROM'.$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;
}
}
测试文件test.php
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);
}
}
functionautoClass($className){
try{
require_onceAPP_DIR.'/class/'.$className.'.php';
}catch(Exception$e){
die('Error:'.$e->getMessage().'<br/>');
}
}
$DB=newDB();
//插入
$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?'成功':'失败').'<br/>';
//更新
$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,$condition,$mapUpConData);
echo'更新行数:'.(int)$changeRows.'<br/>';
//删除
$delVal=rand(1,10);
list($delCon,$mapDelCon)=$DB->FDField('a',$delVal);
$delRet=$DB->delete('a',$delCon,$mapDelCon);
echo'删除a='.$delVal.($delRet?'成功':'失败').'<br/>';
//查询
$data['a']='10';
$judge['a']='>';
$data['b']='400';
$judge['b']='<';
list($conSql,$mapConData)=$DB->FDFields($data,'and',$judge);
$mData=$DB->fetch('select*fromawhere'.$conSql.'orderby`a`desc',$mapConData);
var_dump($mData);
以上所述就是本文的全部内容了，希望大家能够喜欢。