Doctrine DBAL QueryBuilder

最后修改于 2023 年 1 月 9 日

Doctrine DBAL QueryBuilder 教程展示了如何使用 Doctrine 的 QueryBuilder 在 PHP 中编程数据库。

Doctrine

Doctrine 是一组 PHP 库，主要侧重于在 PHP 中提供持久化服务。它的主要项目是对象关系映射器 (ORM) 和数据库抽象层 (DBAL)。Doctrine 是一个开源项目，根据 MIT 许可证免费提供。

Doctrine QueryBuilder

Doctrine QueryBuilder 提供了一个方便、流畅的接口，用于创建和运行数据库查询。它是对运行 SQL 语句的低级细节的抽象。它使程序员免受该过程的复杂性的影响。

Doctrine 有两种查询构建器；一个用于 ORM，一个用于 DBAL。在本教程中，我们介绍了 DBAL 的 QueryBuilder。

Doctrine 数据库抽象层 (DBAL) 是一个位于 PDO 之上的抽象层，提供了一个直观且灵活的 API，用于与最流行的关系数据库通信。

PostgreSQL 数据库

在本教程中，我们使用 PostgreSQL 数据库。

-- cars.sql for PostgreSQL database

DROP TABLE IF EXISTS cars;
CREATE TABLE cars(id SERIAL PRIMARY KEY, name VARCHAR(255), price INT);
INSERT INTO cars(name, price) VALUES('Audi',52642);
INSERT INTO cars(name, price) VALUES('Mercedes',57127);
INSERT INTO cars(name, price) VALUES('Skoda',9000);
INSERT INTO cars(name, price) VALUES('Volvo',29000);
INSERT INTO cars(name, price) VALUES('Bentley',350000);
INSERT INTO cars(name, price) VALUES('Citroen',21000);
INSERT INTO cars(name, price) VALUES('Hummer',41400);
INSERT INTO cars(name, price) VALUES('Volkswagen',21600);

这些 SQL 命令创建了一个 cars 表。

Doctrine 安装

我们安装 Doctrine 和一些辅助工具。

$ composer req doctrine/dbal

我们安装 Doctrine。请注意，DBAL 层包含在 doctrine/dbal 包中。

$ composer req symfony/var-dumper
$ composer req tightenco/collect

我们安装 Symfony 的转储器和 Laravel 集合。我们将在我们的例子中使用它们。

$ composer dumpautoload

我们生成一个需要包含在项目中的所有类的列表。composer 重新读取 composer.json 文件以构建要自动加载的文件列表。

引导 Doctrine CLI 示例

我们创建一个引导文件，该文件将包含在所有示例中。

<?php

require_once "vendor/autoload.php";

use Doctrine\DBAL\DriverManager;

$attrs = ['driver' => 'pdo_pgsql', 'host' => 'localhost', 'dbname' => 'testdb', 
    'port' => 5432, 'user' => 'postgres', 'password' => 's$cret'];

$conn = DriverManager::getConnection($attrs);

在引导文件中，我们包含自动加载文件并设置与 PostgreSQL 数据库的连接。

获取 PostgreSQL 版本

在第一个示例中，我们获取 PostgreSQL 的版本。

<?php

require_once "bootstrap.php";

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder = $queryBuilder->select('version()');

$version = $queryBuilder->execute()->fetchColumn(0);
echo $version . "\n";

该示例打印 PostgreSQL 数据库的版本。

$queryBuilder = $conn->createQueryBuilder();

从连接对象，我们使用 createQueryBuilder() 创建一个查询构建器。

$queryBuilder = $queryBuilder->select('version()');

我们使用 select() 执行 version() 函数。

$version = $queryBuilder->execute()->fetchColumn(0);

我们执行查询并使用 fetchColumn() 获取结果。注意方法调用的链式调用；这称为流畅 API。

$ php version.php
PostgreSQL 11.1, compiled by Visual C++ build 1914, 64-bit

这是输出。

