SQLHelper,一个单态的,没事务冲突的类,基于SQLFilter的数据库常用操作功能

posted by David Chen at April 1, 2008, 1:28 AM    

下面这个类要用到sql filter那个类,请参考 SQLFilter 这篇文章, sql filter是用于拼写sql的,但不是
简单的拼写,是以对象方式,自动生成pdo statement的占位符和给 statement填充参数值.
以下的类是使用了单态模式, 只要系统的db操作都是用下面这个类的话,就不怕事务的不统一了, 需然这样
做会使事务与逻辑放在同一个地方,但是可以实现java中的openSessionInViewer同样的功能.
openSessionInViewer是在视图层就开启db链接,与mvc有冲突, 下面这个SQLHelper就解决了这个问题.
这个SQLHelper是基于小东的SQLHelper类的改写, 在它那个类的基础上改写后的功能.

    require_once( dirname(__FILE__) . '/SQLFilter.php' );

    /**
    $helper = SQLHelper::getInstance();
    $helper = SQLHelper::getInstance();
    $helper = SQLHelper::getInstance();
    如果这3句在一个http线程中顺序运行, helper指向的都是同一instance,
    instance在一个http线程线路束后自动移离内存,变为null,所以可以实现
    在一个http线程中实现单态模式
    因为在一个http线程中,使用单态, __destruct只会执行一次, 所以可以在
    __construct中开始事务,在__destruct中处理事务的提交和回滚
    **/

    class SQLHelper {

        private static $instance = NULL;

        const DB_SERVER = 'localhost';
        const DB_DATABASE = 'myfilm';
        const DB_USERNAME = 'root';
        const DB_PASSWORD = '';

        private $conn;

        public static function getInstance() {
            //var_export( self::$instance );
            if( is_null( self::$instance ) )
                self::$instance = new SQLHelper();
            return self::$instance;
        }

        public function SQLHelper() {
            try {
                $this->conn = new PDO(
                    "mysql:host=" . self::DB_SERVER . ";dbname=" . 
                    self::DB_DATABASE, self::DB_USERNAME, self::DB_PASSWORD, 
                    array( PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8';" )
                    );
            }
            catch(PDOException $ex){
                die( "Error: " .$ex->getMessage() . "<br>" );
            }
            $this->beginTransaction();
        }

        public function __destruct() {
            $this->doTransaction();
            $this->conn = NULL;
        }

        public function beginTransaction() {
            return $this->conn->beginTransaction();
        }

        public function doTransaction() {
            try {
                $this->conn->commit();
            }
            catch (PDOException $ex) {
                $this->conn->rollBack();
            }
        }

        public function query( $sql, $filter_list = NULL, $limit_m = NULL, 
                               $limit_n = NULL, $order = '' ) {
            if( !is_null( $filter_list ) )
                $sql = SQLFilter::get_holders( $filter_list, $sql );
            if( !empty( $order ) )
                $sql .= ' order by ' . $order;
            if( is_int( $limit_m ) && is_int( $limit_n ) )
                $sql .= ' LIMIT $limit_m, $limit_n';

            $stmt = SQLFilter::fill_statement(
                $this->conn->prepare( $sql ),
                $filter_list
                );
            $stmt->execute();
            $rs = $stmt->fetch(PDO::FETCH_BOTH);
            return $rs;
        }

        public function update( $sql, $filter_list = NULL ) {
            if( !is_null( $filter_list ) )
                $sql = SQLFilter::get_holders( $filter_list, $sql );
            $stmt = SQLFilter::fill_statement(
                $this->conn->prepare( $sql ),
                $filter_list
                );
            $stmt->execute();
            return $stmt->rowCount();
        }

        public function query_var( $sql, $filter_list = NULL ) {
            $rs = $this->query( $sql, $filter_list );
            return $rs[0];
        }

        public function query_list( $sql, $filter_list = NULL, $number = NULL,
                                    $rowcount = NULL, $order = NULL ) {
            $rs = NULL;
            if( is_int( $number ) && is_int( $rowcount ) )
                $rs = $this->query( $sql, $filter_list, 
                             ($number-1) * $rowcount, $rowcount, $order );
            $rs = $this->query( $sql, $filter_list, NULL, NULL, $order );
            return $rs;
        }

    }

相关文章:
php中使用sqlcondition,把sql的拼写变成对象,基于pdo的占位符使用