Data Access Objects (DAO)

Data Access Objects (DAO) provides a generic API to access data stored indifferent database management systems (DBMS). As a result, the underlyingDBMS can be changed to a different one without requiring change of the codewhich uses DAO to access the data.

Yii DAO is built on top of PHP Data Objects(PDO) which is an extensionproviding unified data access to many popular DBMS, such as MySQL,PostgreSQL. Therefore, to use Yii DAO, the PDO extension and the specificPDO database driver (e.g. PDO_MYSQL) have to be installed.

Yii DAO mainly consists of the following four classes:

  • CDbConnection: represents a connection to a database.
  • CDbCommand: represents an SQL statement to execute against a database.
  • CDbDataReader: represents a forward-only stream of rows from a query result set.
  • CDbTransaction: represents a DB transaction.
    In the following, we introduce the usage of Yii DAO in differentscenarios.

1. Establishing Database Connection

To establish a database connection, create a CDbConnection instance andactivate it. A data source name (DSN) is needed to specify the informationrequired to connect to the database. A username and password may also beneeded to establish the connection. An exception will be raised in case anerror occurs during establishing the connection (e.g. bad DSN or invalidusername/password).

  1. $connection=new CDbConnection($dsn,$username,$password);
  2. // establish connection. You may try...catch possible exceptions
  3. $connection->active=true;
  4. ......
  5. $connection->active=false;  // close connection

The format of DSN depends on the PDO database driver in use. In general, aDSN consists of the PDO driver name, followed by a colon, followed by thedriver-specific connection syntax. See PDOdocumentation for completeinformation. Below is a list of commonly used DSN formats:

  • SQLite: sqlite:/path/to/dbfile
  • MySQL: mysql:host=localhost;dbname=testdb
  • PostgreSQL: pgsql:host=localhost;port=5432;dbname=testdb
  • SQL Server: mssql:host=localhost;dbname=testdb
  • Oracle: oci:dbname=//localhost:1521/testdb
    Because CDbConnection extends from CApplicationComponent, we can alsouse it as an applicationcomponent. To do so, configurein a db (or other name) application component in the applicationconfiguration as follows,
  1. array(
  2.     ......
  3.     'components'=>array(
  4.         ......
  5.         'db'=>array(
  6.             'class'=>'CDbConnection',
  7.             'connectionString'=>'mysql:host=localhost;dbname=testdb',
  8.             'username'=>'root',
  9.             'password'=>'password',
  10.             'emulatePrepare'=>true,  // needed by some MySQL installations
  11.         ),
  12.     ),
  13. )

We can then access the DB connection via Yii::app()->db which is alreadyactivated automatically, unless we explictly configureCDbConnection::autoConnect to be false. Using this approach, the singleDB connection can be shared in multiple places in our code.

2. Executing SQL Statements

Once a database connection is established, SQL statements can be executedusing CDbCommand. One creates a CDbCommand instance by callingCDbConnection::createCommand() with the specified SQL statement:

  1. $command=$connection->createCommand($sql);
  2. // if needed, the SQL statement may be updated as follows:
  3. // $command->text=$newSQL;

A SQL statement is executed via CDbCommand in one of the following twoways:

  • execute(): performs a non-query SQL statement,such as INSERT, UPDATE and DELETE. If successful, it returns thenumber of rows that are affected by the execution.

  • query(): performs an SQL statement that returnsrows of data, such as SELECT. If successful, it returns a CDbDataReaderinstance from which one can traverse the resulting rows of data. Forconvenience, a set of queryXXX() methods are also implemented whichdirectly return the query results.

An exception will be raised if an error occurs during the execution of SQLstatements.

  1. $rowCount=$command->execute();   // execute the non-query SQL
  2. $dataReader=$command->query();   // execute a query SQL
  3. $rows=$command->queryAll();      // query and return all rows of result
  4. $row=$command->queryRow();       // query and return the first row of result
  5. $column=$command->queryColumn(); // query and return the first column of result
  6. $value=$command->queryScalar();  // query and return the first field in the first row

