原文： https://zetcode.com/doctrine/querybuilder/

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_postgre.sql

  1. -- cars.sql for PostgreSQL database
  3. DROP TABLE IF EXISTS cars;
  4. CREATE TABLE cars(id SERIAL PRIMARY KEY, name VARCHAR(255), price INT);
  5. INSERT INTO cars(name, price) VALUES('Audi',52642);
  6. INSERT INTO cars(name, price) VALUES('Mercedes',57127);
  7. INSERT INTO cars(name, price) VALUES('Skoda',9000);
  8. INSERT INTO cars(name, price) VALUES('Volvo',29000);
  9. INSERT INTO cars(name, price) VALUES('Bentley',350000);
  10. INSERT INTO cars(name, price) VALUES('Citroen',21000);
  11. INSERT INTO cars(name, price) VALUES('Hummer',41400);
  12. INSERT INTO cars(name, price) VALUES('Volkswagen',21600);

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

Doctrine 安装

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

  1. $ composer req doctrine/dbal

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

  1. $ composer req symfony/var-dumper
  2. $ composer req tightenco/collect

我们安装了 Symfony 的 dumper 和 Laravel 集合。 我们将在示例中使用它们。

  1. $ composer dumpautoload

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

引导 Doctrine CLI 示例

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

bootstrap.php

  1. <?php
  3. require_once "vendor/autoload.php";
  5. use Doctrine\DBAL\DriverManager;
  7. $attrs = ['driver' => 'pdo_pgsql', 'host' => 'localhost', 'dbname' => 'testdb', 
  8.     'port' => 5432, 'user' => 'postgres', 'password' => 's$cret'];
  10. $conn = DriverManager::getConnection($attrs);

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

获取 PostgreSQL 版本

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

version.php

  1. <?php
  3. require_once "bootstrap.php";
  5. $queryBuilder = $conn->createQueryBuilder();
  6. $queryBuilder = $queryBuilder->select('version()');
  8. $version = $queryBuilder->execute()->fetchColumn(0);
  9. echo $version . "\n";

该示例显示 PostgreSQL 数据库的版本。

  1. $queryBuilder = $conn->createQueryBuilder();

在连接对象中，我们使用createQueryBuilder()创建查询生成器。

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

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

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

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

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

这是输出。

Doctrine QueryBuilder fetchall

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

featch_all.php

  1. <?php
  3. require_once "bootstrap.php";
  5. $queryBuilder = $conn->createQueryBuilder();
  6. $queryBuilder->select('*')->from('cars');
  8. $stm = $queryBuilder->execute();
  9. $data = $stm->fetchAll();
  11. $coll = collect($data);
  12. $sorted = $coll->sortBy('price');
  14. $sorted->each(function($item, $key) {
  15.     echo sprintf("Id: %d Name: %s Price: %d\n", $item['id'], $item['name'], $item['price']);
  16. });

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

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

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

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

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

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

我们使用 Laravel 集合对数据进行排序。

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

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

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

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

DocQuery QueryBuilder 表别名

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

table_alias.php

  1. <?php
  3. require_once "bootstrap.php";
  5. $queryBuilder = $conn->createQueryBuilder();
  7. $queryBuilder = $queryBuilder->select('*')->from('cars', 'c')
  8.     ->where('c.price < 30000');
  10. $selected = $queryBuilder->execute()->fetchAll();
  11. dump($selected);

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

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

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

  1. dump($selected);

我们用dump()输出数据。

  1. $ php table_alias.php
  2. array:4 [
  3.     0 => array:3 [
  4.     "id" => 3
  5.     "name" => "Skoda"
  6.     "price" => 9000
  7.     ]
  8.     1 => array:3 [
  9.     "id" => 4
  10.     "name" => "Volvo"
  11.     "price" => 29000
  12.     ]
  13.     2 => array:3 [
  14.     "id" => 6
  15.     "name" => "Citroen"
  16.     "price" => 21000
  17.     ]
  18.     3 => array:3 [
  19.     "id" => 8
  20.     "name" => "Volkswagen"
  21.     "price" => 21600
  22.     ]
  23. ]

有四辆价格低于 30000 的汽车。输出使用 Symfony 的自卸车很好地格式化。 输出也会在端子上着色。

Doctrine QueryBuilder setParameter

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

参数化查询用于保护代码免遭 SQL 注入并提高查询效率。

fetch_column.php

  1. <?php
  3. require_once "bootstrap.php";
  5. $id = 6;
  7. $queryBuilder = $conn->createQueryBuilder();
  9. $queryBuilder = $queryBuilder->select('name')->from('cars')
  10.     ->where('id = ?')->setParameter(0, $id);
  12. $car_name = $queryBuilder->execute()->fetchColumn(0);
  13. echo $car_name . "\n";

在示例中，我们获得的行具有 ID6。我们使用参数化查询来获取列。

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

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

  1. $ php fetch_column.php
  2. Citroen

This is the output.

Doctrine QueryBuilder orderBy

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

order_by.php

  1. <?php
  3. require_once "bootstrap.php";
  5. $queryBuilder = $conn->createQueryBuilder();
  6. $queryBuilder
  7.     ->select('*')
  8.     ->from('cars')
  9.     ->orderBy('name', 'desc');
  11. $stm = $queryBuilder->execute();
  12. $data = $stm->fetchAll();
  14. $coll = collect($data);
  16. $coll->each(function($item, $key) {
  17.     echo sprintf("id: %d name: %s price: %d\n", $item['id'], $item['name'], $item['price']);
  18. });

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

  1. $queryBuilder
  2.     ->select('*')
  3.     ->from('cars')
  4.     ->orderBy('name', 'desc');

