php 单例类 mysql pdo_php单例模式下编写PDO类

1 <?php2 /**3 * MyPDO4 * @author Jason.Wei 5 * @license http://www.sunbloger.com/6 * @version 5.0 utf87 */

8 classMyPDO9 {10 protected static $_instance = null;11 protected $dbName = '';12 protected $dsn;13 protected $dbh;14

15 /**16 * 构造17 *18 * @return MyPDO19 */

20 private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)21 {22 try{23 $this->dsn = 'mysql:host='.$dbHost.';dbname='.$dbName;24 $this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd);25 $this->dbh->exec('SET character_set_connection='.$dbCharset.', character_set_results='.$dbCharset.', character_set_client=binary');26 } catch (PDOException $e) {27 $this->outputError($e->getMessage());28 }29 }30

31 /**32 * 防止克隆33 *34 */

35 private function__clone() {}36

37 /**38 * Singleton instance39 *40 * @return Object41 */

42 public static function getInstance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)43 {44 if (self::$_instance === null) {45 self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset);46 }47 return self::$_instance;48 }49

50 /**51 * Query 查询52 *53 * @param String $strSql SQL语句54 * @param String $queryMode 查询方式(All or Row)55 * @param Boolean $debug56 * @return Array57 */

58 public function query($strSql, $queryMode = 'All', $debug = false)59 {60 if ($debug === true) $this->debug($strSql);61 $recordset = $this->dbh->query($strSql);62 $this->getPDOError();63 if ($recordset) {64 $recordset->setFetchMode(PDO::FETCH_ASSOC);65 if ($queryMode == 'All') {66 $result = $recordset->fetchAll();67 } elseif ($queryMode == 'Row') {68 $result = $recordset->fetch();69 }70 } else{71 $result = null;72 }73 return $result;74 }75

76 /**77 * Update 更新78 *79 * @param String $table 表名80 * @param Array $arrayDataValue 字段与值81 * @param String $where 条件82 * @param Boolean $debug83 * @return Int84 */

85 public function update($table, $arrayDataValue, $where = '', $debug = false)86 {87 $this->checkFields($table, $arrayDataValue);88 if ($where) {89 $strSql = '';90 foreach ($arrayDataValue as $key => $value) {91 $strSql .= ", `$key`='$value'";92 }93 $strSql = substr($strSql, 1);94 $strSql = "UPDATE `$table` SET $strSql WHERE $where";95 } else{96 $strSql = "REPLACE INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";97 }98 if ($debug === true) $this->debug($strSql);99 $result = $this->dbh->exec($strSql);100 $this->getPDOError();101 return $result;102 }103

104 /**105 * Insert 插入106 *107 * @param String $table 表名108 * @param Array $arrayDataValue 字段与值109 * @param Boolean $debug110 * @return Int111 */

112 public function insert($table, $arrayDataValue, $debug = false)113 {114 $this->checkFields($table, $arrayDataValue);115 $strSql = "INSERT INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";116 if ($debug === true) $this->debug($strSql);117 $result = $this->dbh->exec($strSql);118 $this->getPDOError();119 return $result;120 }121

122 /**123 * Replace 覆盖方式插入124 *125 * @param String $table 表名126 * @param Array $arrayDataValue 字段与值127 * @param Boolean $debug128 * @return Int129 */

130 public function replace($table, $arrayDataValue, $debug = false)131 {132 $this->checkFields($table, $arrayDataValue);133 $strSql = "REPLACE INTO `$table`(`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";134 if ($debug === true) $this->debug($strSql);135 $result = $this->dbh->exec($strSql);136 $this->getPDOError();137 return $result;138 }139

140 /**141 * Delete 删除142 *143 * @param String $table 表名144 * @param String $where 条件145 * @param Boolean $debug146 * @return Int147 */

148 public function delete($table, $where = '', $debug = false)149 {150 if ($where == '') {151 $this->outputError("'WHERE' is Null");152 } else{153 $strSql = "DELETE FROM `$table` WHERE $where";154 if ($debug === true) $this->debug($strSql);155 $result = $this->dbh->exec($strSql);156 $this->getPDOError();157 return $result;158 }159 }160

161 /**162 * execSql 执行SQL语句163 *164 * @param String $strSql165 * @param Boolean $debug166 * @return Int167 */

