您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP PDO 資料庫操作類：模仿 Spring JdbcTemplate 設計的現代化封裝

編輯：PHP 綜合知識

設計理念

參考 Java Spring 框架的 `JdbcTemplate`，封裝 PDO 常用操作，提供型別安全的查詢方法、自動資源管理、參數綁定防注入，簡化資料庫存取程式碼。

配置類：DbConfig

<?php
/**
 * 資料庫配置類 - 單例模式管理 DSN 與認證資訊
 */
class DbConfig {
    private static string $driver   = 'mysql';
    private static string $host     = '127.0.0.1';
    private static string $port     = '3306';
    private static string $username = '';
    private static string $password = '';
    private static string $database = '';
    private static string $charset  = 'utf8mb4';
    private static ?string $dsn     = null;
    
    // 靜態存取器
    public static function setDriver(string $v): void    { self::$driver = $v; }
    public static function setHost(string $v): void      { self::$host = $v; }
    public static function setPort(string $v): void      { self::$port = $v; }
    public static function setUsername(string $v): void  { self::$username = $v; }
    public static function setPassword(string $v): void  { self::$password = $v; }
    public static function setDatabase(string $v): void  { self::$database = $v; }
    public static function setCharset(string $v): void   { self::$charset = $v; }
    
    public static function getDsn(): string {
        if (self::$dsn === null) {
            self::$dsn = sprintf(
                '%s:host=%s;port=%s;dbname=%s;charset=%s',
                self::$driver, self::$host, self::$port, self::$database, self::$charset
            );
        }
        return self::$dsn;
    }
    
    public static function getUsername(): string { return self::$username; }
    public static function getPassword(): string { return self::$password; }
    
    // 一次性設定（適用於啟動引導）
    public static function configure(array $config): void {
        foreach ($config as $key => $value) {
            $method = 'set' . ucfirst(strtolower($key));
            if (method_exists(self::class, $method)) {
                self::$method($value);
            }
        }
        self::$dsn = null; // 重置 DSN 快取
    }
}

核心操作類：DbTemplate

<?php
/**
 * PDO 資料庫操作範本類
 * 提供：查詢多筆/單筆、標量查詢、更新執行、事務支援
 */
class DbTemplate {
    private PDO $pdo;
    
    public function __construct(?PDO $pdo = null) {
        $this->pdo = $pdo ?? $this->createConnection();
    }
    
    private function createConnection(): PDO {
        $pdo = new PDO(
            DbConfig::getDsn(),
            DbConfig::getUsername(),
            DbConfig::getPassword(),
            [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,     // 使用真實預處理
                PDO::ATTR_STRINGIFY_FETCHES  => false,     // 保持原生型別
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true // MySQL 緩衝查詢
            ]
        );
        return $pdo;
    }
    
    // ==================== 查詢方法 ====================
    
    /**
     * 查詢多筆記錄
     * @return array>
     */
    public function queryRows(string $sql, array $params = []): array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    /**
     * 查詢單筆記錄
     * @return array|null
     */
    public function queryRow(string $sql, array $params = []): ?array {
        $rows = $this->queryRows($sql, $params);
        return $rows ? $rows[0] : null;
    }
    
    /**
     * 查詢單一欄位整數
     */
    public function queryForInt(string $sql, array $params = []): ?int {
        $row = $this->queryRow($sql, $params);
        return $row ? (int)array_values($row)[0] : null;
    }
    
    /**
     * 查詢單一欄位浮點數
     */
    public function queryForFloat(string $sql, array $params = []): ?float {
        $row = $this->queryRow($sql, $params);
        return $row ? (float)array_values($row)[0] : null;
    }
    
    /**
     * 查詢單一欄位雙精度浮點數
     */
    public function queryForDouble(string $sql, array $params = []): ?float {
        $row = $this->queryRow($sql, $params);
        return $row ? (double)array_values($row)[0] : null;
    }
    
    /**
     * 查詢單一欄位任意型別（保持資料庫原始型別）
     */
    public function queryForObject(string $sql, array $params = []): mixed {
        $row = $this->queryRow($sql, $params);
        return $row ? array_values($row)[0] : null;
    }
    
    /**
     * 查詢單一欄位字串
     */
    public function queryForString(string $sql, array $params = []): ?string {
        $val = $this->queryForObject($sql, $params);
        return $val !== null ? (string)$val : null;
    }
    
    // ==================== 更新/寫入方法 ====================
    
