PHP Adapter::createStatement方法代碼示例

 /**
  * @return int
  */
 public function getTotalCount()
 {
     $statement = $this->adapter->createStatement('SELECT COUNT(id) as cnt FROM ' . $this->table);
     $results = $statement->execute();
     $row = $results->current();
     return is_array($row) && isset($row['cnt']) ? (int) $row['cnt'] : 0;
 }

 /**
  * Executes query with prepared statement
  * @param $query
  * @param array $params
  * @return array|bool|int
  * @deprecated
  */
 public function executePreparedQuery($query, array $params = [])
 {
     $this->logQuery($query, $params);
     try {
         $statement = $this->_adapter->createStatement($this->fixTableName($query));
         $res = $statement->execute($params);
         $this->safeDropCache($query);
     } catch (\Exception $e) {
         $this->result = new ResultSet(ResultSet::TYPE_ARRAY, []);
         error_log("ERROR: ADB : executePreparedQuery");
         error_log("query: " . $this->getFullQuery($query, $params));
         error_log($e->getTraceAsString());
         return false;
     }
     $this->result = $res;
     if ($res->isQueryResult()) {
         $res->rewind();
         $returnArray = [];
         while ($res->valid()) {
             $returnArray[] = $res->current();
             $res->next();
         }
     } else {
         $returnArray = $res->getAffectedRows();
     }
     return $returnArray;
 }

 /**
  * @param int $offset
  * @param int $limit
  * @param string $order
  * @return TagCollection
  */
 public function findTags($offset = 0, $limit = PHP_INT_MAX, $order = 'id ASC')
 {
     $sql = new Sql($this->adapter);
     $select = $sql->select();
     $select->from('tag');
     $select->offset($offset);
     $select->limit($limit);
     $select->order($order);
     $statement = $sql->prepareStatementForSqlObject($select);
     $result = $statement->execute();
     $selectStatement = $this->adapter->createStatement('SELECT FOUND_ROWS() as total;');
     $total = $selectStatement->execute();
     $total = (int) $total->current();
     $collection = new TagCollection();
     $collection->setTotalCount($total);
     foreach ($result as $row) {
         $entity = $this->createEntity($row);
         $collection->addElement($entity, $entity->getId());
     }
     return $collection;
 }

 /**
  * _authenticateQuerySelect() - This method accepts a Zend\Db\Sql\Select object and
  * performs a query against the database with that object.
  *
  * @param  DbSelect $dbSelect
  * @throws Exception\RuntimeException when an invalid select object is encountered
  * @return array
  */
 protected function _authenticateQuerySelect(DbSelect $dbSelect)
 {
     $statement = $this->zendDb->createStatement();
     $dbSelect->prepareStatement($this->zendDb, $statement);
     $resultSet = new ResultSet();
     try {
         $resultSet->initialize($statement->execute(array($this->credential, $this->identity)));
         $resultIdentities = $resultSet->toArray();
     } catch (\Exception $e) {
         throw new Exception\RuntimeException('The supplied parameters to DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.', 0, $e);
     }
     return $resultIdentities;
 }

 /**
  * Delete
  * 
  * @param  Closure $where
  * @return type 
  */
 public function delete($where)
 {
     $delete = clone $this->sqlDelete;
     $delete->from($this->tableName, $this->databaseSchema);
     if ($where instanceof \Closure) {
         $where($delete);
     } else {
         $delete->where($where);
     }
     $statement = $this->adapter->createStatement();
     $delete->prepareStatement($this->adapter, $statement);
     $result = $statement->execute();
     return $result->getAffectedRows();
 }

 /**
  * @param $table - table name
  * @param $colName - column name
  * @return array|bool - return list of constrains or false, if constrains not exist
  */
 protected function getConstraintForColumn($table, $colName)
 {
     $rows = $this->db->createStatement("select database() as dbname")->execute();
     $row = $rows->current();
     $dbName = $row['dbname'];
     $sql = "SELECT k.CONSTRAINT_SCHEMA,k.CONSTRAINT_NAME," . "k.TABLE_NAME,k.COLUMN_NAME,k.REFERENCED_TABLE_NAME," . "k.REFERENCED_COLUMN_NAME, r.UPDATE_RULE, r.DELETE_RULE FROM " . "information_schema.key_column_usage k LEFT JOIN " . "information_schema.referential_constraints r ON " . "r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND " . " k.REFERENCED_TABLE_NAME=r.REFERENCED_TABLE_NAME " . "LEFT JOIN information_schema.table_constraints t ON " . "t.CONSTRAINT_SCHEMA = r.CONSTRAINT_SCHEMA WHERE " . " k.constraint_schema='{$dbName}' AND t.CONSTRAINT_TYPE='FOREIGN KEY' " . "AND k.TABLE_NAME='{$table}' AND r.TABLE_NAME='{$table}' " . " AND t.TABLE_NAME='{$table}' AND k.COLUMN_NAME='{$colName}'";
     $rows = $this->db->createStatement($sql)->execute();
     $row = $rows->current();
     if (!count($row)) {
         return false;
     }
     $constraint = array('table' => $table, 'name' => $row['CONSTRAINT_NAME'], 'column' => $row['COLUMN_NAME'], 'reference' => array('table' => $row['REFERENCED_TABLE_NAME'], 'column' => $row['REFERENCED_COLUMN_NAME'], 'update' => $row['UPDATE_RULE'], 'delete' => $row['DELETE_RULE']));
     return $constraint;
 }

 public function reinstall($config)
 {
     $adapter = new Adapter($config);
     $adapter->createStatement('DROP DATABASE IF EXISTS `' . $config['database'] . '`')->execute();
     $config_file = 'config/autoload/local.php';
     $old_config = array();
     if (file_exists($config_file)) {
         $old_config = (include $config_file);
         unset($old_config['db']);
         unlink($config_file);
         $writer = new ConfigWriter();
         $writer->toFile($config_file, new Config($old_config));
     }
     $this->install($config);
 }

 /**
  * @testdox unit test: Test createStatement() produces a statement object
  * @covers Zend\Db\Adapter\Adapter::createStatement
  */
 public function testCreateStatement()
 {
     $this->assertSame($this->mockStatement, $this->adapter->createStatement());
 }

 private function dbQuery($sql)
 {
     return $this->adapter->createStatement($sql)->execute();
 }

 /**
  *
  * @param Options $options
  * @throws Exception\EmptyQueryException
  * @throws Exception\ErrorException
  * @return ResultSet
  */
 public function getData(Options $options = null)
 {
     if ($options === null) {
         $options = $this->getOptions();
     }
     $select = $this->assignOptions(clone $this->getSelect(), $options);
     $sql = new Sql($this->adapter);
     $sql_string = (string) $sql->getSqlStringForSqlObject($select);
     //echo $this->select->getSqlString($this->adapter->getPlatform());
     //echo "----" . var_dump($sql_string) . "----\n";
     // In ZF 2.3.0 an empty query will return SELECT .*
     // In ZF 2.4.0 and empty query will return SELECT *
     if (in_array($sql_string, array('', 'SELECT .*', 'SELECT *'))) {
         throw new Exception\EmptyQueryException(__METHOD__ . ': Cannot return data of an empty query');
     }
     $this->query_string = $sql_string;
     // In case of unbuffered results (default on mysqli) !!!
     // Seems to not be needed anymore in ZF 2.3+
     // Uncomment if necessary, see also below is_mysqli
     /*
      $is_mysqli = false;
      $driver = $this->adapter->getDriver();
      if (false && $driver instanceof \Zend\Db\Adapter\Driver\Mysqli\Mysqli) {
      $stmt_prototype_backup = $driver->getStatementPrototype();
      if (self::$cache_stmt_prototype === null) {
      // With buffer results
      self::$cache_stmt_prototype = new \Zend\Db\Adapter\Driver\Mysqli\Statement($buffer=true);
      }
      $driver->registerStatementPrototype(self::$cache_stmt_prototype);
      $is_mysqli = true;
      }
     */
     /**
      * Check whether there's a column model
      */
     /*
      $limit_columns = false;
      $renderers     = false;
      if ($this->columnModel !== null) {
      // TODO: optimize when the column model haven't been modified.
      $limit_columns = $this->columnModel->getColumns();
      $renderers     = $this->columnModel->getRowRenderers();
      }
     */
     //$cm = $this->getColumnModel();
     //$cm->setExcluded(array('user_id'));
     //$this->columns = $cm->getColumns();
     //var_dump($this->columns);
     //die();
     try {
         $results = $this->adapter->query($sql_string, Adapter::QUERY_MODE_EXECUTE);
         //$stmt = $sql->prepareStatementForSqlObject( $select );
         //$results = $stmt->execute();
         //var_dump(get_class($results));
         $r = new ResultSet($results);
         $r->setSource($this);
         $r->setHydrationOptions($options->getHydrationOptions());
         if ($options->hasLimit()) {
             //$row = $this->adapter->query('select FOUND_ROWS() as total_count')->execute()->current();
             $row = $this->adapter->createStatement('select FOUND_ROWS() as total_count')->execute()->current();
             $r->setTotalRows($row['total_count']);
         } else {
             $r->setTotalRows($r->count());
         }
         // restore result prototype
         // $this->adapter->getDriver()->registerResultPrototype($result_prototype_backup);
         // restore statement prototype
         // seems not needed in zf 2.3
         /*
          if ($is_mysqli) {
          $this->adapter->getDriver()->registerStatementPrototype($stmt_prototype_backup);
          }
         */
     } catch (\Exception $e) {
         // restore result prototype
         //$this->adapter->getDriver()->registerResultPrototype($result_prototype_backup);
         // seems not needed in zf 2.3
         /*
          if ($is_mysqli) {
          $this->adapter->getDriver()->registerStatementPrototype($stmt_prototype_backup);
          }
         */
         throw new Exception\ErrorException(__METHOD__ . ': Cannot retrieve data (' . $e->getMessage() . ')');
     }
     return $r;
 }

 /**
  * Obtain a Zend\DB connection using an option array.
  *
  * @param array $options Options for building adapter
  *
  * @return Adapter
  */
 public function getAdapterFromOptions($options)
 {
     // Set up custom options by database type:
     $driver = strtolower($options['driver']);
     switch ($driver) {
         case 'mysqli':
             $options['charset'] = isset($this->config->Database->charset) ? $this->config->Database->charset : 'utf8';
             $options['options'] = ['buffer_results' => true];
             break;
     }
     // Set up database connection:
     $adapter = new Adapter($options);
     // Special-case setup:
     if ($driver == 'pdo_pgsql' && isset($this->config->Database->schema)) {
         // Set schema
         $statement = $adapter->createStatement('SET search_path TO ' . $this->config->Database->schema);
         $statement->execute();
     }
     return $adapter;
 }

 /**
  * Create users and roles
  *
  * @param \Zend\Db\Adapter\Adapter $dbAdapter     Database adapter
  * @param array                    $configuration Configuration
  * @param string                   $sqlType       Sql database type
  *
  * @return \Zend\View\Model\JsonModel|null
  */
 protected function createUsersAndRoles($dbAdapter, $configuration, $sqlType)
 {
     //Create role
     $roles = (include GC_APPLICATION_PATH . '/data/install/acl/roles.php');
     try {
         foreach ($roles['role'] as $key => $value) {
             $statement = $dbAdapter->createStatement("INSERT INTO user_acl_role (name) VALUES ('" . $value . "')");
             $result = $statement->execute();
         }
     } catch (Exception $e) {
         return $this->returnJson(array('messages' => $e->getMessage()));
     }
     //resources
     $resources = (include GC_APPLICATION_PATH . '/data/install/acl/resources.php');
     try {
         foreach ($resources as $key => $value) {
             $statement = $dbAdapter->createStatement("INSERT INTO user_acl_resource (resource) VALUES ('" . $key . "')");
             $result = $statement->execute();
             $statement = $dbAdapter->createStatement("SELECT id FROM user_acl_resource WHERE resource =  '" . $key . "'");
             $result = $statement->execute();
             $lastInsertId = $result->current();
             $lastInsertId = $lastInsertId['id'];
             $permissions = array();
             foreach ($value as $k => $v) {
                 if (!in_array($k, $permissions)) {
                     $statement = $dbAdapter->createStatement("INSERT INTO user_acl_permission\n                            (\n                                permission,\n                                user_acl_resource_id\n                            )\n                            VALUES ('" . $k . "', '" . $lastInsertId . "')");
                     $result = $statement->execute();
                     $permissions[] = $k;
                 }
             }
         }
         foreach ($resources as $key => $value) {
             $statement = $dbAdapter->createStatement("SELECT id FROM user_acl_resource WHERE resource =  '" . $key . "'");
             $result = $statement->execute();
             $lastResourceInsertId = $result->current();
             $lastResourceInsertId = $lastResourceInsertId['id'];
             foreach ($value as $k => $v) {
                 $statement = $dbAdapter->createStatement("SELECT id\n                        FROM user_acl_permission\n                        WHERE permission =  '" . $k . "'\n                            AND user_acl_resource_id = '" . $lastResourceInsertId . "'");
                 $result = $statement->execute();
                 $lastInsertId = $result->current();
                 $lastInsertId = $lastInsertId['id'];
                 $statement = $dbAdapter->createStatement("SELECT id FROM user_acl_role WHERE name = '" . $v . "'");
                 $result = $statement->execute();
                 $role = $result->current();
                 if (!empty($role['id'])) {
                     $statement = $dbAdapter->createStatement("INSERT INTO user_acl\n                            (\n                                user_acl_role_id,\n                                user_acl_permission_id\n                            )\n                            VALUES ('" . $role['id'] . "', " . $lastInsertId . ')');
                     $result = $statement->execute();
                 }
             }
         }
     } catch (Exception $e) {
         return $this->returnJson(array('messages' => $e->getMessage()));
     }
     //Add admin user
     if ($sqlType == 'mysql') {
         $sqlString = 'INSERT INTO `user`
             (
                 created_at,
                 updated_at,
                 lastname,
                 firstname,
                 email,
                 login,
                 password,
                 user_acl_role_id
             )
             VALUES (NOW(), NOW(), ?, ?, ?, ?, ?, 1)';
     } else {
         $sqlString = 'INSERT INTO "user"
             (
                 created_at,
                 updated_at,
                 lastname,
                 firstname,
                 email,
                 login,
                 password,
                 user_acl_role_id
             )
             VALUES (NOW(), NOW(), ?, ?, ?, ?, ?, 1)';
     }
     $dbAdapter->query($sqlString, array($configuration['admin_lastname'], $configuration['admin_firstname'], $configuration['admin_email'], $configuration['admin_login'], sha1($configuration['admin_password'])));
 }