代码示例

YII 1.1 测试用例

简单的连接并执行简单查询示例如下

<?php

// change the following paths if necessary
$yii=dirname(__FILE__).'/../framework/yii.php';

require_once($yii);

$dsn = "aci:dbname=127.0.0.1:2003/osrdb";
$username = "sysdba";
$password = "szoscar55";

$connection=new CDbConnection($dsn,$username,$password);

$connection->active=true;

$sql="SELECT VERSION";
$command=$connection->createCommand($sql);

$dataReader=$command->query();


foreach($dataReader as $result)
{
    print_r($result);
}


$connection->active=false; // 关闭连接

插入行数据并查询行数据示例

<?php

// change the following paths if necessary
$yii=dirname(__FILE__).'/../framework/yii.php';

require_once($yii);

$dsn = "aci:dbname=127.0.0.1:2003/osrdb";
$username = "sysdba";
$password = "szoscar55";

$connection=new CDbConnection($dsn,$username,$password);

$connection->active=true;

$insertSql = "INSERT INTO TAB1 (name) VALUES (:name)";
$command = $connection->createCommand($insertSql);
$command->bindValue(':name','name11',PDO::PARAM_STR);
$result = $command -> execute();

$sql = "SELECT * FROM TAB1";
$command = $connection->createCommand($sql);

$dataReader=$command->query();

foreach($dataReader as $result)
{
    print_r($result);
}

print("\n");

$rows = $connection->createCommand($sql)->queryAll();

foreach($rows as $row)
{
    print_r($row);
}

$connection->active=false;

查询并筛选示例

<?php

// change the following paths if necessary
$yii=dirname(__FILE__).'/../framework/yii.php';

require_once($yii);

$dsn = "aci:dbname=127.0.0.1:2003/osrdb";
$username = "sysdba";
$password = "szoscar55";

$connection=new CDbConnection($dsn,$username,$password);

$connection->active=true;


$thisRow = $connection->createCommand()
    ->select('ID, NAME')
    ->from('TAB1')
    //->join('tbl_profile p', 'u.id=p.user_id')
    ->where('id=:id', array(':id'=>'1'))
    ->queryRow();

print_r($thisRow);


$connection->active=false;

ActiveRecord 代码示例

<?php

// change the following paths if necessary
$yii=dirname(__FILE__).'/../framework/yii.php';

require_once($yii);


// CREATE TABLE tab_post (
//     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
//     title VARCHAR(128) NOT NULL,
//     content TEXT NOT NULL,
//     create_time INTEGER NOT NULL
// );
class Post extends CActiveRecord
{
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function tableName()
    {
        return 'TAB_POST';
    }

    public function primaryKey()
    {
        return 'ID';
    }

    public function getDbConnection()
    {
        $dsn = "aci:dbname=127.0.0.1:2003/osrdb";
        $username = "sysdba";
        $password = "szoscar55";

        $connection=new CDbConnection($dsn,$username,$password);

        $connection->active=true;

        return $connection;
    }
}

// $post=new Post;
// $post->TITLE='sample post';
// $post->CONTENT='content for the sample post';
// $post->CREATE_TIME=time();
// $post->save();

// $post=Post::model()->find('ID=:postID', array(':postID'=>1));

// $post->TITLE ='post1';
// $post->save();

// $post=Post::model()->find('ID=:postID', array(':postID'=>1));

// print_r($post);


// $posts=Post::model()->findAll('ID>:postID', array(':postID'=>1));

// print_r($posts);

$post=Post::model()->findByPk(2);
$post->delete();

YII 2 测试用例

以下示例展示创建连接并查询表行数据，执行更新操作，展示参数绑定等操作

<?php

require_once __DIR__ . '/../vendor/yiisoft/yii2/Yii.php';
require __DIR__ .'/../vendor/autoload.php';

$connection = new \yii\db\Connection([
    'dsn' => 'aci:dbname=127.0.0.1:2003/osrdb',
    'username' => 'sysdba',
    'password' => 'szoscar55',
    'charset' => 'utf8',
]);
$connection->open();

$command = $connection->createCommand('SELECT * FROM tab1');
$posts = $command->queryAll();

print_r($posts);

$command = $connection->createCommand("UPDATE tab1 SET name='newname1' where id = 1");
$command->execute();

