5.7. Database access using PdoInterceptor
Prev Chapter 5. MISC Next

5.7. Database access using PdoInterceptor

5.7.1. About Example

Example of database access using eptor is in example/misc/pdo. Each class file, a configuration file, and directory composition become the next. 

example/misc/pdo/
 +-- classes/
 |    +-- Paginate.php
 |    +-- PdoInterceptor.php
 |    +-- SqlFileReader.php
 |    +-- StandardDto.php
 |    +-- StandardPdo.php
 +-- example/
 |    +-- config/
 |    |    +-- pdo.dicon
 |    |    +-- SqliteAPdo.php
 |    |    +-- SqliteBPdo.php
 |    +-- db/
 |         +-- example.sql
 |         +-- sqlite_a.db
 |         +-- sqlite_b.db
 |         +-- sqlite_c.db
 +-- tests/
The classes in a clases directory are required in order to use PdoInterceptor.

A data source setup of PDO is set up by the following three files in a config directory. Respectively, the Sqlite database file of db directory is used as the data source.

  • SqliteAPdo.php : using db/sqlite_a.db as DSN
  • SqliteBPdo.php : using db/sqlite_b.db as DSN
  • pdo.dicon : using db/sqlite_c.db as DSN
Each database is built by db/example.sql. Moreover, please refer to Database access using PDO for a setup of PDO.


5.7.2. Summary of PdoInterceptor

The aspect target of PdoInterceptor is a class and an interface. If an aspect is carried out to a class, the method which suited Pointcut is performed. PdoInterceptor will treat the return value of a method as an SQL query, if the return value of a method is string. If the return value of a method is Hash Array, the value is treated as a context at the time of SQL execution. If the return value of a method is Array, the 1st value is treated as an SQL query, the 2nd value is treated as a context at the time of SQL execution. If the return value of a method is null, an SQL file is searched, and an SQL query is acquired when found. A method is not called when the aspect of PdoIterceptor is carried out to the interface. Like the case where the return value of a method is null, an SQL file is searched, and an SQL query is acquired when found.

When an SQL query is acquirable, sql is executed in a database and a result is acquired, and the result is returned as a return value of a method. Prepared Statement of PDO is used for execution of the SQL query to a data base. As for value bound to Prepared Statement, a method argument is used. If the return value of a method is Array, the 2nd value is treated as bind value.

PdoInterceptor is connected to a database using the PDO component which exists in S2Container into which self is registered. PdoInterceptor class and StandardPdo class are registered into the namespace "pdo" by the default.

PdoInterceptor does not have OR map function and automatic SQL construction function.


5.7.3. Aspect to Class

Let's craete Dao class which accesses EMP table. The findAll method which gets all the data from EMP table is implemented in Dao class. As a return value of the method, the SQL query executed to a database is returned.
With the s2component function, Dao class is registered as a component. And pdo.interceptor is aspected to Dao class with the s2aspect function.

  • example/example010.php
<?php
require_once('S2Container/S2Container.php');
define('ROOT_DIR', dirname(dirname(__FILE__)));

use \seasar\container\S2ApplicationContext as s2app;
s2app::import(ROOT_DIR . '/classes');

StandardPdo::$DSN = 'sqlite:' . ROOT_DIR . '/example/db/sqlite_a.db';

class Dao {
    public function findAll() {
        return 'select * from emp';
    }
}

s2component('Dao');
s2aspect('pdo.interceptor', '/Dao$/');

$dao = s2app::get('Dao');
$rows = $dao->findAll();

5.7.4. Setup Bind value By Method result

Let's create Dao class which accesses EMP table. The findById method which searches data with ID from EMP table is implemented in Dao class. An array is returned as a return value of a method, the 1st value is treated as SQL query, the 2nd value is treated as a context at the time of SQL execution.

  • example/example010.php
<?php
require_once('S2Container/S2Container.php');
define('ROOT_DIR', dirname(dirname(__FILE__)));

use \seasar\container\S2ApplicationContext as s2app;
s2app::import(ROOT_DIR . '/classes');

StandardPdo::$DSN = 'sqlite:' . ROOT_DIR . '/example/db/sqlite_a.db';

class Dao {
    public function findById() {
        $sql = 'select * from emp where EMPNO = :id';
        $context = array('id' => 7369);
        return array($sql, $context);
    }
}

s2component('Dao');
s2aspect('pdo.interceptor', '/Dao$/');

$dao = s2app::get('Dao');
$rows = $dao->findById();

5.7.5. Aspect to Interface

Let's create IDao interface which accesses EMP table. The findAll method which gets all the data from EMP table is defined in IDao interface.
With the s2component function, Dao class is registered as a component. And pdo.interceptor is aspected to Dao class with the s2aspect function.

  • example/example020.php