数据已选择并排序。

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

This is the output.

Doctrine QueryBuilder WHERE

下面的示例演示如何使用WHERE IN子句构建查询。

where_in.php

  1. <?php
  3. require_once "bootstrap.php";
  5. use Doctrine\DBAL\Connection;
  7. $ids = [2, 4, 6];
  9. $queryBuilder = $conn->createQueryBuilder();
  11. $queryBuilder->select('*')->from('cars')
  12.     ->where('id IN (?)')->setParameter(0, $ids, Connection::PARAM_INT_ARRAY);
  13. $cars = $queryBuilder->execute()->fetchAll();
  15. $data = collect($cars);
  17. $data->each(function ($e) {
  18.     dump($e);
  19. });

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

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

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

  1. $ php where_in.php
  2. array:3 [
  3.     "id" => 2
  4.     "name" => "Mercedes"
  5.     "price" => 57127
  6. ]
  7. array:3 [
  8.     "id" => 4
  9.     "name" => "Volvo"
  10.     "price" => 29000
  11. ]
  12. array:3 [
  13.     "id" => 6
  14.     "name" => "Citroen"
  15.     "price" => 21000
  16. ]

This is the output.

Doctrine QueryBuilder andWhere

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

and_where.php

  1. <?php
  3. require_once "bootstrap.php";
  5. $minPrice = 10000;
  6. $maxPrice = 50000;
  8. $queryBuilder = $conn->createQueryBuilder();
  9. $queryBuilder
  10.     ->select('*')
  11.     ->from('cars')
  12.     ->where('price > ?')
  13.     ->andWhere('price < ?')
  14.     ->setParameter(0, $minPrice)
  15.     ->setParameter(1, $maxPrice);
  17. $stm = $queryBuilder->execute();
  18. $data = $stm->fetchAll();
  20. $coll = collect($data);
  22. $coll->each(function($item, $key) {
  23.     echo sprintf("id: %d name: %s price: %d\n", $item['id'], $item['name'], $item['price']);
  24. });

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

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

有四辆满足条件的汽车。

Doctrine QueryBuilder插入行

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

insert_row.php

  1. <?php
  3. require_once "bootstrap.php";
  5. $name = 'Oldsmobile';
  6. $price = 28800;
  8. $queryBuilder = $conn->createQueryBuilder();
  10. $queryBuilder = $queryBuilder->insert('cars')
  11.     ->values(['name' => '?', 'price' => '?'])
  12.     ->setParameters([0 => $name, 1 => $price]);
  13. $queryBuilder->execute();
  15. $sql = $queryBuilder->getSQL();
  16. echo "Executed: $sql\n";

该示例将新车插入cars表。

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

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

  1. $sql = $queryBuilder->getSQL();

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

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

输出显示生成的 SQL 语句。

Doctrine QueryBuilder删除

用delete()删除数据。

delete_rows.php

  1. <?php
  3. require_once "bootstrap.php";
  5. $name = 'Oldsmobile';
  6. $price = 26600;
  8. $queryBuilder = $conn->createQueryBuilder();
  10. $queryBuilder = $queryBuilder->delete('cars')
  11.     ->where('id IN (1, 2, 3)');
  13. $n = $queryBuilder->execute();
  15. echo "The query deleted $n rows\n";

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

  1. $n = $queryBuilder->execute();

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

  1. $ php delete_rows.php
  2. The query deleted 3 rows

This is the output.

Doctrine QueryBuilder更新行

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

udpate_row.php

  1. createQueryBuilder();
  3. $queryBuilder = $queryBuilder->update('cars')
  4.     ->set('price', $queryBuilder->createNamedParameter($price))
  5.     ->where('id = 9');
  7. $queryBuilder->execute();
  9. $sql = $queryBuilder->getSQL();
  10. echo "Executed: $sql\n";

该示例使用 ID 9 更新汽车的价格。

Symfony Doctrine 的例子

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

  1. $ composer create-project symfony/skeleton symfapp
  2. $ cd symfapp

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

  1. $ composer require symfony/orm-pack

我们安装 Doctrine。

  1. $ composer require maker --dev
  2. $ composer require server --dev

我们安装maker和server组件。

config/packages/doctrine.yaml

  1. doctrine:
  2.     dbal:
  3.         # configure these for your database server
  4.         driver: pdo_pgsql
  5.         charset: utf8

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

.env

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

在.env文件中，设置DATABASE_URL。

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

我们验证 PostgreSQL 设置。

  1. $ php bin/console make:controller HomeController

与制造商一起，我们创建一个新的控制器。

src/Controller/HomeController.php

  1. <?php
  3. namespace App\Controller;
  5. use Doctrine\DBAL\Connection;
  6. use Symfony\Component\Routing\Annotation\Route;
  7. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  9. class HomeController extends AbstractController
  10. {
  11.     /**
  12.     * @Route("/home", name="home")
  13.     */
  14.     public function index(Connection $conn)
  15.     {
  16.         $queryBuilder = $conn->createQueryBuilder();
  17.         $data = $queryBuilder->select('*')->from('cars')->execute()->fetchAll();
  19.         return $this->json($data);
  20.     }
  21. }

index()方法返回数据库表中的所有行。 请注意，出于简单原因，我们已将查询生成器放入控制器中。 在生产应用中，还应该有一个服务层和一个存储库。

  1. return $this->json($data);

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

  1. $ php bin/console server:run

我们运行开发服务器。

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

使用curl，我们生成一个对应用的请求。

您可能也对以下相关教程感兴趣： PHP PDO 教程， Symfony 简介， PHP 教程或列出所有 PHP 教程。

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