萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP PDO 資料庫操作完整實現:單例連線與 CRUD 封裝

前言

PDO (PHP Data Objects) 提供統一的資料庫存取介面,支援多種資料庫驅動。本文展示一套完整的 PDO 封裝,包含單例模式連線管理、欄位映射防注入、以及 CRUD 核心操作方法。

環境需求

  • PHP 5.1+
  • php.ini 啟用 extension=pdo_mysql(或對應資料庫驅動)

核心類別:DPDO - 單例資料庫連線

<?php
class DPDO {
    private $DSN;
    private $DBUser;
    private $DBPwd;
    private $longLink;
    private $pdo;
    
    // 私有建構子,防止直接實例化
    private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) {
        $this->DSN = $dsn;
        $this->DBUser = $DBUser;
        $this->DBPwd = $DBPwd;
        $this->longLink = $longLink;
        $this->connect();
    }
    
    // 防止複製
    private function __clone() {}
    
    // 靜態工廠方法,支援多連線配置
    static public function instance($dsn, $DBUser, $DBPwd, $longLink = false) {
        static $singleton = array();
        $singIndex = md5($dsn . $DBUser . $DBPwd . $longLink);
        
        if (empty($singleton[$singIndex])) {
            $singleton[$singIndex] = new self($dsn, $DBUser, $DBPwd, $longLink);
        }
        return $singleton[$singIndex]->pdo;
    }
    
    private function connect() {
        try {
            $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
            if ($this->longLink) {
                $options[PDO::ATTR_PERSISTENT] = true;
            }
            $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd, $options);
            $this->pdo->query('SET NAMES UTF8');
        } catch (PDOException $e) {
            die('Connection Error: ' . $e->getMessage());
        }
    }
}

欄位映射輔助方法(防 SQL 注入核心)

<?php
// 批次欄位映射:用於 INSERT/UPDATE 的 SET 子句或 WHERE 條件
public function FDFields($data, $link = ',', $judge = array(), $aliasTable = '') {
    $sql = '';
    $mapData = array();
    foreach ($data as $key => $value) {
        $mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key;
        $sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link;
        $mapData[$mapIndex] = $value;
    }
    return array(trim($sql, $link), $mapData);
}

// 單一欄位映射
public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') {
    $mapIndex = ':' . $preMap . $aliasTable . $field;
    $sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex;
    return array($sql, array($mapIndex => $value));
}

// 條件組合處理
public function FDCondition($condition, $mapData) {
    if (is_string($condition)) {
        return array($condition, $mapData);
    }
    if (is_array($condition)) {
        $where = $condition['str'] ?? '';
        if (isset($condition['data']) && is_array($condition['data'])) {
            $link = $condition['link'] ?? 'AND';
            list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge'] ?? array());
            if ($conSql) {
                $where .= ($where ? ' ' . $link . ' ' : '') . $conSql;
                $mapData = array_merge($mapData, $mapConData);
            }
        }
        return array($where, $mapData);
    }
    return array('', $mapData);
}

CRUD 核心操作實現 (DB.php)

<?php
class DB {
    private $pdo;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    // 單筆查詢
    public function fetch($sql, $params = array(), $mode = PDO::FETCH_ASSOC) {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch($mode);
    }
    
    // 多筆查詢(支援分頁)
    public function fetchAll($sql, $params = array(), $limit = array(0, 10), $mode = PDO::FETCH_ASSOC) {
        $sql .= ' LIMIT ' . (int)$limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll($mode);
    }
    
    // 新增資料
    public function insert($table, $data, $returnId = false) {
        list($fields, $values) = $this->buildFieldValue($data);
        $sql = "INSERT INTO `$table` ($fields) VALUES ($values)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        return $returnId ? $this->pdo->lastInsertId() : $stmt->rowCount();
    }
    
    // 更新資料
    public function update($table, $data, $where, $whereParams = array()) {
        list($setClause, $params) = $this->FDFields($data, ',');
        $sql = "UPDATE `$table` SET $setClause WHERE $where";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute(array_merge($params, $whereParams));
    }
    
    // 刪除資料
    public function delete($table, $where, $params = array()) {
        $sql = "DELETE FROM `$table` WHERE $where";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($params);
    }
    
    // 執行任意 SQL(非查詢)
    public function exec($sql, $params = array()) {
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($params);
    }
    
    private function buildFieldValue($data) {
        $fields = array_keys($data);
        $placeholders = ':' . implode(', :', $fields);
        return array('`' . implode('`, `', $fields) . '`', $placeholders);
    }
}

使用範例

<?php
// 取得 PDO 實例
$pdo = DPDO::instance('mysql:host=localhost;dbname=test', 'user', 'pass');
$db = new DB($pdo);

// 新增
$id = $db->insert('users', array(
    'username' => 'john',
    'email' => 'john@example.com',
    'created_at' => date('Y-m-d H:i:s')
), true);

// 查詢
$user = $db->fetch('SELECT * FROM users WHERE id = :id', array(':id' => $id));

// 更新
$db->update('users', array('email' => 'new@example.com'), 'id = :id', array(':id' => $id));

// 刪除
$db->delete('users', 'id = :id', array(':id' => $id));

// 複雜查詢
$users = $db->fetchAll(
    'SELECT * FROM users WHERE status = :status ORDER BY created_at DESC',
    array(':status' => 1),
    array(0, 20)
);

設計優勢

  • 單例模式:避免重複建立連線,支援長連線選項
  • 參數綁定:所有查詢使用 Prepared Statements,徹底防止 SQL 注入
  • 欄位映射:自動產生 SQL 片段與參數陣列,減少手寫 SQL 錯誤
  • 異常處理:PDO 錯誤模式設為 Exception,便於統一錯誤處理
  • 編碼設定:自動執行 SET NAMES UTF8 避免中文亂碼

此架構適合中大型專案,提供清晰的資料存取層,便於維護與擴充。

copyright © 萬盛學電腦網 all rights reserved