php mysql pdo 封装类_php封装的pdo数据库操作工具类与用法示例

header("Content-Type:text/html;charset=utf-8");

class PdoMysql{

public static $config = array();//设置连接参数，配置信息

public static $link = null;//保存连接标识符

public static $pconnect = false;//是否开启长连接

public static $dbVersion = null;//保存数据库版本

public static $connected = false;//判断是否连接成功

public static $PDOStatement = null;//保证PDOStatement对象

public static $queryStr = null;//保存最后执行的操作

public static $error = null;//保存错误信息

public static $lastInsertId = null;//保存上一步插入操作保存的AUTO_INCREMANT

public static $numRows = null;//受影响记录的条数

/**

* 构造函数，连接数据库

*

* @param array|string $dbConfig The database configuration

*

* @return boolean ( description_of_the_return_value )

*/

public function __construct($dbConfig=''){

if(!class_exists("PDO")){

self::throw_exception("不支持PDO,请先开启");

}

if(!is_array($dbConfig)){

$dbConfig = array(

'hostname' => 'localhost',

'username' => 'root',

'password' => '1234',

'database' => 'test',

'hostport' => '3306',

'dbms' => 'mysql',

'dsn' => 'mysql:host=localhost;dbname=test'

);

}

if(empty($dbConfig['hostname'])){

self::throw_exception("没有定义数据库配置,请先定义");

}

self::$config = $dbConfig;

if(empty(self::$config['params'])){

self::$config['params'] = array();

}

if(!isset(self::$link)){

$configs = self::$config;

if(self::$pconnect){

//开启长连接,添加到配置数组中

$configs['params'][constant("PDO::ATTR_PERSISTENT")] = true;

}

try {

self::$link = new PDO($configs['dsn'],$configs['username'],$configs['password'],$configs['params']);

} catch (PDOException $e) {

self::throw_exception($e->getMessage());

}

if(!self::$link){

self::throw_exception("PDO连接错误");

return false;

}

self::$link->exec("set names utf8");

self::$dbVersion = self::$link->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));

unset($configs);

}

}

/**

* 得到所有记录

*

* @param $sql The sql

*

* @return All.

*/

public static function getAll($sql=null){

if($sql!=null){

self::query($sql);

}

$result = self::$PDOStatement->fetchAll(constant("PDO::FETCH_ASSOC"));

return $result;

}

/**

* 得到一条记录

*

* @param $sql The sql

*

* @return The row.

*/

public static function getRow($sql=null){

if($sql!=null){

self::query($sql);

}

$result = self::$PDOStatement->fetch(constant("PDO::FETCH_ASSOC"));

return $result;

}

/**

* 执行增删改操作，返回受影响记录的条数

*

* @param $sql The sql

*

* @return boolean ( description_of_the_return_value )

*/

public static function execute($sql=null){

$link = self::$link;

if(!$link)return false;

if($sql!=null){

self::$queryStr = $sql;

}

if(!empty(self::$PDOStatement))self::free();

$result = $link->exec(self::$queryStr);

self::haveErrorThrowException();

if($result){

self::$lastInsertId = $link->lastInsertId();

self::$numRows = $result;

return $result;

}else{

return false;

}

}

/**

* 根据主键查找记录

*

* @param $tabName The tab name

* @param $priId The pri identifier

* @param string $fields The fields

*

* @return ( description_of_the_return_value )

*/

public static function findById($tabName,$priId,$fields='*'){

$sql = 'SELECT %s FROM %s WHERE id=%d';

return self::getRow(sprintf($sql,self::parseFields($fields),$tabName,$priId));

}

/**

* 执行普通查询

*

* @param $tables The tables

* @param $where The where

* @param string $fields The fields

* @param $group The group

* @param $having The having

* @param $order The order

* @param $limit The limit

*

* @return ( description_of_the_return_value )

*/

public static function find($tables,$where=null,$fields='*',$group=null,$having=null,$order=null,$limit

=null){

$sql = 'SELECT '.self::parseFields($fields).' FROM '.$tables

.self::parseWhere($where)

.self::parseGroup($group)

.self::parseHaving($having)

.self::parseOrder($order)

.self::parseLimit($limit);

$data = self::getAll($sql);

return $data;

}

/**

* 添加记录

*

* @param $data The data

* @param $table The table

*

* @return ( description_of_the_return_value )

*/

public static function add($data,$table){

$keys = array_keys($data);

array_walk($keys, array('PdoMySQL','addSpecialChar'));

$fieldsStr = join(',',$keys);

$values = "'".join("','",array_values($data))."'";

$sql = "INSERT {$table}({$fieldsStr}) VALUES({$values})";

return self::execute($sql);

}

/**

* 更新数据

*

* @param $data The data

* @param $table The table

* @param $where The where

* @param $order The order

* @param $limit The limit

*/

public static function update($data,$table,$where=null,$order=null,$limit=null){

$sets = '';

foreach ($data as $key => $value) {

$sets .= $key."='".$value."',";

}

$sets = rtrim($sets,',');

$sql = "UPDATE {$table} SET {$sets}".self::parseWhere($where).self::parseOrder($order).self::parseLimit($limit);

echo $sql;

}

/**

* 删除数据

*

* @param $data The data

* @param $table The table

* @param $where The where

* @param $order The order

* @param $limit The limit

*

* @return ( description_of_the_return_value )

*/

