php 数据库类

<?php
/**
 * 对Yii2数据封装
 * @author nike@youfumama.com
 * @date 2017-03-08
 * 抽象类不能被实例化
 * 
    eg1: select
    use common\models as b_model;

    $users_model = b_model\FactoryModel::users();
    $users_model->setPk(1);
    $users_model->setFields('id,name,age,sex');
    $result = $users_model->getInfoByPk();
    =================================================
    eg2: update|insert
    use common\models as b_model;
    use common\models\entity as b_entity;

    $users_entity = new b_entity\Users();
    $users_entity->name = '尼克';
    $users_entity->age = 25;
    $users_model = b_model\FactoryModel::users();
    $users_model->setPk(1);
    $result = $users_model->updateByPk($users_entity);
 */
namespace common\models;

use yii;
use yii\db\ActiveRecord;

/**
 * Base model
 * @author nike@youfumama.com
 * @date 2017-03-08
 */
abstract class BaseModel extends ActiveRecord
{
    protected static $_db = null;
    private $sql;
    private $PrimaryKeyField; //主键字段名
    private $pk; //主键
    private $field_values;//存放数据库字段数组
    protected $table;
    protected $field = '*';
    protected $where;
    protected $orderby;
    protected $limit;
    protected $groupby;
    
    /**
     * set db
     * @param type $db
     */
    protected function _setDb($db = null)
    {
        if(null == $db)
            $db = Yii::$app->db;
        self::$_db = $db;
    }
    /**
     * get db
     * @return type
     */
    protected function _getDb()
    {
        if(empty(self::$_db))
            $this->_setDb ();
        return self::$_db;
    }
    /**
     * 设置SQL语句
     */
    private function setSQL($sql)
    {
        $this->sql = $sql;
    }
    /**
     * 获取SQL语句
     */
    function getSQL()
    {
        return $this->sql;
    }
    /**
     * 设置field_values
     */
    private function setFieldValues($field_values)
    {
        if(!is_object($field_values))
            throw new Exception ('entity must be Object', 500);
        $this->field_values = $field_values;
    }
    /**
     * 获取field_values
     */
    private function getFieldValues()
    {
        return $this->cleanArray((array)$this->field_values);
    }
    /**
     * 清除values of SQL injections
     * @param type $array
     * @return array
     */
    private function cleanArray($array)
    {
        $array = array_map('trim', $array);
        $array = array_map('stripslashes', $array);
        $array = array_map('mysql_real_escape_string', $array);
        return $array;
    }
    /**
     * 设置主键字段名
     */
    protected function setPrimaryKeyField($PrimaryKeyField)
    {
        $this->PrimaryKeyField = $PrimaryKeyField;
    }
    /**
     * 获取主键字段名
     */
    protected function getPrimaryKeyField()
    {
        return $this->PrimaryKeyField;
    }
    /**
     * 设置主键值
     * @param int
     */
    public function setPk($pk)
    {
        $this->pk = $pk;
    }
    /**
     * 获取主键值
     * @return int
     */
    public function getPk()
    {
        return $this->pk;
    }
    /**
     * 设置表名
     */
    protected function setTable($table)
    {
        $this->table = $table;
    }
    /**
     * 获取表名
     */
    protected function getTable()
    {
        return $this->table;
    }
    /**
     * 设置Fields
     */
    function setFields($fields)
    {
        $this->field = $fields;
    }
    /**
     * 获取Fields
     */
    function getFields()
    {
        return $this->field;
    }
    /**
     * 设置where条件
     */
    function setWhere($where)
    {
        $this->where = $where;
    }
    /**
     * 获取where条件
     */
    function getWhere()
    {
        return $this->where;
    }
    /**
     * 设置Group
     */
    function setGroupBy($groupby)
    {
        $this->groupby = $groupby;
    }
    /**
     * 获取Group
     */
    function getGroupBy()
    {
        return $this->groupby;
    }
    /**
     * 设置Order
     */
    function setOrderBy($orderby)
    {
        $this->orderby = $orderby;
    }
    /**
     * 设置Order
     */
    function getOrderBy()
    {
        return $this->orderby;
    }
    /**
     * 设置条数
     */
    function setLimit( $limit )
    {
        $this->limit = $limit;
    }
    /**
     * 获取条数
     */
    function getLimit()
    {
        return $this->limit;
    }
    /**
     * 根据主键获取
     */
    function getInfoByPk()
    {
        $sql = "select {$this->getFields()} "
            ."from {$this->getTable()} "
            ."where {$this->getPrimaryKeyField()}={$this->getPk()}";
        return $this->query_one($sql);
    }
    /**
     * 根据where条件获取一条信息
     */
    function getOneByWhere()
    {
        $sql = "SELECT {$this->getFields()} "
                . "FROM {$this->getTable()} "
                . "WHERE {$this->getWhere()}";
        return $this->query_one($sql);
    }
    /**
     * 根据where条件获取数组列表
     */
    function getListByWhere()
    {
        $sql = "SELECT ";
        $sql .= "{$this->getFields()} "
                . "FROM {$this->getTable()} ";
        if ( $this->getWhere() != null ) {
            $sql .= "WHERE {$this->getWhere()} ";
        }
        if ( $this->getGroupby() != null ) {
            $sql .= "GROUP BY {$this->getGroupby()} ";
        }
        if ( $this->getOrderby() != null ) {
            $sql .= "ORDER BY {$this->getOrderby()} ";
        }
        if ( $this->getLimit() != null ) {
            $sql .= "LIMIT {$this->getLimit()}";
        }
        return $this->query_all($sql);
    }
    /**
     * 根据where获取count
     */
    function getCountByWhere()
    {
        $sql_count = "SELECT COUNT(*) AS total FROM {$this->getTable()} ";
        if ( $this->getWhere() != null ) {
            $sql_count .= "WHERE " . $this->getWhere();
        }
        return $this->query_scalar($sql_count);
    }
    /**
     * 根据主键更新
     */
    function updateByPk($entity)
    {
        $this->setFieldValues($entity);
        $sql = "UPDATE {$this->getTable()} SET ";
        foreach ($this->getFieldValues() as $key => $one){
            if ($one != NULL){
                $sql .= "$key='$one',";
            }
        }
        $sql = rtrim($sql, ',');
        $sql .= " WHERE {$this->getPrimaryKeyField()}='{$this->getPk()}'";
        return $this->execute($sql);
    }
    /**
     * 根据WHERE更新
     */
    function updateByWhere($entity)
    {
        $this->setFieldValues($entity);
        $sql = "UPDATE {$this->getTable()} SET ";
        foreach ($this->getFieldValues() as $key => $one){
            if ($one != NULL){
                $sql .= "$key='$one',";
            }
        }
        $sql = rtrim($sql, ',');
        $sql .= " {$this->getWhere()}";
        return $this->execute($sql);
    }
    /**
     * 根据WHERE更新
     */
    function insert_table($entity)
    {
        $this->setFieldValues($entity);
        $sql_values = '';
        $sql = "INSERT INTO {$this->getTable()} (";
        foreach ($this->getFieldValues() as $key => $one){
            if ($one != NULL){
                $sql .= "$key,";
                $sql_values .= "'$one',";
            }
        }
        $sql = rtrim($sql, ',').") VALUES (".rtrim($sql_values, ',').")";
        return $this->execute($sql);
    }
    
