在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>
运行程序,效果如下: