Doctrine DBAL QueryBuilder 教程 · ZetCode 中文系列教程

# Doctrine DBAL `QueryBuilder`教程 > 原文： [https://zetcode.com/doctrine/querybuilder/](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` ```php -- 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 和一些辅助工具。 ```php $ composer req doctrine/dbal ``` 我们安装 Doctrine。请注意，DBAL 层包含在`doctrine/dbal`包中。 ```php $ composer req symfony/var-dumper $ composer req tightenco/collect ``` 我们安装了 Symfony 的 dumper 和 Laravel 集合。我们将在示例中使用它们。 ```php $ composer dumpautoload ``` 我们生成项目中需要包含的所有类的列表。 `composer`重新读取`composer.json`文件以建立要自动加载的文件列表。 ## 引导 Doctrine CLI 示例我们创建一个引导文件，该文件将包含在所有示例中。 `bootstrap.php` ```php <?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 的版本。 `version.php` ```php <?php require_once "bootstrap.php"; $queryBuilder = $conn->createQueryBuilder(); $queryBuilder = $queryBuilder->select('version()'); $version = $queryBuilder->execute()->fetchColumn(0); echo $version . "\n"; ``` 该示例显示 PostgreSQL 数据库的版本。 ```php $queryBuilder = $conn->createQueryBuilder(); ``` 在连接对象中，我们使用`createQueryBuilder()`创建查询生成器。 ```php $queryBuilder = $queryBuilder->select('version()'); ``` 我们用`select()`执行`version()`函数。 ```php $version = $queryBuilder->execute()->fetchColumn(0); ``` 我们执行查询并使用`fetchColumn()`获取结果。注意方法调用的链接；这称为流利的 API。 ```php $ php version.php PostgreSQL 11.1, compiled by Visual C++ build 1914, 64-bit ``` 这是输出。 ## Doctrine `QueryBuilder` `fetchall` `fetchall()`方法返回表中的所有行。 `featch_all.php` ```php <?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`表中检索所有行。 ```php $queryBuilder->select('*')->from('cars'); ``` 我们从`cars`表中选择所有行。 `select()`将列名显示为参数。 ```php $stm = $queryBuilder->execute(); $data = $stm->fetchAll(); ``` 我们执行查询并使用`fetchAll()`获取所有行。 ```php $coll = collect($data); $sorted = $coll->sortBy('price'); ``` 我们使用 Laravel 集合对数据进行排序。 ```php $sorted->each(function($item, $key) { echo sprintf("Id: %d Name: %s Price: %d\n", $item['id'], $item['name'], $item['price']); }); ``` 排序后的数据将打印到控制台。 ```php $ 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`列排序。 ## DocQuery `QueryBuilder` 表别名我们可以给数据库表起别名。当表名很长并且我们使用多个表时，这很有用。 `table_alias.php` ```php <?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 的所有汽车。 ```php $queryBuilder = $queryBuilder->select('*')->from('cars', 'c') ->where('c.price < 30000'); ``` 我们给`cars`表别名`c`。稍后，我们通过别名引用该表。 ```php dump($selected); ``` 我们用`dump()`输出数据。 ```php $ 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()`用于将参数设置为查询占位符。原则支持位置参数和命名参数。参数化查询用于保护代码免遭 SQL 注入并提高查询效率。 `fetch_column.php` ```php <?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"; ``` 在示例中，我们获得的行具有 ID6。我们使用参数化查询来获取列。 ```php $queryBuilder = $queryBuilder->select('name')->from('cars') ->where('id = ?')->setParameter(0, $id); ``` 占位符用`?`字符标识。此类型称为位置参数。使用`setParameter()`，我们将值映射到占位符。 ```php $ php fetch_column.php Citroen ``` This is the output. ## Doctrine `QueryBuilder` `orderBy` 数据可以通过`orderBy()`进行排序。有时我们无法控制数据发送给我们的方式；在这种情况下，我们可以使用 Laravel 集合对数据进行排序，就像在获取所有示例中所做的那样。 `order_by.php` ```php <?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`表中检索所有行，并按汽车名称降序对其进行排序。 ```php $queryBuilder ->select('*') ->from('cars') ->orderBy('name', 'desc'); ``` 数据已选择并排序。 ```php $ 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 ``` This is the output. ## Doctrine `QueryBuilder` `WHERE` 下面的示例演示如何使用`WHERE IN`子句构建查询。 `where_in.php` ```php <?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 的汽车。 ```php $queryBuilder->select('*')->from('cars') ->where('id IN (?)')->setParameter(0, $ids, Connection::PARAM_INT_ARRAY); ``` 我们需要告诉 Doctrine 我们使用带有`Connection::PARAM_INT_ARRAY`标志的数组作为参数。 ```php $ 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 ] ``` This is the output. ## Doctrine `QueryBuilder` `andWhere` 我们可以通过添加`andWhere()`来合并`WHERE`子句。 `and_where.php` ```php <?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 $ 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()`插入新行。 `insert_row.php` ```php <?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`表。 ```php $queryBuilder = $queryBuilder->insert('cars') ->values(['name' => '?', 'price' => '?']) ->setParameters([0 => $name, 1 => $price]); ``` 可以使用`setParameters()`指定多个参数。 ```php $sql = $queryBuilder->getSQL(); ``` `getSQL()`获取由 Doctrine 生成的 SQL 语句。 ```php $ php insert_row.php Executed: INSERT INTO cars (name, price) VALUES(?, ?) ``` 输出显示生成的 SQL 语句。 ## Doctrine `QueryBuilder`删除用`delete()`删除数据。 `delete_rows.php` ```php <?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 的行。 ```php $n = $queryBuilder->execute(); ``` `execute()`方法返回已删除的行数。 ```php $ php delete_rows.php The query deleted 3 rows ``` This is the output. ## Doctrine `QueryBuilder`更新行用`udpate()`和`set()`更新一行。 `udpate_row.php` ```php 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 进行持久化。 ```php $ composer create-project symfony/skeleton symfapp $ cd symfapp ``` 我们创建一个新的 Symfony 骨架应用。 ```php $ composer require symfony/orm-pack ``` 我们安装 Doctrine。 ```php $ composer require maker --dev $ composer require server --dev ``` 我们安装`maker`和`server`组件。 `config/packages/doctrine.yaml` ```php doctrine: dbal: # configure these for your database server driver: pdo_pgsql charset: utf8 ``` 我们将 Doctrine 配置为使用 PostgreSQL。默认情况下，Symfony 具有 MySQL 的配置。 `.env` ```php ... DATABASE_URL=pgsql://postgres:s$cret@127.0.0.1:5432/testdb ``` 在`.env`文件中，设置`DATABASE_URL`。 ```php $ 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 $ php bin/console make:controller HomeController ``` 与制造商一起，我们创建一个新的控制器。 `src/Controller/HomeController.php` ```php <?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()`方法返回数据库表中的所有行。请注意，出于简单原因，我们已将查询生成器放入控制器中。在生产应用中，还应该有一个服务层和一个存储库。 ```php return $this->json($data); ``` 数据以 JSON 格式发送回客户端。 ```php $ php bin/console server:run ``` 我们运行开发服务器。 ```php $ 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`，我们生成一个对应用的请求。您可能也对以下相关教程感兴趣： [PHP PDO 教程](/php/pdo/)， [Symfony 简介](/symfony/intro/)， [PHP 教程](/lang/php/)或列出[所有 PHP 教程](/all/#php)。在本教程中，我们使用了 Doctrine `QueryBuilder`和 PostgreSQL 数据库。