    //-------------------------Yii2 base----------------------
    //-------------------------Yii2 base----------------------
    //-------------------------Yii2 base----------------------
    //* @author nike@youfumama.com
    //* @date 2017-03-08
    //* 可扩展
    /**
     * 只获取一行，如果该查询没有结果则返回 false
     * @param type $sql
     * @return mix
     */
    private function query_one($sql)
    {
        return $this->_getDb()->createCommand($sql)->queryOne();
    }
    /**
     * 返回所有数组，如果该查询没有结果则返回空数组
     * @param type $sql
     * @return type
     */
    private function query_all($sql)
    {
        return $this->_getDb()->createCommand($sql)->queryAll();
    }
    /**
     * 返回一个标量值，如果该查询没有结果则返回 false
     * @param type $sql
     * @return type
     */
    private function query_scalar($sql)
    {
        return $this->_getDb()->createCommand($sql)->queryScalar();
    }
    /**
     * execute操作
     * @param type $sql
     * @return type
     */
    private function execute($sql)
    {
        return $this->_getDb()->createCommand($sql)->execute();
    }
    /**
     * sql过滤，后期安全扩展
     * @param type $str
     * @return type
     */
    private function cleanString($value = '')
    {
        if (is_string($value)) {
            $value = trim($value);
            $value = stripslashes($value);
            $value = mysqli_real_escape_string($value);
        }
        return $value;
    }
    //-------------------------Yii2 END----------------------
    //-------------------------Yii2 END----------------------
    //-------------------------Yii2 END----------------------
}