pdo操作类

<?php
/**
 * PDO数据库操作类
 */
class PDODataHandle {
    //数据库链接对象
    private $connect;
    //数据库配置
    private $dbconfig;
    //最后的SQL语句
    private $sql;
    //最后绑定的参数
    private $param;
    //对象
    private static $_instance;
    //私有构造方法
    private function __construct($dbconfig){
        $host = $dbconfig['hostname'];
        $dbname = $dbconfig['database'];
        $username = $dbconfig['username'];
        $password = $dbconfig['password'];
        $dsn = "mysql:host={$host};dbname={$dbname}";
        try {
            $this->connect = new PDO($dsn, $username, $password);
            //设置错误处理模式
            $this->connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            //设置字符集
            $this->connect->query("set names utf8");
        }catch (PDOException $ex){
            echo '无法链接数据库。<br />';
            echo '数据库主机：' . $host . '<br />';
            echo "数据库：$dbname<br/>";
            echo '请认真检查数据库配置...<br />';
            exit();
        }
    }
    //私有clone
    private function __clone() {

    }
    /**
     * 单列
     * @param 数据库配置 $dbconfig
     */
    public static function getInstance($dbconfig){
        if(!empty($dbconfig['hostname']) && !empty($dbconfig['database']) && !empty($dbconfig['username']) && !empty($dbconfig['password'])){
            //检测类是否被实例化
            if(!(self::$_instance instanceof self)){
                self::$_instance=new PDODataHandle($dbconfig);
            }
            return self::$_instance;
        }
    }
    /**
     * 插入数据
     * @param string $tbname 表名
     * @param array $data 插入数据
     */
    public function insert($tbname,$data){
        $param = array();
        $fragment = array();
        foreach ($data as $key=>$value){
            $replaceValue = $this->replaceValue($key, $value, $param);
            $fragment [] = $key . ' = ' . $replaceValue;
        }
        $sql = "INSERT INTO `{$tbname}` SET " . implode(',', $fragment);
        try {
            $prepare = $this->connect->prepare($sql);
            $this->sql = $sql;
            $this->param = $param;
            return $prepare->execute($param);
        } catch (PDOException $ex) {
//          exit($ex->getMessage());
            exit;
        }
    }
    /**
     * 更新数据
     * @param string $tbname 表名
     * @param array $data 数据
     * @param array $where 条件
     * @return boolean 执行成功与否
     */
    public function update($tbname,$data,$where){
        $param = array();
        $fragment = array();
        foreach ($data as $key=>$value){
            $replaceValue = $this->replaceValue($key, $value, $param);
            $fragment [] = $key . ' = ' . $replaceValue;
        }
        $result = $this->compileWhere($where);
        $where = $result['sql'];
        $param = array_merge($param,$result['param']);
        $sql = "UPDATE `{$tbname}` SET " .implode(',', $fragment). ' ' . $where;
        try {
            $prepare = $this->connect->prepare($sql);
            $this->sql = $sql;
            $this->param = $param;
            return $prepare->execute($param);
        } catch (PDOException $ex) {
//          exit($ex->getMessage());
            exit;
        }
    }
    /**
     * 删除数据
     * @param string $tbname 表名
     * @param array $where 条件
     */
    public function delete($tbname,$where){
        $data = $this->compileWhere($where);
        $where = $data['sql'];
        $param = $data['param'];
        $sql = "DELETE FROM `{$tbname}` {$where}";
        try {
            $prepare = $this->connect->prepare($sql);
            $this->sql = $sql;
            $this->param = $param;
            return $prepare->execute($param);
        } catch (PDOException $ex) {
//          exit($ex->getMessage());
            exit;
        }
    }
    /**
     * 查询分页数据
     * @param string $tbname 表名
     * @param array $field 字段
     * @param array $where 查询条件
     * @param string $order 排序
     * @param int $pageIndex 当前页
     * @param int $pageSize 页数据量
     */
    public function listinfo($tbname,$field,$where,$order,$pageIndex,$pageSize){
        $pageParam = $this->getPageParam($pageIndex, $pageSize);
        if(is_string($where) && !empty($where)){
            $field = $this->formatField($field);
            $order = $this->formatOrder($order);
            $sql = "SELECT {$field} FROM `{$tbname}` WHERE {$where} {$order} LIMIT {$pageParam['offset']},{$pageParam['limit']}";
            $list = $this->noCompileQuery($sql);
            $sql = "SELECT count(*) as count FROM `{$tbname}` WHERE {$where}";
            $count = intval($this->getValue(array_shift($this->noCompileQuery($sql)), 'count'));
        }else{
            $list = $this->query($tbname,$field,$where,$order,$pageParam['offset'],$pageParam['limit']);
            $count = $this->queryCount($tbname, $where);
        }
        return array('data'=>$list,'count'=>$count);
    }
    /**
     * 执行分组查询，底层仍调用query方法，此方法为简化操作所用
     * @param string $tbname 表名
     * @param array $field 字段
     * @param array $where 条件
     * @param string $group 分组
     * @param string $order 排序
     * @param int $offset offset
     * @param int $limit limit
     * @return array 返回结果集
     */
    public function queryGroup($tbname,$field,$where,$group,$order = '',$offset = '',$limit = ''){
        return $this->query($tbname,$field,$where,$order,$offset,$limit,$group);
    }
    /**
     * 执行查询
     * @param string $tbname 表名
     * @param array $field 字段
     * @param array $where 条件
     * @param string $order 排序
     * @param int $offset offset
     * @param int $limit limit
     * @return array 返回结果集
     */
    public function query($tbname,$field = array('*'),$where = '',$order = '',$offset = '',$limit = '',$group = ''){
        //设置表名
        $field = $this->formatField($field);
        //设置查询条件和绑定参数
        $data = !empty($where) ? $this->compileWhere($where) : $where;
        if(!empty($data)){
            $where = $data['sql'];
            $param = $data['param'];
        }else{
            $where = '';
            $param = '';
        }
        //设置排序
        $order = !empty($order) ? "ORDER BY {$order}" : $order;
        //设置limit
        $limit = $this->formatLimit($offset, $limit);
        //设置分组
        $group = !empty($group) ? "GROUP BY {$group}" : $group ;
        //拼接SQL
        $sql =  <<< SQL
SELECT {$field} FROM {$tbname}  {$where} {$group} {$order} {$limit}
SQL;
        //执行查询
        try {
            $prepare = $this->connect->prepare($sql,array (PDO :: ATTR_CURSOR => PDO :: CURSOR_FWDONLY));
            if(!empty($param)){
                //绑定参数
                $ss = $prepare->execute($param);
            }else{
                $ss = $prepare->execute();
            }
            //设置获取数据模式
            $prepare->setFetchMode(PDO::FETCH_ASSOC);
            $result = $prepare->fetchAll();
        } catch (PDOException $ex) {
//          exit($ex->getMessage());
            exit;
        }
        $this->sql = $sql;
        $this->param = $param;
        return $result;
    }
    /**
     * 获取一条记录
     * @param string $tbname 表名
     * @param array $field 字段
     * @param array $where 条件
     */
    public function queryOne($tbname,$field,$where){
        return array_shift($this->query($tbname,$field,$where,'','',''));
    }
    /**
     * 获取指定条件查询出的记录数
     * @param string $tbname 表名
     * @param array $where 条件
     */
    public function queryCount($tbname,$where){
        return $this->getValue($this->queryOne($tbname, array('count(*) as count'), $where), 'count');
    }
    /**
     * 不编译执行查询
     * @param string $sql 
     * @return array result
     */
    public function noCompileQuery($sql){
        try {
            $rs = $this->connect->query($sql);
            $rs->setFetchMode(PDO::FETCH_ASSOC);
            $this->sql = $sql;
            $this->param = array();
            return $rs->fetchAll();
        } catch (PDOException $ex) {
//          exit($ex->getMessage());
            exit;
        }
    }
    /**
     * 获取执行的最后一条sql语句和绑定的参数
     */
    public function getLastSql(){
        return array('sql'=>$this->sql,'param'=>$this->param);
    }

