PHP Connection::executeQuery方法代碼示例

 public function exportData($export, $reporter)
 {
     $this->conn->beginTransaction();
     try {
         $lastExportedAt = (int) $export['lastExportedAt'];
         $areas = $this->conn->fetchAll('SELECT a.`id`, a.`name`, t.`id` AS `territoryId`, t.`name` AS `territoryName`, a.`customData`, a.`lastUpdatedAt` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'WHERE a.`projectId` = :projectId AND a.`statusId` = :statusId', [':projectId' => $export['projectId'], ':statusId' => $export['areaStatusId']]);
         $block = new ExportBlock();
         foreach ($areas as $area) {
             $block->addId($area['id']);
             if ($area['lastUpdatedAt'] > $lastExportedAt) {
                 $area['customData'] = json_decode($area['customData']);
                 $block->addUpdatedId($area['id']);
                 $block->addUpdate($area);
             }
         }
         $event = new ExportEvent($export['projectId'], $export['lastExportedAt'], $reporter);
         $event->addBlock('area', $block);
         $event = $this->eventDispatcher->dispatch(ExportEvents::EXPORT_ONGOING, $event);
         $this->conn->executeQuery('UPDATE `' . ExportTables::DATA_EXPORT_TBL . '` SET `lastExportedAt` = :time WHERE `id` = :id', [':time' => time(), ':id' => $export['id']]);
         $this->conn->commit();
         return $event->output();
     } catch (Exception $ex) {
         $this->conn->rollBack();
         throw $ex;
     }
 }

 function exportTableList()
 {
     return array_map(function ($row) {
         $values = array_values($row);
         return $values[0];
     }, $this->db->executeQuery('SHOW TABLES')->fetchAll());
 }

 /**
  * @param LoggerInterface $logger
  * @param bool $dryRun
  */
 protected function doExecute(LoggerInterface $logger, $dryRun = false)
 {
     $duplicateEntitiesQuery = 'SELECT
             DISTINCT t2.id
         FROM
             orocrm_campaign_email_stats AS t1
         LEFT JOIN orocrm_campaign_email_stats AS t2
             ON t1.email_campaign_id = t2.email_campaign_id
             AND t1.marketing_list_item_id = t2.marketing_list_item_id
             AND t2.id > t1.id
         WHERE t2.id IS NOT NULL';
     // Done in 2 queries for cross DB support.
     $idsToRemove = array_map(function ($item) {
         if (is_array($item) && array_key_exists('id', $item)) {
             return $item['id'];
         }
         return null;
     }, $this->connection->fetchAll($duplicateEntitiesQuery));
     if ($idsToRemove) {
         $query = 'DELETE FROM orocrm_campaign_email_stats WHERE id IN (?)';
         $logger->notice($query);
         if (!$dryRun) {
             $this->connection->executeQuery($query, [$idsToRemove], [Connection::PARAM_INT_ARRAY]);
         }
     }
 }

 /**
  * @param QueryInterface $query
  * @return int
  */
 public function execute(QueryInterface $query)
 {
     $query->checkReplacements();
     $plainQuery = $query->getPlainQuery();
     list($parameters, $plainQuery) = $this->modifyParametersFromArrayToScalar($query->getParameters(), $plainQuery);
     return $this->connection->executeQuery($query->getPlainQuery(), $parameters);
 }

 public function stopQuery()
 {
     if ($this->explainRunning) {
         return;
     }
     $keys = array_keys($this->queries);
     $key = end($keys);
     $this->queries[$key][self::TIME] = Debugger::timer('doctrine');
     $this->totalTime += $this->queries[$key][self::TIME];
     // get EXPLAIN for SELECT queries
     if ($this->doExplains) {
         if ($this->connection === NULL) {
             throw new \Nette\InvalidStateException('You must set a Doctrine\\DBAL\\Connection to get EXPLAIN.');
         }
         $query = $this->queries[$key][self::SQL];
         if (strtoupper(substr(ltrim($query), 0, 6)) !== 'SELECT') {
             // only SELECTs are supported
             return;
         }
         // prevent logging explains & infinite recursion
         $this->explainRunning = TRUE;
         $params = $this->queries[$key][self::PARAMS];
         $types = $this->queries[$key][self::TYPES];
         $stmt = $this->connection->executeQuery('EXPLAIN ' . $query, $params, $types);
         $this->queries[$key][self::EXPLAIN] = $stmt->fetchAll();
         $this->explainRunning = FALSE;
     }
 }

 /**
  * Update status
  *
  * @param string $nonce
  * @param string $apiUrls
  *
  * @return int
  */
 public function updateStatus($nonce, $apiUrls)
 {
     // Truncate table
     $this->con->executeQuery($this->con->getDatabasePlatform()->getTruncateTableSQL($this->tables['status']));
     // Insert new row
     return $this->con->insert($this->tables['status'], array('nonce' => $nonce, 'noncets' => time(), 'apiurls' => $apiUrls, 'modified' => new \DateTime()), array(\PDO::PARAM_STR, \PDO::PARAM_INT, \PDO::PARAM_STR, 'datetime'));
 }

 /**
  * Retrieves hte index database.
  *
  * @return Connection
  */
 public function getConnection()
 {
     if (!$this->connection) {
         $isNewDatabase = !file_exists($this->databasePath);
         $configuration = new Configuration();
         $this->connection = DriverManager::getConnection(['driver' => 'pdo_sqlite', 'path' => $this->databasePath], $configuration);
         $outOfDate = null;
         if ($isNewDatabase) {
             $this->createDatabaseTables($this->connection);
             // NOTE: This causes a database write and will cause locking problems if multiple PHP processes are
             // spawned and another one is also writing (e.g. indexing).
             $this->connection->executeQuery('PRAGMA user_version=' . $this->databaseVersion);
         } else {
             $version = $this->connection->executeQuery('PRAGMA user_version')->fetchColumn();
             if ($version < $this->databaseVersion) {
                 $this->connection->close();
                 $this->connection = null;
                 @unlink($this->databasePath);
                 return $this->getConnection();
                 // Do it again.
             }
         }
     }
     // Have to be a douche about this as these PRAGMA's seem to reset, even though the connection is not closed.
     $this->connection->executeQuery('PRAGMA foreign_keys=ON');
     // Data could become corrupted if the operating system were to crash during synchronization, but this
     // matters very little as we will just reindex the project next time. In the meantime, this majorly reduces
     // hard disk I/O during indexing and increases indexing speed dramatically (dropped from over a minute to a
     // couple of seconds for a very small (!) project).
     $this->connection->executeQuery('PRAGMA synchronous=OFF');
     return $this->connection;
 }

 private function fetchRows()
 {
     $statement = $this->connection->executeQuery($this->getSQLForAllRows());
     $rows = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $statement->closeCursor();
     return $rows;
 }

 /**
  * Proxy method to connection object. If an error occurred because of unfound table, tries to create table and rerun request.
  *
  * @param string $query      SQL query
  * @param array  $parameters query parameters
  */
 protected function runSQL($query, array $parameters = array())
 {
     try {
         return $this->connection->executeQuery($query, $parameters);
     } catch (\Exception $e) {
         $this->connection->executeQuery(sprintf('CREATE TABLE %s (`key` VARCHAR(40), `value` TEXT);', $this->tableName));
     }
     return $this->connection->executeQuery($query, $parameters);
 }

 /**
  * Proxy method to connection object. If an error occurred because of unfound table, tries to create table and rerun request.
  *
  * @param string $query      SQL query
  * @param array  $parameters query parameters
  */
 protected function runSQL($query, array $parameters = array())
 {
     try {
         return $this->connection->executeQuery($query, $parameters);
     } catch (\Exception $e) {
         $this->connection->executeQuery(sprintf(self::TABLE_CREATE, $this->tableName));
     }
     return $this->connection->executeQuery($query, $parameters);
 }

 /**
  * @inheritdoc
  */
 public function update()
 {
     try {
         $constraint = $this->getForeignKeyConstraint('s_import_export_session', 'log_id');
         $this->dbalConnection->executeQuery('ALTER TABLE s_import_export_session DROP FOREIGN KEY ' . $constraint);
     } catch (\Exception $exception) {
     }
     $this->dbalConnection->executeQuery('ALTER TABLE s_import_export_session DROP COLUMN log_id');
     $this->removeImportFilesAlbum();
 }

 /**
  * Updates the statistics for area requests in the current day.
  * 
  * @param \Cantiga\CoreBundle\Repository\AreaRequestEvent $event
  */
 public function onAreaRequestStatusChange(AreaRequestEvent $event)
 {
     $project = $event->getAreaRequest()->getProject();
     $values = [0 => 0, 1 => 0, 2 => 0, 3 => 0];
     $calculated = $this->conn->fetchAll('SELECT `status`, COUNT(`id`) AS `counted` FROM `' . CoreTables::AREA_REQUEST_TBL . '` WHERE `projectId` = :projectId GROUP BY `status`', [':projectId' => $project->getId()]);
     foreach ($calculated as $row) {
         $values[$row['status']] = $row['counted'];
     }
     $date = date('Y-m-d');
     $this->conn->executeQuery('INSERT INTO `' . CoreTables::STAT_ARQ_TIME_TBL . '` (`projectId`, `datePoint`, `requestsNew`, `requestsVerification`, `requestsApproved`, `requestsRejected`)' . 'VALUES(:projectId, :datePoint, :rn1, :rv1, :ra1, :rr1) ON DUPLICATE KEY UPDATE `requestsNew` = :rn2, `requestsVerification` = :rv2, `requestsApproved` = :ra2, `requestsRejected` = :rr2', [':projectId' => $project->getId(), ':datePoint' => $date, ':rn1' => $values[AreaRequest::STATUS_NEW], ':rv1' => $values[AreaRequest::STATUS_VERIFICATION], ':ra1' => $values[AreaRequest::STATUS_APPROVED], ':rr1' => $values[AreaRequest::STATUS_REVOKED], ':rn2' => $values[AreaRequest::STATUS_NEW], ':rv2' => $values[AreaRequest::STATUS_VERIFICATION], ':ra2' => $values[AreaRequest::STATUS_APPROVED], ':rr2' => $values[AreaRequest::STATUS_REVOKED]]);
 }

 /**
  */
 public function create()
 {
     $createTableSql = 'CREATE TABLE IF NOT EXISTS events (
         organisation TEXT,
         project TEXT,
         event_id INTEGER,
         created TEXT,
         UNIQUE(organisation, project, event_id, created)
     )';
     $this->connection->executeQuery($createTableSql);
 }

 /**
  * @param $table
  * @param $primaryKeyName
  * @param $keyName
  * @param $keyValues
  * @return array
  * @throws \Doctrine\DBAL\DBALException
  */
 public function selectByKey($table, $primaryKeyName, $keyName, $keyValues)
 {
     $basicSql = "SELECT * FROM `{$table}`";
     if (!empty($keyValues)) {
         $where = " WHERE {$keyName} IN (?)";
         $statement = $this->connection->executeQuery($basicSql . $where, [$keyValues], [\Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);
     } else {
         $statement = $this->connection->executeQuery($basicSql);
     }
     return $statement->fetchAll(\PDO::FETCH_ASSOC);
 }

 /**
  * @param string $className
  */
 public function build($className)
 {
     if ($this->reflectionService->isClassAnnotatedWith($className, Table::class) === FALSE) {
         throw new \InvalidArgumentException('The class "' . $className . '" is not annotated properly.', 1428331094);
     }
     /** @var Table $table */
     $table = $this->reflectionService->getClassAnnotation($className, Table::class);
     $query = $this->createTableDefinitionQueryForClassName($className);
     $this->databaseConnection->executeQuery('DROP TABLE IF EXISTS ' . $table->name);
     $this->databaseConnection->executeQuery($query);
 }