这个数据库类主要处理了单例模式下创建数据库对象时,如果有两次较长时间的间隔去执行sql操作,再次处理会出现连接失败的问题,利用一个cache数组存放pdo对象与时间戳,把两次执行之间的时间进行了比较,如果间隔超过了10秒就再次new PDO创建连接,没有超过的情况下会继续使用原来的连接,并且因为每次使用后会使连接续期,cache数组里的时间戳也进行了续期.
每次执行操作都会从cache数组中获取下连接,多次执行不超过10秒的情况下,只会有一个连接
代码中实现读写分离,判断sql语句前面6个字符是select的就查询从库,其余操作查询主库.主库和从库就是分别在配置数组中0和1创建不同的PDO对象连接
代码如下:
<?php class SinaPdoAdapter{ const MASTER = 0; const SLAVE = 1; const DEFAULT_CACHE_EXPIRETIME = 10; private static $options = array( PDO::ATTR_AUTOCOMMIT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //PDO::ATTR_PERSISTENT => true, ); private $dsn = null; private $username = null; private $password = null; private $timeout = null; private $charset = null; private $conns = array(); private $conn = null; private $stmt = null; private static $obj=null; private function __construct($dsn, $username, $password, $timeout = null, $charset = null){ $this->dsn = $dsn; if (!is_array($username)) { $this->username = array($username); } else { $this->username = $username; } if (!is_array($password)) { $this->password = array($password); } else { $this->password = $password; } $this->timeout = intval($timeout); $this->charset = $charset; } private function getConnection($id = self::MASTER){ if (!isset($this->dsn[$id])) { $id = self::MASTER; } $conn = $this->getCachedConn($id); if ($conn) { return $conn; } $opts = self::$options; if ($this->timeout > 0) { $opts[PDO::ATTR_TIMEOUT] = $this->timeout; } $username = isset($this->username[$id]) ? $this->username[$id] : $this->username[self::MASTER]; $password = isset($this->password[$id]) ? $this->password[$id] : $this->password[self::MASTER]; $conn = new PDO($this->dsn[$id], $username, $password, $opts); $this->cacheConn($id, $conn); if ($this->charset) { $conn->exec('set names ' . $this->charset); } return $conn; } public function execute($sql, $params = array()){ $cmd = substr($sql, 0, 6); if (strcasecmp($cmd, 'select') === 0) { $conn = $this->getConnection(self::SLAVE); } else { $conn = $this->getConnection(self::MASTER); } $stmt = $conn->prepare($sql); $stmt->execute($params); $this->stmt = $stmt; $this->conn = $conn; } public function fetch(){ return $this->stmt->fetch(); } public function fetchAll(){ return $this->stmt->fetchAll(); } public function lastInsertId(){ return $this->conn->lastInsertId(); } public function rowCount(){ return $this->stmt->rowCount(); } public static function getInstance($conf){ if(self::$obj == null){ self::$obj = new self($conf->dsn,$conf->username,$conf->password,$conf->timeout,$conf->charset); } return self::$obj; } private function getCachedConn($id){ if (!isset($this->conns[$id])) { return null; } list($conn, $timeout) = $this->conns[$id]; if (time() < $timeout) { $this->cacheConn($id, $conn); return $conn; } else { return null; } } private function cacheConn($id, $conn){ $timeout = time(); if ($this->timeout) { $timeout += $this->timeout; } else { $timeout += self::DEFAULT_CACHE_EXPIRETIME; } $this->conns[$id] = array($conn, $timeout); } } $config=new stdClass(); $config->dsn=array( "mysql:host=127.0.0.1;port=3306;dbname=surframe",//主库 "mysql:host=127.0.0.2;port=3306;dbname=surframe"//从库 ); $config->username=array( 'root', 'root', ); $config->password=array( 'taoshihan1', 'taoshihan1', ); $config->timeout=10; $config->charset="utf8"; $db=SinaPdoAdapter::getInstance($config); $db->execute("select * from admin_users");//使用的从库 $rows=$db->fetchAll(); var_dump($db); $db=SinaPdoAdapter::getInstance($config); $db->execute("select * from admin_users");//使用的从库 $rows=$db->fetchAll(); var_dump($db);