Главная > Блог > Web-разработка > Yii2 и Laravel >

Yii2. Объекты доступа к данным

24.03.2019

Теги: Web-разработка • Yii2 • БазаДанных • Запрос • Теория • Фреймворк

Объекты доступа к данным (DAO) обеспечивают объектно-ориентированный API для доступа к реляционным базам данных. Это основа для других, более продвинутых, методов доступа к базам данных, включая построитель запросов и Active Record. При использовании DAO в основном используется чистый SQL и массивы PHP.

Подключение к базе данных

Для доступа к базе данных, сначала нужно подключится к ней, создав экземпляр класса yii\db\Connection:

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

Так как подключение к БД часто нужно в нескольких местах, распространённой практикой является его настройка как компонента приложения:

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

Теперь можно получить доступ к подключению к БД с помощью выражения Yii::$app->db.

Выполнение SQL запросов

После создания экземпляра соединения, вы можете выполнить SQL запрос, выполнив следующие шаги:

Создать yii\db\Command из запроса SQL;
Привязать параметры (не обязательно);
Вызвать один из методов выполнения SQL из yii\db\Command.

Следующий пример показывает различные способы получения данных из базы дынных:

// Возвращает набор строк, каждая строка — это ассоциативный массив с именами столбцов
// и значений. Если выборка ничего не вернёт, то будет возвращен пустой массив.
$products = Yii::$app->db->createCommand('SELECT * FROM product')->queryAll();

// Вернёт одну строку (первую строку) или false, если ничего не будет выбрано.
$product = Yii::$app->db->createCommand('SELECT * FROM product WHERE id=1')->queryOne();

// Вернёт один столбец (первый столбец) или пустой массив при отсутствии результата
$name = Yii::$app->db->createCommand('SELECT name FROM product')->queryColumn();

// Вернёт скалярное значение или false при отсутствии результата
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM product')->queryScalar();

Чтобы сохранить точность, данные извлекаются как строки, даже если тип поля в базе данных является числовым.

Привязка параметров

При создании команды из SQL запроса с параметрами, нужно использовать привязку параметров для предотвращения атак через SQL инъекции. Например,

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

В SQL запрос можно добавить один или несколько маркеров (например :id в примере выше). Маркеры должны быть строкой, начинающейся с двоеточия. Далее нужно вызвать один из следующих методов для привязки значений к параметрам:

bindValue(): привязка одного параметра по значению
bindValues(): привязка нескольких параметров в одном вызове
bindParam(): похоже на bindValue(), но привязка происходит по ссылке.

Следующий пример показывает альтернативный способ привязки параметров:

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

$products = Yii::$app->db
                     ->createCommand('SELECT * FROM product WHERE id=:id AND active=:active')
                     ->bindValues($params)
                     ->queryOne();

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

Привязка переменных реализована через подготавливаемые запросы. Помимо предотвращения атак путём SQL инъекций, это увеличивает производительность, так как запрос подготавливается один раз, а потом выполняется много раз с разными параметрами. Например

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

$product1 = $command->bindValue(':id', 1)->queryOne();
$product2 = $command->bindValue(':id', 2)->queryOne();
$product3 = $command->bindValue(':id', 3)->queryOne();

Так как bindParam() поддерживает привязку параметров по ссылке, следующий код может быть написан следующим образом:

$command = Yii::$app->db
                    ->createCommand('SELECT * FROM product WHERE id=:id')
                    ->bindParam(':id', $id);
$id = 1;
$product1 = $command->queryOne();
$id = 2;
$product2 = $command->queryOne();
$id = 3;
$product3 = $command->queryOne();

Выполнение не-SELECT запросов

В методах, описанных выше, вызываются SELECT запросы для извлечения данных из базы. Для запросов не возвращающих данные, предназначен метод yii\db\Command::execute(). Например

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

Метод yii\db\Command::execute() возвращает количество строк, обработанных SQL запросом.

Для запросов INSERT, UPDATE и DELETE, вместо написания чистого SQL, можно вызвать методы insert(), update(), delete(), соответственно, для создания указанных SQL конструкций. Например

// запрос INSERT INTO uses ('name', 'age') VALUES ('Сергей', 30)
Yii::$app->db
         ->createCommand()
         ->insert('user', ['name' => 'Сергей','age' => 30])
         ->execute();

// запрос UPDATE user SET status=1 WHERE age>30
Yii::$app->db
         ->createCommand()
         ->update('user', ['status' => 1], 'age > 30')
         ->execute();

// запрос DELETE FROM user WHERE status=0
Yii::$app->db
        ->createCommand()
        ->delete('user', 'status = 0')
        ->execute();

Можно также вызвать метод batchInsert() для вставки множества строк за один вызов. Это более эффективно, чем вставлять записи по одной за раз:

Yii::$app->db
        ->createCommand()
        ->batchInsert(
            'user',
            ['name', 'age']
            [
                ['Сергей', 30],
                ['Николай', 20],
                ['Катерина', 25],

            ]
        )
        ->execute();

Экранирование имён таблиц и столбцов

При написании независимого от базы данных кода, правильно экранировать имена таблиц и столбцов довольно трудно, так как в разных базах данных правила экранирования разные. Чтоб преодолеть данную проблему, можно использовать следующий синтаксис экранирования используемый в Yii:

[[column name]]: заключать имя столбца в двойные квадратные скобки;
{{table name}}: заключать имя таблицы в двойные фигурные скобки.

Yii DAO будет автоматически преобразовывать подобные конструкции в SQL в правильно экранированные имена таблиц и столбцов.

Исполнение транзакций

Когда нужно выполнить несколько зависимых запросов последовательно, может потребоваться обернуть их в транзакцию для обеспечения целостности базы данных. Если в любом из запросов произойдёт ошибка, база данных откатится на состояние, которое было до выполнения запросов.

Следующий код показывает типичное использование транзакций:

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

Код ниже эквивалентен приведённому ниже, но предоставляет больше контроля над обработкой ошибок:

$db = Yii::$app->db;
$transaction = $db->beginTransaction();

try {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // .....
    $transaction->commit();
} catch(\Exception $e) {
    $transaction->rollBack();
    throw $e;
} catch(\Throwable $e) {
    $transaction->rollBack();
}

В коде выше ради совместимости с PHP 5.x использованы два блока catch. Exception реализует интерфейс Throwable, начиная с PHP 7.0. При использовании только PHP 7.x можно пропустить блок с Exception.

Работа со схемой БД

Yii DAO предоставляет целый набор методов для управления схемой базы данных, таких как создание новых таблиц, удаление столбцов из таблицы, и т.д. Эти методы описаны ниже:

createTable(): создание таблицы
renameTable(): переименование таблицы
dropTable(): удаление таблицы
truncateTable(): удаление всех записей в таблице
addColumn(): добавление столбца
renameColumn(): переименование столбца
dropColumn(): удаление столбца
alterColumn(): преобразование столбца
addPrimaryKey(): добавление первичного ключа
dropPrimaryKey(): удаление первичного ключа
addForeignKey(): добавление внешнего ключа
dropForeignKey(): удаление внешнего ключа
createIndex(): создания индекса
dropIndex(): удаление индекса

Поиск: Web-разработка • Yii2 • База данных • Фреймворк • DAO • SQL • Запрос