使用PDO封装的简单数据库操作类

以下是一个使用PDO封装的简单数据库操作类，包含连接数据库、执行SQL查询以及基本的增删改查方法：

PDO封装的数据库操作类

<?php
class Database {
    private $host = 'localhost';
    private $dbname = 'your_database_name';
    private $username = 'your_username';
    private $password = 'your_password';
    private $charset = 'utf8mb4';

    private $pdo;

    public function __construct() {
        try {
            $dsn = "mysql:host=$this->host;dbname=$this->dbname;charset=$this->charset";
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false, // 关闭模拟预处理，增强安全性
            ];
            $this->pdo = new PDO($dsn, $this->username, $this->password, $options);
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage(), (int)$e->getCode());
        }
    }

    /**
     * 执行一条SQL查询语句（主要用于SELECT）
     */
    public function query(string $sql, array $params = []): ?array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * 执行一条SQL语句（主要用于INSERT, UPDATE, DELETE）
     */
    public function execute(string $sql, array $params = []): int {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    /**
     * 插入数据
     */
    public function insert(string $table, array $data): int {
        $columns = implode(',', array_keys($data));
        $values  = ':' . implode(', :', array_keys($data));

        $sql = "INSERT INTO $table ($columns) VALUES ($values)";
        return $this->execute($sql, $data);
    }

    /**
     * 更新数据
     */
    public function update(string $table, array $data, array $where): int {
        $set = [];
        foreach ($data as $key => $value) {
            $set[] = "$key = :$key";
        }
        $set = implode(',', $set);

        $whereClauses = [];
        foreach ($where as $key => $value) {
            $whereClauses[] = "$key = :w_$key";
        }
        $whereClause = implode(' AND ', $whereClauses);

        $sql = "UPDATE $table SET $set WHERE $whereClause";
        $params = array_merge($data, array_combine(array_map(fn($k) => 'w_' . $k, array_keys($where)), $where));

        return $this->execute($sql, $params);
    }

    /**
     * 删除数据
     */
    public function delete(string $table, array $where): int {
        $whereClauses = [];
        foreach ($where as $key => $value) {
            $whereClauses[] = "$key = :w_$key";
        }
        $whereClause = implode(' AND ', $whereClauses);

        $sql = "DELETE FROM $table WHERE $whereClause";
        $params = array_combine(array_map(fn($k) => 'w_' . $k, array_keys($where)), $where);

        return $this->execute($sql, $params);
    }
}

使用示例

$db = new Database();

// 插入数据
$data = ['name' => 'John Doe', 'email' => 'john.doe@example.com'];
$insertedId = $db->insert('users', $data);

// 更新数据
$dataToUpdate = ['name' => 'Jane Doe'];
$where = ['id' => $insertedId];
$db->update('users', $dataToUpdate, $where);

// 删除数据
$whereToDelete = ['id' => $insertedId];
$db->delete('users', $whereToDelete);

// 查询数据
$results = $db->query("SELECT * FROM users WHERE email = :email", ['email' => 'john.doe@example.com']);

请确保替换上述代码中的your_database_name, your_username, your_password为实际数据库配置。同时，请根据实际情况调整SQL语句和参数绑定。