本文代码将一些简单常用的SQL语句，拆分、封装成链式函数与终结函数，链式操作没有先后之分，实现傻瓜式mysql数据库操作。 同时学习下静态成员函数,实现链式操作的具体语法。
链式操作是利用运算符进行连续操作。它的特点是一条语句中出现两个或两个以上相同的操作符。链式操作，说白了其实就是链式的调用对象的方法。既然要实现字符串的链式操作，那么就要实现一个字符串类，然后对这个类的对象进行调用操作。时代在变迁，技术不断进度，代码既要好用，还得优雅。相比传统调用方法，采用链式操作后，一步到位。
封装类常用操作使用示例
| <?php | |
| // 初始化db连接 | |
| $db = new \Workerman\MySQL\Connection('host', 'port', 'user', 'password', 'db_name'); | |
| // 获取所有数据 | |
| $db->select('ID,Sex')->from('Persons')->where('sex= :sex AND ID = :id')->bindValues(array('sex'=>'M', 'id' => 1))->query(); | |
| //等价于 | |
| $db->select('ID,Sex')->from('Persons')->where("sex= 'M' AND ID = 1")->query(); | |
| //等价于 | |
| $db->query("SELECT ID,Sex FROM `Persons` WHERE sex='M' AND ID = 1"); | |
| // 获取一行数据 | |
| $db->select('ID,Sex')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->row(); | |
| //等价于 | |
| $db->select('ID,Sex')->from('Persons')->where("sex= 'M' ")->row(); | |
| //等价于 | |
| $db->row("SELECT ID,Sex FROM `Persons` WHERE sex='M'"); | |
| // 获取一列数据 | |
| $db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->column(); | |
| //等价于 | |
| $db->select('ID')->from('Persons')->where("sex= 'F' ")->column(); | |
| //等价于 | |
| $db->column("SELECT `ID` FROM `Persons` WHERE sex='M'"); | |
| // 获取单个值 | |
| $db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->single(); | |
| // 等价于 | |
| $db->select('ID')->from('Persons')->where("sex= 'F' ")->single(); | |
| //等价于 | |
| $db->single("SELECT ID FROM `Persons` WHERE sex='M'"); | |
| // 复杂查询 | |
| $db->select('*')->from('table1')->innerJoin('table2','table1.uid = table2.uid')->where('age > :age')->groupBy(array('aid'))->having('foo="foo"')->orderByASC/*orderByDESC*/(array('did')) | |
| ->limit(10)->offset(20)->bindValues(array('age' => 13)); | |
| // 等价于 | |
| $db->query('SELECT * FROM `table1` INNER JOIN `table2` ON `table1`.`uid` = `table2`.`uid` | |
| WHERE age > 13 GROUP BY aid HAVING foo="foo" ORDER BY did LIMIT 10 OFFSET 20'); | |
| // 插入 | |
| $insert_id = $db->insert('Persons')->cols(array( | |
| 'Firstname'=>'abc', | |
| 'Lastname'=>'efg', | |
| 'Sex'=>'M', | |
| 'Age'=>13))->query(); | |
| // 等价于 | |
| $insert_id = $db->query("INSERT INTO `Persons` ( `Firstname`,`Lastname`,`Sex`,`Age`) | |
| VALUES ( 'abc', 'efg', 'M', 13)"); | |
| // 更新 | |
| $row_count = $db->update('Persons')->cols(array('sex'))->where('ID=1') | |
| ->bindValue('sex', 'F')->query(); | |
| // 等价于 | |
| $row_count = $db->update('Persons')->cols(array('sex'=>'F'))->where('ID=1')->query(); | |
| // 等价于 | |
| $row_count = $db->query("UPDATE `Persons` SET `sex` = 'F' WHERE ID=1"); | |
| // 删除 | |
| $row_count = $db->delete('Persons')->where('ID=9')->query(); | |
| // 等价于 | |
| $row_count = $db->query("DELETE FROM `Persons` WHERE ID=9"); | |
| // 事务 | |
| $db->beginTrans(); | |
| // .... | |
| $db->commitTrans(); // or $db->rollBackTrans(); |
封装源码（保存文件引用）
| /** | |
| * 数据库连接类，依赖 PDO_MYSQL 扩展 | |
| * 在 https://github.com/auraphp/Aura.SqlQuery 的基础上修改而成 | |
| */ | |
| class Connection | |
| { | |
| /** | |
| * SELECT | |
| * | |
| * @var array | |
| */ | |
| protected $union = array(); | |
| /** | |
| * 是否是更新 | |
| * | |
| * @var bool | |
| */ | |
| protected $for_update = false; | |
| /** | |
| * 选择的列 | |
| * | |
| * @var array | |
| */ | |
| protected $cols = array(); | |
| /** | |
| * 从哪些表里面 SELECT | |
| * | |
| * @var array | |
| */ | |
| protected $from = array(); | |
| /** | |
| * $from 当前的 key | |
| * | |
| * @var int | |
| */ | |
| protected $from_key = -1; | |
| /** | |
| * GROUP BY 的列 | |
| * | |
| * @var array | |
| */ | |
| protected $group_by = array(); | |
| /** | |
| * HAVING 条件数组. | |
| * | |
| * @var array | |
| */ | |
| protected $having = array(); | |
| /** | |
| * HAVING 语句中绑定的值. | |
| * | |
| * @var array | |
| */ | |
| protected $bind_having = array(); | |
| /** | |
| * 每页多少条记录 | |
| * | |
| * @var int | |
| */ | |
| protected $paging = 10; | |
| /** | |
| * sql 中绑定的值 | |
| * | |
| * @var array | |
| */ | |
| protected $bind_values = array(); | |
| /** | |
| * WHERE 条件. | |
| * | |
| * @var array | |
| */ | |
| protected $where = array(); | |
| /** | |
| * WHERE 语句绑定的值 | |
| * | |
| * @var array | |
| */ | |
| protected $bind_where = array(); | |
| /** | |
| * ORDER BY 的列 | |
| * | |
| * @var array | |
| */ | |
| protected $order_by = array(); | |
| /** | |
| * ORDER BY 的排序方式,默认为升序 | |
| * | |
| * @var bool | |
| */ | |
| protected $order_asc = true; | |
| /** | |
| * SELECT 多少记录 | |
| * | |
| * @var int | |
| */ | |
| protected $limit = 0; | |
| /** | |
| * 返回记录的游标 | |
| * | |
| * @var int | |
| */ | |
| protected $offset = 0; | |
| /** | |
| * flags 列表 | |
| * | |
| * @var array | |
| */ | |
| protected $flags = array(); | |
| /** | |
| * 操作哪个表 | |
| * | |
| * @var string | |
| */ | |
| protected $table; | |
| /** | |
| * 表.列 和 last-insert-id 映射 | |
| * | |
| * @var array | |
| */ | |
| protected $last_insert_id_names = array(); | |
| /** | |
| * INSERT 或者 UPDATE 的列 | |
| * | |
| * @param array | |
| */ | |
| protected $col_values; | |
| /** | |
| * 返回的列 | |
| * | |
| * @var array | |
| */ | |
| protected $returning = array(); | |
| /** | |
| * sql 的类型 SELECT INSERT DELETE UPDATE | |
| * | |
| * @var string | |
| */ | |
| protected $type = ''; | |
| /** | |
| * pdo 实例 | |
| * | |
| * @var PDO | |
| */ | |
| protected $pdo; | |
| /** | |
| * PDOStatement 实例 | |
| * | |
| * @var \PDOStatement | |
| */ | |
| protected $sQuery; | |
| /** | |
| * 数据库用户名密码等配置 | |
| * | |
| * @var array | |
| */ | |
| protected $settings = array(); | |
| /** | |
| * sql 的参数 | |
| * | |
| * @var array | |
| */ | |
| protected $parameters = array(); | |
| /** | |
| * 最后一条直行的 sql | |
| * | |
| * @var string | |
| */ | |
| protected $lastSql = ''; | |
| /** | |
| * 是否执行成功 | |
| * | |
| * @var bool | |
| */ | |
| protected $success = false; | |
| /** | |
| * 选择哪些列 | |
| * | |
| * @param string|array $cols | |
| * @return self | |
| */ | |
| public function select($cols = '*') | |
| { | |
| $this->type = 'SELECT'; | |
| if (!is_array($cols)) { | |
| $cols = explode(',', $cols); | |
| } | |
| $this->cols($cols); | |
| return $this; | |
| } | |
| /** | |
| * 从哪个表删除 | |
| * | |
| * @param string $table | |
| * @return self | |
| */ | |
| public function delete($table) | |
| { | |
| $this->type = 'DELETE'; | |
| $this->table = $this->quoteName($table); | |
| $this->fromRaw($this->quoteName($table)); | |
| return $this; | |
| } | |
| /** | |
| * 更新哪个表 | |
| * | |
| * @param string $table | |
| * @return self | |
| */ | |
| public function update($table) | |
| { | |
| $this->type = 'UPDATE'; | |
| $this->table = $this->quoteName($table); | |
| return $this; | |
| } | |
| /** | |
| * 向哪个表插入 | |
| * | |
| * @param string $table | |
| * @return self | |
| */ | |
| public function insert($table) | |
| { | |
| $this->type = 'INSERT'; | |
| $this->table = $this->quoteName($table); | |
| return $this; | |
| } | |
| /** | |
| * | |
| * 设置 SQL_CALC_FOUND_ROWS 标记. | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function calcFoundRows($enable = true) | |
| { | |
| $this->setFlag('SQL_CALC_FOUND_ROWS', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 SQL_CACHE 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function cache($enable = true) | |
| { | |
| $this->setFlag('SQL_CACHE', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 SQL_NO_CACHE 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function noCache($enable = true) | |
| { | |
| $this->setFlag('SQL_NO_CACHE', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 STRAIGHT_JOIN 标记. | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function straightJoin($enable = true) | |
| { | |
| $this->setFlag('STRAIGHT_JOIN', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 HIGH_PRIORITY 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function highPriority($enable = true) | |
| { | |
| $this->setFlag('HIGH_PRIORITY', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 SQL_SMALL_RESULT 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function smallResult($enable = true) | |
| { | |
| $this->setFlag('SQL_SMALL_RESULT', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 SQL_BIG_RESULT 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function bigResult($enable = true) | |
| { | |
| $this->setFlag('SQL_BIG_RESULT', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 SQL_BUFFER_RESULT 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function bufferResult($enable = true) | |
| { | |
| $this->setFlag('SQL_BUFFER_RESULT', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 FOR UPDATE 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function forUpdate($enable = true) | |
| { | |
| $this->for_update = (bool)$enable; | |
| return $this; | |
| } | |
| /** | |
| * 设置 DISTINCT 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function distinct($enable = true) | |
| { | |
| $this->setFlag('DISTINCT', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 LOW_PRIORITY 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function lowPriority($enable = true) | |
| { | |
| $this->setFlag('LOW_PRIORITY', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 IGNORE 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function ignore($enable = true) | |
| { | |
| $this->setFlag('IGNORE', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 QUICK 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function quick($enable = true) | |
| { | |
| $this->setFlag('QUICK', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 设置 DELAYED 标记 | |
| * | |
| * @param bool $enable | |
| * @return self | |
| */ | |
| public function delayed($enable = true) | |
| { | |
| $this->setFlag('DELAYED', $enable); | |
| return $this; | |
| } | |
| /** | |
| * 序列化 | |
| * | |
| * @return string | |
| */ | |
| public function __toString() | |
| { | |
| $union = ''; | |
| if ($this->union) { | |
| $union = implode(' ', $this->union) . ' '; | |
| } | |
| return $union . $this->build(); | |
| } | |
| /** | |
| * 设置每页多少条记录 | |
| * | |
| * @param int $paging | |
| * @return self | |
| */ | |
| public function setPaging($paging) | |
| { | |
| $this->paging = (int)$paging; | |
| return $this; | |
| } | |
| /** | |
| * 获取每页多少条记录 | |
| * | |
| * @return int | |
| */ | |
| public function getPaging() | |
| { | |
| return $this->paging; | |
| } | |
| /** | |
| * 获取绑定在占位符上的值 | |
| */ | |
| public function getBindValues() | |
| { | |
| switch ($this->type) { | |
| case 'SELECT': | |
| return $this->getBindValuesSELECT(); | |
| case 'DELETE': | |
| case 'UPDATE': | |
| case 'INSERT': | |
| return $this->getBindValuesCOMMON(); | |
| default : | |
| throw new Exception("type err"); | |
| } | |
| } | |
| /** | |
| * 获取绑定在占位符上的值 | |
| * | |
| * @return array | |
| */ | |
| public function getBindValuesSELECT() | |
| { | |
| $bind_values = $this->bind_values; | |
| $i = 1; | |
| foreach ($this->bind_where as $val) { | |
| $bind_values[$i] = $val; | |
| $i++; | |
| } | |
| foreach ($this->bind_having as $val) { | |
| $bind_values[$i] = $val; | |
| $i++; | |
| } | |
| return $bind_values; | |
| } | |
| /** | |
| * | |
| * SELECT选择哪些列 | |
| * | |
| * @param mixed $key | |
| * @param string $val | |
| * @return void | |
| */ | |
| protected function addColSELECT($key, $val) | |
| { | |
| if (is_string($key)) { | |
| $this->cols[$val] = $key; | |
| } else { | |
| $this->addColWithAlias($val); | |
| } | |
| } | |
| /** | |
| * SELECT 增加选择的列 | |
| * | |
| * @param string $spec | |
| */ | |
| protected function addColWithAlias($spec) | |
| { | |
| $parts = explode(' ', $spec); | |
| $count = count($parts); | |
| if ($count == 2 && trim($parts[0]) != '' && trim($parts[1]) != '') { | |
| $this->cols[$parts[1]] = $parts[0]; | |
| } elseif ($count == 3 && strtoupper($parts[1]) == 'AS') { | |
| $this->cols[$parts[2]] = $parts[0]; | |
| } else { | |
| $this->cols[] = trim($spec); | |
| } | |
| } | |
| /** | |
| * from 哪个表 | |
| * | |
| * @param string $table | |
| * @return self | |
| */ | |
| public function from($table) | |
| { | |
| return $this->fromRaw($this->quoteName($table)); | |
| } | |
| /** | |
| * from的表 | |
| * | |
| * @param string $table | |
| * @return self | |
| */ | |
| public function fromRaw($table) | |
| { | |
| $this->from[] = array($table); | |
| $this->from_key++; | |
| return $this; | |
| } | |
| /** | |
| * | |
| * 子查询 | |
| * | |
| * @param string $table | |
| * @param string $name The alias name for the sub-select. | |
| * @return self | |
| */ | |
| public function fromSubSelect($table, $name) | |
| { | |
| $this->from[] = array("($table) AS " . $this->quoteName($name)); | |
| $this->from_key++; | |
| return $this; | |
| } | |
| /** | |
| * 增加 join 语句 | |
| * | |
| * @param string $table | |
| * @param string $cond | |
| * @param string $type | |
| * @return self | |
| * @throws Exception | |
| */ | |
| public function join($table, $cond = null, $type = '') | |
| { | |
| return $this->joinInternal($type, $table, $cond); | |
| } | |
| /** | |
| * 增加 join 语句 | |
| * | |
| * @param string $join inner, left, natural | |
| * @param string $table | |
| * @param string $cond | |
| * @return self | |
| * @throws Exception | |
| */ | |
| protected function joinInternal($join, $table, $cond = null) | |
| { | |
| if (!$this->from) { | |
| throw new Exception('Cannot join() without from()'); | |
| } | |
| $join = strtoupper(ltrim("$join JOIN")); | |
| $table = $this->quoteName($table); | |
| $cond = $this->fixJoinCondition($cond); | |
| $this->from[$this->from_key][] = rtrim("$join $table $cond"); | |
| return $this; | |
| } | |
| /** | |
| * quote | |
| * | |
| * @param string $cond | |
| * @return string | |
| * | |
| */ | |
| protected function fixJoinCondition($cond) | |
| { | |
| if (!$cond) { | |
| return ''; | |
| } | |
| $cond = $this->quoteNamesIn($cond); | |
| if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') { | |
| return $cond; | |
| } | |
| if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') { | |
| return $cond; | |
| } | |
| return 'ON ' . $cond; | |
| } | |
| /** | |
| * inner join | |
| * | |
| * @param string $table | |
| * @param string $cond | |
| * @return self | |
| * @throws Exception | |
| */ | |
| public function innerJoin($table, $cond = null) | |
| { | |
| return $this->joinInternal('INNER', $table, $cond); | |
| } | |
| /** | |
| * left join | |
| * | |
| * @param string $table | |
| * @param string $cond | |
| * @return self | |
| * @throws Exception | |
| */ | |
| public function leftJoin($table, $cond = null) | |
| { | |
| return $this->joinInternal('LEFT', $table, $cond); | |
| } | |
| /** | |
| * right join | |
| * | |
| * @param string $table | |
| * @param string $cond | |
| * @return self | |
| * @throws Exception | |
| */ | |
| public function rightJoin($table, $cond = null) | |
| { | |
| return $this->joinInternal('RIGHT', $table, $cond); | |
| } | |
| /** | |
| * joinSubSelect | |
| * | |
| * @param string $join inner, left, natural | |
| * @param string $spec | |
| * @param string $name sub-select 的别名 | |
| * @param string $cond | |
| * @return self | |
| * @throws Exception | |
| */ | |
| public function joinSubSelect($join, $spec, $name, $cond = null) | |
| { | |
| if (!$this->from) { | |
| throw new \Exception('Cannot join() without from() first.'); | |
| } | |
| $join = strtoupper(ltrim("$join JOIN")); | |
| $name = $this->quoteName($name); | |
| $cond = $this->fixJoinCondition($cond); | |
| $this->from[$this->from_key][] = rtrim("$join ($spec) AS $name $cond"); | |
| return $this; | |
| } | |
| /** | |
| * group by 语句 | |
| * | |
| * @param array $cols | |
| * @return self | |
| */ | |
| public function groupBy(array $cols) | |
| { | |
| foreach ($cols as $col) { | |
| $this->group_by[] = $this->quoteNamesIn($col); | |
| } | |
| return $this; | |
| } | |
| /** | |
| * having 语句 | |
| * | |
| * @param string $cond | |
| * @return self | |
| */ | |
| public function having($cond) | |
| { | |
| $this->addClauseCondWithBind('having', 'AND', func_get_args()); | |
| return $this; | |
| } | |
| /** | |
| * or having 语句 | |
| * | |
| * @param string $cond The HAVING condition. | |
| * @return self | |
| */ | |
| public function orHaving($cond) | |
| { | |
| $this->addClauseCondWithBind('having', 'OR', func_get_args()); | |
| return $this; | |
| } | |
| /** | |
| * 设置每页的记录数量 | |
| * | |
| * @param int $page | |
| * @return self | |
| */ | |
| public function page($page) | |
| { | |
| $this->limit = 0; | |
| $this->offset = 0; | |
| $page = (int)$page; | |
| if ($page > 0) { | |
| $this->limit = $this->paging; | |
| $this->offset = $this->paging * ($page - 1); | |
| } | |
| return $this; | |
| } | |
| /** | |
| * union | |
| * | |
| * @return self | |
| */ | |
| public function union() | |
| { | |
| $this->union[] = $this->build() . ' UNION'; | |
| $this->reset(); | |
| return $this; | |
| } | |
| /** | |
| * unionAll | |
| * | |
| * @return self | |
| */ | |
| public function unionAll() | |
| { | |
| $this->union[] = $this->build() . ' UNION ALL'; | |
| $this->reset(); | |
| return $this; | |
| } | |
| /** | |
| * 重置 | |
| */ | |
| protected function reset() | |
| { | |
| $this->resetFlags(); | |
| $this->cols = array(); | |
| $this->from = array(); | |
| $this->from_key = -1; | |
| $this->where = array(); | |
| $this->group_by = array(); | |
| $this->having = array(); | |
| $this->order_by = array(); | |
| $this->limit = 0; | |
| $this->offset = 0; | |
| $this->for_update = false; | |
| } | |
| /** | |
| * 清除所有数据 | |
| */ | |
| protected function resetAll() | |
| { | |
| $this->union = array(); | |
| $this->for_update = false; | |
| $this->cols = array(); | |
| $this->from = array(); | |
| $this->from_key = -1; | |
| $this->group_by = array(); | |
| $this->having = array(); | |
| $this->bind_having = array(); | |
| $this->paging = 10; | |
| $this->bind_values = array(); | |
| $this->where = array(); | |
| $this->bind_where = array(); | |
| $this->order_by = array(); | |
| $this->limit = 0; | |
| $this->offset = 0; | |
| $this->flags = array(); | |
| $this->table = ''; | |
| $this->last_insert_id_names = array(); | |
| $this->col_values = array(); | |
| $this->returning = array(); | |
| $this->parameters = array(); | |
| } | |
| /** | |
| * 创建 SELECT SQL | |
| * | |
| * @return string | |
| */ | |
| protected function buildSELECT() | |
| { | |
| return 'SELECT' | |
| . $this->buildFlags() | |
| . $this->buildCols() | |
| . $this->buildFrom() | |
| . $this->buildWhere() | |
| . $this->buildGroupBy() | |
| . $this->buildHaving() | |
| . $this->buildOrderBy() | |
| . $this->buildLimit() | |
| . $this->buildForUpdate(); | |
| } | |
| /** | |
| * 创建 DELETE SQL | |
| */ | |
| protected function buildDELETE() | |
| { | |
| return 'DELETE' | |
| . $this->buildFlags() | |
| . $this->buildFrom() | |
| . $this->buildWhere() | |
| . $this->buildOrderBy() | |
| . $this->buildLimit() | |
| . $this->buildReturning(); | |
| } | |
| /** | |
| * 生成 SELECT 列语句 | |
| * | |
| * @return string | |
| * @throws Exception | |
| */ | |
| protected function buildCols() | |
| { | |
| if (!$this->cols) { | |
| throw new Exception('No columns in the SELECT.'); | |
| } | |
| $cols = array(); | |
| foreach ($this->cols as $key => $val) { | |
| if (is_int($key)) { | |
| $cols[] = $this->quoteNamesIn($val); | |
| } else { | |
| $cols[] = $this->quoteNamesIn("$val AS $key"); | |
| } | |
| } | |
| return $this->indentCsv($cols); | |
| } | |
| /** | |
| * 生成 FROM 语句. | |
| * | |
| * @return string | |
| */ | |
| protected function buildFrom() | |
| { | |
| if (!$this->from) { | |
| return ''; | |
| } | |
| $refs = array(); | |
| foreach ($this->from as $from) { | |
| $refs[] = implode(' ', $from); | |
| } | |
| return ' FROM' . $this->indentCsv($refs); | |
| } | |
| /** | |
| * 生成 GROUP BY 语句. | |
| * | |
| * @return string | |
| */ | |
| protected function buildGroupBy() | |
| { | |
| if (!$this->group_by) { | |
| return ''; | |
| } | |
| return ' GROUP BY' . $this->indentCsv($this->group_by); | |
| } | |
| /** | |
| * 生成 HAVING 语句. | |
| * | |
| * @return string | |
| */ | |
| protected function buildHaving() | |
| { | |
| if (!$this->having) { | |
| return ''; | |
| } | |
| return ' HAVING' . $this->indent($this->having); | |
| } | |
| /** | |
| * 生成 FOR UPDATE 语句 | |
| * | |
| * @return string | |
| */ | |
| protected function buildForUpdate() | |
| { | |
| if (!$this->for_update) { | |
| return ''; | |
| } | |
| return ' FOR UPDATE'; | |
| } | |
| /** | |
| * where | |
| * | |
| * @param string|array $cond | |
| * @return self | |
| */ | |
| public function where($cond) | |
| { | |
| if (is_array($cond)) { | |
| foreach ($cond as $key => $val) { | |
| if (is_string($key)) { | |
| $this->addWhere('AND', array($key, $val)); | |
| } else { | |
| $this->addWhere('AND', array($val)); | |
| } | |
| } | |
| } else { | |
| $this->addWhere('AND', func_get_args()); | |
| } | |
| return $this; | |
| } | |
| /** | |
| * or where | |
| * | |
| * @param string|array $cond | |
| * @return self | |
| */ | |
| public function orWhere($cond) | |
| { | |
| if (is_array($cond)) { | |
| foreach ($cond as $key => $val) { | |
| if (is_string($key)) { | |
| $this->addWhere('OR', array($key, $val)); | |
| } else { | |
| $this->addWhere('OR', array($val)); | |
| } | |
| } | |
| } else { | |
| $this->addWhere('OR', func_get_args()); | |
| } | |
| return $this; | |
| } | |
| /** | |
| * limit | |
| * | |
| * @param int $limit | |
| * @return self | |
| */ | |
| public function limit($limit) | |
| { | |
| $this->limit = (int)$limit; | |
| return $this; | |
| } | |
| /** | |
| * limit offset | |
| * | |
| * @param int $offset | |
| * @return self | |
| */ | |
| public function offset($offset) | |
| { | |
| $this->offset = (int)$offset; | |
| return $this; | |
| } | |
| /** | |
| * orderby. | |
| * | |
| * @param array $cols | |
| * @return self | |
| */ | |
| public function orderBy(array $cols) | |
| { | |
| return $this->addOrderBy($cols); | |
| } | |
| /** | |
| * order by ASC OR DESC | |
| * | |
| * @param array $cols | |
| * @param bool $order_asc | |
| * @return self | |
| */ | |
| public function orderByASC(array $cols, $order_asc = true) | |
| { | |
| $this->order_asc = $order_asc; | |
| return $this->addOrderBy($cols); | |
| } | |
| /** | |
| * order by DESC | |
| * | |
| * @param array $cols | |
| * @return self | |
| */ | |
| public function orderByDESC(array $cols) | |
| { | |
| $this->order_asc = false; | |
| return $this->addOrderBy($cols); | |
| } | |
| // -------------abstractquery---------- | |
| /** | |
| * 返回逗号分隔的字符串 | |
| * | |
| * @param array $list | |
| * @return string | |
| */ | |
| protected function indentCsv(array $list) | |
| { | |
| return ' ' . implode(',', $list); | |
| } | |
| /** | |
| * 返回空格分隔的字符串 | |
| * | |
| * @param array $list | |
| * @return string | |
| */ | |
| protected function indent(array $list) | |
| { | |
| return ' ' . implode(' ', $list); | |
| } | |
| /** | |
| * 批量为占位符绑定值 | |
| * | |
| * @param array $bind_values | |
| * @return self | |
| * | |
| */ | |
| public function bindValues(array $bind_values) | |
| { | |
| foreach ($bind_values as $key => $val) { | |
| $this->bindValue($key, $val); | |
| } | |
| return $this; | |
| } | |
| /** | |
| * 单个为占位符绑定值 | |
| * | |
| * @param string $name | |
| * @param mixed $value | |
| * @return self | |
| */ | |
| public function bindValue($name, $value) | |
| { | |
| $this->bind_values[$name] = $value; | |
| return $this; | |
| } | |
| /** | |
| * 生成 flag | |
| * | |
| * @return string | |
| */ | |
| protected function buildFlags() | |
| { | |
| if (!$this->flags) { | |
| return ''; | |
| } | |
| return ' ' . implode(' ', array_keys($this->flags)); | |
| } | |
| /** | |
| * 设置 flag. | |
| * | |
| * @param string $flag | |
| * @param bool $enable | |
| */ | |
| protected function setFlag($flag, $enable = true) | |
| { | |
| if ($enable) { | |
| $this->flags[$flag] = true; | |
| } else { | |
| unset($this->flags[$flag]); | |
| } | |
| } | |
| /** | |
| * 重置 flag | |
| */ | |
| protected function resetFlags() | |
| { | |
| $this->flags = array(); | |
| } | |
| /** | |
| * | |
| * 添加 where 语句 | |
| * | |
| * @param string $andor 'AND' or 'OR | |
| * @param array $conditions | |
| * @return self | |
| * | |
| */ | |
| protected function addWhere($andor, $conditions) | |
| { | |
| $this->addClauseCondWithBind('where', $andor, $conditions); | |
| return $this; | |
| } | |
| /** | |
| * 添加条件和绑定值 | |
| * | |
| * @param string $clause where 、having等 | |
| * @param string $andor AND、OR等 | |
| * @param array $conditions | |
| */ | |
| protected function addClauseCondWithBind($clause, $andor, $conditions) | |
| { | |
| $cond = array_shift($conditions); | |
| $cond = $this->quoteNamesIn($cond); | |
| $bind =& $this->{"bind_{$clause}"}; | |
| foreach ($conditions as $value) { | |
| $bind[] = $value; | |
| } | |
| $clause =& $this->$clause; | |
| if ($clause) { | |
| $clause[] = "$andor $cond"; | |
| } else { | |
| $clause[] = $cond; | |
| } | |
| } | |
| /** | |
| * 生成 where 语句 | |
| * | |
| * @return string | |
| */ | |
| protected function buildWhere() | |
| { | |
| if (!$this->where) { | |
| return ''; | |
| } | |
| return ' WHERE' . $this->indent($this->where); | |
| } | |
| /** | |
| * 增加 order by | |
| * | |
| * @param array $spec The columns and direction to order by. | |
| * @return self | |
| */ | |
| protected function addOrderBy(array $spec) | |
| { | |
| foreach ($spec as $col) { | |
| $this->order_by[] = $this->quoteNamesIn($col); | |
| } | |
| return $this; | |
| } | |
| /** | |
| * 生成 order by 语句 | |
| * | |
| * @return string | |
| */ | |
| protected function buildOrderBy() | |
| { | |
| if (!$this->order_by) { | |
| return ''; | |
| } | |
| if ($this->order_asc) { | |
| return ' ORDER BY' . $this->indentCsv($this->order_by) . ' ASC'; | |
| } else { | |
| return ' ORDER BY' . $this->indentCsv($this->order_by) . ' DESC'; | |
| } | |
| } | |
| /** | |
| * 生成 limit 语句 | |
| * | |
| * @return string | |
| */ | |
| protected function buildLimit() | |
| { | |
| $has_limit = $this->type == 'DELETE' || $this->type == 'UPDATE'; | |
| $has_offset = $this->type == 'SELECT'; | |
| if ($has_offset && $this->limit) { | |
| $clause = " LIMIT {$this->limit}"; | |
| if ($this->offset) { | |
| $clause .= " OFFSET {$this->offset}"; | |
| } | |
| return $clause; | |
| } elseif ($has_limit && $this->limit) { | |
| return " LIMIT {$this->limit}"; | |
| } | |
| return ''; | |
| } | |
| /** | |
| * Quotes | |
| * | |
| * @param string $spec | |
| * @return string|array | |
| */ | |
| public function quoteName($spec) | |
| { | |
| $spec = trim($spec); | |
| $seps = array(' AS ', ' ', '.'); | |
| foreach ($seps as $sep) { | |
| $pos = strripos($spec, $sep); | |
| if ($pos) { | |
| return $this->quoteNameWithSeparator($spec, $sep, $pos); | |
| } | |
| } | |
| return $this->replaceName($spec); | |
| } | |
| /** | |
| * 指定分隔符的 Quotes | |
| * | |
| * @param string $spec | |
| * @param string $sep | |
| * @param int $pos | |
| * @return string | |
| */ | |
| protected function quoteNameWithSeparator($spec, $sep, $pos) | |
| { | |
| $len = strlen($sep); | |
| $part1 = $this->quoteName(substr($spec, 0, $pos)); | |
| $part2 = $this->replaceName(substr($spec, $pos + $len)); | |
| return "{$part1}{$sep}{$part2}"; | |
| } | |
| /** | |
| * Quotes "table.col" 格式的字符串 | |
| * | |
| * @param string $text | |
| * @return string|array | |
| */ | |
| public function quoteNamesIn($text) | |
| { | |
| $list = $this->getListForQuoteNamesIn($text); | |
| $last = count($list) - 1; | |
| $text = null; | |
| foreach ($list as $key => $val) { | |
| if (($key + 1) % 3) { | |
| $text .= $this->quoteNamesInLoop($val, $key == $last); | |
| } | |
| } | |
| return $text; | |
| } | |
| /** | |
| * 返回 quote 元素列表 | |
| * | |
| * @param string $text | |
| * @return array | |
| */ | |
| protected function getListForQuoteNamesIn($text) | |
| { | |
| $apos = "'"; | |
| $quot = '"'; | |
| return preg_split( | |
| "/(($apos+|$quot+|\\$apos+|\\$quot+).*?\\2)/", | |
| $text, | |
| -1, | |
| PREG_SPLIT_DELIM_CAPTURE | |
| ); | |
| } | |
| /** | |
| * 循环 quote | |
| * | |
| * @param string $val | |
| * @param bool $is_last | |
| * @return string | |
| */ | |
| protected function quoteNamesInLoop($val, $is_last) | |
| { | |
| if ($is_last) { | |
| return $this->replaceNamesAndAliasIn($val); | |
| } | |
| return $this->replaceNamesIn($val); | |
| } | |
| /** | |
| * 替换成别名 | |
| * | |
| * @param string $val | |
| * @return string | |
| */ | |
| protected function replaceNamesAndAliasIn($val) | |
| { | |
| $quoted = $this->replaceNamesIn($val); | |
| $pos = strripos($quoted, ' AS '); | |
| if ($pos !== false) { | |
| $bracket = strripos($quoted, ')'); | |
| if ($bracket === false) { | |
| $alias = $this->replaceName(substr($quoted, $pos + 4)); | |
| $quoted = substr($quoted, 0, $pos) . " AS $alias"; | |
| } | |
| } | |
| return $quoted; | |
| } | |
| /** | |
| * Quotes name | |
| * | |
| * @param string $name | |
| * @return string | |
| */ | |
| protected function replaceName($name) | |
| { | |
| $name = trim($name); | |
| if ($name == '*') { | |
| return $name; | |
| } | |
| return '`' . $name . '`'; | |
| } | |
| /** | |
| * Quotes | |
| * | |
| * @param string $text | |
| * @return string|array | |
| */ | |
| protected function replaceNamesIn($text) | |
| { | |
| $is_string_literal = strpos($text, "'") !== false | |
| || strpos($text, '"') !== false; | |
| if ($is_string_literal) { | |
| return $text; | |
| } | |
| $word = '[a-z_][a-z0-9_]*'; | |
| $find = "/(\\b)($word)\\.($word)(\\b)/i"; | |
| $repl = '$1`$2`.`$3`$4'; | |
| $text = preg_replace($find, $repl, $text); | |
| return $text; | |
| } | |
| // ---------- insert -------------- | |
| /** | |
| * 设置 `table.column` 与 last-insert-id 的映射 | |
| * | |
| * @param array $last_insert_id_names | |
| */ | |
| public function setLastInsertIdNames(array $last_insert_id_names) | |
| { | |
| $this->last_insert_id_names = $last_insert_id_names; | |
| } | |
| /** | |
| * insert into. | |
| * | |
| * @param string $table | |
| * @return self | |
| */ | |
| public function into($table) | |
| { | |
| $this->table = $this->quoteName($table); | |
| return $this; | |
| } | |
| /** | |
| * 生成 INSERT 语句 | |
| * | |
| * @return string | |
| */ | |
| protected function buildINSERT() | |
| { | |
| return 'INSERT' | |
| . $this->buildFlags() | |
| . $this->buildInto() | |
| . $this->buildValuesForInsert() | |
| . $this->buildReturning(); | |
| } | |
| /** | |
| * 生成 INTO 语句 | |
| * | |
| * @return string | |
| */ | |
| protected function buildInto() | |
| { | |
| return " INTO " . $this->table; | |
| } | |
| /** | |
| * PDO::lastInsertId() | |
| * | |
| * @param string $col | |
| * @return mixed | |
| */ | |
| public function getLastInsertIdName($col) | |
| { | |
| $key = str_replace('`', '', $this->table) . '.' . $col; | |
| if (isset($this->last_insert_id_names[$key])) { | |
| return $this->last_insert_id_names[$key]; | |
| } | |
| return null; | |
| } | |
| /** | |
| * 设置一列，如果有第二各参数，则把第二个参数绑定在占位符上 | |
| * | |
| * @param string $col | |
| * @return self | |
| */ | |
| public function col($col) | |
| { | |
| return call_user_func_array(array($this, 'addCol'), func_get_args()); | |
| } | |
| /** | |
| * 设置多列 | |
| * | |
| * @param array $cols | |
| * @return self | |
| */ | |
| public function cols(array $cols) | |
| { | |
| if ($this->type == 'SELECT') { | |
| foreach ($cols as $key => $val) { | |
| $this->addColSELECT($key, $val); | |
| } | |
| return $this; | |
| } | |
| return $this->addCols($cols); | |
| } | |
| /** | |
| * 直接设置列的值 | |
| * | |
| * @param string $col | |
| * @param string $value | |
| * @return self | |
| */ | |
| public function set($col, $value) | |
| { | |
| return $this->setCol($col, $value); | |
| } | |
| /** | |
| * 为 INSERT 语句绑定值 | |
| * | |
| * @return string | |
| */ | |
| protected function buildValuesForInsert() | |
| { | |
| return ' (' . $this->indentCsv(array_keys($this->col_values)) . ') VALUES (' . | |
| $this->indentCsv(array_values($this->col_values)) . ')'; | |
| } | |
| // ------update------- | |
| /** | |
| * 更新哪个表 | |
| * | |
| * @param string $table | |
| * @return self | |
| */ | |
| public function table($table) | |
| { | |
| $this->table = $this->quoteName($table); | |
| return $this; | |
| } | |
| /** | |
| * 生成完整 SQL 语句 | |
| * | |
| * @return string | |
| * @throws Exception | |
| */ | |
| protected function build() | |
| { | |
| switch ($this->type) { | |
| case 'DELETE': | |
| return $this->buildDELETE(); | |
| case 'INSERT': | |
| return $this->buildINSERT(); | |
| case 'UPDATE': | |
| return $this->buildUPDATE(); | |
| case 'SELECT': | |
| return $this->buildSELECT(); | |
| } | |
| throw new Exception("type empty"); | |
| } | |
| /** | |
| * 生成更新的 SQL 语句 | |
| */ | |
| protected function buildUPDATE() | |
| { | |
| return 'UPDATE' | |
| . $this->buildFlags() | |
| . $this->buildTable() | |
| . $this->buildValuesForUpdate() | |
| . $this->buildWhere() | |
| . $this->buildOrderBy() | |
| . $this->buildLimit() | |
| . $this->buildReturning(); | |
| } | |
| /** | |
| * 哪个表 | |
| * | |
| * @return string | |
| */ | |
| protected function buildTable() | |
| { | |
| return " {$this->table}"; | |
| } | |
| /** | |
| * 为更新语句绑定值 | |
| * | |
| * @return string | |
| */ | |
| protected function buildValuesForUpdate() | |
| { | |
| $values = array(); | |
| foreach ($this->col_values as $col => $value) { | |
| $values[] = "{$col} = {$value}"; | |
| } | |
| return ' SET' . $this->indentCsv($values); | |
| } | |
| // ----------Dml--------------- | |
| /** | |
| * 获取绑定的值 | |
| * | |
| * @return array | |
| */ | |
| public function getBindValuesCOMMON() | |
| { | |
| $bind_values = $this->bind_values; | |
| $i = 1; | |
| foreach ($this->bind_where as $val) { | |
| $bind_values[$i] = $val; | |
| $i++; | |
| } | |
| return $bind_values; | |
| } | |
| /** | |
| * 设置列 | |
| * | |
| * @param string $col | |
| * @return self | |
| */ | |
| protected function addCol($col) | |
| { | |
| $key = $this->quoteName($col); | |
| $this->col_values[$key] = ":$col"; | |
| $args = func_get_args(); | |
| if (count($args) > 1) { | |
| $this->bindValue($col, $args[1]); | |
| } | |
| return $this; | |
| } | |
| /** | |
| * 设置多个列 | |
| * | |
| * @param array $cols | |
| * @return self | |
| */ | |
| protected function addCols(array $cols) | |
| { | |
| foreach ($cols as $key => $val) { | |
| if (is_int($key)) { | |
| $this->addCol($val); | |
| } else { | |
| $this->addCol($key, $val); | |
| } | |
| } | |
| return $this; | |
| } | |
| /** | |
| * 设置单列的值 | |
| * | |
| * @param string $col . | |
| * @param string $value | |
| * @return self | |
| */ | |
| protected function setCol($col, $value) | |
| { | |
| if ($value === null) { | |
| $value = 'NULL'; | |
| } | |
| $key = $this->quoteName($col); | |
| $value = $this->quoteNamesIn($value); | |
| $this->col_values[$key] = $value; | |
| return $this; | |
| } | |
| /** | |
| * 增加返回的列 | |
| * | |
| * @param array $cols | |
| * @return self | |
| * | |
| */ | |
| protected function addReturning(array $cols) | |
| { | |
| foreach ($cols as $col) { | |
| $this->returning[] = $this->quoteNamesIn($col); | |
| } | |
| return $this; | |
| } | |
| /** | |
| * 生成 RETURNING 语句 | |
| * | |
| * @return string | |
| */ | |
| protected function buildReturning() | |
| { | |
| if (!$this->returning) { | |
| return ''; | |
| } | |
| return ' RETURNING' . $this->indentCsv($this->returning); | |
| } | |
| /** | |
| * 构造函数 | |
| * | |
| * @param string $host | |
| * @param int $port | |
| * @param string $user | |
| * @param string $password | |
| * @param string $db_name | |
| * @param string $charset | |
| */ | |
| public function __construct($host, $port, $user, $password, $db_name, $charset = 'utf8') | |
| { | |
| $this->settings = array( | |
| 'host' => $host, | |
| 'port' => $port, | |
| 'user' => $user, | |
| 'password' => $password, | |
| 'dbname' => $db_name, | |
| 'charset' => $charset, | |
| ); | |
| $this->connect(); | |
| } | |
| /** | |
| * 创建 PDO 实例 | |
| */ | |
| protected function connect() | |
| { | |
| $dsn = 'mysql:dbname=' . $this->settings["dbname"] . ';host=' . | |
| $this->settings["host"] . ';port=' . $this->settings['port']; | |
| $this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"], | |
| array( | |
| PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . (!empty($this->settings['charset']) ? | |
| $this->settings['charset'] : 'utf8') | |
| )); | |
| $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | |
| $this->pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false); | |
| $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); | |
| } | |
| /** | |
| * 关闭连接 | |
| */ | |
| public function closeConnection() | |
| { | |
| $this->pdo = null; | |
| } | |
| /** | |
| * 执行 | |
| * | |
| * @param string $query | |
| * @param string $parameters | |
| * @throws PDOException | |
| */ | |
| protected function execute($query, $parameters = "") | |
| { | |
| try { | |
| if (is_null($this->pdo)) { | |
| $this->connect(); | |
| } | |
| $this->sQuery = @$this->pdo->prepare($query); | |
| $this->bindMore($parameters); | |
| if (!empty($this->parameters)) { | |
| foreach ($this->parameters as $param) { | |
| $this->sQuery->bindParam($param[0], $param[1]); | |
| } | |
| } | |
| $this->success = $this->sQuery->execute(); | |
| } catch (PDOException $e) { | |
| // 服务端断开时重连一次 | |
| if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) { | |
| $this->closeConnection(); | |
| $this->connect(); | |
| try { | |
| $this->sQuery = $this->pdo->prepare($query); | |
| $this->bindMore($parameters); | |
| if (!empty($this->parameters)) { | |
| foreach ($this->parameters as $param) { | |
| $this->sQuery->bindParam($param[0], $param[1]); | |
| } | |
| } | |
| $this->success = $this->sQuery->execute(); | |
| } catch (PDOException $ex) { | |
| $this->rollBackTrans(); | |
| throw $ex; | |
| } | |
| } else { | |
| $this->rollBackTrans(); | |
| $msg = $e->getMessage(); | |
| $err_msg = "SQL:".$this->lastSQL()." ".$msg; | |
| $exception = new \PDOException($err_msg, (int)$e->getCode()); | |
| throw $exception; | |
| } | |
| } | |
| $this->parameters = array(); | |
| } | |
| /** | |
| * 绑定 | |
| * | |
| * @param string $para | |
| * @param string $value | |
| */ | |
| public function bind($para, $value) | |
| { | |
| if (is_string($para)) { | |
| $this->parameters[sizeof($this->parameters)] = array(":" . $para, $value); | |
| } else { | |
| $this->parameters[sizeof($this->parameters)] = array($para, $value); | |
| } | |
| } | |
| /** | |
| * 绑定多个 | |
| * | |
| * @param array $parray | |
| */ | |
| public function bindMore($parray) | |
| { | |
| if (empty($this->parameters) && is_array($parray)) { | |
| $columns = array_keys($parray); | |
| foreach ($columns as $i => &$column) { | |
| $this->bind($column, $parray[$column]); | |
| } | |
| } | |
| } | |
| /** | |
| * 执行 SQL | |
| * | |
| * @param string $query | |
| * @param array $params | |
| * @param int $fetchmode | |
| * @return mixed | |
| */ | |
| public function query($query = '', $params = null, $fetchmode = PDO::FETCH_ASSOC) | |
| { | |
| $query = trim($query); | |
| if (empty($query)) { | |
| $query = $this->build(); | |
| if (!$params) { | |
| $params = $this->getBindValues(); | |
| } | |
| } | |
| $this->resetAll(); | |
| $this->lastSql = $query; | |
| $this->execute($query, $params); | |
| $rawStatement = explode(" ", $query); | |
| $statement = strtolower(trim($rawStatement[0])); | |
| if ($statement === 'select' || $statement === 'show') { | |
| return $this->sQuery->fetchAll($fetchmode); | |
| } elseif ($statement === 'update' || $statement === 'delete' || $statement === 'replace') { | |
| return $this->sQuery->rowCount(); | |
| } elseif ($statement === 'insert') { | |
| if ($this->sQuery->rowCount() > 0) { | |
| return $this->lastInsertId(); | |
| } | |
| } else { | |
| return null; | |
| } | |
| return null; | |
| } | |
| /** | |
| * 返回一列 | |
| * | |
| * @param string $query | |
| * @param array $params | |
| * @return array | |
| */ | |
| public function column($query = '', $params = null) | |
| { | |
| $query = trim($query); | |
| if (empty($query)) { | |
| $query = $this->build(); | |
| if (!$params) { | |
| $params = $this->getBindValues(); | |
| } | |
| } | |
| $this->resetAll(); | |
| $this->lastSql = $query; | |
| $this->execute($query, $params); | |
| $columns = $this->sQuery->fetchAll(PDO::FETCH_NUM); | |
| $column = null; | |
| foreach ($columns as $cells) { | |
| $column[] = $cells[0]; | |
| } | |
| return $column; | |
| } | |
| /** | |
| * 返回一行 | |
| * | |
| * @param string $query | |
| * @param array $params | |
| * @param int $fetchmode | |
| * @return array | |
| */ | |
| public function row($query = '', $params = null, $fetchmode = PDO::FETCH_ASSOC) | |
| { | |
| $query = trim($query); | |
| if (empty($query)) { | |
| $query = $this->build(); | |
| if (!$params) { | |
| $params = $this->getBindValues(); | |
| } | |
| } | |
| $this->resetAll(); | |
| $this->lastSql = $query; | |
| $this->execute($query, $params); | |
| return $this->sQuery->fetch($fetchmode); | |
| } | |
| /** | |
| * 返回单个值 | |
| * | |
| * @param string $query | |
| * @param array $params | |
| * @return string | |
| */ | |
| public function single($query = '', $params = null) | |
| { | |
| $query = trim($query); | |
| if (empty($query)) { | |
| $query = $this->build(); | |
| if (!$params) { | |
| $params = $this->getBindValues(); | |
| } | |
| } | |
| $this->resetAll(); | |
| $this->lastSql = $query; | |
| $this->execute($query, $params); | |
| return $this->sQuery->fetchColumn(); | |
| } | |
| /** | |
| * 返回 lastInsertId | |
| * | |
| * @return string | |
| */ | |
| public function lastInsertId() | |
| { | |
| return $this->pdo->lastInsertId(); | |
| } | |
| /** | |
| * 返回最后一条执行的 sql | |
| * | |
| * @return string | |
| */ | |
| public function lastSQL() | |
| { | |
| return $this->lastSql; | |
| } | |
| /** | |
| * 开始事务 | |
| */ | |
| public function beginTrans() | |
| { | |
| try { | |
| if (is_null($this->pdo)) { | |
| $this->connect(); | |
| } | |
| return $this->pdo->beginTransaction(); | |
| } catch (PDOException $e) { | |
| // 服务端断开时重连一次 | |
| if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) { | |
| $this->closeConnection(); | |
| $this->connect(); | |
| return $this->pdo->beginTransaction(); | |
| } else { | |
| throw $e; | |
| } | |
| } | |
| } | |
| /** | |
| * 提交事务 | |
| */ | |
| public function commitTrans() | |
| { | |
| return $this->pdo->commit(); | |
| } | |
| /** | |
| * 事务回滚 | |
| */ | |
| public function rollBackTrans() | |
| { | |
| if ($this->pdo->inTransaction()) { | |
| return $this->pdo->rollBack(); | |
| } | |
| return true; | |
| } | |
| } |
链式操作的关键是在做完操作后要return $this。return $this表示方法结束后，返回的是当前对象，它可以实现链式操作。