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 數據庫。