YY 下 sql查询封装类 不知道好不好使

<?php
class Pkg_Db_Actor {
	
	/**
	 * 读 记录集
	 */
	const MODE_READ_GETALL = 0x1001;
	
	/**
	 * 读 第一条记录
	 */
	const MODE_READ_GETROW = 0x1002;
	
	/**
	 * 读 第一条记录的第一个字段
	 */
	const MODE_READ_GETONE = 0x1003;
	
	/**
	 * 读 记录集的指定列
	 */
	const MODE_READ_GETCOL = 0x1004;
	
	/**
	 * 写 (插入) 操作
	 */
	const MODE_WRITE_INSERT = 0x2001;
	
	/**
	 * 写 (更新) 操作
	 */
	const MODE_WRITE_UPDATE = 0x2002;
	
	/**
	 * 写 (删除) 操作
	 */
	const MODE_WRITE_DELETE = 0x2002;
		
	/**
	 * 执行 读 操作
	 * 
	 * @param string $mode 模式 [MODE_READ_GETALL,MODE_READ_GETROW,MODE_READ_GETONE,MODE_READ_GETCOL]
	 * @param mixed $arguments 参数[不同模式参数不同,缺省为sql字符串]
	 * @param string $callback 查询记录集的回调处理函数
	 * @param mixed $return 处理的结果
	 * 
	 * @return void
	 */
	static function read($mode, $arguments, $callback, &$return = null){
		$dbo = Core_App::$defaultDbExecutor->getReader();
		
		$arguments = (array) $arguments;
		
		$sql = array_shift($arguments);// 缺省第一个参数是sql字符串
		
		switch ($mode){
			case self::MODE_READ_GETALL: // 如果sql里面带了limit则不能使用counter
				$limit = array_shift($arguments);
				$counter = array_shift($arguments);
				if ($counter){
					$counter_sql = "SELECT COUNT(*) FROM ( $sql ) as _count_table";					
				}else {
					$counter_sql = null;
				}
				if ($limit) $sql .= Core_DBSqlHelper::getLimitSql($limit);
				$result = $dbo->getAll($sql);
				if ($counter_sql){
					$result = array(
						'counter' => $dbo->getOne($counter_sql),
						'rows' => $result,
					);
				}				
				break;
			case self::MODE_READ_GETROW:
				$result = $dbo->getRow($sql);
				break;
			case self::MODE_READ_GETONE:
				$result = $dbo->getOne($sql);
				break;
			case self::MODE_READ_GETCOL:
				$col = (int) array_shift($arguments);
				$result = $dbo->getCol($sql,$col);
				break;
			default:
				throw new Exception("未定义的 Pkg_Db_Actor READ 模式");
		}
		
		$callback = self::callbackWraper($callback);
		$return = empty($callback) ? $result : call_user_func_array($callback,array($result));
	}
	
	/**
	 * 执行 更新/删除 操作
	 * 
	 * @param string $mode 模式 [MODE_WRITE_UPDATE,MODE_WRITE_DELETE]
	 * @param mixed $arguments 参数[不同模式参数不同,缺省为sql字符串]
	 * @param string $callback 查询结果集的回调处理函数
	 * @param mixed $return 处理的结果
	 * 
	 * @return void
	 */
	static function write($mode, $arguments, $callback, &$return = null){
		$dbo = Core_App::$defaultDbExecutor->getWriter();
		
		$arguments = (array) $arguments;		
		$sql = array_shift($arguments);// 缺省第一个参数是sql字符串
		
		$result = $dbo->execute($sql);
		
		switch ($mode){			
			case self::MODE_WRITE_INSERT: // 插入操作可选 得到主键标识
				$id = array_shift($arguments);				
				$result = $id ? $dbo->lastInsertId() : $dbo->lastQueryAffectedRows();
				break;
			case self::MODE_WRITE_UPDATE:
			case self::MODE_WRITE_DELETE:
				$result = $dbo->lastQueryAffectedRows();
				break;
			default:
				throw new Exception("未定义的 Pkg_Db_Actor WRITE 模式");
		}
		$callback = self::callbackWraper($callback);
		$return = empty($callback) ? $result : call_user_func_array($callback,array($result));
	}
		
	/**
	 * 回调函数包装器
	 * 
	 * 如果函数是可调用的,则返回 回调函数,否则返回NULL
	 * 
	 * @param string $callback 回调函数
	 * 
	 * @return callback
	 */
	static function callbackWraper($callback){		
		if (!empty($callback)){
			if (is_callable($callback)) return $callback;// call_user_func_array($callback, $args);
			$cbInst = Core_Helper_Callback::getInstance();
			if ($cbInst->exists($callback))	return $cbInst->get($callback);
		}		
		return null;
	}
	
}

 

测试代码如下:

<?php
/**
 * 
 * @author xuxh
 */
