php 设计模式-数据映射模式（应用程序与数据库交互模式）

最新推荐文章于 2021-04-03 15:39:38 发布

piperzero

最新推荐文章于 2021-04-03 15:39:38 发布

阅读量203

点赞数

文章标签：数据库 php 设计模式

前面提到的设计模式大大提高了代码的可读性与可维护性。然而，在WEB应用设计与开发中一个基本的需求与挑战：数据库应用，这些设计模式都没有涉及到。数据映射模式使您能更好的组织你的应用程序与数据库进行交互。

下面我将用实际代码说明，如果一个表发生变动。我们要修改客户端代码就可以了。特别是游戏项目，需求经常可能会经常变动。修改表结构，可能引起大片代码的改动。

首先我们使用pdo进行数据库访问：

<?php /** * Filename:db.class.php * * db class ,use PDO lib * * @author guisu.huang * @version 1.0 * */ class Db { public static $db = null; private $_dbh = null; public static function getInstance() { if( self::$db == null ){ self::$db = new self(BACKEND_DBHOST ,BACKEND_DBUSER ,BACKEND_DBPW ,BACKEND_DBNAME); } return self::$db; } private function __construct( $host ,$user ,$pass ,$dbname ){ try { $this->_dbh = new PDO('mysql:dbname='.$dbname.';host='.$host,$user,$pass); $this->_dbh->query('SET NAMES '. BACKEND_DBCHARSET); $this->_dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); $this->_dbh->setAttribute(PDO::ATTR_ERRMODE, true); } catch (PDOException $e) { throw new Exception('Can not connect db'); } } private function getExecuteResult($sql, $sth){ $type = strtolower(substr(trim($sql), 0,6)); switch ($type) { case 'update': case 'delete': $result = $sth->rowcount();//返回影响的行数 break; case 'insert': $result = $this->getLastId(); break; case 'select': $result = $sth->fetchAll(PDO::FETCH_ASSOC); break; default: break; } return $result; } /**************************************sql ************************/ public function getOne($sql){ try { $rs = $this->_dbh->query($sql); $result = $rs->fetch(PDO::FETCH_ASSOC); if(!empty($result)) { return $result; } } catch (PDOException $e) { throw new Exception($this->_dbh->errorInfo()); } return false; } public function getAll($sql){ try { $rs = $this->_dbh->query($sql); $result = $rs->fetchAll(PDO::FETCH_ASSOC); if(!empty($result)) { return $result; } } catch (PDOException $e) { throw new Exception($this->_dbh->errorInfo()); } return false; } public function exec($sql){ try { $exec = $this->_dbh->exec($sql); } catch (PDOException $e){ throw new Exception($this->_dbh->errorInfo()); } return $exec; } /** * 不关注键值 * Execute a prepared statement by passing an array of values $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?'); $sth->execute(array(150, 'red')); $red = $sth->fetchAll(); $sth->execute(array(175, 'yellow')); $yellow = $sth->fetchAll(); * @param unknown_type $sql * @param unknown_type $arr * @return unknown */ public function executeArr($sql, $arr){ try { $sth = $this->_dbh->prepare($sql); $r = $sth->execute($arr); if ($r) { return $this->getExecuteResult($sql, $sth); } } catch (PDOException $e){ throw new Exception($e->getMessage() . $this->_dbh->errorInfo()); } } /** * 关联数组: * Execute a prepared statement by passing an array of values $sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour'; $sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $sth->execute(array(':calories' => 150, ':colour' => 'red')); $red = $sth->fetchAll(); * * @param unknown_type $sql * @param unknown_type $arr * @return unknown */ public function executeAsoc($sql, $arr){ try { $array = array(); if ($arr) { foreach ($arr as $key=>$v) { if (strpos($sql, ':' . $key )!==false) { $array[':' . $key] = $v; } } } $sth = $this->_dbh->prepare($sql); $r = $sth->execute($array); if ($r) { return $this->getExecuteResult($sql, $sth); } } catch (PDOException $e){ throw new Exception($e->getMessage() . $this->_dbh->errorInfo()); } } public function beginTransaction(){ return $this->_dbh->beginTransaction(); } public function commit(){ return $this->_dbh->commit(); } public function rollBack(){ return $this->_dbh->rollBack(); } public function getLastId() { return $this->_dbh->lastInsertId(); } } ?>
数据映相关类射类，使用__call达到动态生成getter 和setter方法.

<?php /** * 抽象数据映射 * */ abstract class Table{ public function __call($method, $args) { if (preg_match('/^(get|set)(\w+)/', strtolower($method), $match) && $attribute = $this->validateAttribute($match[2])) { if ('get' == $match[1]) { return $this->$attribute; } else { $this->$attribute = $args[0]; } }else { throw new Exception( 'Call to undefined method ' . __CLASS__ . '::'.$method.'()'); } } protected function validateAttribute($method) { if ( in_array(strtolower($method), array_keys(get_class_vars(get_class($this))))) { return strtolower($method); } } } /** * 数据映射到表 * 一般根据表的结构由工具自动生成， * 要不然程序员经常得copy和修改这个类 * */ class UserTable extends Table { /** * fields * * @var unknown_type */ protected $uid = null; protected $username = null ; protected $level = null; protected $exp = null; protected $ctime = null; protected $mtime = null; /** * table * * @var unknown_type */ public $tableName = 'user'; public $primaryKey = 'uid'; public static $tablefileds = array( 'uid', 'username', 'level', 'exp', 'ctime', 'mtime', ); /** * 对象生成数组 * * @return array */ function toArray(){ $arr = array(); foreach (UserTable::$tablefileds as $filed) { $getMethod = 'get' .ucwords($filed); $value = $this->$getMethod(); if ($value !== null) { $arr[$filed] = $value; } } return $arr; } /** * 数组生成对象 * * @return array */ function toObj($arr){ if (!$arr) { return $this; } foreach (UserTable::$tablefileds as $filed) { $setMethod = 'set' .ucwords($filed); $this->$setMethod($arr[$filed]); } return $this; } } /** * * */ class Mapper{ protected $conn = null; /** * 自动插入 * 不想对某一列插入，把对应的属性设置成null就ok * * @param Table $table * @return unknown */ function save(Table $table){ $arr = $table->toArray(); $set = ''; if ($arr) { foreach ($arr as $field=> $v) { if ($set) $set .=','; $set .= $field . "='" . $v ."'"; } } if ($set) { $this->conn->exec( 'insert into ' . $table->tableName . ' SET ' . $set); return $this->conn->getLastId(); } } /** * 更新 * 不想对某一列更新，把对应的属性设置成null就ok * * @param Table $table * @return unknown */ function update(Table $table){ $arr = $table->toArray(); $set = ''; if ($arr) { foreach ($arr as $field=> $v) { if ($set) $set .=','; $set .= $field . "='" . $v ."'"; } } $primayGet = 'get'.ucwords($table->primaryKey); if ($set) { return $this->conn->exec( 'update ' . $table->tableName . ' SET ' . $set . ' where ' . $table->primaryKey ."='" . $table->$primayGet() . "'" ); } } } class UserMapper extends Mapper { const INSERT_SQL = "insert into user (username, level,exp, ctime, mtime) values (:username, :level, :exp, now(), now())"; const UPDATE_SQL = "update user SET username=:username, level=:level, exp=:exp WHERE uid=:uid "; const SELECT_SQL = "select * from user WHERE uid=:uid "; const DELETE_SQL = "delete from user WHERE uid=:uid "; function __construct(){ $this->conn = Db::getInstance(); } /** * 我们可以实现覆盖save * * @param unknown_type $userTable */ public function save2($userTable) { $rs = Db::getInstance()->executeArr( self::INSERT_SQL, $userTable->toArray()); return $rs; } /** * Enter description here... * * @param unknown_type $userTable */ public function update2($userTable) { return $this->conn->execute(self::UPDATE_SQL, $userTable->toArray()); } /** * Enter description here... * * @param unknown_type $arr */ public function find($userTable) { $rs = $this->conn->executeAsoc( self::SELECT_SQL, $userTable->toArray()); return $rs ? $userTable->toObj($rs[0]) : $userTable; } } ?>
实际客户测试：

<?php /** * 数据库配置文件 * */ define('BACKEND_DBHOST', 'localhost'); define('BACKEND_DBUSER', 'root'); define('BACKEND_DBPW', '123456'); define('BACKEND_DBNAME', 'sample'); define('BACKEND_DBCHARSET', 'utf-8'); //sql /* CREATE TABLE IF NOT EXISTS `user` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(64) NOT NULL, `level` int(11) NOT NULL DEFAULT '0', `exp` int(11) NOT NULL DEFAULT '0', `ctime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `mtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`uid`), KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; */ class client{ static function main(){ $userMapper = new UserMapper(); $user = new UserTable(); //插入 //$user->setUserName('guisu'); //$user->setLevel(1); //$user->setExp(10); // //$userMapper = new UserMapper(); //$r = $userMapper->save($user); //查找 $user->setUid(10); $user = $userMapper->find($user); var_dump($user); //更新 $user->setUserName('guisu2'); $r = $userMapper->update($user); var_dump($r); } }

一般，client是业务逻辑层，UserMapper是数据访问层。UserTable底层数据结构。

我们尽量做到如果表User修改了：

1）工具重新自动生成UserTable类

2）只修改client代码和少量的UserMapper代码，一般修改UserMapper的常量const的内容就可以了。