Doctrine QueryBuilder fetchall

fetchall() 方法从表中返回所有行。

<?php

require_once "bootstrap.php";

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder->select('*')->from('cars');

$stm = $queryBuilder->execute();
$data = $stm->fetchAll();

$coll = collect($data);
$sorted = $coll->sortBy('price');

$sorted->each(function($item, $key) {
    echo sprintf("Id: %d Name: %s Price: %d\n", $item['id'], $item['name'], $item['price']);
});

该示例从 cars 表中检索所有行。

$queryBuilder->select('*')->from('cars');

我们从 cars 表中选择所有行。select() 接受要显示为参数的列名。

$stm = $queryBuilder->execute();
$data = $stm->fetchAll();

我们执行查询并使用 fetchAll() 获取所有行。

$coll = collect($data);
$sorted = $coll->sortBy('price');

我们使用 Laravel 集合来排序数据。

$sorted->each(function($item, $key) {
    echo sprintf("Id: %d Name: %s Price: %d\n", $item['id'], $item['name'], $item['price']);
});

排序后的数据将打印到控制台。

$ php fetch_all.php
Id: 3 Name: Skoda Price: 9000
Id: 6 Name: Citroen Price: 21000
Id: 8 Name: Volkswagen Price: 21600
Id: 4 Name: Volvo Price: 29000
Id: 7 Name: Hummer Price: 41400
Id: 1 Name: Audi Price: 52642
Id: 2 Name: Mercedes Price: 57127
Id: 5 Name: Bentley Price: 350000

这是输出。数据按 price 列排序。

Doctrine QueryBuilder 表别名

我们可以给数据库表起别名。当表名很长并且我们使用多个表时，这很有用。

<?php

require_once "bootstrap.php";

$queryBuilder = $conn->createQueryBuilder();

$queryBuilder = $queryBuilder->select('*')->from('cars', 'c')
    ->where('c.price < 30000');

$selected = $queryBuilder->execute()->fetchAll();
dump($selected);

该示例打印所有价格低于 30000 的汽车。

$queryBuilder = $queryBuilder->select('*')->from('cars', 'c')
    ->where('c.price < 30000');

我们给 cars 表起了别名 c。稍后，我们通过别名引用该表。

dump($selected);

我们使用 dump() 输出数据。

$ php table_alias.php
array:4 [
    0 => array:3 [
    "id" => 3
    "name" => "Skoda"
    "price" => 9000
    ]
    1 => array:3 [
    "id" => 4
    "name" => "Volvo"
    "price" => 29000
    ]
    2 => array:3 [
    "id" => 6
    "name" => "Citroen"
    "price" => 21000
    ]
    3 => array:3 [
    "id" => 8
    "name" => "Volkswagen"
    "price" => 21600
    ]
]

有四辆汽车的价格低于 30000。输出使用 Symfony 的转储器进行了很好的格式化。输出也在终端上着色。

Doctrine QueryBuilder setParameter

setParameter() 用于将参数设置为查询占位符。Doctrine 支持位置参数和命名参数。

参数化查询用于保护代码免受 SQL 注入攻击，并使查询更有效。

<?php

require_once "bootstrap.php";

$id = 6;

$queryBuilder = $conn->createQueryBuilder();

$queryBuilder = $queryBuilder->select('name')->from('cars')
    ->where('id = ?')->setParameter(0, $id);

$car_name = $queryBuilder->execute()->fetchColumn(0);
echo $car_name . "\n";

在示例中，我们获取 ID 为 6 的行。我们使用参数化查询来获取列。

$queryBuilder = $queryBuilder->select('name')->from('cars')
    ->where('id = ?')->setParameter(0, $id);

占位符用 ? 字符标识。这种类型称为位置参数。使用 setParameter()，我们将值映射到占位符。

$ php fetch_column.php
Citroen

这是输出。

Doctrine QueryBuilder orderBy