<?php
require_once('S2Container/S2Container.php');
define('ROOT_DIR', dirname(dirname(__FILE__)));

use \seasar\container\S2ApplicationContext as s2app;
s2app::import(ROOT_DIR . '/classes');

StandardPdo::$DSN = 'sqlite:' . ROOT_DIR . '/example/db/sqlite_a.db';

interface IDao {
    public function findAll();
}

s2component('IDao');
s2aspect('pdo.interceptor', '/Dao$/');

$dao = s2app::get('IDao');
$rows = $dao->findAll();

The SQL query executed when findAll method is called is described to SQL file. SQL file is located to the same directory as Interface file. SQL file name is "Interaface name"_"method name".sql. In the case of the findAll method of IDao interface, it is set to "IDao_findAll.sql".

  • example/IDao_findAll.sql
select * from emp

5.7.6. Setup Bind value by Method argument

Let's create IDao interface which accesses EMP table. The findById method which searches data with ID from EMP table is defined in IDao class. ID which searches data is specified by the argument of findById method.

  • example/example020.php
<?php
require_once('S2Container/S2Container.php');
define('ROOT_DIR', dirname(dirname(__FILE__)));

use \seasar\container\S2ApplicationContext as s2app;
s2app::import(ROOT_DIR . '/classes');

StandardPdo::$DSN = 'sqlite:' . ROOT_DIR . '/example/db/sqlite_a.db';

interface IDao {
    public function findById($id);
}

s2component('IDao');
s2aspect('pdo.interceptor', '/Dao$/');

$dao = s2app::get('IDao');
$rows = $dao->findAll();

The SQL query executed when findAll method is called is described to SQL file. SQL file is located to the same directory as Interface file.

  • example/IDao_findById.sql
select * from emp where EMPNO = :id

5.7.7. Using @DTO Annotation

PdoInterceptor specifies StandardDto class as PDO::FETCH_CLASS as a default value. The StandardDto class implemets the __call method and supports the accessor method to a column. When a column name is "ABC_XYZ", a column value can be accessed by the setAbcXyz method and the getAbcXyz method.

When specifying PDO::FETCH_CLASS, you can specify it by @DTO annotation. A form is as follows. 

/**
 * @DTO(DTO class name)
 */

Let's create IDao interface which accesses EMP table. The findById method which searches data with ID from EMP table is defined in IDao class. The BarDto class is specified as PDO::FETCH_CLASS class.

  • example/example030.php
<?php
require_once('S2Container/S2Container.php');
define('ROOT_DIR', dirname(dirname(__FILE__)));

use \seasar\container\S2ApplicationContext as s2app;
s2app::import(ROOT_DIR . '/classes');

StandardPdo::$DSN = 'sqlite:' . ROOT_DIR . '/example/db/sqlite_a.db';

class BarDto extends StandardDto{}
interface IDao {
    /**
     * @DTO('BarDto')
     */
    public function findById($id);
}

s2component('IDao');
s2aspect('pdo.interceptor', '/Dao$/');

$dao = s2app::get('IDao');

$rows = $dao->findById(7369);

5.7.8. Using PDO in Dao

If using PDO directly in Dao, injection of PDO component is carried out to Dao component. In the following example, in order to carry out injection of the SqliteBPdo component to CdDao class, "sqliteBPdo" public property is prepared. Thereby, injection of PDO component is carried out to CdDao component by the automatic injection function of S2Container. In the sampleTransaction method, the transaction is started using injected PDO component.

  • example/CdDao.php
<?php
class CdDao {
    private $pdo = null;
    public function setPdo(Pdo $pdo) {
        $this->pdo = $pdo;
    }
    public function sampleTransaction() {
        try {
            \seasar\log\S2Logger::getInstance(__NAMESPACE__)->info('start transaction.', __METHOD__);
            $this->pdo->beginTransaction();
            $this->insert(10, 'aaa', 'bbb');
            $this->updateTitle(10, 'AAA');
            $this->delete(10);
            \seasar\log\S2Logger::getInstance(__NAMESPACE__)->info('commit transaction.', __METHOD__);
            $this->pdo->commit();
        } catch (Exception $e) {
            \seasar\log\S2Logger::getInstance(__NAMESPACE__)->info($e->getMessage(), __METHOD__);
            \seasar\log\S2Logger::getInstance(__NAMESPACE__)->info('rollback transaction.', __METHOD__);
            $this->pdo->rollBack();
        }
    }
    public function insert($id, $title, $content) {
        return "insert into CD values(:id, :title, :content)";
    }
    public function updateTitle($id, $title) {
        return "update CD set title = :title where id = :id";
    }
    public function delete($id) {
        return "delete from CD where id = :id";
    }
}

An executable file becomes the next.

  • example/example040.php
<?php
require_once('S2Container/S2Container.php');
define('ROOT_DIR', dirname(dirname(__FILE__)));