    /**
     * 執行更新（INSERT/UPDATE/DELETE）
     * @return int 影響行數
     */
    public function update(string $sql, array $params = []): int {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
    
    /**
     * 執行插入並回傳自增 ID
     */
    public function insert(string $sql, array $params = []): string|int {
        $this->update($sql, $params);
        return $this->pdo->lastInsertId();
    }
    
    /**
     * 批次執行（同一 SQL，多組參數）
     * @param array $paramsList
     * @return int 總影響行數
     */
    public function batchUpdate(string $sql, array $paramsList): int {
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare($sql);
            $total = 0;
            foreach ($paramsList as $params) {
                $stmt->execute($params);
                $total += $stmt->rowCount();
            }
            $this->pdo->commit();
            return $total;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
    
    // ==================== 事務支援 ====================
    
    public function beginTransaction(): void {
        $this->pdo->beginTransaction();
    }
    
    public function commit(): void {
        $this->pdo->commit();
    }
    
    public function rollBack(): void {
        $this->pdo->rollBack();
    }
    
    /**
     * 事務執行範本：自動提交/回滾
     * @param callable(DbTemplate): mixed $callback
     * @return mixed 回傳 callback 結果
     */
    public function transactional(callable $callback): mixed {
        $this->beginTransaction();
        try {
            $result = $callback($this);
            $this->commit();
            return $result;
        } catch (Throwable $e) {
            $this->rollBack();
            throw $e;
        }
    }
    
    // ==================== 進階查詢 ====================
    
    /**
     * 分頁查詢
     * @return array{data: array, total: int, page: int, pageSize: int, totalPages: int}
     */
    public function queryPaginated(
        string $selectSql, 
        string $countSql, 
        array $params = [], 
        int $page = 1, 
        int $pageSize = 20
    ): array {
        $total = $this->queryForInt($countSql, $params) ?? 0;
        $offset = ($page - 1) * $pageSize;
        
        $data = $total > 0 
            ? $this->queryRows($selectSql . " LIMIT $offset, $pageSize", $params)
            : [];
            
        return [
            'data'       => $data,
            'total'      => $total,
            'page'       => $page,
            'pageSize'   => $pageSize,
            'totalPages' => (int)ceil($total / $pageSize)
        ];
    }
    
    /**
     * 取得 PDO 實例（進階操作）
     */
    public function getPdo(): PDO {
        return $this->pdo;
    }
}

使用範例

<?php
// 啟動時設定
DbConfig::configure([
    'driver'   => 'mysql',
    'host'     => '127.0.0.1',
    'port'     => '3306',
    'database' => 'myapp',
    'username' => 'app_user',
    'password' => 'secret',
    'charset'  => 'utf8mb4'
]);

$db = new DbTemplate();

// 1. 查詢多筆
$users = $db->queryRows("SELECT id, name, email FROM users WHERE status = ? ORDER BY created_at DESC", [1]);
foreach ($users as $user) {
    echo "{$user['name']} <{$user['email']}>\n";
}

// 2. 查詢單筆
$user = $db->queryRow("SELECT * FROM users WHERE id = ?", [1001]);
if ($user) {
    echo "找到用戶: {$user['name']}\n";
}

// 3. 標量查詢
$count    = $db->queryForInt("SELECT COUNT(*) FROM users WHERE status = ?", [1]);
$avgScore = $db->queryForFloat("SELECT AVG(score) FROM exams WHERE user_id = ?", [1001]);
$setting  = $db->queryForString("SELECT value FROM settings WHERE `key` = ?", ['site_name']);

// 4. 更新操作
$affected = $db->update(
    "UPDATE users SET last_login = NOW(), login_count = login_count + 1 WHERE id = ?",
    [1001]
);
echo "更新了 $affected 筆資料\n";

// 5. 插入取得 ID
$newId = $db->insert(
    "INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())",
    ['Alice', 'alice@example.com']
);
echo "新用戶 ID: $newId\n";

// 6. 事務範例：轉帳
try {
    $db->transactional(function($db) {
        $db->update("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
        $db->update("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");
        // 記錄流水
        $db->insert("INSERT INTO transactions (from_id, to_id, amount, created_at) VALUES (1, 2, 100, NOW())");
    });
    echo "轉帳成功";
} catch (Throwable $e) {
    echo "轉帳失敗: " . $e->getMessage();
}

// 7. 分頁查詢
$result = $db->queryPaginated(
    "SELECT id, title, created_at FROM articles WHERE status = 1 ORDER BY created_at DESC",
    "SELECT COUNT(*) FROM articles WHERE status = 1",
    [],
    page: 2,
    pageSize: 10
);
echo "第 {$result['page']} 頁，共 {$result['totalPages']} 頁，總計 {$result['total']} 筆\n";

關鍵設計決策解析

決策	說明	優點
ATTR_EMULATE_PREPARES = false	使用真實預處理語句	防 SQL 注入、型別安全、效能較好
ATTR_STRINGIFY_FETCHES = false	保持數值型別不轉字串	型別正確、JSON 序列化無多餘引號
ATTR_ERRMODE = EXCEPTION	錯誤拋出例外	統一錯誤處理、避免靜默失敗
單一職責方法	queryForInt/Float/String 等	呼叫端型別明確、減少手動轉型
transactional() 範本方法	自動管理事務邊界	減少樣板碼、確保正確提交/回滾