php mysql自封装简易ORM类

  1 <?php
  2 
  3 class OrmModel{
  4 const JOIN_TYPE_INNER = 'INNER';
  5 const JOIN_TYPE_LEFT = 'LEFT';
  6 const JOIN_TYPE_RIGHT = 'RIGHT';
  7 const INSERT_IGNORE = 'IGNORE';
  8 const SELECT = 'SELECT %1$s FROM %2$s';
  9 const JOIN = '%1$s JOIN %2$s';
 10 const WHERE = 'WHERE %1$s';
 11 const JOIN_ON = 'ON %1$s';
 12 const INSERT = 'INSERT %1$s INTO %2$s(%3$s) VALUES%4$s';
 13 const UPDATE = 'UPDATE %1$s SET %2$s';
 14 const DELETE = 'DELETE FROM %1$s';
 15 const INSERT_UPDATE = 'ON DUPLICATE KEY UPDATE %1$s';
 16 private $sqlFormar = array(
 17 'group'=> 'GROUP BY %1$s',
 18 'order'=> 'ORDER BY %1$s',
 19 'limit'=> 'LIMIT %1$s',
 20 'offset'=> 'OFFSET %1$s',
 21 );
 22 public $sqlLog;
 23 public static $db;
 24 public $where;
 25 public $dbConfig;
 26 public $offset;
 27 public $limit;
 28 public $order;
 29 public $columns = array('*');
 30 public $table;
 31 public $group;
 32 public $joins;
 33 public $ignore = '';
 34 public $reset = TRUE;
 35 public $debug = FALSE;
 36 public $isColumnVal = FALSE;
 37 public $stop;
 38 public $tmpTable;
 39 private $orderField;
 40 public $preAttribute = array();
 41 public $search = array('select','join','where','group','order','limit','offset');
 42 /*****************************
 43 * orm参数配置区块 *
 44 *****************************/
 45 
 46 /**
 47 * 配置数据库配置
 48 * @param array $dbConfig pdo数据库配置
 49 * @return $this
 50 */
 51 public function setDbConfig($dbConfig) {
 52 $this->dbConfig = $dbConfig;
 53 return $this;
 54 }
 55 public function noReset(){
 56 $this->reset = false;
 57 return $this;
 58 }
 59 public function usePreSqlAttr(){
 60 foreach ($this->preAttribute as $k=>$v){
 61 $this->$k = $v;
 62 }
 63 return $this;
 64 }
 65 //该方法需要得到数据库赋值
 66 public function db(){
 67 if(!self::$db){
 68 try {
 69 self::$db = new PDO($this->dbConfig['dsn'],$this->dbConfig['username'],$this->dbConfig['password'],$this->dbConfig['driver']);
 70 } catch (PDOException $e) {
 71 echo 'Connection failed: ' . $e->getMessage();
 72 }
 73 }
 74 return self::$db;
 75 }
 76 /**
 77 * 配置表名
 78 */
 79 public function from($table){
 80 if(!empty($table)){
 81 $this->tmpTable = $table;
 82 }
 83 return $this;
 84 }
 85 /**
 86 * 配置where语句
 87 * @param type $where where数组
 88 * @param type $additional 重置where语句
 89 * @return $this
 90 */
 91 public function where($where,$additional = true){
 92 if($additional && !empty($this->where)){
 93 $this->where = array_merge($this->where,$where);
 94 }else{
 95 $this->where = $where;
 96 } 
 97 return $this;
 98 }
 99 /**
100 * 配置组
101 * @param type $group
102 * @return $this
103 */
104 public function group($group){
105 $this->group = $group;
106 return $this;
107 }
108 /**
109 * 配置链表
110 * @param type $table 表名
111 * @param type $on 链表条件
112 * @param type $columns 获取字段
113 * @param type $type 链表方式
114 * @return $this
115 */
116 public function join($table,$on,$columns=array(),$type='INNER'){
117 $this->joins[] = array(
118 'table'=>$table,
119 'on'=>$on,
120 'columns'=>$columns,
121 'type'=>$type,
122 );
123 return $this;
124 }
125 /**
126 * 配置获取当前表的列
127 * @param array $columns 列名数组
128 * @return $this
129 */
130 public function columns(array $columns){
131 $this->columns = $columns;
132 return $this;
133 }
134 /**
135 * 配置数据起点
136 * @param int $num 多少条数据开始
137 * @return $this
138 */
139 public function offset($num){
140 $this->offset = intval($num);
141 return $this;
142 }
143 /**
144 * 配置数据获取条数
145 * @param int $num 获取数据条数
146 * @return $this
147 */
148 public function limit($num){
149 $this->limit = intval($num);
150 return $this;
151 }
152 /**
153 * 配置排序规则
154 * @param string $order 排序规则
155 * @return $this
156 */
157 public function order($order){
158 $this->order = $order;
159 return $this;
160 }
161 
162 /*****************************
163 * orm参数配置区块结束 *
164 *****************************/
165 
166 public function produceSql(){
167 foreach ($this->search as $v){
168 $process = 'process'.ucfirst($v);
169 $sqlArr[] = method_exists($this, $process) ? call_user_func(array($this,$process)) :$this->replaceParam($v);
170 }
171 $sql = implode(' ', $sqlArr);
172 $this->outputSql($sql);
173 $this->initCondition();
174 return $sql;
175 }
176 public function isColumnVal(){
177 $this->isColumnVal = TRUE;
178 return $this;
179 }
180 //默认处理
181 protected function replaceParam($k){
182 return isset($this->{$k}) && !empty($this->{$k}) ? sprintf($this->sqlFormar[$k], $this->{$k}) : '';
183 }
184 public function initCondition(){
185 //是否重置配置
186 if(!$this->reset){
187 $this->reset = true;
188 return ;
189 }
190 $this->preSqlAttribute('columns',array('*'));
191 $this->preSqlAttribute('where');
192 $this->preSqlAttribute('group');
193 $this->preSqlAttribute('order');
194 $this->preSqlAttribute('tmpTable');
195 $this->preSqlAttribute('joins',array());
196 $this->preSqlAttribute('isColumnVal',FALSE);
197 $this->preSqlAttribute('ignore');
198 $this->preSqlAttribute('onUpdate');
199 $this->preSqlAttribute('offset');
200 $this->preSqlAttribute('limit');
201 }
202 public function preSqlAttribute($attr,$value=''){
203 $this->preAttribute[$attr] = $this->$attr;
204 $this->$attr = $value;
205 return $this;
206 }
207 public function processColumns($table,$columns){
208 $columnsList = array();
209 $table = '`'.(is_array($table) ? key($table) : $table).'`.';
210 foreach ($columns as $k=>$v){
211 if(empty($v)){
212 continue;
213 }
214 $vv = $v instanceof OrmExpression ? $v->expression : $table.$v;
215 $columnsList[] = !is_numeric($k) ? $vv.' `'.$k.'`' : $vv;
216 }
217 return implode(',',$columnsList);
218 }
219 public function getTable(){
220 $table = $this->tmpTable ? $this->tmpTable : $this->table;
221 if(empty($table)){
222 throw new Exception('no have table');
223 }
224 return $table;
225 }
226 public function getTableName(){
227 $table = $this->getTable();
228 return is_array($table) ? current($table):$table;
229 }
230 public function processTable($table){
231 if(is_array($table)){
232 $alias = key($table);
233 $realTable = current($table);
234 $table = $realTable.' AS '.$alias;
235 }
236 return $table;
237 }
238 public function processSelect(){
239 $columnList = array();
240 if(!empty($this->joins) && !$this->isColumnVal){
241 foreach ($this->joins as $v){
242 if(!empty($v['columns'])){
243 $columnList[] = $this->processColumns($v['table'], $v['columns']);
244 }
245 }
246 }
247 if(!empty($this->columns)){
248 $columnList[] = $this->processColumns($this->getTable(), $this->columns);
249 }
250 
251 if(empty($columnList)){
252 throw new Exception('sql no have columns');
253 }
254 
255 $column = implode(',', $columnList);
256 $sql = sprintf(self::SELECT,$column,$this->processTable($this->getTable()));
257 return $sql;
258 }
259 public function processWhere(){
260 if(empty($this->where)){
261 return '';
262 }
263 if(is_array($this->where)){
264 $tmp = array();
265 foreach ($this->where as $k=>$v){
266 if(is_numeric($k)){
267 $tmp[] = $v;
268 }elseif(is_array($v)){
269 if(empty($v)){
270 continue;
271 }
272 $ids = implode(',', $v);
273 $tmp[] = $k.' in ('.$ids.')';
274 if($this->orderField == $k){
275 $this->order('FIELD('.$k.','.$ids.')');
276 }
277 }else{
278 $tmp[] = $k.'="'.$v.'"';
279 }
280 }
281 $tmpstr = implode(' AND ', $tmp);
282 }else{
283 $tmpstr = $this->where;
284 }
285 $sql = sprintf(self::WHERE,$tmpstr);
286 
287 return $sql;
288 }
289 public function orderField($field){
290 $this->orderField = $field;
291 return $this;
292 }
293 public function processJoin(){
294 $sql = array();
295 if(!empty($this->joins)){
296 foreach ($this->joins as $v){
297 $sql[] = sprintf(self::JOIN,$v['type'],$this->processTable($v['table']));
298 if(!empty($v['on'])){
299 $sql[] = sprintf(self::JOIN_ON,$v['on']);
300 }
301 }
302 }
303 return implode(' ',$sql);
304 }
305 public function selectAll($where=array(),$table=false){
306 return $this->from($table)->where($where)->getAll();
307 }
308 public function selectOne($where,$table=''){
309 return $this->from($table)->where($where)->getRow();
310 }
311 public function selectCount($where,$table=''){
312 return $this->from($table)->where($where)->count();
313 }
314 public function selectColumnVal($column,$where,$table=''){
315 return $this->from($table)->where($where)->getColumnVal($column);
316 }
317 public function selectColumnList($column,$where,$table=''){
318 return $this->from($table)->where($where)->getColumnList($column);
319 }
320 public function getAll(){
321 $sql = $this->produceSql();
322 $data = $this->getQuery($sql)->fetchAll(PDO::FETCH_ASSOC);
323 return $data;
324 }
325 public function getQuery($sql){
326 $query = $this->db()->query($sql);
327 if(!is_object($query)){
328 throw new Exception($sql);
329 }
330 return $query;
331 }
332 public function query($sql){
333 return $this->db()->query($sql);
334 }
335 public function lastInsertId(){
336 return $this->db()->lastInsertId();
337 }
338 public function commit(){
339 return $this->db()->commit();
340 }
341 public function beginTransaction(){
342 return $this->db()->beginTransaction();
343 }
344 public function rollBack(){
345 return $this->db()->rollBack();
346 }
347 
348 public function getRow(){
349 $sql = $this->produceSql();
350 $data = $this->getQuery($sql)->fetch(PDO::FETCH_ASSOC);
351 return empty($data) ? array() : $data;
352 }
353 public function count(){
354 //初始化条数限制
355 $this->offset(0)->limit(0);
356 if(!empty($this->group)){
357 $this->from(array('a'=>'('.$this->produceSql().')'));
358 }
359 return $this->getColumnVal('count(*)');
360 }
361 public function getColumnVal($column){
362 $row = $this->isColumnVal()->columns(array('columnVal'=>new OrmExpression($column)))->getRow();
363 return $row['columnVal'];
364 }
365 public function getColumnList($column){
366 $list = $this->isColumnVal()->columns(array('columnVal'=>new OrmExpression($column)))->getAll();
367 return empty($list) ? array() : array_column($list, 'columnVal');
368 }
369 public function onUpdate(array $update){
370 $this->onUpdate = $update;
371 return $this;
372 }
373 //处理插入更新
374 public function processOnUpdate(){
375 if(empty($this->onUpdate)){
376 return '';
377 }
378 foreach ($this->onUpdate as $k=>$v){
379 $sqlArr[] = is_numeric($k) ? "{$v}=VALUES({$v})" : "{$k}={$v}";
380 }
381 $sql = implode(',',$sqlArr);
382 return sprintf(self::INSERT_UPDATE,$sql);
383 }
384 public function ignore($ignore){
385 $this->ignore = $ignore;
386 return $this;
387 }
388 public function insert($info,$table=''){
389 $key = key($info);
390 //处理批量插入数据
391 if(is_numeric($key)){
392 $oneInfo = current($info);
393 $columns = '`'.implode('`,`',array_keys($oneInfo)).'`';
394 $values = '';
395 foreach ($info as $v){
396 $values .= "('". implode("','", array_map('addslashes',$v))."'),";
397 }
398 $values = rtrim($values,',');
399 }else{
400 $columns = '`'.implode('`,`',array_keys($info)).'`';
401 $values = "('". implode("','", array_map('addslashes',$info))."')";
402 }
403 $sql = sprintf(self::INSERT, $this->ignore, $this->from($table)->getTableName(),$columns,$values).' '.$this->processOnUpdate();
404 $this->outputSql($sql);
405 $this->initCondition();
406 return $this->db()->exec($sql);
407 }
408 
409 public function update($info,$where=array(),$table=''){
410 foreach ($info as $k=>$v){
411 $values[] = $v instanceof OrmExpression ? "`{$k}`={$v->expression}" : "`{$k}`='{$v}'";
412 }
413 $sql = sprintf(self::UPDATE,$this->from($table)->getTableName(), implode(',',$values)).' '.$this->where($where)->processWhere();
414 $this->outputSql($sql);
415 $this->initCondition();
416 return $this->db()->exec($sql);
417 }
418 public function delete($where=array(),$table=''){
419 $sql = sprintf(self::DELETE, $this->from($table)->getTableName()).' '.$this->where($where)->processWhere();
420 $this->outputSql($sql);
421 $this->initCondition();
422 return $this->db()->exec($sql);
423 }
424 public function debug($stop=true){
425 $this->debug = TRUE;
426 $this->stop = $stop;
427 return $this;
428 }
429 public function outputSql($sql){
430 if($this->stop){
431 echo $sql;exit;
432 }
433 if($this->debug){
434 $this->sqlLog[] = $sql;
435 }
436 }
437 public function log(){
438 return $this->sqlLog;
439 }
440 }
441 
442 class OrmExpression{
443 public $expression;
444 public function __construct($expression) {
445 $this->expression = $expression;
446 }
447 }
448 
449