/ yii2

yii2-dao

Yii DAO(Database Access Objects)建立在PDO之上,为访问关系数据库提供了面向对象的API。

Yii DAO 是其他更高级的数据库访问方法的基础,包括:

  • query builder(查询构建器)
  • active record(活动记录)

SELECT - Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{employee}}") ->queryScalar();
INSERT - Yii::$app->db->createCommand()->insert('user', ['name' => 'Sam','age' =>30,])->execute();
UPDATE - Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();
DELETE - Yii::$app->db->createCommand()->delete('user', 'status = 0')->execute();

数据库操作:

1. 创建数据库连接

想要访问数据库, 你首先需要通过创建一个 yii\db\Connection 实例来与之建立连接。

$connection = new yii\db\Connection([
    'dsn' => 'mysql:host=localhost;dbname=example',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
]);
$connection->open();

After the DB connection is established, one can execute SQL statements like the following:

$command = $connection->createCommand('SELECT * FROM post WHERE id=:id');
$command->bindValue(':id', $_GET['id']);
$post = $command->query();

// UPDATE (table name, column values, condition)
Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

// DELETE (table name, condition)
Yii::$app->db->createCommand()->delete('user', 'status = 0')->execute();

因为数据库连接经常需要在多个地方使用到, 一个常见的做法是以应用组件的方式来配置它, 如下:

return [
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=example',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
        ],
    ],
    // ...
];

之后你就可以通过语句 Yii::$app->db 来使用数据库连接了。

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id')
    ->bindValue(':id', $_GET['id'])
    ->query();

Tip:如果你的应用需要访问多个数据库,你可以配置多个 DB 应用组件。

2. 执行 SQL 查询

一旦你拥有了 DB Connection 实例, 你可以按照下列步骤来执行 SQL 查询:

  1. 使用纯SQL查询来创建出 yii\db\Command;
  2. 绑定参数 (可选的);
  3. 调用 yii\db\Command 里 SQL 执行方法中的一个。

下列例子展示了几种不同的从数据库取得数据的方法:

// 返回多行. 每行都是列名和值的关联数组.
// 如果该查询没有结果则返回空数组
$posts = Yii::$app->db->createCommand('SELECT * FROM post')
            ->queryAll();

// 返回一行 (第一行)
// 如果该查询没有结果则返回 false
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1')
           ->queryOne();

// 返回一列 (第一列)
// 如果该查询没有结果则返回空数组
$titles = Yii::$app->db->createCommand('SELECT title FROM post')
             ->queryColumn();

// 返回一个标量值
// 如果该查询没有结果则返回 false
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM post')
             ->queryScalar();

Tip: 为了保持精度, 即使对应的数据库列类型为数值型, 所有从数据库取得的数据都被表现为字符串。

3. 参数绑定

当使用带参数的 SQL 来创建数据库命令时, 你几乎总是应该使用绑定参数的方法来防止 SQL 注入攻击,例如:

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValue(':id', $_GET['id'])
           ->bindValue(':status', 1)
           ->queryOne();

在 SQL 语句中, 你可以嵌入一个或多个参数占位符(例如,上述例子中的 :id )。 一个参数占位符应该是以冒号开头的字符串。 之后你可以调用下面绑定参数的方法来绑定参数值:

  • bindValue():绑定一个参数值
  • bindValues():在一次调用中绑定多个参数值
  • bindParam():与 bindValue() 相似,但是也支持绑定参数引用。

下面的例子展示了几个可供选择的绑定参数的方法:

$params = [':id' => $_GET['id'], ':status' => 1];

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValues($params)
           ->queryOne();
           
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status', $params)
           ->queryOne();

绑定参数是通过 预处理语句 实现的。 除了防止 SQL 注入攻击, 它也可以通过一次预处理 SQL 语句, 使用不同参数多次执行, 来提升性能。 例如:

$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id');

$post1 = $command->bindValue(':id', 1)->queryOne();
$post2 = $command->bindValue(':id', 2)->queryOne();
// ...

因为 bindParam() 支持通过引用来绑定参数, 上述代码也可以像下面这样写:

$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id')
              ->bindParam(':id', $id);

$id = 1;
$post1 = $command->queryOne();

$id = 2;
$post2 = $command->queryOne();
// ...

具体实现:

$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ?');

$stmt->bindValue(':elapse_time', $e_time, PDO::PARAM_STR);

参考文档:http://php.net/manual/en/pdo.constants.php

4. 执行非查询语句

上面部分中介绍的 queryXyz() 方法都处理的是从数据库返回数据的查询语句。 对于那些不取回数据的语句, 你应该调用的是 yii\db\Command::execute() 方法。 例如:

Yii::$app->db->createCommand('UPDATE post SET status=1 WHERE id=1')
   ->execute();

yii\db\Command::execute() 方法返回执行 SQL 所影响到的行数。

对于 INSERT, UPDATEDELETE 语句, 不再需要写纯SQL语句了, 你可以直接调用 insert()、 update()、 delete(), 来构建相应的 SQL 语句。 这些方法将正确地引用表和列名称以及绑定参数值。 例如:

// INSERT (table name, column values)
Yii::$app->db->createCommand()->insert('user', [
    'name' => 'Sam',
    'age' => 30,
])->execute();

// UPDATE (table name, column values, condition)
Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

// DELETE (table name, condition)
Yii::$app->db->createCommand()->delete('user', 'status = 0')->execute();

你也可以调用 batchInsert() 来一次插入多行, 这比一次插入一行要高效得多:

// table name, column names, column values
Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
])->execute();

预处理语句

为安全传递查询参数可以使用预处理语句,首先应当使用 :placeholder 占位,再将变量绑定到对应占位符。

事务

Yii::$app->db->transaction(function($db) {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
});

The above code is equivalent to the following, which gives you more control about the error handling code:

$db = Yii::$app->db;
$transaction = $db->beginTransaction();
try {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
    
    $transaction->commit();
} catch(\Exception $e) {
    $transaction->rollBack();
    throw $e;
} catch(\Throwable $e) {
    $transaction->rollBack();
    throw $e;
}