Yii Framework official guide series 23-database use: Data Access Object (DAO)

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 ....

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, Yii DAO, PDO extension, and specific PDO database drivers (suchPDO_MYSQL) Must be installed. 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.

$ 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.

• 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 inherits from CApplicationComponent, it can also be used as an application component. To do this, configuredb(Or other names) the application components are 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 useYii::app()->dbThe database is connected. 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.

$ 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, suchINSERT,UPDATEAndDELETE. 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 exampleSELECT. If it succeeds, it returns a CDbDataReader instance through which the result row of data can be traversed. For simplicity, (Yii) also implements a seriesqueryXXX()Method to directly return the query results.

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

$ 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 alsoforeachUse CDbDataReader to retrieve data in one row in the language structure.

$ 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 (), allqueryXXX()Method directly returns 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:

$ 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.

// 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.

$ 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, an application can usetbl_Used as the table prefix and another one can useyii_. To use the table prefix, set the CDbConnection: tablePrefix attribute to the expected table prefix. Then, use{{TableName}}The name of the table.TableNameTable name without a prefix. For example, if the database containstbl_userAndtbl_If the table prefix is configured, we can use the following code to execute user-related queries:

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

The above is the Yii Framework official guide series 23-use the database: Data Access Object (DAO) content. For more information, please follow the PHP Chinese network (www.php1.cn )!