Edited at

Doctrine DBALの基本的な使い方

More than 3 years have passed since last update.

公式ドキュメントを参考に基本的な使い方をまとめる。

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/index.html


接続

$conn = \Doctrine\DBAL\DriverManager::getConnection([

'path' => './dbal-demo.sqlite',
'driver' => 'pdo_sqlite',
]);

各DBごとにパラメータが違う。詳しくはドキュメントを参照:

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/configuration.html#connection-details


SQL実行

参考:

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html


直SQL


基本

$statement = $conn->executeQuery('SELECT * FROM users WHERE id = ?', [1]);

$user = $statement->fetch();
/*
[
"id" => "1",
"name" => "bob"
]
*/

第三引数でパラメータの型を「配列」と指定してあげれば、IN句に配列を渡すことが出来る。

(他のメソッドでもだいたい最後の引数で指定できるみたい)

$conn->fetchAll(

'select * from users WHERE id IN (?)',
[[1, 2]],
[\Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
);


update

$count = $conn->executeUpdate('UPDATE users SET name = ? WHERE id = ?', ['smith', 1]);

echo $count; // 1


insert

$conn->insert('users', ['id' => 2, 'name' => 'mike']);


update

$conn->update('users', ['name' => 'smith'], ['id' => 1]);


delete

$conn->delete('users', ['id' => 1]);


select


複数

$conn->fetchAll('SELECT * FROM users');

/*
[
[
"id" => "1",
"name" => "bob"
],
[
"id" => "2",
"name" => "mike"
]
]
*/


一件

$user = $conn->fetchAssoc('SELECT * FROM users WHERE id = ?', [1]);

/*
[
"id" => "1",
"name" => "bob"
]
*/


カラム指定

$name = $conn->fetchColumn('SELECT name FROM users WHERE id = ?', [1]);

echo $name; // "bob"


クエリログ

DB接続時にConfiguration経由でSQLLoggerのインスタンスを渡す。

$logger = new \Doctrine\DBAL\Logging\DebugStack();

$config = new \Doctrine\DBAL\Configuration();
$config->setSQLLogger($logger);
$conn = \Doctrine\DBAL\DriverManager::getConnection([
'path' => './dbal-demo.sqlite',
'driver' => 'pdo_sqlite',
], $config);

もしくはあとから設定。

$logger = new \Doctrine\DBAL\Logging\DebugStack();

$conn->getConfiguration()->setSQLLogger($logger);

クエリを実行したあと、queriesプロパティにログがたまっている。

$conn->fetchAll(

'select * from users WHERE id IN (?)',
[[1, 2]],
[\Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
);

$conn->getConfiguration()->getSQLLogger()->queries;
/*
[
1 => [
"sql" => "select * from users WHERE id IN (?)",
"params" => [
[
1,
2
]
],
"types" => [
101
],
"executionMS" => 0.0022280216217041
]
]
*/


まとめ


  • "便利なPDO"として使えそう

  • ややこしい機能は省略した


    • 型のバインディング

    • スキーマ管理

    • シャーディング

    • など