可以使用 orderBy() 对数据进行排序。有时我们无法控制数据发送给我们；在这种情况下，我们可以使用 Laravel 集合来排序数据，就像我们在 fetch all 示例中所做的那样。

<?php

require_once "bootstrap.php";

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
    ->select('*')
    ->from('cars')
    ->orderBy('name', 'desc');

$stm = $queryBuilder->execute();
$data = $stm->fetchAll();

$coll = collect($data);

$coll->each(function($item, $key) {
    echo sprintf("id: %d name: %s price: %d\n", $item['id'], $item['name'], $item['price']);
});

该示例从 cars 表中检索所有行，并按车名降序排序。

$queryBuilder
    ->select('*')
    ->from('cars')
    ->orderBy('name', 'desc');

数据被选择和排序。

$ php order_by.php
id: 4 name: Volvo price: 29000
id: 8 name: Volkswagen price: 21600
id: 3 name: Skoda price: 9000
id: 2 name: Mercedes price: 57127
id: 7 name: Hummer price: 41400
id: 6 name: Citroen price: 21000
id: 5 name: Bentley price: 350000
id: 1 name: Audi price: 52642

Doctrine QueryBuilder where in

以下示例展示了如何使用 WHERE IN 子句构建查询。

<?php

require_once "bootstrap.php";

use Doctrine\DBAL\Connection;

$ids = [2, 4, 6];

$queryBuilder = $conn->createQueryBuilder();

$queryBuilder->select('*')->from('cars')
    ->where('id IN (?)')->setParameter(0, $ids, Connection::PARAM_INT_ARRAY);
$cars = $queryBuilder->execute()->fetchAll();

$data = collect($cars);

$data->each(function ($e) {
    dump($e);
});

该示例打印具有指定 ID 的汽车：2、4 和 6。

$queryBuilder->select('*')->from('cars')
    ->where('id IN (?)')->setParameter(0, $ids, Connection::PARAM_INT_ARRAY);

我们需要告诉 Doctrine 我们使用数组作为参数，使用 Connection::PARAM_INT_ARRAY 标志。

$ php where_in.php
array:3 [
    "id" => 2
    "name" => "Mercedes"
    "price" => 57127
]
array:3 [
    "id" => 4
    "name" => "Volvo"
    "price" => 29000
]
array:3 [
    "id" => 6
    "name" => "Citroen"
    "price" => 21000
]

这是输出。

Doctrine QueryBuilder 组合 WHERE 子句

我们可以通过添加 andWhere() 来组合 WHERE 子句。

<?php

require_once "bootstrap.php";

$minPrice = 10000;
$maxPrice = 50000;

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
    ->select('*')
    ->from('cars')
    ->where('price > ?')
    ->andWhere('price < ?')
    ->setParameter(0, $minPrice)
    ->setParameter(1, $maxPrice);

$stm = $queryBuilder->execute();
$data = $stm->fetchAll();

$coll = collect($data);

$coll->each(function($item, $key) {
    echo sprintf("id: %d name: %s price: %d\n", $item['id'], $item['name'], $item['price']);
});

该示例显示了价格在给定最小价格和最大价格之间的所有汽车。

$ php and_where.php
id: 4 name: Volvo price: 29000
id: 6 name: Citroen price: 21000
id: 7 name: Hummer price: 41400
id: 8 name: Volkswagen price: 21600

有四辆汽车满足条件。

Doctrine QueryBuilder 插入行

使用 insert() 和 values() 插入新行。

<?php

require_once "bootstrap.php";

$name = 'Oldsmobile';
$price = 28800;

$queryBuilder = $conn->createQueryBuilder();

$queryBuilder = $queryBuilder->insert('cars')
    ->values(['name' => '?', 'price' => '?'])
    ->setParameters([0 => $name, 1 => $price]);
$queryBuilder->execute();

$sql = $queryBuilder->getSQL();
echo "Executed: $sql\n";

该示例将一辆新车插入到 cars 表中。

$queryBuilder = $queryBuilder->insert('cars')
    ->values(['name' => '?', 'price' => '?'])
    ->setParameters([0 => $name, 1 => $price]);