3. Fetching Query Results

After CDbCommand::query() generates the CDbDataReader instance, onecan retrieve rows of resulting data by calling CDbDataReader::read()repeatedly. One can also use CDbDataReader in PHP's foreach languageconstruct to retrieve row by row.

  1. $dataReader=$command->query();
  2. // calling read() repeatedly until it returns false
  3. while(($row=$dataReader->read())!==false) { ... }
  4. // using foreach to traverse through every row of data
  5. foreach($dataReader as $row) { ... }
  6. // retrieving all rows at once in a single array
  7. $rows=$dataReader->readAll();
Note: Unlike query(), all queryXXX() methods return data directly. For example, queryRow() returns an array representing the first row of the querying result.

4. Using Transactions

When an application executes a few queries, each reading and/or writinginformation in the database, it is important to be sure that the databaseis not left with only some of the queries carried out. A transaction,represented as a CDbTransaction instance in Yii, may be initiated in thiscase:

  • Begin the transaction.
  • Execute queries one by one. Any updates to the database are not visible to the outside world.
  • Commit the transaction. Updates become visible if the transaction is successful.
  • If one of the queries fails, the entire transaction is rolled back.
    The above workflow can be implemented using the following code:
  1. $transaction=$connection->beginTransaction();
  2. try
  3. {
  4.     $connection->createCommand($sql1)->execute();
  5.     $connection->createCommand($sql2)->execute();
  6.     //.... other SQL executions
  7.     $transaction->commit();
  8. }
  9. catch(Exception $e) // an exception is raised if a query fails
  10. {
  11.     $transaction->rollBack();
  12. }

5. Binding Parameters

To avoid SQL injectionattacks and to improveperformance of executing repeatedly used SQL statements, one can "prepare"an SQL statement with optional parameter placeholders that are to bereplaced with the actual parameters during the parameter binding process.

The parameter placeholders can be either named (represented as uniquetokens) or unnamed (represented as question marks). CallCDbCommand::bindParam() or CDbCommand::bindValue() to replace theseplaceholders with the actual parameters. The parameters do not need to bequoted: the underlying database driver does it for you. Parameter bindingmust be done before the SQL statement is executed.

  1. // an SQL with two placeholders ":username" and ":email"
  2. $sql="INSERT INTO users(username, email) VALUES(:username,:email)";
  3. $command=$connection->createCommand($sql);
  4. // replace the placeholder ":username" with the actual username value
  5. $command->bindParam(":username",$username,PDO::PARAM_STR);
  6. // replace the placeholder ":email" with the actual email value
  7. $command->bindParam(":email",$email,PDO::PARAM_STR);
  8. $command->execute();
  9. // insert another row with a new set of parameters
  10. $command->bindParam(":username",$username2,PDO::PARAM_STR);
  11. $command->bindParam(":email",$email2,PDO::PARAM_STR);
  12. $command->execute();

The methods bindParam() andbindValue() are very similar. The only differenceis that the former binds a parameter with a PHP variable reference whilethe latter with a value. For parameters that represent large block of datamemory, the former is preferred for performance consideration.

For more details about binding parameters, see the relevant PHPdocumentation.

6. Binding Columns

When fetching query results, one can also bind columns with PHP variablesso that they are automatically populated with the latest data each time arow is fetched.

  1. $sql="SELECT username, email FROM users";
  2. $dataReader=$connection->createCommand($sql)->query();
  3. // bind the 1st column (username) with the $username variable
  4. $dataReader->bindColumn(1,$username);
  5. // bind the 2nd column (email) with the $email variable
  6. $dataReader->bindColumn(2,$email);
  7. while($dataReader->read()!==false)
  8. {
  9.     // $username and $email contain the username and email in the current row
  10. }

原文: https://www.yiichina.com/doc/guide/1.0/database.dao