pdo访问mysql基类

/**
* MySql类
*/
class MySql {

/**
* 数据库连接
* @var type
*/
private $conn;

/**
* 数据库操作对象
* @var type
*/
private $stmt;

/**
* 是否正在事务中
*/
private $tran = 0;

/**
* 数据库连接信息
*/
private $dbinfo = array();

/**
* 解密算法
*/
private $des = null;

/**
* 构造函数
*/
function __construct($dbinfo) {
$this->dbinfo = $dbinfo;
}

/**
* 连接数据库
*/
private function connect() {
try {
if (!$this->conn instanceof PDO) {
$dsn = sprintf('mysql:host=%s;dbname=%s;port=%s;charset=UTF8;', $this->dbinfo['server'], $this->dbinfo['database'], $this->dbinfo['port']);
$this->conn = new PDO($dsn, $this->dbinfo['username'], $this->dbinfo['password']);
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //有错误时抛出异常
}
} catch (PDOException $e) {
throw new Exception('连接数据库出错：' . $e->getMessage() . "\n");
}
}

/**
* 关闭连接
*/
private function close() {
if ($this->conn instanceof PDO && $this->tran == 0) {
$this->conn = null;
}

if ($this->stmt instanceof PDOStatement && $this->tran == 0) {
$this->stmt = null;
}
}

/**
* 开始事务
*/
public function BeginTran() {
try {
$this->connect();
if ($this->tran == 0) {
if (!$this->conn->beginTransaction()) {
throw new Exception("BeginTran出错");
}
$this->tran = 1;
}
} catch (Exception $e) {
$this->close();
throw new Exception($e->getMessage());
}
}

/**
* 回滚事务
*/
public function RollbackTran() {
try {
if ($this->tran == 1) {
if (!$this->conn->rollBack()) {
throw new Exception("RollbackTran出错");
}
}
} catch (Exception $e) {
throw new Exception($e->getMessage());
} finally {
$this->tran = 0;
$this->close();
}
}

/**
* 提交事务
*/
public function CommitTran() {
try {
if ($this->tran == 1) {
if (!$this->conn->commit()) {
throw new Exception("CommitTran出错");
}
}
} catch (Exception $e) {
throw new Exception($e->getMessage());
} finally {
$this->tran = 0;
$this->close();
}
}

/**
* 执行Sql句
* @param string $strSql 需要执行的语句
* @param array $arrParams 参数
* @param bool $blnGetInsertID 是否要获取自增长主键，默认为true
* @return int 如果有自增长主键的话返回主键ID,否则返回受影响行数
*/
public function ExecuteSql($strSql, array $arrParams = array(), $blnGetInsertID = false) {
try {
//1.参数处理
$arrParams = $this->DealParam($strSql, $arrParams);
//2.连接数据库
$this->connect();
//3.具体处理
$this->stmt = $this->conn->prepare($strSql);
$this->ExecuteStatement($this->stmt, $arrParams);
if ($blnGetInsertID && ctype_digit($this->conn->lastInsertId()) && $this->conn->lastInsertId() != '0') {
$intRes = $this->conn->lastInsertId();
} else {
$intRes = $this->stmt->rowCount();
}
//4.返回结果
return $intRes;
} catch (Exception $e) {
throw new Exception($e->getMessage());
} finally {
//关闭数据库
$this->close();
}
}

/**
* 执行select语句，无需分页
* @param string $strSql 需要执行的语句
* @param array $arrParams 参数
* @param bool $blnWithNolock 是否脏读，默认为是。如果需要不脏读值为false
* @return array 返回语句的查询结果
*/
public function ExecuteSelect($strSql, array $arrParams = array(), $blnWithNolock = true) {
try {
//1.参数处理
$arrParams = $this->DealParam($strSql, $arrParams);
//2.连接数据库
$this->connect();
//3.具体处理
$this->stmt = $this->conn->prepare($strSql);
$this->ExecuteStatement($this->stmt, $arrParams, $blnWithNolock);
$arrRes = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
//4.返回结果
return $arrRes;
} catch (Exception $e) {
throw new Exception($e->getMessage());
} finally {
//关闭数据库
$this->close();
}
}

/**
* 执行select语句，需分页
* @param string $strSql 数据集语句
* @param int $intStart 开始行
* @param int $intPageSize 每页条数
* @param array $arrParams 参数
* @param bool $blnWithNolock 是否脏读，默认为是。如果需要不脏读值为false
* @return array 返回语句的查询结果,如果出错返回空数组
*/
public function ExecuteSelectPage($strSql, $intStart, $intPageSize, array $arrParams = array(), $blnWithNolock = true) {
try {
//1.参数处理
$strSqlOld = $strSql;
$arrParams = $this->DealParam($strSql, $arrParams);
//2.连接数据库
$this->connect();
//3.分页查询语句
$strSql = $this->getSqlPage($strSql, $intStart, $intPageSize);
//4.具体处理
$this->stmt = $this->conn->prepare($strSql);
$this->ExecuteStatement($this->stmt, $arrParams, $blnWithNolock);
$arrRes = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
//5.返回结果
return $arrRes;
} catch (Exception $e) {
throw new Exception($e->getMessage());
} finally {
//关闭数据库
$this->close();
}
}

/**
* 执行存储过程
* @param string $strProcName 需要执行的存储过程，如SP_Test
* @param string $strProcParams sp参数，如:id
* @param array $arrParams 参数数组，如array(":id"=>"999")
* @param array $arrReturn 返回存储过程的查询结果,如果出错返回空数组
* @return bool 是否成功
*/
public function ExecuteProc($strProcName, $strProcParams, $arrParams = array(), &$arrReturn = array()) {
try {
//1.连接数据库
$this->connect();
//2.具体处理
$this->stmt = $this->conn->prepare(sprintf('call %s(%s)', $strProcName, $strProcParams));
$this->ExecuteStatement($this->stmt, $arrParams);
//3.循环读取所有数据集
do {
if ($this->stmt->columnCount() > 0) {
$arrReturn[] = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
}
} while ($this->stmt->nextRowset());
//4.返回正确标识
return true;
} catch (Exception $e) {
throw new Exception($e->getMessage());
} finally {
//关闭数据库
$this->close();
}
}

/**
* 执行prepare的语句
* @param PDOStatement $statement prepare的statement
* @param array $params 参数
* @param bool $withnolock 是否脏读，默认为false。如果需要脏读值为true
* @throws Exception 执行失败抛出异常
*/
private function ExecuteStatement(PDOStatement $statement, array $params, $withnolock = false) {
if (!$statement->execute($params)) {
throw new Exception('执行statement错误');
}
}

/**
* 处理参数
* @param array $arrParams
*/
private function DealParam($strSql, array $arrParams = array()) {
$arrParamsTmp = array();
foreach ($arrParams as $key => $value) {
//使用like查询时，将\_%替换为\\、\_、\%查询
if (strpos($value, '%') !== false && strpos(str_replace(' ', '', strtolower($strSql)), strtolower('like' . $key)) !== false) {
// $value = preg_replace('/(?<!^)((\[\])|([%_^[]))(?!$)/', '$1', $value); // Sql Server
$value = preg_replace('/(?<!^)((\\\\)|([%_]))(?!$)/','\\\\$1', $value); // Mysql
$arrParamsTmp[$key] = $value;
} else {
$arrParamsTmp[$key] = $value;
}
}
return $arrParamsTmp;
}

/**
* 获取分页查询语句
* @param string $strSql 查询语句
*/
private function getSqlPage($strSql, $intStart, $intPageSize) {
$strSql = sprintf('%s limit %s,%s', $strSql, ($intStart - 1) * $intPageSize, $intPageSize);
return $strSql;
}

}