php 數據庫類

<?php
/*** 對Yii2數據封裝* @author nike@youfumama.com* @date 2017-03-08* 抽象類不能被實例化* eg1: selectuse 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|insertuse 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----------------------
}