本文記錄如何在Symfony3中使用Doctrine DBAL進行CRUD操作,相關函數有prepare()executeUpdate()executeQuery()fetch()fetchAll()fetchColumn()delete()insert()update()delete()

Introduction

Symfony默認使用第三方庫Doctrine與數據庫交互。

Doctrine’s sole goal is to give you powerful tools to make database interactions easy and flexible. — Databases and Doctrine

但文檔中的示例並不是我所需要的,我需要的是能夠執行原生SQL語句的操作示例。

文檔中提到 >Doctrine is totally decoupled from Symfony and using it is optional. This chapter is all about the Doctrine ORM, which aims to let you map objects to a relational database (such as MySQL, PostgreSQL or Microsoft SQL). If you prefer to use raw database queries, this is easy, and explained in the “How to Use Doctrine DBAL” article.

You can also persist data to MongoDB using Doctrine ODM library. For more information, read the “DoctrineMongoDBBundle” documentation. — Databases and Doctrine

根據提示點開 How to Use Doctrine DBAL,其中有介紹

The Doctrine Database Abstraction Layer (DBAL) is an abstraction layer that sits on top of PDO and offers an intuitive and flexible API for communicating with the most popular relational databases. In other words, the DBAL library makes it easy to execute queries and perform other database actions. — How to Use Doctrine DBAL

也有簡單的示例

class UserController extends Controller
{
    public function indexAction()
    {
        $conn = $this->get('database_connection');
        $users = $conn->fetchAll('SELECT * FROM users');

        // ...
    }
}

但仍過於簡單,無法滿足我的需求。

文中有提到 >Read the official Doctrine DBAL Documentation to learn all the details and capabilities of Doctrine’s DBAL library.

本文便是根據 4. Data Retrieval And Manipulation的說明進行試驗後整理而成。

Preparation

Development Environment

開發環境信息

item content
OS CentOS Linux release 7.2.1511 (Core)
Kernel 3.10.0-327.28.3.el7.x86_64
Composer 1.2.0
PHP 5.6.24
Nginx 1.10.1
MariaDB 10.1.16

測試數據庫信息

item content
Host 127.0.0.1
Port 3306
Username symfony
Password 12345
Database arsenal

Configuration

Database User Creation

創建用戶命令

create user 'symfony'@'%' identified by '12345';
grant all on arsenal.* to 'symfony'@'%';
flush privileges;
MariaDB [(none)]> select Host,User from mysql.user where user like '%symfony%';
+------+---------+
| Host | User    |
+------+---------+
| %    | symfony |
+------+---------+
1 row in set (0.00 sec)

MariaDB [(none)]>

parameters.yml Configuration

根據 Configuring the Database 說明,在文件app/config/parameters.yml中修改數據庫配置參數

parameters:
    database_host: 127.0.0.1
    database_port: 3306
    database_name: arsenal
    database_user: symfony
    database_password: 12345

Setting Controller

在目錄src/AppBundle/Controller下的*Controller.php文件中,寫入如下信息

<?php

namespace AppBundle\Controller;

use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
// 必須添加
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Response;

// 此處的TestController可以是其它名稱,但必須與文件名成一致,同時須extend Controller

class TestController extends Controller
{
    public function indexAction()
    {
        $conn = $this->get('database_connection');

        // ...
    }
}

You can then access the Doctrine DBAL connection by accessing the database_connection service.

通過訪問database_connection服務訪問 DBAL,實現與數據庫的交互。

VarDumper

重要: 開發過程中需要對代碼、數據進行調試,在PHP中可使用print_r()var_dump()等函數進行輸出,但是在Symfony中無法直接使用,會報如下錯

The controller must return a response (null given). Did you forget to add a return statement somewhere in your controller?

可通過兩種方式進行輸出: 一種 是使用Doctrine提供的Debug功能

use Doctrine\Common\Util\Debug;

Debug::dump($object);

另一種 則是Symfony提供的VarDumper組件

PHP/Symfony2 - print_r on Object

Installation

執行如下命令安裝var-dumper

composer global require symfony/var-dumper

If using it inside a Symfony application, make sure that the DebugBundle is enabled in your app/AppKernel.php file.

查看文件app/AppKernel.php確保DebugBundle被啟用。

The VarDumper component creates a global dump() function that you can use instead of e.g. var_dump.

Usage

使用方式如下

class TestController extends Controller
{
    public function indexAction()
    {
        $conn = $this->get('database_connection');

        // ...
        return new Response(dump($object));
    }
}

在項目所在目錄中執行

php bin/console server:run

在瀏覽器URL中輸入對應的URL地址即可看到相關輸出信息。

CRUD

以下操作命令參考自Doctrine官方文檔 4. Data Retrieval And Manipulation

數據庫增刪改查,以測試數據庫arsenal中的department數據表為例:

MariaDB [(none)]> select id,name from arsenal.department where level=1 order by id;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | 總經理室        |
|  2 | 財務部          |
|  3 | 人事行政部      |
|  4 | 產品項目部      |
|  5 | 研發部          |
|  6 | 業務客服部      |
|  7 | 快巴項目部      |
|  8 | 技術工程部      |
|  9 | 客戶業務部      |
| 10 | IT運維部        |
+----+-----------------+
10 rows in set (0.00 sec)

MariaDB [(none)]>

注意: 使用DBAL時需要注意,enum類型只能指定其中的具體的string,不能用其十進制位置替代,否則無法獲取數據。

以下內容來自 Using Prepared Statements

prepare($sql) - Create a prepared statement of the type Doctrine\DBAL\Statement. Using this method is preferred if you want to re-use the statement to execute several queries with the same SQL statement only with different parameters.

executeQuery($sql, $params, $types) - Create a prepared statement for the passed SQL query, bind the given params with their binding types and execute the query. This method returns the executed prepared statement for iteration and is useful for SELECT statements.

executeUpdate($sql, $params, $types) - Create a prepared statement for the passed SQL query, bind the given params with their binding types and execute the query. This method returns the number of affected rows by the executed query and is useful for UPDATE, DELETE and INSERT statements.

bindValue($pos, $value, $type) - Bind a given value to the positional or named parameter in the prepared statement. bindParam($pos, &$param, $type) - Bind a given reference to the positional or named parameter in the prepared statement.

If you are finished with binding parameters you have to call execute() on the statement, which will trigger a query to the database. After the query is finished you can access the results of this query using the fetch API of a statement:

fetch($fetchStyle) - Retrieves the next row from the statement or false if there are none. Moves the pointer forward one row, so that consecutive calls will always return the next row.

fetchColumn($column) - Retrieves only one column of the next row specified by column index. Moves the pointer forward one row, so that consecutive calls will always return the next row.

fetchAll($fetchStyle) - Retrieves all rows from the statement.

Retrieve Data

Using Prepared Statements中提取數據的只有fetch($fetchStyle)fetchColumn($column)fetchAll($fetchStyle)三種。

  • fetch($fetchStyle) - Retrieves the next row from the statement or false if there are none. Moves the pointer forward one row, so that consecutive calls will always return the next row.
  • fetchColumn($column) - Retrieves only one column of the next row specified by column index. Moves the pointer forward one row, so that consecutive calls will always return the next row.
  • fetchAll($fetchStyle) - Retrieves all rows from the statement.

如果使用fetchAssoc()fetchArray()則會報如下錯

// fetchAssoc
Attempted to call an undefined method named "fetchAssoc" of class "Doctrine\DBAL\Statement".

// fetchArray
Attempted to call an undefined method named "fetchArray" of class "Doctrine\DBAL\Statement".

executeQuery

$id=1;

$conn = $this->get('database_connection');
$sql = 'select id,name from department where id = ?';

$statement = $conn->executeQuery($sql, array($id));
$result = $statement->fetch();
return new Response(dump($result));

返回結果

TestController.php on line 34:
array:2 [▼
  "id" => "1"
  "name" => "總經理室"
]

fetch

如果有多條數據,只返回最前面的一條

$id=5;
$level='top';
$conn = $this->get('database_connection');


$sql = 'select id,name from department where id < ? and level = ? order by id';
$stmt = $conn->prepare($sql);
$stmt->bindValue(1,$id);
$stmt->bindValue(2,$level);
$stmt->execute();
$result = $stmt->fetch();

return new Response(dump($result));

返回結果

TestController.php on line 44:
array:2 [▼
  "id" => "1"
  "name" => "總經理室"
]

只返回一條數據,實際應有4條返回數據

fetchAll

fetch()對比,返回所有符合條件的數據

$id=5;
$level='top';
$conn = $this->get('database_connection');

$sql = 'select id,name from department where id < :id and level = :level order by id';
$stmt = $conn->prepare($sql);
$stmt->bindValue('id',$id);
$stmt->bindValue('level',$level);
$stmt->execute();
$result = $stmt->fetchAll();

return new Response(dump($result));

返回數據

TestController.php on line 44:
array:4 [▼
  0 => array:2 [▼
    "id" => "1"
    "name" => "總經理室"
  ]
  1 => array:2 [▼
    "id" => "2"
    "name" => "財務部"
  ]
  2 => array:2 [▼
    "id" => "3"
    "name" => "人事行政部"
  ]
  3 => array:2 [▼
    "id" => "4"
    "name" => "產品項目部"
  ]
]

fetchColumn

返回獲得數據集中的第一條數據,如果未設置索引位置fetchColumn()(等效於fetchColumn(0),索引位置從0開始),則默認返回select語句中的第一個column。

如果要獲取第二個column,則使用fetchColumn(1),第三個使用fetchColumn(2),依次類推。

$id=5;
$level='top';
$conn = $this->get('database_connection');