use \seasar\container\S2ApplicationContext as s2app;
s2app::import(ROOT_DIR . '/classes');
s2app::import(ROOT_DIR . '/example/CdDao.php');

StandardPdo::$DSN = 'sqlite:' . ROOT_DIR . '/example/db/sqlite_b.db';
s2aspect('pdo.interceptor', '/Dao$/', '/^(insert|update|delete)/');

$dao = s2app::get('CdDao');
$rows = $dao->sampleTransaction();

5.7.9. Paging by Paginate class

Paginate class is a utility class which performs paging when acquiring data from Database. The EmpDao class which accesses EMP table is created as an example.

  • example/EmpDao.php
<?php
class EmpDao {
    public function byPaginate(\Paginate $paginate) {
        if (!$paginate->hasTotal()) {
            list($row) = $this->findAllTotal($paginate);
            $paginate->setTotal($row->total);
        }
        return $this->findAll($paginate);
    }
    public function findAllTotal(\Paginate $paginate) {
        return 'select count(*) as total from EMP order by EMPNO';
    }

    public function findAll(\Paginate $paginate) {
        $sql = 'select * from EMP order by EMPNO limit :limit offset :offset';
        $context = array('limit' => $paginate->getLimit(), 'offset' => $paginate->getOffset());
        return array($sql, $context);
    }
}

In the byPaginate method, fistly the total number of data is acquired by the findAllTotal method, and it is set to paginate instance. (In order to carry out paging processing with Paginate class, the total number of data is required.) Then, findAll was performed and data is acquired. By the findAll method, the limit/offset information which the paginate instance has is set up, and paging is carried out.

Let's create example050.php which performs the next processing.

  1. Reading of a common setting file
  2. PdoInterceptor is applied to Dao class by automatic aspect function
  3. The classes directory is imported by the import method of S2ApplicationContext
  4. SqlitePdoA.php which sets sqlite_a.db as DSN is imported by the import method of S2ApplicationContext
  5. Acquisition of EmpDao component
  6. Paginate instance is generated and 1 Paige's number of items is set up.
  7. Execution of the ByPaginate method
  8. Changes to next Paige
  9. Execution of the ByPaginate method
  • example/example050.php
<?php
require_once('S2Container/S2Container.php');
define('ROOT_DIR', dirname(dirname(__FILE__)));

use \seasar\container\S2ApplicationContext as s2app;
s2app::import(ROOT_DIR . '/classes');
s2app::import(ROOT_DIR . '/example/EmpDao.php');

StandardPdo::$DSN = 'sqlite:' . ROOT_DIR . '/example/db/sqlite_a.db';
s2aspect('pdo.interceptor', '/Dao$/', '/^find/');

$dao = s2app::get('EmpDao');
$paginate = new Paginate;
$paginate->setLimit(2);
$rows = $dao->byPaginate($paginate);
var_dump($rows);

$paginate->next();
$rows = $dao->byPaginate($paginate);
var_dump($rows);

5.7.9.1. Paginate API Reference

Paginate::getTotalPage method. 

All the number of pages is returned. (total records / page limit) 

    /**
     * @return integer
     */
    final public function getTotalPage();

Paginate::getPage method. 

Current page number is returned. 

    /**
     * @return integer
     */
    final public function getPage();

Paginate::setPage method. 

Page number is set up. 

    /**
     * @param integer $page
     * @throw Exception
     */
    final public function setPage($page);

Paginate::getOffset method. 

Current offset position is returned. 

    /**
     * @return integer
     */
    final public function getOffset();

Paginate::setLimit method. 

The number of cases per 1 Paige is set up. 

    /**
     * @prama integer $limit
     */
    final public function setLimit($limit);

Paginate::getTotal method. 

The total number is returned. 

    /**
     * @return integer
     * @throw Exception 全件数が未設定の場合にスローされます。
     */
    final public function getTotal();

Paginate::setTotal method. 

The total number is set up. 

    /**
     * @param integer $total
     */
    final public function setTotal($total);

Paginate::setWindow method. 

The number of Paige displayed on a window is set up.。 

    /**
     * @param integer $window
     */
    final public function setWindow($window);

Paginate::next method. 

1 page is advanced. 

    final public function next();

Paginate::isNext method. 

It is returned whether there is any next page. 

    /**
     * @return boolean
     */
    final public function isNext();

Paginate::prev method. 

1 page returns. 

    final public function prev();

Paginate::isPrev method. 

It is returned whether there is any previous page. 

    /**
     * @return boolean
     */
    final public function isPrev();

Paginate::pages method. 

The page number settled in window is enumerated. 

    /**
     * @return array
     */
    final public function pages() {
Prev Up Next
5.6. Database access using PDO Home 5.8. StrictInterceptor


© Copyright The Seasar Foundation and the others 2005-2010, all rights reserved.