PHP OOP中高效管理数据库连接：避免重复实例化PDO

class UserCrud {
    protected $host;
    protected $db;
    protected $user;
    protected $password;

    public function __construct(string $host, string $db, string $user, string $password) {
        $this->host = $host;
        $this->db = $db;
        $this->user = $user;
        $this->password = $password;
    }

    protected function insertUser(array $userData){
        // 每次调用此方法都会创建一个新的数据库连接
        $connection = new PDO(
            'mysql:host='. $this->host .';dbname='.$this->db,
            $this->user,
            $this->password
        );
        // ... 使用 $connection 执行插入操作
        echo "用户插入成功！";
    }

    protected function getUserById(int $id){
        // 每次调用此方法也会创建一个新的数据库连接
        $connection = new PDO(
            'mysql:host='. $this->host .';dbname='.$this->db,
            $this->user,
            $this->password
        );
        // ... 使用 $connection 执行查询操作
        echo "查询用户成功！";
    }
}

class DB {
    protected PDO $connection; // 声明PDO连接属性

    /**
     * 构造函数：在对象创建时建立数据库连接
     *
     * @param string $host 数据库主机名
     * @param string $db 数据库名
     * @param string $user 数据库用户名
     * @param string $password 数据库密码
     */
    public function __construct(string $host, string $db, string $user, string $password) {
        try {
            $dsn = 'mysql:host=' . $host . ';dbname=' . $db . ';charset=utf8mb4';
            $options = [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // 抛出异常
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,     // 默认以关联数组形式返回结果
                PDO::ATTR_EMULATE_PREPARES   => false,                // 禁用模拟预处理，使用原生预处理
            ];
            $this->connection = new PDO($dsn, $user, $password, $options);
        } catch (PDOException $e) {
            // 捕获连接异常，并抛出更具体的错误信息
            throw new PDOException("数据库连接失败: " . $e->getMessage(), (int)$e->getCode());
        }
    }
}

class DB {
    protected PDO $connection;

    public function __construct(string $host, string $db, string $user, string $password) {
        // ... (同上，连接初始化代码) ...
    }

    /**
     * 执行一个SQL查询并返回所有结果
     *
     * @param string $sql SQL查询语句
     * @param array $parameters 预处理语句的参数数组
     * @return array 查询结果集
     */
    public function runQueryAndGetResult(string $sql, array $parameters = []): array {
        $statement = $this->connection->prepare($sql);
        $statement->execute($parameters);
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * 执行一个SQL更新/插入/删除语句
     *
     * @param string $sql SQL语句
     * @param array $parameters 预处理语句的参数数组
     * @return int 受影响的行数
     */
    public function execute(string $sql, array $parameters = []): int {
        $statement = $this->connection->prepare($sql);
        $statement->execute($parameters);
        return $statement->rowCount();
    }

    /**
     * 获取最后插入行的ID
     *
     * @return string|false
     */
    public function lastInsertId(): string|false {
        return $this->connection->lastInsertId();
    }
}

class UserCrud {
    private DB $db; // 声明DB类的实例

    public function __construct(DB $db) {
        $this->db = $db; // 注入DB实例
    }

    public function insertUser(string $username, string $email): int {
        $sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
        $parameters = [
            ':username' => $username,
            ':email' => $email
        ];
        $this->db->execute($sql, $parameters); // 调用DB类的方法执行插入
        return (int)$this->db->lastInsertId(); // 获取插入ID
    }

    public function getUserById(int $id): ?array {
        $sql = "SELECT id, username, email FROM users WHERE id = :id";
        $parameters = [':id' => $id];
        $result = $this->db->runQueryAndGetResult($sql, $parameters); // 调用DB类的方法执行查询
        return $result[0] ?? null; // 返回第一条记录或null
    }

    public function updateUserEmail(int $id, string $newEmail): int {
        $sql = "UPDATE users SET email = :email WHERE id = :id";
        $parameters = [
            ':email' => $newEmail,
            ':id' => $id
        ];
        return $this->db->execute($sql, $parameters); // 调用DB类的方法执行更新
    }

    public function deleteUser(int $id): int {
        $sql = "DELETE FROM users WHERE id = :id";
        $parameters = [':id' => $id];
        return $this->db->execute($sql, $parameters); // 调用DB类的方法执行删除
    }
}

// 示例用法
try {
    // 1. 实例化DB类 (只进行一次数据库连接)
    $db = new DB('localhost', 'your_database', 'your_user', 'your_password');

    // 2. 实例化UserCrud类，并将DB实例注入
    $userCrud = new UserCrud($db);

    // 3. 执行CRUD操作
    $newUserId = $userCrud->insertUser('john_doe', 'john@example.com');
    echo "新用户ID: " . $newUserId . PHP_EOL;

    $user = $userCrud->getUserById($newUserId);
    if ($user) {
        echo "查询到用户: " . json_encode($user) . PHP_EOL;
    }

    $affectedRows = $userCrud->updateUserEmail($newUserId, 'john.doe@newdomain.com');
    echo "更新了 " . $affectedRows . " 行" . PHP_EOL;

    $affectedRows = $userCrud->deleteUser($newUserId);
    echo "删除了 " . $affectedRows . " 行" . PHP_EOL;

} catch (PDOException $e) {
    error_log("数据库操作失败: " . $e->getMessage());
    echo "发生数据库错误，请稍后再试。" . PHP_EOL;
} catch (Exception $e) {
    error_log("应用程序错误: " . $e->getMessage());
    echo "发生应用程序错误，请稍后再试。" . PHP_EOL;
}