PDO方式連接MySQL數據庫 · 基于thinkphp5小功能設計與實現

~~~ class Model { protected static $_instance; protected static $_link; protected $whereStr = '';//用來存儲where條件 /** * 單例模式不允許在類外對類進行實例化 */ private function __construct(){} /** * 獲得類的實例 * @return mixed|Model */ public static function getStringleton(){ //判斷我們類的實例是否存在，沒有則創建之 if(!isset(self::$_instance)){ self::$_instance = new self(); } //連接數據庫 self::connect(HOST,UNAME,UPASS,DBNAME); return self::$_instance; } /** * 連接數據庫方法 * @param $host 服務器地址 * @param $username 數據庫賬號 * @param $userpass 數據庫密碼 * @param $dbname 操作的數據庫名 */ protected static function connect($host,$username,$userpass,$dbname){ try{ self::$_link = new PDO("mysql:host={$host};dbname={$dbname}",$username,$userpass); //設置返回數據的類型 self::$_link->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); //設置操作數據庫的報錯模式 // self::$_link->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); // if(self::$_link){ // echo '連接數據庫成功'; // } }catch (PDOException $e){ MyException::showError($e->getMessage()); } } /** * 直接執行sql語句查詢數據庫的方法 * @param $sql mysql語句 * @param array $where 條件數據 * @return mixed 成功數組 */ public function queryString($sql,$where=array()){ try{ //使用預處理語句來執行sql $stmt = self::$_link->prepare($sql); //判斷是否有條件數組 if(empty($where)){ $stmt->execute(); }else{ $stmt->execute($where); } //判斷執行是否成功 if($stmt->rowCount() > 0){ return $stmt->fetchAll(); }else { //得到錯誤信息 $err = $stmt->errorInfo(); throw new PDOException($err[2]); } }catch(PDOException $e){ MyException::showError($e->getMessage()); } } /** * 內部sql處理好的查詢方法 * @param $table 表名 * @param array $where 查詢條件 * @return mixed 成功返回數組 */ public function select($table,$where = array()){ $sql = "select * from {$table} "; if(!empty($this->whereStr)){ $sql .= $this->whereStr; } try{ //執行sql語句 $stmt = self::$_link->prepare($sql); if(empty($where)){ $stmt->execute(); }else{ $stmt->execute($where); } //判斷是否成功，如果不成功爆出異常 if($stmt->rowCount() > 0){ return $stmt->fetchAll(); }else{ $err = $stmt->errorInfo(); return $err[2]; } }catch(PDOException $e){ MyException::showError($e->getMessage()); } } /** * where條件方法 * @param string $whereStr * @return $this */ public function where($whereStr = ''){ $this->whereStr = $whereStr; return $this;//返回當前對象 } /** * 查詢單條數據的方法 * @param $table 表名 * @param array $where 查詢的條件，:key=value * @return mixed 成功返回數組 */ public function find($table,$where = array()){ $sql = "select * from {$table} "; if(!empty($this->whereStr)){ $sql .= $this->whereStr; } try{ //執行sql $stmt = self::$_link->prepare($sql); if(empty($where)){ $stmt->execute(); }else{ $stmt->execute($where); } //判斷是否成功 if($stmt->rowCount() > 0){ $result = $stmt->fetchAll(); return $result[0]; }else{ $err = $stmt->errorInfo(); throw new PDOException($err[2]); } }catch(PDOException $e){ MyException::showError($e->getMessage()); } } /** * 添加單條數據的方法 * @param $table 表名 * @param array(':username'=>'zhang6',':userpass'=>md5(123456),':create_time'=>time()) $data * @return int 成功返回1 */ public function insert($table,array $data){ $sql = "insert into {$table} "; $fields = ""; $values = ""; foreach($data as $k => $v){ $fields .= ltrim($k,":").","; $values .= "'".ltrim($v,":")."',"; } $sql .= "(".rtrim($fields,",").") values (".rtrim($values,",").")"; try{ //開啟事務 self::$_link->beginTransaction(); $stmt = self::$_link->prepare($sql); $stmt->execute($data); if($stmt->rowCount() > 0){ self::$_link->commit(); return 1; }else{ self::$_link->rollback(); $err = $stmt->errorInfo(); throw new PDOException($err[2]); } }catch(PDOException $e){ MyException::showError($e->getMessage()); } } /** * 更新數據 * @param $table 表名 * @param array $data array(':username'=>'lisi',':userpass'=>md5(456789)); * @param array $where array(':id'=>9); * @return int */ public function update($table,array $data,array $where){ $sql = "update {$table} set "; $set_str = ''; foreach($data as $k => $v){ $set_str .= ltrim($k,":")."=$k,"; } $sql .= rtrim($set_str,',').' '.$this->whereStr; try{ self::$_link->beginTransaction(); $stmt = self::$_link->prepare($sql); $data2 = array_merge($data,$where); $stmt->execute($data2); if($stmt->rowCount() > 0){ self::$_link->commit(); return 1; }else{ self::$_link->rollback(); $err = $stmt->errorInfo(); throw new PDOException($err[2]); } }catch(PDOException $e){ MyException::showError($e->getMessage()); } } /** * 刪除數據方法 * @param $table 表名 * @param array $where * @return int */ public function delete($table,array $where){ $sql = "delete from {$table} ".$this->whereStr; try{ self::$_link->beginTransaction(); $stmt = self::$_link->prepare($sql); $stmt->execute($where); if($stmt->rowCount() > 0){ self::$_link->commit(); return 1; }else{ self::$_link->rollback(); $err = $stmt->errorInfo(); throw new PDOException($err[2]); } }catch(PDOException $e){ MyException::showError($e->getMessage()); } } /** * 析構方法 * 銷毀對象 */ public function __destruct(){ self::$_link = null; } } ~~~ 測試 ~~~ echo "<meta charset='utf-8'>"; require_once 'core/App.class.php'; require_once 'config/constants.php'; //注冊一個 define('APP','app'); spl_autoload_register(array('App','myAutoloader')); try{ App::run(); }catch(MyException $e){ $e->showError($e->getMessage()); } $db = Model::getStringleton(); //直接執行sql語句 //$result = $db->queryString('select * from users where username=:username',array(':username'=>'zhang')); //查詢某個表 //$result = $db->select('users'); //查詢某個表，并增加where條件 //$result = $db->where('where username=:username')->select('users',array(':username'=>'zhang')); //查詢單條數據 //$result = $db->where('where id=:id')->find('users',array(':id'=>'3')); //插入數據 //$data = array(':username'=>'zhang6',':userpass'=>md5(123456),':create_time2'=>time()); //$result = $db->insert('users',$data); //更新數據 //$data = array(':username'=>'lisi',':userpass'=>md5(456789)); //$where = array(':id'=>9); //$result = $db->where('where id=:id')->update('users',$data,$where); //刪除數據 $result = $db->where('where id=:id')->delete('users',array('id'=>9)); echo "<pre>"; print_r($result); ~~~