PHP 封裝資料庫操作類完整實作：Configuration、DB、Model 三層架構範例

前言

將資料庫操作封裝為可重用類別，是從「寫腳本」邁向「工程化」的關鍵步驟。本文示範 Configuration → DB Core → Model → Controller 四層分離架構，採用 PDO 現代寫法。

一、配置檔：configuration.php

<?php
// configuration.php
return [
    'db' => [
        'host'     => '127.0.0.1',
        'port'     => 3306,
        'dbname'   => 'myapp',
        'username' => 'app_user',
        'password' => 'secure_password',
        'charset'  => 'utf8mb4',
        'options'  => [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::ATTR_PERSISTENT         => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
        ],
    ],
    'timezone' => 'Asia/Taipei',
];
?>

二、核心資料庫類：Database.php (單例模式)

<?php
// Database.php
class Database {
    private static $instance = null;
    private $pdo;

    private function __construct(array $config) {
        $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['dbname']};charset={$config['charset']}";
        $this->pdo = new PDO($dsn, $config['username'], $config['password'], $config['options'] ?? []);
    }

    public static function getInstance(array $config = null): self {
        if (self::$instance === null) {
            if ($config === null) throw new Exception('Database config required for first initialization');
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    public function getPdo(): PDO {
        return $this->pdo;
    }

    // 禁止複製
    private function __clone() {}
    public function __wakeup() { throw new Exception("Cannot unserialize singleton"); }
}
?>

三、基礎 Model 類別：BaseModel.php

<?php
// BaseModel.php
abstract class BaseModel {
    protected $table;
    protected $primaryKey = 'id';
    protected $fillable = []; // 允許批量賦值欄位
    protected $db;

    public function __construct() {
        $this->db = Database::getInstance()->getPdo();
    }

    // 通用查詢：依條件取得單筆
    public function findBy(string $column, $value): ?array {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE $column = ? LIMIT 1");
        $stmt->execute([$value]);
        return $stmt->fetch() ?: null;
    }

    // 通用查詢：取得所有 (可加條件)
    public function all(array $where = [], string $orderBy = ''): array {
        $sql = "SELECT * FROM {$this->table}";
        $params = [];
        if ($where) {
            $conditions = [];
            foreach ($where as $col => $val) {
                $conditions[] = "$col = ?";
                $params[] = $val;
            }
            $sql .= " WHERE " . implode(" AND ", $conditions);
        }
        if ($orderBy) $sql .= " ORDER BY $orderBy";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    // 新增
    public function create(array $data): int {
        $data = $this->filterFillable($data);
        $cols = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        $sql = "INSERT INTO {$this->table} ($cols) VALUES ($placeholders)";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($data);
        return (int)$this->db->lastInsertId();
    }

    // 更新
    public function update(int $id, array $data): bool {
        $data = $this->filterFillable($data);
        if (empty($data)) return false;
        $sets = implode(', ', array_map(fn($c) => "$c = :$c", array_keys($data)));
        $sql = "UPDATE {$this->table} SET $sets WHERE {$this->primaryKey} = :id";
        $data['id'] = $id;
        $stmt = $this->db->prepare($sql);
        return $stmt->execute($data);
    }

    // 刪除
    public function delete(int $id): bool {
        $stmt = $this->db->prepare("DELETE FROM {$this->table} WHERE {$this->primaryKey} = ?");
        return $stmt->execute([$id]);
    }

    // 取得欄位清單
    public function getColumns(): array {
        $stmt = $this->db->query("DESCRIBE {$this->table}");
        return array_column($stmt->fetchAll(), 'Field');
    }

    private function filterFillable(array $data): array {
        return $this->fillable ? array_intersect_key($data, array_flip($this->fillable)) : $data;
    }
}
?>

四、具體 Model：User.php

<?php
// User.php
class User extends BaseModel {
    protected $table = 'users';
    protected $primaryKey = 'id';
    protected $fillable = ['name', 'email', 'password', 'status', 'created_at'];

    public function findByEmail(string $email): ?array {
        return $this->findBy('email', $email);
    }

    public function findByName(string $name): ?array {
        return $this->findBy('name', $name);
    }

    public function getActiveUsers(): array {
        return $this->all(['status' => 1], 'created_at DESC');
    }
}
?>

五、測試腳本：test.php

<?php
// test.php
require_once 'configuration.php';
require_once 'Database.php';
require_once 'BaseModel.php';
require_once 'User.php';

$config = require 'configuration.php';
date_default_timezone_set($config['timezone']);
Database::getInstance($config['db']);

$userModel = new User();

// 1. 新增
$newId = $userModel->create([
    'name'     => 'HelloWorld',
    'email'    => 'hello@example.com',
    'password' => password_hash('123456', PASSWORD_DEFAULT),
    'status'   => 1,
    'created_at' => date('Y-m-d H:i:s'),
]);
echo "新增成功，ID: $newId\n";

// 2. 查詢單筆
$user = $userModel->findByEmail('hello@example.com');
echo "查詢結果: " . json_encode($user, JSON_UNESCAPED_UNICODE) . "\n";

// 3. 查詢所有
$users = $userModel->getActiveUsers();
foreach ($users as $u) {
    echo "ID: {$u['id']}, Name: {$u['name']}, Email: {$u['email']}\n";
}

// 4. 更新
$userModel->update($newId, ['name' => 'UpdatedName']);
echo "更新完成\n";

// 5. 刪除 (測試用)
// $userModel->delete($newId);
?>

六、架構優勢總結

七、進階擴充建議

• Query Builder：引入 illuminate/database 或自建 QueryBuilder 類別
• Repository Pattern：進一步解耦 Model 與資料存取邏輯
• Migration：用 phinx 或 Laravel Migration 管理結構變更
• 事件/觀察者：建立/更新時自動觸發日誌、快取清除、通知
• 讀寫分離：Database 類別支援 master/slave 連線池
• 單元測試：BaseModel 易於 Mock PDO 進行測試

八、從 mysql_* 遷移重點對照

這套三層架構雖簡單，但包含現代 PHP 開發核心要素：依賴注入、單一職責、預處理語句、異常處理、配置外部化，是建立可維護專案的紮實基礎。

1. 上一篇文章： PHP 匯出 Excel 實戰範例：樣式美化、欄寬調整、圖片插入與下載回應
2. 下一篇文章： PHP mysqli_affected_rows 詳解：影響行數 vs 匹配行數、OO/程序雙模式範例

PHP 相關知識

微信公眾平台開發入門教程（二）：接口配置全流程詳解

前言 微信公眾平台作為連接商家與用戶的核心橋樑，已成為企業數

FFmpeg 常用命令參數完整解析

FFmpeg 常用命令參數完整解析 FFmpeg 命令行參數

PHP time()/date() 時間與本地相差 8 小時：date_default_timezone_set 時區設置完整指南

問題現象 date()、time() 輸出時間比本地時間少

PHP Easter Egg (彩蛋) 洩漏版本資訊風險與關閉指南

什麼是 PHP Easter Egg？ PHP 內建幾個特殊