寫一個“特殊”的查詢構造器 – (七、DML 語句、事務)

MrQin發表於2019-02-16

查詢語句 (DQL) 的構造功能開發完畢，我們再給查詢構造器增加一些對 DML (Data Manipulation Language) 語句的支援，如簡單的 insert、update、delete 操作。

insert

我們先回顧下 PDO 原生的 insert 操作怎麼進行：

// 預編譯
$pdoSt = $pdo->prepare("INSERT INTO test_table (`username`, `age`) VALUES (:username, :age);");
// 繫結引數
$pdoSt->bindValue(`:username`, `Jack`, PDO::PARAM_STR)
$pdoSt->bindValue(`:age`, 18, PDO::PARAM_INT)
// 執行
$pdoSt->execute(); 
// 獲取執行資料
$count = $pdoSt->rowCount(); // 返回被影響的行數
$lastId = $pdo->lastInsertId(); // 獲取最後插入行的 ID

資料插入

和查詢語句的執行過程並沒有太大差別，只是語法不同。回想第二篇，我們新建了 _buildQuery() 方法去構造最終的 SQL，對於 insert，我們在基類新建 _buildInsert() 方法：

protected function _buildInsert()
{
    $this->_prepare_sql = `INSERT INTO `.$this->_table.$this->_insert_str;
}

基類新增 _insert_str 屬性：

protected $_insert_str = ``;

修改 _reset() 函式，將 _insert_str 屬性的初始化過程新增進去：

protected function _reset()
{
    $this->_table = ``;
    $this->_prepare_sql = ``;
    $this->_cols_str = ` * `;
    $this->_where_str = ``;
    $this->_orderby_str = ``;
    $this->_groupby_str = ``;
    $this->_having_str = ``;
    $this->_join_str = ``;
    $this->_limit_str = ``;
    $this->_insert_str = ``; // 重置 insert 語句
    $this->_bind_params = [];
}

基類中新增 insert() 方法：

public function insert(array $data)
{
    // 構建字串
    $field_str = ``;
    $value_str = ``;
    foreach ($data as $key => $value) {
        $field_str .= ` `.self::_wrapRow($key).`,`;
        $plh = self::_getPlh(); // 生產佔位符
        $this->_bind_params[$plh] = $value; //儲存繫結資料
        $value_str .= ` `.$plh.`,`;
    }
    // 清除右側多餘的逗號
    $field_str = rtrim($field_str, `,`);
    $value_str = rtrim($value_str, `,`);

    $this->_insert_str = ` (`.$field_str.`) VALUES (`.$value_str.`) `;
    // 構造 insert 語句
    $this->_buildInsert();
    // 執行
    $this->_execute();
    // 獲取影響的行數
    return $this->_pdoSt->rowCount();
}

對上述程式碼，我們申明瞭 insert() 方法的引數是一個鍵值陣列，用來傳入要插入的欄位、值對映。預設返回被影響的行數 (比較通用)。

測試

試著插入一條資料吧：


$insert_data = [
    `username` => `jack`,
    `age`      => 18,
];

$results = $driver->table(`test_table`)->insert($insert_data);

獲取最後插入行的 ID

當一個表中有自增 id 且為主鍵時，這個 id 可以被看作區分資料的唯一標識。而在插入一條資料後獲取這條新增資料的 id 也是常見的業務需求。

PDO 提供了一個簡單的獲取最後插入行的 ID 的方法 PDO::lastInsertId() 供我們使用。

基類新增 insertGetLastId() 方法：

public function insertGetLastId(array $data)
{
    $this->insert($data);

    return $this->_pdo->lastInsertId();
}

測試：

$insert_data = [
    `username` => `jack`,
    `age`      => 18,
];

$lastId = $driver->table(`test_table`)->insertGetLastId($insert_data);

個體差異

然而，上述的 insertGetLastId() 方法在 PostgreSql 中並不奏效。PostgreSql 中，使用 PDO::lastInsertId() 獲取結果需要傳入正確的自增序列名 (PostgreSQL 中建立表時，如果使用 serial 型別，預設生成的自增序列名為：表名 + + 欄位名 + + seq)。【1】

但是這個方式並不好用，因為訪問 insertGetLastId() 方法時必須手動傳入這個序列名稱，這樣 insertGetLastId() 方法對底層的依賴嚴重，比如當底層驅動從 postgresql 換到 mysql 時，需要更改上層應用。而我們希望無論是 mysql 還是 postgresql，上層應用呼叫 insertGetLastId() 方法時是無差別的，即底層對上層透明。

為了解決這個問題，就需要用到 postgresql 的 returning 語法了。postgresql 中 insert、update 和 delete 操作都有一個可選的 returning 子句，可以指定最後執行的欄位進行返回，返回的資料可以像 select 一樣取結果。【2】

對於我們返回最後插入行的 ID 的需求，只需 returning id 就好。

當然，基類的 insertGetLastId() 方法對於 postgresql 而言已經無效了，我們在 Pgsql 驅動類中重寫 insertGetLastId() 方法：

