数据库之DAO

数据库之DAO DAO (Data Access Object) 数据访问对象是第一个面向对象的接口 –百度百科 数据库之DAO CRUD 查询条件 联合查询 join 事务Transaction CRUD class IndexController extends CController { public function actionCreate () { $rval = Yii::app(

数据库之DAO

DAO (Data Access Object) 数据访问对象是第一个面向对象的接口
–百度百科

• 数据库之DAO
• CRUD
• 查询条件
• 联合查询 join
• 事务Transaction

CRUD

<code><span><span>class</span> <span>IndexController</span> <span>extends</span> <span>CController</span>{</span>
    <span>public</span> <span><span>function</span> <span>actionCreate</span><span>()</span>
    {</span>
        <span>$rval</span> = Yii::app()->db->createCommand()->insert(<span>'user'</span>,<span>array</span>(
            <span>'username'</span>=><span>'blue'</span> <span>//传入数组不需要担心注入 ， yii自动会对数组进行 参数绑定的操作写入</span>
        ));
    }
    <span>public</span> <span><span>function</span> <span>actionDelete</span><span>(<span>$id</span>)</span>
    {</span>
        Yii::app()->db->createCommand()->delete(<span>'user'</span>,<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>));
    }
    <span>public</span> <span><span>function</span> <span>actionUpdate</span><span>(<span>$id</span>)</span>
    {</span>
        Yii::app()->db->createCommand()->update(<span>'user'</span>,<span>array</span>(<span>'username'</span>=><span>'blue'</span>),<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>));
    }
    <span>//一维数组 单条数据</span>
    <span>public</span> <span><span>function</span> <span>actionReadRow</span><span>(<span>$id</span>)</span>
    {</span>
        <span>$res</span> = Yii::app()->db->createCommand()->select(<span>'username'</span>)->from(<span>'user'</span>)->where(<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>))->queryRow();
        var_dump(<span>$res</span>);
    }
    <span>//查询列</span>
    <span>//比如说 查询的是所有的username</span>
    <span>//返回的数组是 array('姓名1','姓名2','姓名3')</span>
    <span>public</span> <span><span>function</span> <span>actionReadColumn</span><span>(<span>$id</span>)</span>
    {</span>
        <span>$res</span> = Yii::app()->db->createCommand()->select(<span>'username'</span>)->from(<span>'user'</span>)->where(<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>))->queryColumn();
        var_dump(<span>$res</span>);
    }
    <span>//二维数组 查询所有</span>
    <span>public</span> <span><span>function</span> <span>actionReadAll</span><span>(<span>$id</span>)</span>
    {</span>
        <span>$res</span> = Yii::app()->db->createCommand()->select(<span>'username'</span>)->from(<span>'user'</span>)->where(<span>'id=:id'</span>,<span>array</span>(<span>':id'</span>=><span>$id</span>))->queryAll();
        var_dump(<span>$res</span>);
    }
    <span>//查询数量</span>
    <span>//直接返回对应值，而不是数组</span>
    <span>public</span> <span><span>function</span> <span>actionReadScalar</span><span>()</span>
    {</span>
        <span>$res</span> = Yii::app()->db->createCommand()->select(<span>'count(*)'</span>)->from(<span>'user'</span>)->queryScalar();
        var_dump(<span>$res</span>);
    }
}</code>

查询条件

where,like,in,limit,order,group

<code><span>public</span> <span><span>function</span> <span>actionWhere</span><span>()</span>
    {</span>
        <span>$connect</span> = Yii::app()->db;

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>'id<:id>,<span>array</span>(<span>':id'</span>=><span>3</span>))
                ->queryAll();


        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>'id>:lid and id ,<span>array</span>(<span>':lid'</span>=><span>3</span>,<span>":mid"</span>=><span>7</span>))
                ->queryAll();


        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>'id > :lid'</span>,<span>array</span>(<span>':lid'</span>=><span>3</span>))
                ->andWhere(<span>'id ,<span>array</span>(<span>':mid'</span>=><span>7</span>))
                ->queryAll();

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>array</span>(<span>'and'</span>,<span>'id > :lid'</span>,<span>'id ),<span>array</span>(<span>':lid'</span>=><span>3</span>,<span>":mid"</span>=><span>8</span>))
                ->queryAll();

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>array</span>(<span>'and'</span>,<span>'id > :lid'</span>,<span>'id ),<span>array</span>(<span>':lid'</span>=><span>3</span>,<span>":mid"</span>=><span>8</span>))
                ->queryAll();

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>array</span>(<span>'in'</span>,<span>'id'</span>,<span>array</span>(<span>3</span>,<span>4</span>,<span>5</span>)))
                <span>//->where(array('not in','id',array(3,4,5)))</span>
                ->queryAll();

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>array</span>(<span>'like'</span>,<span>'username'</span>,<span>'%g%'</span>))
<span>//                ->where(array('not like','username','%g%'))</span>
<span>//                ->where(array('like','username',array('%g%','%o%')))</span>
                ->queryAll();

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>array</span>(<span>'and'</span>,<span>'id > :id'</span>,<span>'id ,<span>array</span>(<span>'or'</span>,<span>'username = :user1'</span>,<span>'username = :user2'</span>)),
                        <span>array</span>(<span>':id'</span>=><span>4</span>,
                            <span>':mid'</span>=><span>10</span>,
                            <span>'user1'</span>=><span>'blue'</span>,
                            <span>'user2'</span>=><span>'green'</span>
                            ))
                ->queryAll();

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>array</span>(<span>'like'</span>,<span>'username'</span>,<span>'%g%'</span>))
                ->offset(<span>1</span>)
                ->limit(<span>2</span>)
                ->queryAll();

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*'</span>)->from(<span>'user'</span>)
                ->where(<span>array</span>(<span>'like'</span>,<span>'username'</span>,<span>'%g%'</span>))
                ->order(<span>'id desc'</span>)
                ->queryAll();

        <span>$res</span> = <span>$connect</span>->createCommand()->select(<span>'*,count(*)'</span>)->from(<span>'user'</span>)
                ->group(<span>'username'</span>)
                ->queryAll();

        var_dump(<span>$res</span>);
    }</span></span></span></span></span></:id></span></code>

联合查询 (join)

<code><span>public</span> <span><span>function</span> <span>actionJoin</span><span>()</span>
    {</span>
        <span>$res</span> = Yii::app()->db->createCommand()
                ->select(<span>'*'</span>)
                ->from(<span>'user as u'</span>)
                ->join(<span>'city as c'</span>,<span>'u.city = c.id'</span>)
<span>//                ->leftJoin('city as c','u.city = c.id')</span>
                ->queryAll();
        var_dump(<span>$res</span>);
    }</code>

事务(Transaction)

<code><span>//yiiChina 例子</span>
<span>$transaction</span><span>=</span><span>$connection</span><span>-></span>beginTransaction();
try
{
    <span>$connection</span><span>-></span>createCommand(<span>$sql1</span>)<span>-></span>execute();
    <span>$connection</span><span>-></span>createCommand(<span>$sql2</span>)<span>-></span>execute();
    <span>//.... other SQL executions</span>
    <span>$transaction</span><span>-></span>commit();
}
catch(Exception <span>$e</span>) <span>// 如果有一条查询失败，则会抛出异常</span>
{
    <span>$transaction</span><span>-></span>rollBack();
}</code>