php實現簡單的SQLBuilder
最近在使用Swoole C擴充套件來開發自己的專案, 本來想要使用golang 可是由於團隊中的程式設計師大多數都是php 便放棄了。 我選用的是 swoole framework 來開發 後端api服務, 深入應用的時候 讓我最痛苦的是那相當乾淨的官方文件, 最終我讀了一遍原始碼 才順利上手 。
可能由於框架很久沒有維護了 發現其orm中 有幾處小bug, 於是便自己寫了一個 簡單的sql 構造器,還算挺方便的,貼下程式碼 。
/**
* PHPProject
* SqlBuilder.php Created by usher.yue.
* User: usher.yue
* Date: 16/7/7
* Time: 下午12:32
* 心懷教育夢－煙臺網格軟體技術有限公司
*/
class SqlBuilder{
/**
* sql結果
* @var
*/
protected $_sql="";
protected $_select="";
protected $_from="";
protected $_where="";
protected $_update="";
protected $_insert="";
protected $_join="";
protected $_value="";
protected $_limit="";
protected $_set="";
protected $_method;
protected $_order="";
//執行方式
const SQL_INSERT=0;
const SQL_UPDATE=1;
const SQL_SELECT=2;
/**
* clear sql
*/
public function clear(){
$this->_select="";
$this->_from="";
$this->_where="";
$this->_update="";
$this->_insert="";
$this->_method="";
$this->_value="";
$this->_set="";
$this->_limit="";
$this->_order="";
}
/**
* @param array $fields
* @return $this
*/
public function select($fields=array()){
$this->clear();
$this->_method=SqlBuilder::SQL_SELECT ;
if(is_array($fields)){
$selectFields=implode(` , `,$fields);
$this->_select=($selectFields=="")?" * ":$selectFields;
}else if(is_string($fields)){
$this->_select=$fields;
}
return $this;
}
/**
* @param array $tables
* @return $this
*/
public function from($tables=array()){
if(is_array($tables)){
$from=implode(`,`,$tables);
$this->_from=` ` . $from .` `;
}else if(is_string($tables)){
$this->_from=$tables;
}
return $this ;
}
/**配合表示式
* @param array $conditionExpr
* @return $this
*/
public function wheres($conditionExpr=array()){
//拼接expr
if(array_key_exists(`expr`,$conditionExpr)){
$this->_where.=$conditionExpr[`expr`];
unset($conditionExpr[`expr`]);
}
$arrSetExpr=[];
foreach($conditionExpr as $k=>$v){
$arrSetExpr[]="$k=`$v`";
}
$condition=implode(` and `,$arrSetExpr);
$this->_where=($condition==``?$this->_where:$this->_where.` and `.$condition);
return $this ;
}
/**
* @param $conditon
* @return $this
*/
public function where($conditon){
if(is_string($conditon)){
$this->_where.=" $conditon";
}
else if(is_array($conditon)){
$arrCondition=[];
foreach($conditon as $k=>$v){
$arrCondition[]="$k=`$v`";
}
if(!empty($arrCondition)){
$this->_where.=implode(" and ",$arrCondition);
}
}
return $this ;
}
/**
* @param $condition
* @return $this
*/
public function on($condition){
if(is_string($condition)){
$this->_join.=" on $condition ";
}else if(is_array($condition)){
$arrCondition=[];
foreach($condition as $k=>$v){
$arrCondition[]="$k=`$v`";
}
$this->_join.=" on ".implode(" and ",$arrCondition);
}
return $this ;
}
/**
* @param $tablename
* @param string $join_type
* @return $this
*/
public function join($tablename,$join_type=`join`){
$this->_join.=" $join_type $tablename ";
return $this ;
}
/**
* @param $arr
*/
public function in($arr=""){
if(is_array($arr)){
foreach($arr as &$field){
$field=```.$field.```;
}
$ins=` in (` .implode(`,`,$arr).`)`;
$this->_where.=" $ins";
}else if(is_string($arr)){
$this->_where.=" in($arr)";
}
return $this;
}
/**
* 支援關聯陣列和字串
* @param $field
*/
public function and_($field){
if(is_string($field)){
$this->_where.=" and $field";
}
else if(is_array($field)){
$arrCondition=[];
foreach($field as $k=>$v){
$arrCondition[]="$k=`$v`";
}
if(!empty($arrCondition)){
$this->_where.=" and ".implode(" and ",$arrCondition);
}
}
return $this ;
}
/**
* @param $condition
* @return $this
*/
public function onOr($condition){
$this->_join.=" or $condition ";
return $this;
}
/**
* @param array $tables
* @return $this
*/
public function update($tables){
$this->clear();
$this->_method=SqlBuilder::SQL_UPDATE ;
if(is_array($tables)){
$this->_update=implode(",",$tables);
}else if(is_string($tables)){
$this->_update=" $tables ";
}
return $this ;
}
/**
* @param array $conditionExpr
* @return $this
*/
public function set($conditionExpr=array()){
//拼接expr
if(is_array($conditionExpr)){
if(array_key_exists(`expr`,$conditionExpr)){
$this->_set.=$conditionExpr[`expr`];
unset($conditionExpr[`expr`]);
}
$arrSetExpr=[];
foreach($conditionExpr as $k=>$v){
$arrSetExpr[]="$k=`$v`";
}
$condition=implode(` , `,$arrSetExpr);
$this->_set=($this->_set==``?$condition:$this->_set.` , `.$condition);
}else if(is_string($conditionExpr)){
$this->_set.=" $conditionExpr ";
}
return $this;
}
/** get sql
* @return string
*/
public function sql(){
$sqlCmd="";
switch($this->_method){
case SqlBuilder::SQL_SELECT:
{
$sqlCmd="select {$this->_select} from {$this->_from}" ;
if($this->_join){
$sqlCmd.=$this->_join;
}
if($this->_where){
$sqlCmd.=` where `.$this->_where;
}
if($this->_limit){
$sqlCmd.=$this->_limit;
}
break;
}
case SqlBuilder::SQL_UPDATE:
{
$sqlCmd="update {$this->_update } set {$this->_set}" ;
if($this->_where){
$sqlCmd.=` where `.$this->_where;
}
break;
}
case SqlBuilder::SQL_INSERT:
{
$sqlCmd="insert into {$this->_insert } VALUES {$this->_value}" ;
break;
}
}
return $sqlCmd ;
}
/**
* @param array $fields
* @param array $values
*/
public function values($values=[[]]){
$valueList=[];
foreach($values as &$value){
foreach($value as &$v){
$v="`$v`";
}
$valueList[]="(".implode(",",$value).")";
}
$this->_value=implode(",",$valueList);
return $this;
}
/**
* @param array $table
* @param array $fields
* @return $this
*/
public function insertinto($table,$fields){
$this->clear();
$this->_method=SqlBuilder::SQL_INSERT;
if(is_array($table)){
$this->_insert=implode(",",$table) ;
}else if(is_string($table)){
$this->_insert=$table ;
}
if(is_array($fields)){
$insertFields=implode(`,`,$fields);
if($insertFields!=""){
$this->_insert.= " ($insertFields) ";
}
}else if(is_string($fields)){
$this->_insert.= " ($fields) ";
}
return $this;
}
/**
* @param $num
*/
public function limit($num,$offset=0){
if($offset<=0)
$this->_limit=" limit $num";
else
$this->_limit=" limit $num,$offset ";
return $this ;
}
/**
* @param $field
*/
public function orderby($field=``,$order=`desc`){
if(is_array($field)){
$this->_order=" ".implode(`,`,$field)." $order";
}else{
$this->_order=" $field $order";
}
return $this ;
}
}
//$builder=new SqlBuilder() ;
//$builder->select([`a`,`b`])->from([`aa as `,`bb vd`])->join("t1")->on("a=5")->onAnd("c=1")->wheres([`ax`=>1,`expr`=>"cx>=1"])->limit(100);
//var_dump($builder->sql());
//$builder->update([`a`,`b`])->set([`a`=>1,`ss`=>2])->where("a>1")->whereAnd("b<1");
//var_dump($builder->sql());
//$builder->insertinto("table_1",["a","b","c","d"])->values([["aaa","bbb","ccc","ddd"],["aaa","bbb","ccc","ddd"],["aaa","bbb","ccc","ddd"]]);
//var_dump($builder->sql());
//完整測試
//echo $builder->select("*")->from("a,b as 3")->join("b")->on([`ccsxx`=>34566,`dss`=>"3"])->where(["a4"=>12,"2b"=>32,"c23"=>"42"])->and_("b=2")->and_(["a"=>1,"b"=>2,"c"=>"4"])->and_("s")->in([1,2,3,43,2,77])->orderby("cc desc")->limit(1,2)->sql();
//echo $builder->insertinto("a","c,d,s,d,e")->values([[1,2,3,4,5],[2,3,4,5,6],[32,43,43,43]])->sql();
//echo $builder->update("a")->set("a=1,b=4,c=c+1")->where("c>c+1")->and_("c<6")->sql();
經過測試應該沒多大問題
相關文章
- php實現一個簡單的socketPHP
- php實現一個簡單的堆PHP
- php實現簡單路由router功能PHP路由
- PHP實現簡單順序棧PHP
- 延時 (遲) 操作的 PHP 簡單實現PHP
- PHP實現的簡單日曆程式碼PHP
- PHP 實現簡單阻塞分散式鎖PHP分散式
- 技術卡片 - PHP 鏈式呼叫的簡單實現PHP
- php實現抽獎的簡單概率演算法PHP演算法
- PHP實現最簡單的聊天室應用PHP
- PHP 實現簡單的資料採集併入庫PHP
- php 實現一個簡單的圖片邊緣檢測PHP
- 51行程式碼實現簡單的PHP區塊鏈行程PHP區塊鏈
- 實現簡單的BitMap
- ArrayList的簡單實現
- AOP的簡單實現
- 簡單的 HashMap 實現HashMap
- 簡單的Filter實現Filter
- Promise的簡單實現Promise
- 利用PHP SOAP擴充套件實現簡單Web ServicesPHP套件Web
- PHP實現單例模式PHP單例模式
- 簡單的實現vue原理Vue
- 簡單的實現React原理React
- java實現簡單的JDBCJavaJDBC
- kd樹的簡單實現
- Promise 簡單實現Promise
- FastClick簡單實現AST
- Express 簡單實現Express
- AspectJ簡單實現
- getElementsByClassName簡單實現
- php+nginx實現最簡單的遠端呼叫rpc(微服務)PHPNginxRPC微服務
- java實現簡單的單點登入Java
- jQuery實現的簡單投票簡單程式碼例項jQuery
- 互斥鎖mutex的簡單實現Mutex
- React + Antd實現簡單的todolistReact
- Unity實現簡單的物件池Unity物件
- Promise 基本方法的簡單實現Promise
- java實現棧的簡單操作Java