public function insertGetLastId(array $data)
{
    // 構建語句字串、繫結資料
    $field_str = ``;
    $value_str = ``;
    foreach ($data as $key => $value) {
        $field_str .= ` `.self::_wrapRow($key).`,`;
        $plh = self::_getPlh();
        $this->_bind_params[$plh] = $value;
        $value_str .= ` `.$plh.`,`;
    }

    $field_str = rtrim($field_str, `,`);
    $value_str = rtrim($value_str, `,`);
    // 使用 returning 子句返回 id
    $this->_insert_str = ` (`.$field_str.`) VALUES (`.$value_str.`) RETURNING id `;
    // execute
    $this->_buildInsert();
    $this->_execute();
    // 使用 returning 子句後，可以像使用 SELECT 一樣獲取一個 returning 指定欄位的結果集。 
    $result = $this->_pdoSt->fetch(PDO::FETCH_ASSOC);
    // 返回 id
    return $result[`id`];
}

OK，我們再來測試看看，是不是就好用了呢？

update

做完 insert，update 就很簡單了，不同的是為了防止全域性更新的失誤發生，update 構造時強行要求使用 where 子句。

同樣的，新增 _update_str 屬性，修改 _reset() 函式：

protected $_update_str = ``;

...

protected function _reset()
{
    $this->_table = ``;
    $this->_prepare_sql = ``;
    $this->_cols_str = ` * `;
    $this->_where_str = ``;
    $this->_orderby_str = ``;
    $this->_groupby_str = ``;
    $this->_having_str = ``;
    $this->_join_str = ``;
    $this->_limit_str = ``;
    $this->_insert_str = ``;
    $this->_update_str = ``;
    $this->_bind_params = [];
}

構造 update 語句的方法：

protected function _buildUpdate()
{
    $this->_prepare_sql = `UPDATE `.$this->_table.$this->_update_str.$this->_where_str;
}

基類中新增 update() 方法：

public function update(array $data)
{
    // 檢測有沒有設定 where 子句
    if(empty($this->_where_str)) {
        throw new InvalidArgumentException("Need where condition");
    }
    // 構建語句、引數繫結
    $this->_update_str = ` SET `;
    foreach ($data as $key => $value) {
        $plh = self::_getPlh();
        $this->_bind_params[$plh] = $value;
        $this->_update_str .= ` `.self::_wrapRow($key).` = `.$plh.`,`;
    }

    $this->_update_str = rtrim($this->_update_str, `,`);

    $this->_buildUpdate();
    $this->_execute();
    // 返回影響的行數
    return $this->_pdoSt->rowCount();
}

更新資料示例：

$update_data = [
    `username` => `lucy`,
    `age`      => 22,
];

$results = $driver->table(`test_table`)
            ->where(`username`, `jack`)
            ->update($update_data);

delete

相比 insert、update，delete 語句更為簡單，只需 where 子句即可。和 update 一樣，需要防止誤操作刪除所有資料。

構造 delete 語句的方法：

protected function _buildDelete()
{
    $this->_prepare_sql = `DELETE FROM `.$this->_table.$this->_where_str;
}

基類中新增 delete() 方法：

public function delete()
{
    // 檢測有沒有設定 where 子句
    if(empty($this->_where_str)) {
        throw new InvalidArgumentException("Need where condition");
    }

    $this->_buildDelete();
    $this->_execute();

    return $this->_pdoSt->rowCount();
}

刪除資料示例：

$results = $driver->table(`test_table`)
            ->where(`age`, 18)
            ->delete();

事務

既然有了 DML 操作，那麼就少不了事務。對於事務，我們可以直接使用 PDO 提供的 PDO::beginTransaction()、PDO::commit()、PDO::rollBack()、PDO::inTransaction() 方法來實現。

基類新增 beginTrans() 方法：

// 開始事務
public function beginTrans()
{
    try {
        return $this->_pdo->beginTransaction();
    } catch (PDOException $e) {
        // 斷線重連
        if ($this->_isTimeout($e)) {

            $this->_closeConnection();
            $this->_connect();

            try {
                return $this->_pdo->beginTransaction();
            } catch (PDOException $e) {
                throw $e;
            }

        } else {
            throw $e;
        }
    }
}

注：因為 PDO::beginTransaction() 也是和 PDO::prepare() 一樣會連線資料庫的方法，所以需要做斷線重連的操作。

commitTrans() 方法：

// 提交事務
public function commitTrans()
{
    return $this->_pdo->commit(); 
}

rollBackTrans() 方法：

// 回滾事務
public function rollBackTrans()
{
    if ($this->_pdo->inTransaction()) {
        // 如果已經開始了事務，則執行回滾操作
        return $this->_pdo->rollBack();
    }
}

事務使用示例：

// 註冊事務
$driver->beginTrans();
$results = $driver->table(`test_table`)
            ->where(`age`, 18)
            ->delete();
$driver->commitTrans(); // 確認刪除

// 回滾事務
$driver->beginTrans();
$results = $driver->table(`test_table`)
            ->where(`age`, 18)
            ->delete();
$driver->rollBackTrans(); // 撤銷刪除

參考

【1】PHP Manual – PDO::lastInsertId

【2】PostgreSQL Documentation – Returning Data From Modified Rows

相關文章