<?php/** * 数据库管理 * * @author wangaibo168@163.com * @charset utf-8 * 不支持sqlserver2005(包括)以下的版本 */class Db {    /**     * @var 数据库连接配置     */    private static $DbLink;    /**     * @var 数据库连接配置     */    private static $DbConfig;    /**     * @var 需要执行的SQL语句     */    private static $SqlStr;    /**     * @var 最后错误信息     */    private static $ErrorMsg;    /**     * 默认构造函数     */    public function __construct(){        self::set(‘host‘,‘localhost‘);        self::set(‘port‘,‘33333‘);        self::set(‘username‘,‘lpinfoe‘);        self::set(‘password‘,‘LOPALinfo8e‘);        self::set(‘database‘,‘lopal2015e‘);        self::set(‘charset‘,‘utf-8‘);    }    /**     * 配置连接参数     * @param $opt     */    public static function configure($opt){        if(!is_array($opt)) return;        self::$DbConfig = $opt;    }    /**     * 设置连接参数     * @param $key     * @param $value     */    public static function set($key,$value){        if(empty($key)) return;        if(!is_array(self::$DbConfig)) self::$DbConfig = array();        self::$DbConfig[$key] = $value;    }    /**     * 读取连接参数     * @param $key     * @return null     */    public static function get($key){        if(!is_array(self::$DbConfig) || empty($key) || !array_key_exists($key,self::$DbConfig)) return null;        return self::$DbConfig[$key];    }    /**     * 数据库连接参数检查     * @return bool     */    public static function check(){        $host = self::get(‘host‘);        if(empty($host)) return false;        $port = self::get(‘port‘);        if(!is_numeric($port) || $port<0 || $port>65535) return false;        $username = self::get(‘username‘);        if(empty($username)) return false;        $database = self::get(‘database‘);        if(empty($database)) return false;        $charset = self::get(‘charset‘);        if(empty($charset)) return false;        return true;    }    /**     * 连接初始化     * @param bool $reconnect     */    public static function connect($reconnect=false){        if(!$reconnect && is_resource(self::$DbLink)) return;        self::close();        if(!self::check()) die(‘Database Configuration Error‘);        $opt = array(‘Database‘=>self::get(‘database‘),‘CharacterSet‘=>self::get(‘charset‘),‘UID‘=>self::get(‘username‘),‘ReturnDatesAsStrings‘=>true,‘PWD‘=>self::get(‘password‘));        self::$DbLink = sqlsrv_connect(self::get(‘host‘).‘,‘.self::get(‘port‘),$opt);        if(!is_resource(self::$DbLink)){            $err = sqlsrv_errors();            die(‘Database Connection Error(‘.$err[0][‘message‘].‘)‘);        }        // 测试连接是否可用/*        $stmt = sqlsrv_query(self::$DbLink,‘select 1 num‘);        if(!is_resource($stmt)){            $err = sqlsrv_errors();            die(‘Database Query Error(‘.$err[0][‘message‘].‘)‘);        }        sqlsrv_free_stmt($stmt);*/    }    /**     * 查询SQL语句     * @param $sql     * @param array $params     * @return array|bool     */    public static function executeQuery($sql,$params=array()){        self::$SqlStr = $sql;        self::$ErrorMsg = ‘‘;        if(empty($sql)) return false;        self::connect();        if(!is_array($params)){            $params = array();        }        $stmt = sqlsrv_query(self::$DbLink,self::$SqlStr,$params);        if(!is_resource($stmt)){            $err = sqlsrv_errors();            self::$ErrorMsg = $err[0][‘message‘];            return false;        }        $arr = explode(‘;‘,$sql);        if(count($arr)>1){            for($i=count($arr);$i>1;$i--){                sqlsrv_next_result($stmt);            }        }        $rows = array();        while($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC)){            $rows[] = $row;        }        sqlsrv_free_stmt($stmt);        return $rows;    }    /**     * 查询SQL语句     * @param $sql     * @param array $params     * @return bool|int     */    public static function executeCount($sql,$params=array()){        self::$SqlStr = $sql;        self::$ErrorMsg = ‘‘;        if(empty($sql)) return false;        self::connect();        if(!is_array($params)){            $params = array();        }        $stmt = sqlsrv_query(self::$DbLink,self::$SqlStr,$params);        if(!is_resource($stmt)){            $err = sqlsrv_errors();            self::$ErrorMsg = $err[0][‘message‘];            return false;        }        $row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_NUMERIC);        $count = 0;        if(is_array($row) && count($row)==1){            $count = intval($row[0]);        }        sqlsrv_free_stmt($stmt);        return $count;    }    /**     * 执行SQL语句     * @param $sql     * @param array $params     * @return bool|int     */    public static function executeUpdate($sql,$params=array()){        self::connect();        self::$SqlStr = $sql;        self::$ErrorMsg = ‘‘;        if(empty($sql)) return false;        self::connect();        if(!is_array($params)){            $params = array();        }        $stmt = sqlsrv_query(self::$DbLink,self::$SqlStr,$params);        if(!is_resource($stmt)){            $err = sqlsrv_errors();            self::$ErrorMsg = $err[0][‘message‘];            return false;        }        $num = sqlsrv_rows_affected($stmt);        sqlsrv_free_stmt($stmt);        return $num;    }    /**     * 返回服务器信息     * @return array     */    public static function serverInfo(){        self::connect();        return sqlsrv_server_info(self::$DbLink);    }    /**     * 返回客户端信息     * @return array|null     */    public static function clientInfo(){        self::connect();        return sqlsrv_client_info(self::$DbLink);    }    /**     * 构建表SQL     * @param $table     * @return string     */    private static function tableSql($table){        if(empty($table)) return ‘‘;        if(is_array($table)){            $arr = array();            foreach($table as $k=>$v){                $arr[] = ‘[‘.$v.‘] ‘.$k;            }            $tableSql = implode(‘,‘,$arr);        }else{            $tableSql = ‘[‘.$table.‘]‘;        }        return $tableSql;    }    /**     * 构建字段SQL     * @param $field     * @return string     */    private static function fieldSql($field){        if(empty($field)) return ‘*‘;        if(is_array($field)){            $fieldSql = ‘[‘.implode(‘],[‘,$field).‘]‘;        }else{            $fieldSql = $field;        }        return $fieldSql;    }    /**     * 构建条件SQL     * @param $where     * @return string     */    private static function whereSql($where){        if(empty($where)) return ‘‘;        $whereSql = ‘ where ‘;        if(is_array($where)){            $whereSql .= implode(‘ and ‘,$where);        }else{            $whereSql .= $where;        }        return $whereSql;    }    /**     * 构建排序SQL     * @param $order     * @return string     */    private static function orderSql($order){        if(empty($order)) return ‘‘;        $orderSql = ‘ order by ‘;        if(is_array($order)){            $orderSql .= implode(‘,‘,$order);        }else{            $orderSql .= $order;        }        return $orderSql;    }    /**     * 统计数据量     * @param $table     * @param $where     * @param null $values     * @return bool|int     */    public static function count($table,$where,$values=null){        if(empty($table)) return false;        $tableSql = self::tableSql($table);        $whereSql = self::whereSql($where);        self::$SqlStr = ‘select count(*) from ‘.$tableSql.$whereSql;        return self::executeCount(self::$SqlStr,$values);    }    /**     * 添加数据     * @param $table     * @param $data     * @return bool|int|string     */    public static function add($table,$data){        if(empty($table) || !is_array($data) || count($data)==0) return false;        self::connect();        $fields = array();        $values = array();        $places = array();        foreach($data as $key=>$value){ // 忽略以下划线开头的键            if(stripos($key,‘_‘)===0) continue;            $fields[] = ‘[‘.$key.‘]‘;            $values[] = $value;            $places[] = ‘?‘;        }        self::$SqlStr = ‘insert into [‘.$table.‘](‘.implode(‘,‘,$fields).‘)values(‘.implode(‘,‘,$places).‘)‘;        $num = self::executeUpdate(self::$SqlStr,$values);        return $num>0;    }    /**     * 插入数据并获取最后插入数据的ID(自增长数据)     * @param $table     * @param $data     * @return bool|int|string     */    public static function autoIdAdd($table,$data){        if(empty($table) || !is_array($data) || count($data)==0) return false;        self::connect();        $fields = array();        $values = array();        $places = array();        foreach($data as $key=>$value){ // 忽略以下划线开头的键            if(stripos($key,‘_‘)===0) continue;            $fields[] = ‘[‘.$key.‘]‘;            $values[] = $value;            $places[] = ‘?‘;        }        self::$SqlStr = ‘insert into [‘.$table.‘](‘.implode(‘,‘,$fields).‘)values(‘.implode(‘,‘,$places).‘);select top 1 SCOPE_IDENTITY() id‘;        $rows = self::executeQuery(self::$SqlStr,$values);        if(!is_array($rows) || count($rows)==0) return false;        $id = $rows[0][‘id‘];        if(!is_numeric($id)) return false;        return $id;    }    /**     * 更新数据     * @param $table     * @param $data     * @param $where     * @param null $value     * @return bool     */    public static function update($table,$data,$where,$value=null){        if(empty($table) || empty($where)) return false;        self::connect();        $whereSql = self::whereSql($where);        $values = array();        $places = array();        foreach($data as $key=>$v){            if(stripos($key,‘_‘)===0) continue;            $values[] = $v;            $places[] = ‘[‘.$key.‘]=?‘;        }        foreach($value as $v){            $values[] = $v;        }        self::$SqlStr = ‘update [‘.$table.‘] set ‘.implode(‘,‘,$places).$whereSql;        $num = self::executeUpdate(self::$SqlStr,$values);        return $num>0;    }    /**     * 删除数据     * @param $table     * @param $where     * @param null $values     * @return bool     */    public static function delete($table,$where,$values=null){        if(empty($table) || empty($where)) return false;        self::connect();        $tableSql = self::tableSql($table);        $whereSql = self::whereSql($where);        self::$SqlStr = ‘delete from ‘.$tableSql.$whereSql;        $num = self::executeUpdate(self::$SqlStr,$values);        return $num>0;    }    /**     * 查询数据(全部)     * @param $table     * @param $field     * @param $where     * @param $order     * @param null $values     * @return array|bool     */    public static function fetchAll($table,$field,$where,$order,$values=null){        if(empty($table)) return false;        $tableSql = self::tableSql($table);        $fieldSql = self::fieldSql($field);        $whereSql = self::whereSql($where);        $orderSql = self::orderSql($order);        self::$SqlStr = ‘select ‘.$fieldSql.‘ from ‘.$tableSql.$whereSql.$orderSql;        $rows = self::executeQuery(self::$SqlStr,$values);        return $rows;    }    /**     * 查询数据(分页)     * @param $table     * @param $page     * @param $size     * @param $field     * @param $where     * @param $order     * @param null $values     * @return array|bool     */    public static function fetchPage($table,$page,$size,$field,$where,$order,$values=null){        if(empty($table)) return false;        $tableSql = self::tableSql($table);        $fieldSql = self::fieldSql($field);        $whereSql = self::whereSql($where);        $orderSql = self::orderSql($order);        $size = intval($size);        if($size<=0) $size = 10;        $start = ($page-1)*$size;        if($start<0) $start = 0;        $end = $start+$size;        self::$SqlStr = ‘select * from (select ROW_NUMBER()over(order by tempcolumn) temprownumber,* from (select top ‘.$end.‘ tempcolumn=0,‘.$fieldSql.‘ from ‘.$tableSql.$whereSql.$orderSql.‘) temptable1) temptable2 where temprownumber>‘.$start;        $rows = self::executeQuery(self::$SqlStr,$values);        return $rows;    }    /**     * 查询数据(单条)     * @param $table     * @param $field     * @param $where     * @param $order     * @param null $values     * @return bool     */    public static function fetchOne($table,$field,$where,$order,$values=null){        if(empty($table)) return false;        $tableSql = self::tableSql($table);        $fieldSql = self::fieldSql($field);        $whereSql = self::whereSql($where);        $orderSql = self::orderSql($order);        self::$SqlStr = ‘select top 1 ‘.$fieldSql.‘ from ‘.$tableSql.$whereSql.$orderSql;        $rows = self::executeQuery(self::$SqlStr,$values);        if(!is_array($rows) || count($rows)!=1) return false;        return $rows[0];    }    /**     * 获取单个字段数据     * @param $table     * @param $name     * @param string $def     * @param null $where     * @param null $order     * @param null $values     * @return string     */    public static function fetchField($table,$name,$def=‘‘,$where=null,$order=null,$values=null){        if(empty($name)) return $def;        $data = self::fetchOne($table,$name,$where,$order,$values);        if(!is_array($data)) return $def;        return $data[$name];    }    /**     * 获取最后插入的数据ID     * @param $table     * @return int|string     */    public static function lastId($table){        $sql = ‘select top 1 IDENT_CURRENT(\‘[‘.$table.‘]\‘) id‘;        $rows = self::executeQuery($sql);        if(!is_array($rows) || count($rows)!=1) return -1;        $id = $rows[0][‘id‘];        if(!is_numeric($id)) return -1;        return $id;    }    /**     * 获取最后执行的SQL语句     * @return mixed     */    public static function lastSql(){        return self::$SqlStr;    }    /**     * 获取错误     * @return mixed     */    public static function lastError(){        return self::$ErrorMsg;    }    /**     * 获取连接字串     * @return mixed     */    public static function getDb(){        return self::$DbLink;    }    /**     * 关闭连接     */    public static function close(){        if(is_resource(self::$DbLink)) sqlsrv_close(self::$DbLink);        self::$DbLink = null;    }}?>