Doctrine DBAL 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

-- 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 的 dumper 和 Laravel 集合。 我们将在示例中使用它们。

$ composer dumpautoload

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

引导 Doctrine CLI 示例

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

bootstrap.php

<?php

require_once "vendor/autoload.php";

use Doctrine\DBAL\DriverManager;

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

conn = DriverManager::getConnection(attrs);

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

获取 PostgreSQL 版本

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

version.php

<?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()方法返回表中的所有行。

featch_all.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表中检索所有行。

$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列排序。

DocQuery QueryBuilder 表别名

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

table_alias.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 的所有汽车。

$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()用于将参数设置为查询占位符。 原则支持位置参数和命名参数。

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

fetch_column.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。我们使用参数化查询来获取列。

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

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

$ php fetch_column.php
Citroen

This is the output.

Doctrine QueryBuilder orderBy

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

order_by.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表中检索所有行，并按汽车名称降序对其进行排序。

$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

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

where_in.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 的汽车。

$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 andWhere

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

and_where.php

<?php

require_once "bootstrap.php";

minPrice = 10000;maxPrice = 50000;

queryBuilder =conn->createQueryBuilder();
queryBuilder
    ->select('*')
    ->from('cars')
    ->where('price>?')
    ->andWhere('priceminPrice)
    ->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()插入新行。

insert_row.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表。

$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()删除数据。

delete_rows.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 的行。

$n = $queryBuilder->execute();

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

$ php delete_rows.php
The query deleted 3 rows

学说 QueryBuilder 更新行

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

udpate_row.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 进行持久化。

$ 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组件。

config/packages/doctrine.yaml

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

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

.env

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

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

src/Controller/HomeController.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();

        returnthis->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，我们生成一个对应用的请求。

您可能也对以下相关教程感兴趣： PHP PDO 教程， Rakit 验证教程，PHP 教程。