168 public function execSql($strSql, $debug = false)169 {170 if ($debug === true) $this->debug($strSql);171 $result = $this->dbh->exec($strSql);172 $this->getPDOError();173 return $result;174 }175

176 /**177 * 获取字段最大值178 *179 * @param string $table 表名180 * @param string $field_name 字段名181 * @param string $where 条件182 */

183 public function getMaxValue($table, $field_name, $where = '', $debug = false)184 {185 $strSql = "SELECT MAX(".$field_name.") AS MAX_VALUE FROM $table";186 if ($where != '') $strSql .= " WHERE $where";187 if ($debug === true) $this->debug($strSql);188 $arrTemp = $this->query($strSql, 'Row');189 $maxValue = $arrTemp["MAX_VALUE"];190 if ($maxValue == "" || $maxValue == null) {191 $maxValue = 0;192 }193 return $maxValue;194 }195

196 /**197 * 获取指定列的数量198 *199 * @param string $table200 * @param string $field_name201 * @param string $where202 * @param bool $debug203 * @return int204 */

205 public function getCount($table, $field_name, $where = '', $debug = false)206 {207 $strSql = "SELECT COUNT($field_name) AS NUM FROM $table";208 if ($where != '') $strSql .= " WHERE $where";209 if ($debug === true) $this->debug($strSql);210 $arrTemp = $this->query($strSql, 'Row');211 return $arrTemp['NUM'];212 }213

214 /**215 * 获取表引擎216 *217 * @param String $dbName 库名218 * @param String $tableName 表名219 * @param Boolean $debug220 * @return String221 */

222 public function getTableEngine($dbName, $tableName)223 {224 $strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name='".$tableName."'";225 $arrayTableInfo = $this->query($strSql);226 $this->getPDOError();227 return $arrayTableInfo[0]['Engine'];228 }229

230 /**231 * beginTransaction 事务开始232 */

233 private functionbeginTransaction()234 {235 $this->dbh->beginTransaction();236 }237

238 /**239 * commit 事务提交240 */

241 private functioncommit()242 {243 $this->dbh->commit();244 }245

246 /**247 * rollback 事务回滚248 */

249 private functionrollback()250 {251 $this->dbh->rollback();252 }253

254 /**255 * transaction 通过事务处理多条SQL语句256 * 调用前需通过getTableEngine判断表引擎是否支持事务257 *258 * @param array $arraySql259 * @return Boolean260 */

261 public function execTransaction($arraySql)262 {263 $retval = 1;264 $this->beginTransaction();265 foreach ($arraySql as $strSql) {266 if ($this->execSql($strSql) == 0) $retval = 0;267 }268 if ($retval == 0) {269 $this->rollback();270 return false;271 } else{272 $this->commit();273 return true;274 }275 }276

277 /**278 * checkFields 检查指定字段是否在指定数据表中存在279 *280 * @param String $table281 * @param array $arrayField282 */

283 private function checkFields($table, $arrayFields)284 {285 $fields = $this->getFields($table);286 foreach ($arrayFields as $key => $value) {287 if (!in_array($key, $fields)) {288 $this->outputError("Unknown column `$key` in field list.");289 }290 }291 }292

293 /**294 * getFields 获取指定数据表中的全部字段名295 *296 * @param String $table 表名297 * @return array298 */

299 private function getFields($table)300 {301 $fields = array();302 $recordset = $this->dbh->query("SHOW COLUMNS FROM $table");303 $this->getPDOError();304 $recordset->setFetchMode(PDO::FETCH_ASSOC);305 $result = $recordset->fetchAll();306 foreach ($result as $rows) {307 $fields[] = $rows['Field'];308 }309 return $fields;310 }311

312 /**313 * getPDOError 捕获PDO错误信息314 */

315 private functiongetPDOError()316 {317 if ($this->dbh->errorCode() != '00000') {318 $arrayError = $this->dbh->errorInfo();319 $this->outputError($arrayError[2]);320 }321 }322

323 /**324 * debug325 *326 * @param mixed $debuginfo327 */

328 private function debug($debuginfo)329 {330 var_dump($debuginfo);331 exit();332 }333

334 /**335 * 输出错误信息336 *337 * @param String $strErrMsg338 */

339 private function outputError($strErrMsg)340 {341 throw new Exception('MySQL Error: '.$strErrMsg);342 }343

344 /**345 * destruct 关闭数据库连接346 */

347 public functiondestruct()348 {349 $this->dbh = null;350 }351 }352 ?>