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

<?php
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   <type> $sql  the sql
   *
   * @return   <type> all.
   */
  public static function getall($sql=null){
    if($sql!=null){
      self::query($sql);
    }
    $result = self::$pdostatement->fetchall(constant("pdo::fetch_assoc"));
    return $result;
  }
  /**
   * 得到一条记录
   *
   * @param   <type> $sql  the sql
   *
   * @return   <type> 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   <type>  $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   <type> $tabname the tab name
   * @param   <type> $priid  the pri identifier
   * @param   string $fields  the fields
   *
   * @return   <type> ( 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   <type> $tables the tables
   * @param   <type> $where  the where
   * @param   string $fields the fields
   * @param   <type> $group  the group
   * @param   <type> $having the having
   * @param   <type> $order  the order
   * @param   <type> $limit  the limit
   *
   * @return   <type> ( 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   <type> $data  the data
   * @param   <type> $table the table
   *
   * @return   <type> ( 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   <type> $data  the data
   * @param   <type> $table the table
   * @param   <type> $where the where
   * @param   <type> $order the order
   * @param   <type> $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   <type> $data  the data
   * @param   <type> $table the table
   * @param   <type> $where the where
   * @param   <type> $order the order
   * @param   <type> $limit the limit
   *
   * @return   <type> ( 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   <type> $where the where
   *
   * @return   <type> ( 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   <type> $group the group
   *
   * @return   <type> ( 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   <type> $having the having
   *
   * @return   <type> ( 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   <type> $order the order
   *
   * @return   <type> ( 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   <type> $limit the limit
   *
   * @return   <type> ( 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   <type> $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].'<br/>sql error=>'.$arrerror[2].'<br/>error sql=>'.self::$querystr;
      self::throw_exception(self::$error);
      return false;
    }
    if(self::$querystr==''){
      self::throw_exception('没有执行sql语句');
      return false;
    }
  }
  /**
   * 自定义错误处理
   *
   * @param   <type> $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());