数据库操作类

这是一个简单的、使用PHP语言实现的、采用PDO连接的方式的mysql数据库操作类，主要用于连接mysql数据库和进行sql语句操作（包括常用到的增、删、改、查，另外也可以执行DDL语句）。

首先是一个mysql数据库的配置文件 config.ini，其中只是简单的包括了PDO连接方式的配置信息（数据源、用户名和密码）：

[pdo]
db_source = "mysql:host=localhost;dbname=test"
db_user = root
db_password = 123

下面就是这个完整的数据库操作类（上面的配置文件和下面的数据库操作类文件在同一目录结构下）：

<?php
/**
 * A PHP class to access MySQL database with PDO methods
 * @author  27_Man(http://www.cnblogs.com/27man/)
 */
final class Db {

    // the instance of class Db.
    private static $instance;

    // the handle and statement of database.
    private $handle, $stmt;

    // the dsn, user and password of database.
    private $dsn, $user, $pass;

    // to be singleton pattern.
    private function __construct() {
        $this->init();
    }

    // to be singleton pattern.
    private function __clone() {}

    // initialize dsn, user and password of database from 'ini'.
    private function init() {
        $ini_array = parse_ini_file('config.ini');
        $this->dsn = $ini_array['db_source'];
        $this->user = $ini_array['db_user'];
        $this->pass = $ini_array['db_password'];
    }

    // connect database when we start.
    public function connect() {
        if ($this->handle instanceof PDO) {
            return;
        }
        try{
            $this->handle = new PDO($this->dsn, $this->user, $this->pass);
            $this->handle->exec("set names utf8");
            $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }catch(PDOException $e){
            echo "Failed to connect database" .$e->getMessage();
            die();
        }
    }

    // get the instance of class Db.
    public static function getInstance() {
        if (!(self::$instance instanceof self)) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    // when sql is 'select', use it.
    public function query($str, $params_array=NULL) {
        if (isset($params_array)) {
            $this->pst_query($str, $params_array);
        } else {
            $this->sql_query($str);
        }

        $this->stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $this->stmt;
    }

    private function sql_query($sql) {
        $this->stmt = $this->handle->query($sql);
    }

    private function pst_query($pst, $params_array) {
        $this->stmt = $this->handle->prepare($pst);
        $this->stmt->execute($params_array);
    }

    // when sql is 'insert, update, delete and DDL', use it.
    public function execute($str, $params_array=NULL) {
        if (isset($params_array)) {
            return $this->pst_exec($str, $params_array);
        } else {
            return $this->sql_exec($str);
        }
    }

    private function sql_exec($sql) {
        return $this->handle->exec($sql);
    }

    private function pst_exec($pst, $params_array) {
        $this->stmt = $this->handle->prepare($pst);
        $this->stmt->execute($params_array);
        return $this->stmt->rowCount();
    }

    // to begin the transaction.
    public function beginTransaction() {
        $this->handle->beginTransaction();
    }

    // to commit the transaction.
    public function commit() {
        $this->handle->commit();
    }

    // to rollback.
    public function rollBack() {
        $this->handle->rollBack();
    }

    // when done, we should release resources of database.
    public function free() {
        $this->handle = null;
    }
}

使用方法：

// 获取Db类的实例
$db = Db::getInstance();

// 连接数据库
$db->connect();

// do something...

// 操作完后释放数据库连接
$db->free();

一个具体的例子：

$db = Db::getInstance();

$db->connect();

try{
    $db->beginTransaction();
    $db->execute(
        'UPDATE user SET pwd=? WHERE id=?',
        array('123456', 15)
    );
    $db->commit();
}catch(Exception $e){
    $db->rollBack();
    echo $e->getMessage();
}

$user = $db->query('SELECT * FROM user WHERE id=?', array(15))->fetch();

$db->free();

print_r($user);

最后的补充：

　　当使用 query 方法后，获取结果可以用fetch()、fetchAll()、fetchColumn()、fetchObject()，具体请参照php手册中的PDOStatement中的方法。