public static function delete($table,$where=null,$order=null,$limit=null){

$sql = "DELETE FROM {$table} ".self::parseWhere($where).self::parseOrder($order).self::parseLimit($limit);

return self::execute($sql);

}

/**

* 执行查询

*

* @param string $sql The sql

*

* @return boolean ( description_of_the_return_value )

*/

public static function query($sql=''){

$link = self::$link;

if(!$link)return false;

//判断之前是否有结果集，如果有的话，释放结果集

if(!empty(self::$PDOStatement))self::free();

self::$queryStr = $sql;

self::$PDOStatement = $link->prepare(self::$queryStr);

$res = self::$PDOStatement->execute();

self::haveErrorThrowException();

return $res;

}

/**

* 获取最后执行的sql

*

* @return boolean The last sql.

*/

public static function getLastSql(){

$link = self::$link;

if(!$link){

return false;

}

return self::$queryStr;

}

/**

* 获取最后插入的ID

*

* @return boolean The last insert identifier.

*/

public static function getLastInsertId(){

$link = self::$link;

if(!$link){

return false;

}

return self::$lastInsertId;

}

/**

* 获得数据库的版本

*

* @return boolean The database version.

*/

public static function getDbVersion(){

$link = self::$link;

if(!$link){

return false;

}

return self::$dbVersion;

}

/**

* 得到数据库中表

*

* @return array ( description_of_the_return_value )

*/

public static function showTables(){

$tables = array();

if(self::query("show tables")){

$result = self::getAll();

foreach ($result as $key => $value) {

$tables[$key] = current($value);

}

}

return $tables;

}

/**

* 解析where条件

*

* @param $where The where

*

* @return ( description_of_the_return_value )

*/

public static function parseWhere($where){

$whereStr = '';

if(is_string($where)&&!empty($where)){

$whereStr = $where;

}

return empty($whereStr) ? '' : ' WHERE '.$whereStr;

}

/**

* 解析group

*

* @param $group The group

*

* @return ( description_of_the_return_value )

*/

public static function parseGroup($group){

$groupStr = '';

if(is_array($group)){

$groupStr = implode(',', $group);

}elseif(is_string($group)&&!empty($group)){

$groupStr = $group;

}

return empty($groupStr) ? '' : ' GROUP BY '.$groupStr;

}

/**

* 解析having

*

* @param $having The having

*

* @return ( description_of_the_return_value )

*/

public static function parseHaving($having){

$havingStr = '';

if(is_string($having)&&!empty($having)){

$havingStr = $having;

}

return empty($havingStr) ? '' : ' HAVING '.$havingStr;

}

/**

* 解析order

*

* @param $order The order

*

* @return ( description_of_the_return_value )

*/

public static function parseOrder($order){

$orderStr = '';

if(is_string($order)&&!empty($order)){

$orderStr = $order;

}

return empty($orderStr) ? '' : ' ORDER BY '.$orderStr;

}

/**

* 解析limit

*

* @param $limit The limit

*

* @return ( description_of_the_return_value )

*/

public static function parseLimit($limit){

$limitStr = '';

if(is_array($limit)){

$limitStr = implode(',', $limit);

}elseif(is_string($limit)&&!empty($limit)){

$limitStr = $limit;

}

return empty($limitStr) ? '' : ' LIMIT '.$limitStr;

}

/**

* 解析字段

*

* @param $fields The fields

*

* @return string ( description_of_the_return_value )

*/

public static function parseFields($fields){

if(is_array($fields)){

array_walk($fields, array('PdoMySQL','addSpecialChar'));

$fieldsStr = implode(',', $fields);

}elseif (is_string($fields)&&!(empty($fields))) {

if(strpos($fields, '`')===false){

$fields = explode(',', $fields);

array_walk($fields, array('PdoMySQL','addSpecialChar'));

$fieldsStr = implode(',', $fields);

}else{

$fieldsStr = $fields;

}

}else{

$fieldsStr = "*";

}

return $fieldsStr;

}

/**

* 通过反引号引用字字段

*

* @param string $value The value

*

* @return string ( description_of_the_return_value )

*/

public static function addSpecialChar(&$value){

if($value==="*"||strpos($value,'.')!==false||strpos($value,'`')!==false){

//不用做处理

}elseif(strpos($value, '`')===false){

$value = '`'.trim($value).'`';

}

return $value;

}

/**

* 释放结果集

*/

public static function free(){

self::$PDOStatement = null;

}

/**

* 抛出错误信息

*

* @return boolean ( description_of_the_return_value )

*/

public static function haveErrorThrowException(){

$obj = empty(self::$PDOStatement) ? self::$link : self::$PDOStatement;

$arrError = $obj->errorInfo();

if($arrError[0]!='00000'){

self::$error = 'SQLSTATE=>'.$arrError[0].'
SQL Error=>'.$arrError[2].'
Error SQL=>'.self::$queryStr;

self::throw_exception(self::$error);

return false;

}

if(self::$queryStr==''){

self::throw_exception('没有执行SQL语句');

return false;

}

}

/**

* 自定义错误处理

*

* @param $errMsg The error message

*/

public static function throw_exception($errMsg){

echo $errMsg;

}

/**

* 销毁连接对象，关闭数据库

*/

public static function close(){

self::$link = null;

}

}

$pdo = new PdoMysql();

var_dump($pdo->showTables());