Yii Learning Summary-Data Access Object (DAO)

This is the second article in The YII study summary series. it mainly introduces the Data Access Object (DAO), which is very detailed, if you need it, refer to this article. this is the second article in The YII learning Summary Series. it mainly introduces you to the Data Access Object (DAO), which is very detailed. if you need it, refer to it.

Yii provides powerful database programming support. The Yii Data Access Object (DAO) is built on the data object (PDO) extension of PHP, so that a single unified interface can access different database management systems (DBMS ). Applications developed using DAO of Yii can easily switch to different database management systems without modifying the data access code.

The Data Access Object (DAO) provides a common API for accessing data stored in different database management systems (DBMS. Therefore, when replacing the underlying DBMS with another one, you do not need to modify the code that uses DAO to access data.

Yii DAO is built based on PHP Data Objects (PDO. It is an extension that provides unified data access for many popular DBMS, including MySQL and PostgreSQL. Therefore, to use Yii DAO, PDO extensions, and specific PDO database drivers (such as PDO_MYSQL), you must install them.

Yii DAO mainly includes the following four classes:

CDbConnection: represents a database connection.
CDbCommand: indicates an SQL statement executed through the database.
CDbDataReader: a stream that only moves forward from a row in a query result set.
CDbTransaction: represents a database transaction.
Next, we will introduce the application of Yii DAO in different scenarios.

1. establish a database connection
To establish a database connection, create a CDbConnection instance and activate it. To connect to the database, a data source name (DSN) is required to specify the connection information. The user name and password may also be used. When an error occurs during Database Connection (for example, the incorrect DSN or invalid username/password), an exception is thrown.

The code is as follows:

$ Connection = new CDbConnection ($ dsn, $ username, $ password); // establish a connection. You can use try... catch to catch exceptions that may be thrown $ connection-> active = true; ...... $ connection-> active = false; // close the connection.

The DSN format depends on the PDO database driver used. In general, DSN must contain the name of the PDO driver, followed by a colon, and followed by the driver-specific connection syntax. For more information, see The PDO documentation. The following is a list of commonly used DSN formats.

The code is as follows:

SQLite: sqlite:/path/to/dbfileMySQL: mysql:host=localhost;dbname=testdbPostgreSQL: pgsql:host=localhost;port=5432;dbname=testdbSQL Server: mssql:host=localhost;dbname=testdbOracle: oci:dbname=//localhost:1521/testdb

Because CDbConnection inherits from CApplicationComponent, it can also be used as an application component. To do this, configure a db (or other name) application component in the application configuration as follows:

The code is as follows:

array(    ......    'components'=>array(        ......        'db'=>array(            'class'=>'CDbConnection',            'connectionString'=>'mysql:host=localhost;dbname=testdb',            'username'=>'root',            'password'=>'password',            'emulatePrepare'=>true,  // needed by some MySQL installations        ),    ),)

Then we can access the database connection through Yii: app ()-> db. It has been automatically activated, unless we have specially configured CDbConnection: autoConnect to false. In this way, this separate DB connection can be shared in many places in our code.

2. execute SQL statements
After the database connection is established, the SQL statement can be executed by using CDbCommand. You can call CDbConnection: createCommand () using the specified SQL statement as the parameter to create a CDbCommand instance.

The code is as follows:

$ Connection = Yii: app ()-> db; // assume that you have established a "db" connection. // If not, you may need to explicitly establish a connection: // $ connection = new CDbConnection ($ dsn, $ username, $ password); $ command = $ connection-> createCommand ($ SQL); // if necessary, this SQL statement can be modified as follows: // $ command-> text = $ newSQL;

An SQL statement is executed in the following two methods:

Execute (): execute a non-query SQL statement, such as INSERT, UPDATE, and DELETE. If the execution succeeds, it returns the number of rows affected by the execution.

Query (): execute an SQL statement that returns several rows of data, for example, SELECT. If it succeeds, it returns a CDbDataReader instance through which the result row of data can be traversed. For convenience, (Yii) also implements a series of queryXXX () methods to directly return the query results.

If an error occurs during SQL statement execution, an exception is thrown.

The code is as follows:

$ RowCount = $ command-> execute (); // run the SQL statement without query $ dataReader = $ command-> query (); // execute an SQL query $ rows = $ command-> queryAll (); // query and return all rows in the result $ row = $ command-> queryRow (); // The first row in the query result $ column = $ command-> queryColumn (); // The first column in the query result $ value = $ command-> queryScalar (); // query and return the first field in the first row of the result

3. obtain query results
After CDbCommand: query () generates a CDbDataReader instance, you can call CDbDataReader: read () to obtain the rows in the result. You can also use CDbDataReader to retrieve data in a row in the foreach structure of PHP.

The code is as follows:

$ DataReader = $ command-> query (); // call read () repeatedly until it returns falsewhile ($ row = $ dataReader-> read ())! = False ){...} // use foreach to traverse each row of data. foreach ($ dataReader as $ row ){...} // extract all rows to an array at a time $ rows = $ dataReader-> readAll ();

Note: Unlike query (), all queryXXX () methods directly return data. For example, queryRow () returns an array representing the first row of the query result.

4. use transactions
When an application executes several queries and each query reads information from the database and/or writes information to the database, it is very important to ensure that the database does not leave several queries but only executes several other queries. Transaction, represented as a CDbTransaction instance in Yii, may be started in the following situations:

Start transaction.
Perform queries one by one. Any updates to the database are invisible to the outside world.
Commit a transaction. If the transaction is successful, the update becomes visible.
If one of the queries fails, the entire transaction is rolled back.
The preceding workflow can be implemented using the following code:

The code is as follows:

$ Transaction = $ connection-> beginTransaction (); try {$ connection-> createCommand ($ sql1)-> execute (); $ connection-> createCommand ($ sql2) -> execute ();//.... other SQL executions $ transaction-> commit ();} catch (Exception $ e) // if a query fails, an Exception {$ transaction-> rollBack ();} is thrown ();}

5. bind parameters
To avoid SQL injection attacks and improve the efficiency of repeatedly executing SQL statements, you can "prepare (prepare)" an SQL statement containing the optional parameter placeholders. when binding parameters, these placeholders are replaced with actual parameters.

Parameter placeholders can be named (represented as a unique tag) or unnamed (represented as a question mark ). Call CDbCommand: bindParam () or CDbCommand: bindValue () to replace these placeholders with actual parameters. These parameters do not need to be enclosed by quotation marks: the underlying database driver will handle this for you. Parameter binding must be completed before the SQL statement is executed.

The code is as follows:

// An SQL statement with two placeholders ": username" and ": email" $ SQL = "INSERT INTO tbl_user (username, email) VALUES (: username,: email )"; $ command = $ connection-> createCommand ($ SQL); // replace the placeholder ": username" $ command-> bindParam (": username", $ username, PDO: PARAM_STR); // replace the placeholder with the actual Email ": email" $ command-> bindParam (": email", $ email, PDO: PARAM_STR ); $ command-> execute (); // use the new parameter set to insert another row $ command-> bindParam (": username", $ username2, PDO: PARAM_STR ); $ command-> bindParam (": email", $ email2, PDO: PARAM_STR); $ command-> execute ();

The bindParam () method is very similar to bindValue. The only difference is that the former uses a PHP variable to bind a parameter, while the latter uses a value. For the big data block parameters in the memory, the performance should be considered first.

For more information about binding parameters, see related PHP documents.

6. bind columns
When obtaining the query result, you can also bind the column with the PHP variable. In this way, the latest value is automatically filled with each row in the query result.

The code is as follows:

$ SQL = "SELECT username, email FROM tbl_user"; $ dataReader = $ connection-> createCommand ($ SQL)-> query (); // bind the first column (username) $ dataReader-> bindColumn (1, $ username) with the $ username variable; // bind the second column (email) with the $ email variable) $ dataReader-> bindColumn (2, $ email); while ($ dataReader-> read ()! = False) {// $ username and $ email contain the username and email in the current row}

7. use the table prefix
From version 1.1.0, Yii provides integrated support for table prefix usage. A table prefix is a string that is added before the name of a data table in the currently connected database. It is often used in a shared server environment. in this environment, multiple applications may share the same database. different table prefixes are used to differentiate each other. For example, one application can use tbl _ as the table prefix, and the other can use yii _.

To use the table prefix, set the CDbConnection: tablePrefix attribute to the expected table prefix. Then, {TableName} is used in the SQL statement to represent the table name, where TableName refers to the table name without a prefix. For example, if the database contains a table named tbl_user and tbl _ is configured as the table prefix, we can use the following code to execute user-related queries:

The code is as follows:

$sql='SELECT * FROM {{user}}';$users=$connection->createCommand($sql)->queryAll();