Zend2学习笔记（2）——TableGateway

在Zend2框架中进行数据库操作，推荐使用TableGateway，以下是一个具体示例：

本例可能需要完成一些Zend2框架程序搭建的步骤，可以参考：http://blog.youkuaiyun.com/xwlyun/article/details/40888523

本例程序相关结构如图：

1.配置数据连接 /config/autoload/global.php

<?php
/**
 * Global Configuration Override
 *
 * You can use this file for overriding configuration values from modules, etc.
 * You would place values in here that are agnostic to the environment and not
 * sensitive to security.
 *
 * @NOTE: In practice, this file will typically be INCLUDED in your source
 * control, so do not include passwords or other sensitive information in this
 * file.
 */

return array(
    // ...

	/*
	 * No.5
	 * 数据库配置 dbname、host
	 * username、password可以写在local.php文件中
	 */
	'db' => array(
		'driver' => 'Pdo',	// 数据库驱动类型
		'dsn' => 'mysql:dbname=my_zend;host=127.0.0.1',
		'driver_options' => array(
			PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
		),
	),

	/*
	 * 预备使用 ServiceManager(服务管理器) 的工厂模式获取 数据库配置
	 */
	'service_manager' => array(
		'factories' => array(
			'Zend\Db\Adapter\Adapter' => 'Zend\Db\Adapter\AdapterServiceFactory',
		),
	),
);

这里还配置了 service_manager 服务管理器，以便我们在后面可以很方便的完成程序内部的相互引用
数据库的用户名&密码，可以写在 /config/autoload/local.php

<?php
/**
 * No.6
 * 配置数据库 username、password
 * @xwl
 * 2014-10-22 13:28
 */

return array(
	'db' => array(
		'username' => 'root',
		'password' => 'root',
	),
);

2.添加并修改 /module/Post/src/Post/Model/Post.php

这个Post Module中，用来封装所有对数据库的操作，返回数据

<?php
/**
 * No.7
 * 创建model类
 * 封装数据库操作集，使用了 TableGateway 方案
 * @xwl
 * 2014-10-22 13:15
 */
/*
CREATE TABLE IF NOT EXISTS `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `post` (`id`, `title`, `content`, `user_id`, `created`) VALUES
(1, '今天是2014年10月17日', '今天是个好天气~', 1, '2014-10-17 12:09:30');
 */

namespace Post\Model;

/*
 * 使用 TableGateway 操作数据库
 */
use Zend\Db\TableGateway\TableGateway;

use Zend\Db\Sql\Select;

class Post
{
	protected $tableGateway;

	public function __construct(TableGateway $tableGateway)
	{
		$this->tableGateway = $tableGateway;
	}

