Model与数据库

鉴于每个用户的使用习惯问题，EasySwoole本身并不提供封装好的数据库操作与Model层，但我们强力推荐在项目中使用第三方开源库https://github.com/joshcam/PHP-MySQLi-Database-Class
作为数据操作类库，并构建自己的Model。

使用指导

项目引入

  • 克隆(下载)MySQLi项目，并解压提取其中的MysqliDb.php文件，放入项目 ‘/App/Vendor/Db/‘ 路径下。
  • Class引入。在框架的frameInitialize事件中引入MysqliDb.php。
    1.   function frameInitialize()
    2.   {
    3.       // TODO: Implement frameInitialize() method.
    4.       AutoLoader::getInstance()->requireFile("App/Vendor/Db/MysqliDb.php");
    5.   }

    引入成功后即可在项目任意位置创建MysqliDb对象。

利用IOC容器实现单例长连接

在框架的frameInitialized中，引入了MysqliDb.php后，即可进行IOC注入。

  1. Di::getInstance()->set('MYSQL',\MysqliDb::class,Array (
  2.             'host' => 'host',
  3.             'username' => 'username',
  4.             'password' => 'password',
  5.             'db'=> 'dbName',
  6.             'port' => 3306,
  7.             'charset' => 'utf8')
  8. );
  9. //或者是
  10. $conf = Config::getInstance()->getConf('MYSQL'); 
  11. Di::getInstance()->set(SysConst::MYSQL,\MysqliDb::class,$conf['HOST'],$conf['USER'],$conf['PASSWORD'],$conf['DB_NAME']);

获取数据库示例

  1. $db = Di::getInstance()->get('MYSQL');

注意：为避免出现多个进程复用同一个数据库连接的情况，请勿在服务启动前的任一位置执行Di::getInstance()->get(‘MYSQL’)。
若在frameInitialized或者是beforeWorkerStart事件中使用数据库，请以手动new class()的方式来获取一个数据库对象。具体请见文档的常见问题章节。其次，在单例子模式下，请注意数据库断线重连问题。
MysqliDb类库中有实现断线自动重连。

数据库基础用法

Insert

  1. $data = Array (
  2.     "productName" => "test product",
  3.     "userId" => $userIdQ,
  4.     "lastUpdated" => $db->now()
  5. );
  6. $id = $db->insert ("products", $data);
  7. // Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());

Update Query

  1. $data = Array (
  2.     'firstName' => 'Bobby',
  3.     'lastName' => 'Tables',
  4.     'editCount' => $db->inc(2),
  5.     // editCount = editCount + 2;
  6.     'active' => $db->not()
  7.     // active = !active;
  8. );
  9. $db->where ('id', 1);
  10. if ($db->update ('users', $data))
  11.     echo $db->count . ' records were updated';
  12. else
  13.     echo 'update failed: ' . $db->getLastError();

update() also support limit parameter:

  1. $db->update ('users', $data, 10);

// Gives: UPDATE users SET … LIMIT 10

Select Query

After any select/get function calls amount or returned rows is stored in $count variable

  1. $users = $db->get('users'); //contains an Array of all users 
  2. $users = $db->get('users', 10); //contains an Array 10 users

or select with custom columns set. Functions also could be used

  1. $cols = Array ("id", "name", "email");
  2. $users = $db->get ("users", null, $cols);
  3. if ($db->count > 0)
  4.     foreach ($users as $user) { 
  5.         print_r ($user);
  6.     }
  8. or select just one row
  10. $db->where ("id", 1);
  11. $user = $db->getOne ("users");
  12. echo $user['id'];
  14. $stats = $db->getOne ("users", "sum(id), count(*) as cnt");
  15. echo "total ".$stats['cnt']. "users found";
  17. or select one column value or function result
  19. $count = $db->getValue ("users", "count(*)");
  20. echo "{$count} users found";

select one column value or function result from multiple rows:

  1. $logins = $db->getValue ("users", "login", null);
  2. // select login from users
  3. $logins = $db->getValue ("users", "login", 5);
  4. // select login from users limit 5
  5. foreach ($logins as $login)
  6.     echo $login;

JOIN method

  1. $db->join("users u", "p.tenantID=u.tenantID", "LEFT");
  2. $db->where("u.id", 6);
  3. $products = $db->get ("products p", null, "u.name, p.productName");
  4. print_r ($products);

Join Conditions

Add AND condition to join statement

  1. $db->join("users u", "p.tenantID=u.tenantID", "LEFT");
  2. $db->joinWhere("users u", "u.tenantID", 5);
  3. $products = $db->get ("products p", null, "u.name, p.productName");
  4. print_r ($products);
  5. // Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)

Add OR condition to join statement

  2. $db->join("users u", "p.tenantID=u.tenantID", "LEFT");
  3. $db->joinOrWhere("users u", "u.tenantID", 5);
  4. $products = $db->get ("products p", null, "u.name, p.productName");
  5. print_r ($products);
  6. // Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID OR u.tenantID = 5)

更多用法请见: https://github.com/joshcam/PHP-MySQLi-Database-Class

创建自己的Model

  1. namespace App\Model\Goods;
  4. use Core\Component\Spl\SplBean;
  6. class Bean extends SplBean
  7. {
  8.     protected $goodsId;
  9.     protected $goodsName;
  10.     protected $addTime;
  11.     protected function initialize()
  12.     {
  13.         // TODO: Implement initialize() method.
  14.         $this->addTime = time();
  15.     }
  17. }
  1. namespace App\Model\Goods;
  4. use Core\Component\Di;
  6. class Goods
  7. {
  8.     protected $db;
  9.     protected $tableName = 'goods_list';
  10.     function __construct()
  11.     {
  12.         $db = Di::getInstance()->get("MYSQL");
  13.         if($db instanceof \MysqliDb){
  14.             $this->db = $db;
  15.         }
  16.     }
  18.     function add(Bean $bean){
  19.         return $this->db->insert($this->tableName,$bean->toArray($bean::FILTER_TYPE_NOT_NULL));
  20.     }
  21. }

注意：数据库若使用单例模式保持长连接，一定要处理断线问题。本文中推荐的数据库类已经处理了。