pdo 帮助类 pdo class php 数据库处理层 pdo dbhelper

[php] view plain copy

<?php
define("DB_SERVER","127.0.0.1");
define("DB_PORT",3306);
define("DB_CATALOG","test");
define("DB_USERID","root");
define("DB_PASSWORD","");
class DAL {
private static $__queries=0;
private static $__PDO=null;
private static function connect() {
if(isset(self::$__PDO))return;
try {
$dsn="mysql:host=".DB_SERVER.";port=".DB_PORT.";dbname=".DB_CATALOG;
self::$__PDO=new PDO($dsn, DB_USERID, DB_PASSWORD);
} catch(PDOException $e) {
raiseError($e);
}
}
/**
*
* @param <type> $sql
* @param <type> $params
* @return <type> $stmt
*
*
* $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
* $stmt->bindParam(1, $name);
* $stmt->bindParam(2, $value);
* // insert one row
* $name = 'one';
* $value = 1;
* $stmt->execute();
*
* $sql = "select * from SampleMarry where UserId=?";
* $arr[0] = $userId;
* return DAL::getall($sql,$arr);
*/
private static function execute($sql,$params) {
self::connect();
try {
$stmt=self::$__PDO->prepare($sql);
if($params!==null) {
if(is_array($params) || is_object($params)) {
$i=1;
foreach($params as $param) {
$stmt->bindValue($i++,$param);
}
} else {
$stmt->bindValue(1,$params);
}
}
if($stmt->execute()) {
self::$__queries++;
return $stmt;
} else {
$err=$stmt->errorInfo();
throw new PDOException($err[2],$err[1]);
}
} catch(PDOException $e) {
raiseError($e);
}
}
/************************************************************************
public function start
************************************************************************/
//for insert start
public static function insert($sql,$params=null) {
$stmt=self::execute($sql,$params);
return self::$__PDO->lastInsertId();
}
/**
*
* @param <type> $table
* @param <type> $entity
* @return <type>
$data=new stdClass;
$data->SampleBook_Id=$bookid;
$data->UserId=$userId;
$data->EducationType="School";
$data->Name=$scoolname;
$data->Subject="";
$data->AttendedDate=strtotime($startdate);
$data->FinishedDate=strtotime($enddate);
return DAL::createRecord("SampleEducation",$item);
*/
public static function createRecord($table,$entity) {
$f=array();
$v=array();
$c=0;
foreach($entity as $prop=>$val) {
$f[]=$prop;
$v[]=$val;
$c++;
}
$sql="INSERT INTO $table (".implode(",",$f).") VALUES(".trim(str_repeat('?,',$c),",").")";
return self::insert($sql,$v);
}
//for insert end
//for delete start
/**
*
* @param <type> $sql
* @param <type> $params
* @return <type>
$sql="delete from SampleLived where id=?";
$arr[] = $id;
DAL::delete($sql,$arr);
*/
public static function delete($sql,$params=null) {
$stmt=self::execute($sql,$params);
return $stmt->rowCount();
}
public static function remove($sql,$params=null) {
$stmt=self::execute($sql,$params);
return $stmt->rowCount();
}
//for delete end
//for update start
public static function update($sql,$params=null) {
$stmt=self::execute($sql,$params);
return $stmt->rowCount();
}
public static function updateRecord($table,$entity,$pkdfiledname) {
$f=array();
$v=array();
foreach($entity as $prop=>$val) {
if($prop==$pkdfiledname)continue;
$f[]=$prop."=?";
$v[]=$val;
}
$v[]=$entity->$pkdfiledname;
$sql="UPDATE $table SET ".trim(implode(',',$f),',')." WHERE $pkdfiledname=?";
return self::update($sql,$v);
}
//for update end
//for query start
public static function query($sql,$params=null) {
$stmt=self::execute($sql,$params);
return new DBRecordset($stmt);
}
public static function getOne($sql,$params=null) {
$stmt=self::execute($sql,$params);
return $stmt->fetchColumn();
}
public static function getRow($sql,$params=null) {
$stmt=self::execute($sql,$params);
return $stmt->fetch(PDO::FETCH_OBJ);
}
public static function getall($sql,$params=null) {
$stmt=self::execute($sql,$params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
//for query end
//for Transaction start
public static function beginTransaction() {
return self::$__PDO->beginTransaction();
}
public static function rollBack() {
return self::$__PDO->rollBack();
}
public static function commit() {
return self::$__PDO->commit();
}
//for Transaction end
public static function pagination($countSql,$selectSql,$params,&$pageNow,$pageSize,&$count) {
if($pageNow<=0)$pageNow=1;
$count=self::getOne($countSql,$params);
$pageCount=ceil($count/$pageSize);
if($pageNow>$pageCount)$pageNow=$pageCount;
if($pageNow<=0)$pageNow=1;
$offset=($pageNow-1)*$pageSize;
$sql=$selectSql.' LIMIT '.$offset.','.$pageSize;
return self::query($sql,$params);
}
/************************************************************************
public function end
************************************************************************/
}
class DBRecordset {
private $PDOStatement;
public function __construct(&$stmt) {
$this->PDOStatement=&$stmt;
$this->PDOStatement->setFetchMode(PDO::FETCH_OBJ);
}
public function next() {
return $this->PDOStatement->fetch();
}
public function count() {
//for mysql PDOStatement will return the number of rows in the resultset
return $this->PDOStatement->rowCount();
}
public function getAllRows() {
return $this->PDOStatement->fetchAll();
}
public function columnCount() {
return $this->PDOStatement->columnCount();
}
public function free() {
$this->PDOStatement=null;
}
public function __destruct() {
$this->free();
}
}
?>

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。