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. }
  7. or select just one row
  8. $db->where ("id", 1);
  9. $user = $db->getOne ("users");
  10. echo $user['id'];
  11. $stats = $db->getOne ("users", "sum(id), count(*) as cnt");
  12. echo "total ".$stats['cnt']. "users found";
  13. or select one column value or function result
  14. $count = $db->getValue ("users", "count(*)");
  15. 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

  1. $db->join("users u", "p.tenantID=u.tenantID", "LEFT");
  2. $db->joinOrWhere("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 OR u.tenantID = 5)

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

创建自己的Model

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

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