可以使用 setParameters() 指定多个参数。

$sql = $queryBuilder->getSQL();

getSQL() 获取 Doctrine 生成的 SQL 语句。

$ php insert_row.php
Executed: INSERT INTO cars (name, price) VALUES(?, ?)

输出显示生成的 SQL 语句。

Doctrine QueryBuilder 删除

使用 delete() 删除数据。

<?php

require_once "bootstrap.php";

$name = 'Oldsmobile';
$price = 26600;

$queryBuilder = $conn->createQueryBuilder();

$queryBuilder = $queryBuilder->delete('cars')
    ->where('id IN (1, 2, 3)');

$n = $queryBuilder->execute();

echo "The query deleted $n rows\n";

该示例删除 ID 为 1、2 和 3 的行。

$n = $queryBuilder->execute();

execute() 方法返回已删除的行数。

$ php delete_rows.php
The query deleted 3 rows

这是输出。

Doctrine QueryBuilder 更新行

使用 udpate() 和 set() 更新行。

<?php

require_once "bootstrap.php";

$name = 'Oldsmobile';
$price = 26600;

$queryBuilder = $conn->createQueryBuilder();

$queryBuilder = $queryBuilder->update('cars')
    ->set('price', $queryBuilder->createNamedParameter($price))
    ->where('id = 9');

$queryBuilder->execute();

$sql = $queryBuilder->getSQL();
echo "Executed: $sql\n";

该示例更新了 ID 为 9 的汽车的价格。

Symfony Doctrine 示例

以下示例是一个简单的 Symfony Web 应用程序。Symfony 使用 Doctrine 进行持久化。

$ composer create-project symfony/skeleton symfapp
$ cd symfapp

我们创建一个新的 Symfony 骨架应用程序。

$ composer require symfony/orm-pack

我们安装 Doctrine。

$ composer require maker --dev
$ composer require server --dev

我们安装 maker 和 server 组件。

doctrine:
    dbal:
        # configure these for your database server
        driver: pdo_pgsql
        charset: utf8

我们配置 Doctrine 以使用 PostgreSQL。默认情况下，Symfony 有 MySQL 的配置。

...
DATABASE_URL=pgsql://postgres:s$cret@127.0.0.1:5432/testdb

在 .env 文件中，我们设置 DATABASE_URL。

$ php bin/console doctrine:query:sql "select version()"
array(1) {
    [0]=>
    array(1) {
    ["version"]=>
    string(58) "PostgreSQL 11.1, compiled by Visual C++ build 1914, 64-bit"
    }
}

我们验证 PostgreSQL 设置。

$ php bin/console make:controller HomeController

使用 maker，我们创建一个新的控制器。

<?php

namespace App\Controller;

use Doctrine\DBAL\Connection;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;

class HomeController extends AbstractController
{
    /**
    * @Route("/home", name="home")
    */
    public function index(Connection $conn)
    {
        $queryBuilder = $conn->createQueryBuilder();
        $data = $queryBuilder->select('*')->from('cars')->execute()->fetchAll();

        return $this->json($data);
    }
}

index() 方法从数据库表中返回所有行。请注意，由于简单起见，我们已将查询构建器放在控制器中。在生产应用程序中，也应该有一个服务层和一个存储库。

return $this->json($data);

数据以 JSON 格式发送回客户端。

$ php bin/console server:run

我们运行开发服务器。

$ curl localhost:8000/home
[{"id":4,"name":"Volvo","price":29000},{"id":5,"name":"Bentley","price":350000},
{"id":6,"name":"Citroen","price":21000},{"id":7,"name":"Hummer","price":41400},
{"id":8,"name":"Volkswagen","price":21600},{"id":9,"name":"Oldsmobile","price":26600}]

使用 curl，我们向应用程序生成一个请求。

在本文中，我们使用了 Doctrine QueryBuilder 和 PostgreSQL 数据库。