$sql = 'select id,name,name_old from department where id < :id and level = :level order by id';
$stmt = $conn->prepare($sql);
$stmt->bindValue('id',$id);
$stmt->bindValue('level',$level);
$stmt->execute();
$result0 = $stmt->fetchColumn();
$result1 = $stmt->fetchColumn(1);
$result2 = $stmt->fetchColumn(2);
$result = [$result0,$result1,$result2];

return new Response(dump($result));

返回結果

TestController.php on line 47:
array:3 [▼
  0 => "1"
  1 => "財務部"
  2 => "測試數據"
]

Write Data

executeUpdate

返回操作執行後的影響行數

$id=5;
$conn = $this->get('database_connection');
$sql = 'update department set name_old = ? where id < ?';
$count = $conn->executeUpdate($sql, array('測試數據',$id));
return new Response(dump($count));

返回結果

TestController.php on line 36:
4
MariaDB [arsenal]> select id,name,name_old from department where level=1;
+----+-----------------+--------------+
| id | name            | name_old     |
+----+-----------------+--------------+
|  1 | 總經理室        | 測試數據     |
|  2 | 財務部          | 測試數據     |
|  3 | 人事行政部      | 測試數據     |
|  4 | 產品項目部      | 測試數據     |
|  5 | 研發部          | NULL         |
|  6 | 業務客服部      | NULL         |
|  7 | 快巴項目部      | NULL         |
|  8 | 技術工程部      | NULL         |
|  9 | 客戶業務部      | NULL         |
| 10 | IT運維部        | NULL         |
+----+-----------------+--------------+
10 rows in set (0.00 sec)

MariaDB [arsenal]>

Insert

$name = '測試數據';
$conn = $this->get('database_connection');

$sql = 'insert into department (name,parent_company) values (:name ,1)';
$stmt = $conn->prepare($sql);
$stmt->bindValue('name',$name);
$result = $stmt->execute();

return new Response(dump($result));

如果執行成功,則返回true

TestController.php on line 46:
true
last_insert_id

如果要獲取新插入數據的自增id,可通過函數last_insert_id()獲取

$name = '測試數據';
$conn = $this->get('database_connection');
$sql = 'insert into department (name,parent_company) values (:name ,1)';
$stmt = $conn->prepare($sql);
$stmt->bindValue('name',$name);
$result = $stmt->execute();

// 如果返回true代表數據insert成功
if ($result) {
    $lastid = $conn->prepare('select last_insert_id();');
    $lastid->execute();
    // 通過fetchColumn獲取數值
    $lastid = $lastid->fetchColumn();
}

return new Response(dump($lastid));

Update

$name = '測試數據';
$conn = $this->get('database_connection');
$sql = 'update department set name_old = :name_old where name = :name';
$stmt = $conn->prepare($sql);

$stmt->bindValue('name',$name);
$stmt->bindValue('name_old',$name);

$result = $stmt->execute();
return new Response(dump($result));

返回結果,執行成功返回true

TestController.php on line 54:
true
MariaDB [arsenal]> select id,name,name_old from department where name='測試數據';
+----+--------------+--------------+
| id | name         | name_old     |
+----+--------------+--------------+
| 29 | 測試數據     | 測試數據     |
+----+--------------+--------------+
1 row in set (0.00 sec)

MariaDB [arsenal]>

Delete

$name = '測試數據';
$conn = $this->get('database_connection');
$sql = 'delete from department where name = :name';
$stmt = $conn->prepare($sql);
$stmt->bindValue('name',$name);
$result = $stmt->execute();
return new Response(dump($result));

返回結果,執行成功返回true

TestController.php on line 47:
true
MariaDB [arsenal]> select id,name,name_old from department where name='測試數據';
Empty set (0.00 sec)

MariaDB [arsenal]> select id,name from department order by id desc limit 1;
+----+-----------------+
| id | name            |
+----+-----------------+
| 22 | 軟件測試組      |
+----+-----------------+
1 row in set (0.00 sec)

MariaDB [arsenal]>

Error Occuring

Error1

執行php bin/console doctrine:schema:update --force時提示

Unknown database type enum requested, Doctrine\DBAL\Platforms\MySqlPlatform may not support it.

參考How to Use Doctrine DBALRegistering custom Mapping Types in the SchemaTool在文件app/config/config.yml中進行如下增加如下參數

# app/config/config.yml
doctrine:
    dbal:
       mapping_types:
          enum: string

另見 FIXED: Doctrine 2 Unknown database type enum requested

Error 2

按照 Sharing your Database Connection Information設置session信息存儲在數據庫中,報如下錯

SQLSTATE[HY000]: General error: 1665 Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

參考MySQL Binary Logging Problem with InnoDB When Creating a Workflow中方案,在MariaDB配置文件/etc/my.cnf中將binlog格式更改為row

binlog_format=row

重啟mysql服務後正常。

References

Bibliography

Change Logs

  • 2016.08.24 12:39 Wed Aisa/Shanghai
    • 初稿完成

  • Note Time: 2016.08.24 12:39 Wed
  • Note Location: Asia/Shanghai
  • Writer: lempstacker