easyswoole 数据库相关操作集合大复盘

初始化ORM连接池

1.在dev.php 配置文件中配置其MYSQL数据库连接配置

<?php
return [
   'MYSQL' =>[
        'host'          => '',
        'port'          => 3300,
        'user'          => '',
        'password'      => '',
        'database'      => '',
        'timeout'       => 5,
        'charset'       => 'utf8mb4',
   ],
];

 2.在其EasyswooleEvent.php 进行注册

<?php
namespace EasySwoole\EasySwoole;
use EasySwoole\EasySwoole\Config;
use EasySwoole\ORM\Db\Connection;
use EasySwoole\ORM\DbManager;

class EasyswooleEvent implements Event {
   public function initialize() {
       //MYSQL ORM连接池
         $mysql_config = Config::getInstance()->getConf('MYSQL');
         $config = new \EasySwoole\ORM\DB\Config($mysql_config);
         try {
             //连接池配置
            $config->setGetObjectTimeout(3.0); //设置获取连接池对象超时时间
            $config->setIntervalCheckTime(30*1000); //设置检测连接存活执行回收和创建的周期
            $config->setMaxIdleTime(15); //连接池对象最大闲置时间(秒)
            $config->setMinObjectNum(5); //设置最小连接池存在连接对象数量
            $config->setMaxObjectNum(20); //设置最大连接池存在连接对象数量
            $config->setAutoPing(5); //设置自动ping客户端链接的间隔
            DbManager::getInstance()->addConnection(new Connection($config));
         } catch (\Throwable $e) {
             var_dump('注册mysql连接池异常：'.$e->getMessage());
         }
   } 

}

自定义SQL执行

有时候你可能需要在查询中使用原生表达式。你可以使用 QueryBuilder 构造一个原生 SQL 表达式

ORM 内部依赖的是 mysqli 组件的QueryBuilder

namespace App\Model;
use EasySwoole\Mysqli\QueryBuilder;

//直接使用
public function getData(){
     $queryBuild = new QueryBuilder();
     // 支持参数绑定 第二个参数非必传
     $queryBuild->raw("select * from test where name = ?", ['siam']);
     // $queryBuild->raw("select * from test where name = 'siam'");

     // 第二个参数 raw  指定true，表示执行原生sql
     // 第三个参数 connectionName 指定使用的连接名，默认 default
     $data = DbManager::getInstance()->query($queryBuild, true, 'default');
     return $data;
}

//采用invoke方式  
public function runSqlGetData($sql,$param){
     return DbManager::getInstance()-invoke(function ($client) use ($sql,$param) {
        $queryBuilder = new QueryBuilder();
        $query_raw_obj = $queryBuilder->raw($sql,$param);
        $res_obj = $client->query($query_raw_obj);
        //获取结果
        $result = $res_obj->getResult();
        return $result;
     });
}

在模型中使用 

 代码如下：

<?php
namespace App\Model;

use EasySwoole\Mysqli\QueryBuilder;
use EasySwoole\ORM\DbManager;
use EasySwoole\ORM\AbstractModel;

class DepartModel extends AbstractModel
{
    protected $tableName = 'region';
    
    //$field
    public function get_depart_region($where,$field="*")
    {
        $region = $this->where($where)
            ->field('*')
            ->withTotalCount()
            ->all();
        $total = $this->lastQueryResult()->getTotalCount(); //获取总条数
        $sql = $this->lastQuery()->getlastQuery(); //获取执行的sql
        return $this->lastQueryResult()->toArray();
     }
}

 使用  $this->lastQueryResult()->toArray(); 获取结果

使用 withTotalCount 与 $this->lastQueryResult()->getTotalCount() 获取其数据总条数

获取执行的SQL 

使用 $this->lastQuery()->getlastQuery(); //获取执行的sql

发现 使用

$this->lastQuery()->getlastQuery()  

$this->lastQuery()->getLastQuery()

$this->lastQuery()->getLastquery()  

效果一样
效果一样

效果一样

获取结果 

1. 使用  $this->lastQueryResult()->toArray(); 获取结果

2. 使用 $this->lastQueryResult()->getResult();  //获取结果  二维数组  多个结果

3.使用 $this->lastQueryResult()->getResultOne();  //获取结果  一维数组  单个结果

模型使用invoke 

<?php
namespace App\Model;
use EasySwoole\ORM\AbstractModel;
use EasySwoole\ORM\DbManager;


class ReginModel extends AbstractModel {