    /**
     * 计算分页参数
     * @param int $pageIndex
     * @param int $pageSize
     */
    private function getPageParam($pageIndex,$pageSize){
        $pageIndex = intval($pageIndex);
        $pageSize = intval($pageSize);
        if(empty($pageIndex) || empty($pageSize)){
            //如果分页参数有误,则只返回一条数据
            $offset = 0;
            $limit = 1;
        }else{
            $offset = $pageSize * ($pageIndex - 1);
            $limit = $pageSize;
        }
        return array('offset' => $offset,'limit' => $limit);
    }
    /**
     * 解析where数组，生成sql语句
     * @param array $where
     * @return string sql 语句
     */
    private function compileWhere($where){
        if(empty($where)){return '';}
        $sql = '';
        $sqlFragment = array();
        $param = array();
        if(isset($where['connector'])){
            $connector = $where['connector'];
            unset($where['connector']);
        }else{
            $connector = null;
        }
        //处理自定义约束条件
        $this->handleConstraint($where, $sqlFragment, $param);
        //处理自定义连接符
        $sql = $this->handleConnector($sqlFragment, $param,$connector);
        return array(
            'sql' => $sql,
            'param' => $param
        );
    }
    /**
     * 将参数值替换为绑定参数
     * @param string $key key
     * @param string $join 连接符
     * @param string $value value
     * @param array $param 绑定参数数组,引用传递
     * @return string 替换后的参数
     */
    private function replaceValue($key,$value,&$param){
        if(is_array($value)){
            $n =1;
            $keys = array();
            foreach ($value as $v){
                $keys[] = ':'.$key.$n;
                $param[':'.$key.$n] = $v;
                $n++;
            }
            return $keys;
        }else{
            $param[':'.$key] = $value;
            return ':'.$key;
        }
    }
    /**
     * 生成where语句后跟的sql条件判断
     * @param string $key key
     * @param string $join 连接符
     * @param string $value value
     * @return string sql语句
     */
    private function createConstraintSql($key,$join,$value){
        switch ($join){
            case 'in':return $key.' '.$join.' '.'('.implode(",", $value).')';
            case 'between and':return $key .' BETWEEN '. array_shift($value) . ' AND ' . array_shift($value);
            default:return $key.' '.$join.' '.$value;
        }
    }
    /**
     * 处理自定义约束条件
     * @param array $where 输入的where数组
     * @param array $sql sql片段数组，用于后续操作，引用传递
     * @param array $param 绑定参数数组，用于后续操作，引用传递
     */
    private function handleConstraint($where,&$sql,&$param){
        foreach ($where as $key=>$v){
            if(is_array($v)){
                //限定约束条件
                $join = $v['join'];
                $value = $v['value'];
                $sql[] = $this->createConstraintSql($key, $join, $this->replaceValue($key, $value,$param));
            }else{
                //未限定约束条件，默认为'='
                $join = '=';
                $value = $v;
                $sql[] = $this->createConstraintSql($key, $join, $this->replaceValue($key, $value,$param));
            }
        }
    }
    /**
     * 处理自定义连接符
     * @param array $where 输入的where数组
     * @param array $sql sql片段数组，用于后续操作，引用传递
     * @param array $return 返回数组
     */
    private function handleConnector(&$sql,&$param,$connector){
        if(count($sql) >1){
            if(!empty($connector)){
                //手动设置连接符
                foreach ($sql as $section){
                    $con = array_shift($connector);
                    if(!empty($con)){
                        $return .= ' '.$section .' '. $con . ' ';
                    }else{
                        $return .= ' '.$section.' ';
                    }
                }
            }else{
                //未手动设置，则默认为AND
                $con = 'AND';
                $return = ' '.implode(" {$con} ", $sql).' ';
            }
            return ' WHERE ' . $return.' ';
        }else{
            return " WHERE ".array_shift($sql)." ";
        }
    }
    /**
     * 格式化field
     * @param array $field
     * @return string field
     */
    private function formatField($field){
        if($field =='*'){
            return '*';
        }else{
            return implode(',', $field);
        }
    }
    /**
     * 格式化limit
     * @param int $offset
     * @param int $limit
     */
    private function formatLimit($offset,$limit){
        if(is_numeric($offset)&&is_numeric($limit)){
            return "LIMIT {$offset},{$limit}";
        }else{
            return '';
        }
    }
    /**
     * 格式化order
     * @param string $order 排序
     */
    private function formatOrder($order){
        if(isset($order) && !empty($order)){
            return 'ORDER BY '.$order;
        }else{
            return '';
        }
    }
    /**
     * 获取数组键值
     * @param array $array
     * @param string|int $key
     */
    private function getValue($array,$key){
        return $array[$key];
    }
}