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

傷城~ 2022-06-12 11:17 126阅读 0赞

> 前面提到的设计模式大大提高了代码的可读性与可维护性。然而，在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的内容就可以了。

说明转载最好说明出处：[php 设计模式-数据映射模式（应用程序与数据库交互模式）][php _-][http://blog.csdn.net/hguisu/article/details/7569968][php _-]

[php _-]: http://blog.csdn.net/hguisu/article/details/7569968