Yii Learning Summary Data Access object (DAO), yiidao_php tutorial

Yii Learning Summary Data Access object (DAO), Yiidao

YII provides powerful database programming support. The Yii data Access Object (DAO) is built on the PHP data object (PDO) extension, making it possible to access different database management systems (DBMS) in a single unified interface. Applications developed using Yii's DAO can easily switch between using different database management systems without needing to modify the data access code.

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

Yii DAO is built on PHP Data Objects (PDO). It is an extension of unified data access for many popular DBMS including MySQL, PostgreSQL, and so on. Therefore, to use YII dao,pdo extensions and specific PDO database drivers (such as pdo_mysql) must be installed.

Yii DAO mainly consists of the following four classes:

Cdbconnection: Represents a database connection.
Cdbcommand: Represents a SQL statement executed through the database.
Cdbdatareader: Represents a stream that moves forward only, from a row in a query result set.
Cdbtransaction: Represents a database transaction.
Below, we introduce the application of Yii DAO in different scenarios.

1. Establishing a database connection
To establish a database connection, create a cdbconnection instance and activate it. Connecting to a database requires a data source name (DSN) to specify the connection information. The user name and password may also be used. An exception is thrown when an error occurs during connection to the database (for example, an incorrect DSN or an invalid user name/password).

Copy the Code code as follows:
$connection =new cdbconnection ($dsn, $username, $password);
Establish the connection. You can use Try...catch to catch exceptions that can be thrown
$connection->active=true;
......
$connection->active=false; Close connection

The format of the DSN depends on the PDO database driver used. In general, the DSN should contain the PDO-driven name, followed by a colon, followed by the driver-specific connection syntax. You can consult the PDO documentation for more information. The following is a list of commonly used DSN formats.

Copy the Code code as follows:
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

Since Cdbconnection inherits from Capplicationcomponent, we can also use it as an application component. To do this, configure a db (or other Name) application component in the application configuration as follows:

Copy the Code code 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 intentionally configured Cdbconnection::autoconnect to False. In this way, this single DB connection can be shared in many places in our code.

2. Execute SQL statements
Once the database connection is established, the SQL statement can be executed by using Cdbcommand. You can create an Cdbcommand instance by calling Cdbconnection::createcommand () as a parameter by using the specified SQL statement.

Copy the Code code as follows:
$connection =yii::app ()->db; Suppose 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 in the following ways:
$command->text= $newSQL;

An SQL statement is executed in the following two ways through Cdbcommand:

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

Query (): Executes an SQL statement that returns several rows of data, such as SELECT. If successful, it returns a Cdbdatareader instance through which the result row of the data can be traversed. For simplicity, (YII) also implements a series of queryxxx () methods to directly return query results.

An exception will be thrown if an error occurs while executing the SQL statement.

Copy the Code code as follows:
$rowCount = $command->execute (); Execute Query-free SQL
$dataReader = $command->query (); Execute an SQL query
$rows = $command->queryall (); Query and return all rows in the result
$row = $command->queryrow (); Query and return the first row in the result
$column = $command->querycolumn (); Query and return the first column in the result
$value = $command->queryscalar (); Query and return the first field in the first row of the result

3. Get query Results
After Cdbcommand::query () generates the Cdbdatareader instance, you can get the rows in the results by repeating the call to Cdbdatareader::read (). You can also retrieve data in the Foreach language structure of PHP using a cdbdatareader row of rows.

Copy the Code code as follows:
$dataReader = $command->query ();
Repeats the call to read () until it returns false
while (($row = $dataReader->read ())!==false) {...}
Use foreach to iterate through each row in the data
foreach ($dataReader as $row) {...}
Extracts all rows to an array at once
$rows = $dataReader->readall ();

Note: Unlike query (), all queryxxx () methods return data directly. For example, Queryrow () returns an array representing the first row of the query results.

4. Using Transactions
When an application executes several queries and each query is read from the database and/or writes information to the database, it is important to ensure that the database does not leave a few queries and that only a few other queries are executed. transactions, which are represented as cdbtransaction instances in Yii, may be started in the following scenarios:

Begins a transaction.
Execute the query. Any updates to the database are not visible to the outside world.
Commits the transaction. If the transaction succeeds, the update becomes visible.
If one of the queries fails, the entire transaction is rolled back.
The above workflow can be implemented with the following code:

Copy the Code code as follows:
$transaction = $connection->begintransaction ();
Try
{
$connection->createcommand ($sql 1)->execute ();
$connection->createcommand ($sql 2)->execute ();
.... Other SQL executions
$transaction->commit ();
}
catch (Exception $e)//If a query fails, an exception is thrown
{
$transaction->rollback ();
}

5. Binding parameters
To avoid SQL injection attacks and increase the efficiency of repeated SQL statements, you can "prepare" (prepare) a SQL statement with optional parameter placeholders, which will be replaced with actual parameters at parameter binding.

The parameter placeholder can be either named (represented as a unique token) or unnamed (represented as a question mark). Call Cdbcommand::bindparam () or Cdbcommand::bindvalue () to replace these placeholders with the actual parameters. These parameters do not need to be quoted: the underlying database driver will take care of this for you. Parameter bindings must be completed before the SQL statement executes.

Copy the Code code as follows:
A SQL with two placeholders ": Username" and ": E-mail"
$sql = "INSERT into Tbl_user (username, email) VALUES (: Username,:email)";
$command = $connection->createcommand ($sql);
Replace the placeholder ": username" with the actual user name
$command->bindparam (": Username", $username, PDO::P aram_str);
Replace the placeholder with the actual email ": E-mail"
$command->bindparam (": Email", $email, PDO::P aram_str);
$command->execute ();
Insert another row with a new parameter set
$command->bindparam (": username", $username 2,pdo::P aram_str);
$command->bindparam (": Email", $email 2,pdo::P aram_str);
$command->execute ();

Methods Bindparam () and Bindvalue () are very similar. The only difference is that the former uses a PHP variable binding parameter, and the latter uses a value. For those large data block parameters in memory, in the performance considerations, the former should be used preferentially.

For more information on binding parameters, please refer to the relevant PHP documentation.

6. Binding columns
When you get the results of a query, you can also bind columns using PHP variables. This will automatically populate with the most recent values each time you get a row in the query results.

Copy the Code code as follows:
$sql = "Select username, email from tbl_user";
$dataReader = $connection->createcommand ($sql)->query ();
Binding the first column with a $username variable (username)
$dataReader->bindcolumn (1, $username);
Bind second column with $email variable (email)
$dataReader->bindcolumn (2, $email);
while ($dataReader->read ()!==false)
{
$username and $email contain username and email in the current line
}

7. Using Table prefixes
From version 1.1.0, YII provides integrated support for using table prefixes. A table prefix is a string that is added before the name of the data table in the currently connected database. It is often used in a shared server environment where multiple applications may share the same database, using different table prefixes to differentiate each other. For example, one app can use Tbl_ as the table prefix and the other can use Yii_.

To use the table prefix, configure the Cdbconnection::tableprefix property to the desired table prefix. The {{TableName}} is then used in the SQL statement to represent the name of the table, 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 a table prefix, then we can execute user-related queries using the following code:

Copy the Code code as follows:
$sql = ' SELECT * from {{user}} ';
$users = $connection->createcommand ($sql)->queryall ();

http://www.bkjia.com/PHPjc/959100.html www.bkjia.com true http://www.bkjia.com/PHPjc/959100.html techarticle Yii learns to summarize data Access Objects (DAO), Yiidao Yii provides strong database programming support. Yii data Access Object (DAO) is built on PHP data Object (PDO) extension, making it in a ...