class App_Controller_Cui_Actor_Run extends Core_Mvc_Web_Controller
{
	
	function II(array $rows){
		return array(
			'counter' => 3,
			'rows' => $rows
		);
	} 
	
	function actionIndex(){
		
//		$sql = "SELECT * FROM worksheet_order Limit 1";		
//		Pkg_Db_Actor::read(Pkg_Db_Actor::MODE_READ_GETALL ,$sql,array($this,'II'),$return);		
//		Core_AppUtils::dump($return,'Pkg_Db_Actor::MODE_READ_GETALL');
//		
//		Pkg_Db_Actor::read(Pkg_Db_Actor::MODE_READ_GETONE ,"SELECT count(*) FROM worksheet_order",NULL,$return);		
//		Core_AppUtils::dump($return,'Pkg_Db_Actor::MODE_READ_GETONE');
		
		Pkg_Db_Actor::read(Pkg_Db_Actor::MODE_READ_GETALL ,array(
			"SELECT id,sale_order_id FROM worksheet_order",array(1,3),true
		),NULL,$return);		
		Core_AppUtils::dump($return,'Pkg_Db_Actor::MODE_READ_GETALL');
		
	}
	
}

 

测试结果如下:

D:\AppServ\www\orderun\prototype\app>php rake.php q=cui.actor.run.index
Pkg_Db_Actor::MODE_READ_GETALL :
Array
(
    [counter] => 2957
    [rows] => Array
        (
            [0] => Array
                (
                    [id] => 5
                    [sale_order_id] => 5
                )

        )

)

 

评论

1 楼 vb2005xu 2012-07-19  

在这个基础上扩展了一个工具类Pkg_Db_Plugin_Pager

<?php
/**
 * Pkg_Db_Plugin_Pager 类提供数据查询分页功能
 *
 * 构造时传入 sql 和当前页码 参数 即可
 *
 * @author vb2005xu
 */
class Pkg_Db_Plugin_Pager
{
    /**
     * 检索的 sql 源
     * 
     * @var string
     */
    private $source;

    /**
     * 计算实际页码时的基数
     *
     * @var int
     */
    private $_basePageIndex = 0;

    /**
     * 每页记录数
     *
     * @var int
     */
    private $pageSize = -1;

    /**
     * 数据表中符合查询条件的记录总数
     *
     * @var int
     */
    private $totalCount = -1;

    /**
     * 数据表中符合查询条件的记录总数
     *
     * @var int
     */
    private $count = -1;

    /**
     * 符合条件的记录页数
     *
     * @var int
     */
    private $pageCount = -1;

    /**
     * 第一页的索引，从 0 开始
     *
     * @var int
     */
    private $firstPage = -1;

    /**
     * 第一页的页码
     *
     * @var int
     */
    private $firstPageNumber = -1;

    /**
     * 最后一页的索引，从 0 开始
     *
     * @var int
     */
    private $lastPage = -1;

    /**
     * 最后一页的页码
     *
     * @var int
     */
    private $lastPageNumber = -1;

    /**
     * 上一页的索引
     *
     * @var int
     */
    private $prevPage = -1;

    /**
     * 上一页的页码
     *
     * @var int
     */
    private $prevPageNumber = -1;

    /**
     * 下一页的索引
     *
     * @var int
     */
    private $nextPage = -1;

    /**
     * 下一页的页码
     *
     * @var int
     */
    private $nextPageNumber = -1;

    /**
     * 当前页的索引
     *
     * @var int
     */
    private $currentPage = -1;

    /**
     * 构造函数中提供的当前页索引，用于 setBasePageIndex() 后重新计算页码
     *
     * @var int
     */
    private $_currentPage = -1;

    /**
     * 当前页的页码
     *
     * @var int
     */
    private $currentPageNumber = -1;

    /**
     * 构造函数
     *
     * $source 参数假定为不带limit的 SQL 语句
     *
     * @param string $source
     * @param int $currentPage
     * @param int $pageSize
     * @param int $basePageIndex
     */
    function __construct($source, $currentPage, $pageSize = 20, $basePageIndex = 0)
    {
    	// 设置分页索引第一页的基数
        $this->_basePageIndex = $basePageIndex;
        $this->_currentPage = $this->currentPage = $currentPage;
        
        $this->pageSize = $pageSize;
        
		$this->source = $source;
        $sql = "SELECT COUNT(*) FROM ( $source ) as _count_table";
        
        Pkg_Db_Actor::read(Pkg_Db_Actor::MODE_READ_GETONE ,$sql,NULL,$totalCount);
        $this->totalCount = $this->count = (int)$totalCount;
        $this->computingPage();
    }

    /**
     * 设置当前页码，以便用 findAll() 获得其他页的数据
     *
     * @param int $page
     */
    function setPage($page)
    {
        $this->_currentPage = $page;
        $this->currentPage = $page;
        $this->computingPage();
    }

