需要开启php的pdo支持，php5.1以上版本支持
实现数据库连接单例化，有三要素 静态变量、静态实例化方法、私有构造函数 DPDO.php
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
class DPDO{ private $DSN; private $DBUser; private $DBPwd; private $longLink; private $pdo; //私有构造函数 防止被直接实例化 private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) { $this->DSN = $dsn; $this->DBUser = $DBUser; $this->DBPwd = $DBPwd; $this->longLink = $longLink; $this->connect(); } //私有 空克隆函数 防止被克隆 private function __clone(){} //静态 实例化函数 返回一个pdo对象 static public function instance($dsn, $DBUser, $DBPwd, $longLink = false){ static $singleton = array();//静态函数 用于存储实例化对象 $singIndex = md5($dsn . $DBUser . $DBPwd . $longLink); if (empty($singleton[$singIndex])) { $singleton[$singIndex] = new self($dsn, $DBUser, $DBPwd, $longLink = false); } return $singleton[$singIndex]->pdo; } private function connect(){ try{ if($this->longLink){ $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd, array(PDO::ATTR_PERSISTENT => true)); }else{ $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd); } $this->pdo->query('SET NAMES UTF-8'); } catch(PDOException $e) { die('Error:' . $e->getMessage() . '<br/>'); } }} |
用于处理字段映射，使用pdo的字段映射，可以有效避免sql注入
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
//字段关联数组处理， 主要用于写入和更新数据、同and 或 or 的查询条件，产生sql语句和映射字段的数组 public function FDFields($data, $link = ',', $judge = array(), $aliasTable = ''){ $sql = ''; $mapData = array(); foreach($data as $key => $value) { $mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key; $sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link; $mapData[$mapIndex] = $value; } $sql = trim($sql, $link); return array($sql, $mapData); } //用于处理单个字段处理 public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') { $mapIndex = ':' . $preMap . $aliasTable . $field; $sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex; $mapData[$mapIndex] = $value; return array($sql, $mapData); } //使用刚方法可以便捷产生查询条件及对应数据数组 public function FDCondition($condition, $mapData) { if(is_string($condition)) { $where = $condition; } else if (is_array($condition)) { if($condition['str']) { if (is_string($condition['str'])) { $where = $condition['str']; } else { return false; } } if(is_array($condition['data'])) { $link = $condition['link'] ? $condition['link'] : 'and'; list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge']); if ($conSql) { $where .= ($where ? ' ' . $link : '') . $conSql; $mapData = array_merge($mapData, $mapConData); } } } return array($where, $mapData); } |
增删改查的具体实现DB.php
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) { if ($sql) { $sql .= ' limit 1'; $pdoStatement = $this->pdo->prepare($sql, $preType); $pdoStatement->execute($searchData); return $data = $pdoStatement->fetch($dataMode); } else { return false; } } public function fetchAll($sql, $searchData = array(), $limit = array(0, 10), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) { if ($sql) { $sql .= ' limit ' . (int) $limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10); $pdoStatement = $this->pdo->prepare($sql, $preType); $pdoStatement->execute($searchData); return $data = $pdoStatement->fetchAll($dataMode); } else { return false; } } public function insert($tableName, $data, $returnInsertId = false, $replace = false) { if(!empty($tableName) && count($data) > 0){ $sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO '; list($setSql, $mapData) = $this->FDFields($data); $sql .= $tableName . ' set ' . $setSql; $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $execRet = $pdoStatement->execute($mapData); return $execRet ? ($returnInsertId ? $this->pdo->lastInsertId() : $execRet) : false; } else { return false; } } public function update($tableName, $data, $condition, $mapData = array(), $returnRowCount = true) { if(!empty($tableName) && count($data) > 0) { $sql = 'UPDATE ' . $tableName . ' SET '; list($setSql, $mapSetData) = $this->FDFields($data); $sql .= $setSql; $mapData = array_merge($mapData, $mapSetData); list($where, $mapData) = $this->FDCondition($condition, $mapData); $sql .= $where ? ' WHERE ' . $where : ''; $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $execRet = $pdoStatement->execute($mapData); return $execRet ? ($returnRowCount ? $pdoStatement->rowCount() : $execRet) : false; } else { return false; } } public function delete($tableName, $condition, $mapData = array()) { if(!empty($tableName) && $condition){ $sql = 'DELETE FROM ' . $tableName; list($where, $mapData) = $this->FDCondition($condition, $mapData); $sql .= $where ? ' WHERE ' . $where : ''; $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $execRet = $pdoStatement->execute($mapData); return $execRet; } } |
测试文件test.php
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
header("Content-type: text/html; charset=utf-8");define('APP_DIR', dirname(__FILE__)); if (function_exists('spl_autoload_register')) { spl_autoload_register('autoClass');} else { function __auto_load($className){ autoClass($className); }} function autoClass($className){ try{ require_once APP_DIR.'/class/'.$className.'.php'; } catch (Exception $e) { die('Error:' . $e->getMessage() . '<br />'); }}$DB = new DB();//插入$inData['a'] = rand(1, 100);$inData['b'] = rand(1, 1000);$inData['c'] = rand(1,200) . '.' . rand(1,100);$ret = $DB->insert('a', $inData);echo '插入' . ($ret ? '成功' : '失败') . '<br/>';//更新$upConData['a'] = 100;$upConJudge['a'] = '<';$upConData['b'] = 30;$upConJudge['b'] = '>';list($upConStr, $mapUpConData) = $DB->FDField('b', 200, '<', 'gt');$condition = array( 'str' => $upConStr, 'data' => $upConData, 'judge' => $upConJudge, 'link' => 'and');$upData['a'] = rand(1, 10);$upData['b'] = 1;$upData['c'] = 1.00;$changeRows = $DB->update('a', $upData, $condition, $mapUpConData);echo '更新行数:' . (int) $changeRows . '<br/>';//删除$delVal = rand(1, 10);list($delCon, $mapDelCon) = $DB->FDField('a', $delVal);$delRet = $DB->delete('a', $delCon, $mapDelCon);echo '删除a=' . $delVal . ($delRet ? '成功' : '失败') . '<br/>'; //查询$data['a'] = '10';$judge['a'] = '>';$data['b'] = '400';$judge['b'] = '<';list($conSql, $mapConData) = $DB->FDFields($data, 'and', $judge);$mData = $DB->fetch('select * from a where ' . $conSql . ' order by `a` desc', $mapConData); var_dump($mData); |
以上所述就是本文的全部内容了，希望大家能够喜欢。