$command = $connection->createCommand('SELECT * FROM tab1 WHERE id=:id');
$command->bindValue(':id', 1);
$reader = $command->query();

// while ($row = $reader->read()) {
//     $rows[] = $row;
// }

$rows = $reader->readAll();
print_r($rows);

以下示例展示 YII2 查询系列操作，以及增删改查操作。

<?php

require_once __DIR__ . '/../vendor/yiisoft/yii2/Yii.php';
require __DIR__ .'/../vendor/autoload.php';

$connection = new \yii\db\Connection([
    'dsn' => 'aci:dbname=127.0.0.1:2003/osrdb',
    'username' => 'sysdba',
    'password' => 'szoscar55',
    'charset' => 'utf8',
]);
$connection->open();

// 返回多行. 每行都是列名和值的关联数组.
// 如果该查询没有结果则返回空数组
// $posts = $connection->createCommand('SELECT * FROM tab1')
//             ->queryAll();

// print_r($posts);

// // 返回一行 (第一行)
// // 如果该查询没有结果则返回 false
// $post = $connection->createCommand('SELECT * FROM tab1 WHERE id=1')
//            ->queryOne();

// print_r($post);

// // 返回一列 (第一列)
// // 如果该查询没有结果则返回空数组
// $titles = $connection->createCommand('SELECT name FROM tab1')
//              ->queryColumn();

// print_r($titles);

// // 返回一个标量值
// // 如果该查询没有结果则返回 false
// $count = $connection->createCommand('SELECT COUNT(*) FROM tab1')
//              ->queryScalar();

// print_r($count);
// print("\n");


// $command = $connection->createCommand('SELECT * FROM tab1 WHERE id=:id')
//               ->bindParam(':id', $id);


// // $id = 1;
// // $post1 = $command->queryOne();

// // $id = 2;
// // $post2 = $command->queryOne();

// $post2 = $command->bindValue(':id', 2)->queryOne();
// $post1 = $command->bindValue(':id', 1)->queryOne();


// print_r($post1);
// print_r($post2);


$result = $connection->createCommand()->batchInsert('TAB1', ['NAME'], [
    ['test1'],
    ['test2'],
    ['test3'],
])->execute();

// INSERT
$connection->createCommand()->insert('TAB1', [
    'NAME' => 'Sam'
])->execute();

// UPDATE
$connection->createCommand()->update('TAB1', ['NAME' => 'Sam1'], "NAME = 'Sam'")->execute();

// DELETE
$connection->createCommand()->delete('TAB1',  "NAME = 'Sam1'")->execute();

YII2 Query 操作，代码示例如下

<?php

require_once __DIR__ . '/../vendor/yiisoft/yii2/Yii.php';
require __DIR__ .'/../vendor/autoload.php';

$connection = new \yii\db\Connection([
    'dsn' => 'aci:dbname=127.0.0.1:2003/osrdb',
    'username' => 'sysdba',
    'password' => 'szoscar55',
    'charset' => 'utf8',
]);
$connection->open();

$query = new \yii\db\Query();

$rows = $query->select(['ID', 'NAME'])
->from('TAB1')
->where(['NAME' => 'test2'])
->limit(10)
->all($connection);

print_r($rows);

YII2 ActiveRecord 代码示例

<?php

require_once __DIR__ . '/../vendor/yiisoft/yii2/Yii.php';
require __DIR__ .'/../vendor/autoload.php';


use yii\db\ActiveRecord;

class Customer extends ActiveRecord
{

    /**
    * @return string Active Record 类关联的数据库表名称
    */
    public static function tableName()
    {
        return 'TAB_SEQ';
    }

    public static function getDb()
    {
        // 使用 "db2" 组件
        $connection = new \yii\db\Connection([
            'dsn' => 'aci:dbname=127.0.0.1:2003/osrdb',
            'username' => 'sysdba',
            'password' => 'szoscar55',
            'charset' => 'utf8',
        ]);
        $connection->open();
        return $connection;
    }

}

$result = Customer::getDb()->createCommand()->batchInsert(Customer::tableName(), ['NAME'], [
    ['test1'],
    ['test2'],
    ['test3'],
])->execute();

$customer = new Customer;

$customer->NAME = "test4";

$customer->save();

$customer = new Customer;

$customer->NAME = "test5";

$customer->save();


$customers = Customer::find()
->indexBy('ID')
->all();

print_r($customers);