    /**
     * 返回当前页对应的记录集
     *
     * @return array
     */
    function findAll()
    {
        $offset = ($this->currentPage - $this->_basePageIndex) * $this->pageSize;
        
        Pkg_Db_Actor::read(Pkg_Db_Actor::MODE_READ_GETALL,array($this->source,array($this->pageSize,$offset)),NULL,$rowset);
        
        return $rowset;
    }

    /**
     * 返回分页信息，方便在模版中使用
     *
     * @param boolean $returnPageNumbers
     *
     * @return array
     */
    function getPagerData($returnPageNumbers = true)
    {
        $data = array(
            'pageSize' => $this->pageSize,
            'totalCount' => $this->totalCount,
            'count' => $this->count,
            'pageCount' => $this->pageCount,
            'firstPage' => $this->firstPage,
            'firstPageNumber' => $this->firstPageNumber,
            'lastPage' => $this->lastPage,
            'lastPageNumber' => $this->lastPageNumber,
            'prevPage' => $this->prevPage,
            'prevPageNumber' => $this->prevPageNumber,
            'nextPage' => $this->nextPage,
            'nextPageNumber' => $this->nextPageNumber,
            'currentPage' => $this->currentPage,
            'currentPageNumber' => $this->currentPageNumber,
        );

        if ($returnPageNumbers) {
            $data['pagesNumber'] = array();
            for ($i = 0; $i < $this->pageCount; $i++) {
                $data['pagesNumber'][$i] = $i + 1;
            }
        }

        return $data;
    }

    /**
     * 产生指定范围内的页面索引和页号
     *
     * @param int $currentPage
     * @param int $navbarLen
     *
     * @return array
     */
    function getNavbarIndexs($currentPage = 0, $navbarLen = 8)
    {
        $mid = intval($navbarLen / 2);
        if ($currentPage < $this->firstPage) {
            $currentPage = $this->firstPage;
        }
        if ($currentPage > $this->lastPage) {
            $currentPage = $this->lastPage;
        }

        $begin = $currentPage - $mid;
        if ($begin < $this->firstPage) { $begin = $this->firstPage; }
        $end = $begin + $navbarLen - 1;
        if ($end >= $this->lastPage) {
            $end = $this->lastPage;
            $begin = $end - $navbarLen + 1;
            if ($begin < $this->firstPage) { $begin = $this->firstPage; }
        }

        $data = array();
        for ($i = $begin; $i <= $end; $i++) {
            $data[] = array('index' => $i, 'number' => ($i + 1 - $this->_basePageIndex));
        }
        return $data;
    }

    /**
     * 生成一个页面选择跳转控件
     *
     * @param string $caption
     * @param string $jsfunc
     */
    function renderPageJumper($caption = '%u', $jsfunc = 'fnOnPageChanged')
    {
        $out = "<select name=\"PageJumper\" onchange=\"{$jsfunc}(this.value);\">\n";
        for ($i = $this->firstPage; $i <= $this->lastPage; $i++) {
            $out .= "<option value=\"{$i}\"";
            if ($i == $this->currentPage) {
                $out .= " selected";
            }
            $out .=">";
            $out .= sprintf($caption, $i + 1 - $this->_basePageIndex);
            $out .= "</option>\n";
        }
        $out .= "</select>\n";
        echo $out;
    }

    /**
     * 计算各项分页参数
     */
    protected function computingPage()
    {
        $this->pageCount = ceil($this->count / $this->pageSize);
        $this->firstPage = $this->_basePageIndex;
        $this->lastPage = $this->pageCount + $this->_basePageIndex - 1;
        if ($this->lastPage < $this->firstPage) { $this->lastPage = $this->firstPage; }

        if ($this->lastPage < $this->_basePageIndex) {
            $this->lastPage = $this->_basePageIndex;
        }

        if ($this->currentPage >= $this->pageCount + $this->_basePageIndex) {
            $this->currentPage = $this->lastPage;
        }

        if ($this->currentPage < $this->_basePageIndex) {
            $this->currentPage = $this->firstPage;
        }

        if ($this->currentPage < $this->lastPage - 1) {
            $this->nextPage = $this->currentPage + 1;
        } else {
            $this->nextPage = $this->lastPage;
        }

        if ($this->currentPage > $this->_basePageIndex) {
            $this->prevPage = $this->currentPage - 1;
        } else {
            $this->prevPage = $this->_basePageIndex;
        }

        $this->firstPageNumber = $this->firstPage + 1 - $this->_basePageIndex;
        $this->lastPageNumber = $this->lastPage + 1 - $this->_basePageIndex;
        $this->nextPageNumber = $this->nextPage + 1 - $this->_basePageIndex;
        $this->prevPageNumber = $this->prevPage + 1 - $this->_basePageIndex;
        $this->currentPageNumber = $this->currentPage + 1 - $this->_basePageIndex;
    }
}