<?php /** * Description of MySqlHelper * * @author fx678 */ class MySqlHelper { private static $pdoDB = null; public function __destruct() { $this->pdoDB = null; } /** * 获取ini配置文件中PDO连接信息，并返回PDO对象 * $section: 数据库配置节点 * 返回 PDO对象 **/ private static function getPdoDB($section ="db"){ if(self::$pdoDB !== null && !array_key_exists($section,array('dsn','username','password'))){ if(!array_key_exists($section,self::$pdoDB)){ return self::$pdoDB; } } $config = Config::getConfig($section); try { self::$pdoDB = new PDO( $config['dsn'], $config['username'], $config['password'], array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8';") ); } catch (Exception $ex) { throw new Exception('DB connection error:'.$ex->getMessage()); } return self::$pdoDB; } /** * 执行不带参数的查询语句 * $connectionString:数据库配置信息节点 * $strSql：要执行的T-sql 语句 * 返回 PDOStatement 对象 **/ public static function query($connectionString,$strSql){ try { $statement = self::getPdoDB($connectionString)->query($strSql,PDO::FETCH_ASSOC); if($statement === false){ throw new Exception($this->getDB($connectionString)->errorInfo()); } } catch (Exception $ex) { throw new Execption($ex->getMessage()); } return $statement; } /** * 执行带参数的查询语句 * $connectionString:数据库配置信息节点 * $strSql：要执行的T-sql 语句 * $paramArr：参数数组 * 返回 PDOStatement 对象 **/ public static function queryParam($connectionString,$strSql,$paramArr){ try { $statement = self::getPdoDB($connectionString)->prepare($strSql); //获取对应参数 if(isset($paramArr) && is_array($paramArr)){ foreach ($paramArr as $key => $val) { $statement->bindParam("$key",$val[0],$val[1]); } } //执行 $statement->execute(); } catch (Exception $ex) { throw new Execption($ex->getMessage()); } if($statement === false){ throw new Exception($this->getDB($connectionString)->errorInfo()); } return $statement; } /** * 执行带参数并使用事物机制的(增删改)语句 * $connectionString:数据库配置信息节点 * $strSql：要执行的T-sql 语句 * $paramArr：参数数组 * 返回 返回受影响的行数(int) **/ public static function prepareParamTransaction($connectionString,$strSql,$paramArr){ $count = 0; try { $pdo = self::getPdoDB($connectionString); //关闭自动提交(默认为自动提交) $pdo ->setAttribute(PDO::ATTR_AUTOCOMMIT, 0); //开启异常处理 $pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //开始事物 $pdo -> beginTransaction(); //获取 Statement 对象 $statement = $pdo->prepare($strSql); //获取对应参数 if(isset($paramArr) && is_array($paramArr)){ foreach ($paramArr as $key => $val) { $statement->bindParam("$key",$val[0],$val[1]); } } //执行 $statement -> execute(); $count = (int)($statement-> rowCount()); //提交事物 $pdo -> commit(); } catch (Exception $ex){ throw new Execption($ex->getMessage()); } if($statement === false){ throw new Exception($this->getPdoDB($connectionString)->errorInfo()); } //返回受影响的行数 return $count; } } ?>

调用参考示例1：

 $strSql = "SELECT * FROM NEWSINDEX n WHERE n.`Id`=:Id LIMIT 1;";
        
        $paramArr = array(
            ':Id' => array($Id,  PDO::PARAM_INT)
        );
        
        foreach (self::queryParam(self::$connectionString, $strSql, $paramArr) as $row) {
            $model = new htNewsIndex();
            HtNewsIndexMapper::map($model, $row);
            return $model;
        }
        
        return null;

调用参考示例2：

$strSql = "INSERT INTO NEWS_INDEX(BSID,IDX_PERIOD,preVIOUS_PRICE,ACTUAL_PRICE,REVISED_PRICE,SURVEY_PRICE,PUBLISH_TIME,CREATE_TIME,STATUS) "
        . "VALUES(:BSId,:IdxPeriod,:PreviousPrice,:ActualPrice,:RevisedPrice,:SurveyPrice,:PublishTime,:CreateTime,:Status);";
        
        //参数数组
        $params = array(
            ':BSId'           => array($model->getBSId(),PDO::PARAM_STR),
            ':IdxPeriod'      => array($model->getIdxPeriod(),PDO::PARAM_STR),
            ':PreviousPrice'  => array($model->getPreviousPrice(),PDO::PARAM_STR),
            ':ActualPrice'    => array($model->getActualPrice(),PDO::PARAM_STR),
            ':RevisedPrice'   => array($model->getRevisedPrice(),PDO::PARAM_STR),
            ':SurveyPrice'    => array($model->getSurveyPrice(),PDO::PARAM_STR),
            ':PublishTime'    => array($model->getPublishTime(),PDO::PARAM_STR),
            ':CreateTime'     => array($model->getCreateTime(),PDO::PARAM_STR),
            ':Status'         => array($model->getStatus(),PDO::PARAM_INT)
        );
        
        try {
           return (int)self::prepareParamTransaction(self::$connectionString,$strSql,$params);
        } catch (Exception $ex) {
            throw new Exception($ex->getTraceAsString());
        }

由于认识有限，不足之处请多指教！
在这个过程中，我发现使用 PDO 参数化模糊查询，始终没有结果，也没有异常抛出，如下语句：

SELECT * FROM NEWS n WHERE n.`Title` LIKE('%:key%');

SELECT * FROM NEWS n WHERE n.`Title` LIKE('%?%');