    protected $tableName = 'region';
    
    
    //采用invoke方式  
    public function runSqlGetData($where,$field,$order,$pn,$page_size,$where2=""){
    
         return DbManager::getInstance()-invoke(function ($client) use 
           ($where,$field,$order,$pn,$page_size,$where2) {
                $model = self::invoke($client);
                $model = alias('a');
                $model->where($where);
                if($where2!='') {
                     $model->where($where2);
                }
                $model->join("user b",'b.id=a.user_id','left');
                      ->limit(($pn-1)*$page_size , $page_size)
                      ->field($field)
                      ->order($order[0],$order[1])
                      ->all();
                //获取结果
                $result = $model->lastQueryResult()->getResult();
                //var_dump($model->lastQuery()->getlastQuery());  //执行的SQL语句
                return $result;
     });


    
}

新增 、修改、删除操作

代码如下：

//新增
public function addInfo($data) {
    return DbManager::getInstance()->invoke(function ($client) use ($data)) {
         $m = self::invoke($client);
         return $m->data($data,false)->save();  //返回insert_id 
    }
}


//批量新增
//@param $data 二维数组
public function addMutiInfo($data,$table_name){
    return DbManager::getInstance()->invoke(function ($client) use ($data,$table_name)) {
         $query_builder = new QueryBuilder();
         $res_obj = $query_builder->insetAll($table_name,$data);
         $res_obj1 = $client->query($res_obj);
         return $res_obj1->getResult();
    }
}

//删除数据
public function delInfo($where) {
    return DbManager::getInstance()->invoke(function ($client) use ($where)) {
         $m = self::invoke($client);
         $result = $m->destroy($where);
         //var_dump($m->lastQuery()->getLastQuery());
         return $result;  
    }
}


//修改数据
public function updateInfo($data,$where) {
     return DbManager::getInstance()->invoke(function ($client) use ($data,$where)) {
         $m = self::invoke($client);
         $m->update($data,$where);
         return $m->lastQueryResult()->getAffectedRows();  
    }
}

使用ORM时，需要每个模型定义一个操作表，这样操作不方便，进而我们再次使用QueryBuilder进行封装  代码如下

<?php
namespace App\Model;

use EasySwoole\Mysqli\QueryBuilder;
use EasySwoole\ORM\DbManager;

class CommonModel extends QueryBulider{

    private static ?self $_instance = null;

    public function execute(){
        return DbManage::getInstance()->query($this,true)->getResult();    
    }

    public function showData($where=[],$table_name,$field="*") {
        return $this->whereArray($where)->get($table_name,null,$field)->execute();
    }

    public function whereArray($where){
        foreach ($where as $key=>$value)  {
            if(!is_array($value)) {
                $this->where($key,$value,'=');
                continue;
            } 
            foreach( $value as $value2) {
                 if(is_array($value2)) {
                     $this->where($key,...$value);
                     goto BREAK_EXECUTE;  
                 }              
            }
            foreach( $value as $item) {
                $this->where($key,...$item);
            }
            goto BREAK_EXECUTE;  
            continue
             
        }
        return $this;
    }

}

 在使用是 直接调用模型 

$model = new CommonModel();

$where['id'] = 1;

$table_name = "test";

$field = "name,age";

$data = $model->showData($where,$table_name,$field);

---

分割线

Mysqli

安装

composer require easyswoole/mysqli

 1.在dev.php 配置文件中配置其MYSQL数据库连接配置

<?php
return [
   'MYSQL' =>[
        'host'          => '',
        'port'          => 3300,
        'user'          => '',
        'password'      => '',
        'database'      => '',
        'timeout'       => 5,
        'charset'       => 'utf8mb4',
   ],
];

 2.在其EasyswooleEvent.php 进行注册

<?php
namespace EasySwoole\EasySwoole;
use EasySwoole\EasySwoole\Config;
class EasySwooleEvent implements Event
{
    public static function mainServerCreate(EventRegister $register)
    {
    
         $mysqlConfig = new \EasySwoole\Mysqli\Config(Config::getInstance()->getConf('MYSQL'));
         try {
            \EasySwoole\MysqliPool\Mysql::getInstance()->register('mysql', $mysqlConfig);
           
        } catch (MysqlPoolException $e) {
            echo "[Warn] --> mysql池注册失败\n";
        }
          
    }

}

使用BaseModel

namespace App\Model;

use EasySwoole\MysqliPool\Mysql;

class BaseModel
{
    protected $db;

    public function __construct(\EasySwoole\Mysqli\Mysqli $dbObject = null)
    {
        if (!$dbObject)
            $dbObject = Mysql::defer("mysql");
  
        $this->db = $dbObject;
    }

    public function getDb(): \EasySwoole\Mysqli\Mysqli
    {
        return $this->db;
    }

使用模型  继承BaseModel 模型

<?php
namespace App\Model;
class UserModel extends BaseModel{
    protected $table = 'user';


    public function getUseableMonitorNum(){
        $num = $this->db->where("status",1)->count($this->table);
        return $num;
    }

    /**
     * 根据条件查询布控数据
     * @param $where str 查询条件 
     */
    public function searchData($where,$field = '*'){
        if ($where) {
            $data = $this->db->where($where)->orderBy("cap_id",'desc')->get($this->table,null,$field);
        } else {
            $data = $this->db->orderBy("cap_id",'desc')->get($this->table,null,$field);
        }
        // var_dump($this->db->getLastQuery());  
        if ($data) {
            return $data;
        }
        return [];
    }

 // var_dump($this->db->getLastQuery());   获取执行的sql

2022年11月22日18:41:11  在使用Mysqli 是  其实也可以使用invoke 方式  代码如下：

<?php
namespace App\Model;

use EasySwoole\MysqliPool\Mysql;

class TestModel {
      
      //获取数据
      public function getData(){
           //此处的mysql 由上面的注册名称 保持一致
           $data = Mysql::invoker('mysql',function ($conn) {
                return $conn->rawQuery('select version()');
           });
           var_dump($data);
      }
}