	public function select($limit = 1)
	{
		/*
		 * tableGateway -> select
		 * sql：select * from `post` where `id`=2
		 */
//		$resultSet = $this->tableGateway->select(array('id' => 2));
//		return $resultSet;

		/*
		 * 使用 Zend\Db\Sql\Select
		 * sql：select * from `post` where `id`=2 limit 1
		 */
//		$select = new Select($this->tableGateway->table);
//		$select->where(array('id'=>2));
//		$select->limit($limit);
//		$resultSet = $this->tableGateway->selectWith($select);
//		return $resultSet;

		/*
		 * select where 链式
		 * sql：select * from `post` where `id`>1 order by `id` desc limit 2
		 */
//		$select = new Select($this->tableGateway->table);
//		$select->where('id>1')->order('id desc')->limit(2);
//		$resultSet = $this->tableGateway->selectWith($select);
//		return $resultSet;

		/*
		 * select where 闭包
		 * sql：select * from `post` where `id`<4 and `id`>1 order by `id` desc limit 2
		 */
//		$select = new Select($this->tableGateway->table);
//		$select->where(function($where){
//			$where->lessThan('id',4);
//			$where->greaterThan('id',1);
//			return $where;
//		})->order('id desc')->limit(2);
//		$resultSet = $this->tableGateway->selectWith($select);
//		return $resultSet;

		/*
		 * select where 非闭包
		 * sql：select * from `post` where `id`<4 and `id`>1 order by `id` desc limit 2
		 */
//		$select = new Select($this->tableGateway->table);
//		$where = $select->where;
//		$where->lessThan('id',4);
//		$where->greaterThan('id',1);
//		$select->order('id desc')->limit(2);
//		$resultSet = $this->tableGateway->selectWith($select);
//		return $resultSet;

		/*
		 * select where and复合条件
		 * sql：select * from `post` where `id`>0 and `id`<3 order by `id` desc limit 2
		 */
//		$select = new Select($this->tableGateway->table);
//		$select->where(array('id>0'))->where(array('id<3'))->order('id desc')->limit(2);
//		$resultSet = $this->tableGateway->selectWith($select);
//		return $resultSet;

		/*
		 * select where or复合条件
		 * sql：select * from `post` where `id`<2 or `id`>3 order by `id` desc limit 2
		 */
//		$select = new Select($this->tableGateway->table);
//		$select->where(
//			array('id<2')
//		)->where(
//			array('id>3'), \Zend\Db\Sql\Where::OP_OR
//		)->order('id desc')->limit(2);
//		$resultSet = $this->tableGateway->selectWith($select);
//		return $resultSet;

		/*
		 * select where 复杂条件
		 * sql：select * from `post` where (`id`<2 or `id`>3) and (`user_id`=1 or `user_id`=2) order by `id` desc limit 2
		 */
		$select = new Select($this->tableGateway->table);
		$select->where(function($where){

			$subwhere1 = clone $where;
			$subwhere2 = clone $where;

			$subwhere1->lessThan('id',2);
			$subwhere1->or;
			$subwhere1->greaterThan('id',3);
			$where->addPredicate($subwhere1);

			$subwhere2->equalTo('user_id',1);
			$subwhere2->or;
			$subwhere2->equalTo('user_id',2);
			$where->addPredicate($subwhere2);

			return $where;
		})->order('id desc')->limit(2);
		$resultSet = $this->tableGateway->selectWith($select);
		return $resultSet;

	}

}

这里使用了Zend框架自带的 Zend\Db\TableGateway\TableGateway 来完成对数据库的查询操作，

也可以引用 Zend\Bd\Sql\Select 来满足更加灵活的查询操作

3.添加并修改控制器PostController来显示Post model返回的数据：/model/Post/src/Post/Controller/PostController.php

<?php
/**
 * No.4
 * 创建控制器(Controller)
 * @xwl
 * 2014-10-21 15:38
 */

namespace Post\Controller;

/*
 * ZF2 提供了两个常见的抽象类 AbstractActionController、AbstractRestfulController
 * 一般使用标准的 abstract action controller
 */
use Zend\Mvc\Controller\AbstractActionController;

/*
 * 构建视图(view)必须的引用
 */
use Zend\View\Model\ViewModel;

class PostController extends AbstractActionController
{
	/*
	 * No.10
	 * 使用 ServiceManager 调用数据库操作封装集 class PostTable
	 * 数据库对象
	 */
	protected $postTable;
	public function getPostTable()
	{
		if(!$this->postTable){
			$sm = $this->getServiceLocator();
			$this->postTable = $sm->get('Post');	// 对应在 Module.php 中的声明
		}
		return $this->postTable;
	}


	public function indexAction()
	{
		return new ViewModel(
			array(
				'posts'=>$this->getPostTable()->select()	// 属于 class Post 的一个 function
			)
		);
	}

	public function addAction()
	{
		return new ViewModel();
	}

	public function editAction()
	{
		return new ViewModel();
	}

	public function deleteAction()
	{
		return new ViewModel();
	}
}

因为在global.php中我们声明使用ServiceManager，并在Module.php中配置了相关内部引用的声明，

所以可以在PostController中很方便进行使用：先$this->getServiceLocator()，再根据配置中的名字进行引用

4.添加并修改一个简单的显示页view来测试我们的程序：/module/Post/view/post/post/index.phtml

<h1>posts index</h1>
<table class="table">
	<thead>
	<tr>
		<td>标题</td>
		<td>作者</td>
		<td>时间</td>
	</tr>
	</thead>
	<tbody>
		<?php foreach($posts as $row){ ?>
			<tr>
				<td><?php echo $row->title;?></td>
				<td><?php echo $row->user_id;?></td>
				<td><?php echo $row->created;?></td>
			</tr>
		<?php } ?>
	</tbody>
</table>

运行程序，效果如下：