ZendFramework2学习笔记 连接第二数据库

    ZendFramework2一般在项目的/confg/autoload/global.php中配置好了数据库信息和在



项目的/confg/autoload/local.php中配置好了登陆数据库的登录名和密码信息，有了这些信息，我们就可以在控制器中使用这样的代码获取数据库连接：

$adapter = new \Zend\Db\Adapter\Adapter();

    之后就可以执行sql：

$adapter->query($sqlStr, $adapter::QUERY_MODE_EXECUTE);

    如果要连接除了这个默认数据库之外的数据库呢？

    其实\Zend\Db\Adapter\Adapter类的构造函数接收一个关联数组参数，这个关联数组就是数据库的连接信息和登陆信息的配置参数！所以，我们可以这样连接第二数据库：

        $dbcfg = array(
            'driver' => 'Pdo',
            'dsn' => 'sqlite:' . __DIR__ . '/sqlite.db',
        );
        $adapter = new \Zend\Db\Adapter\Adapter($dbcfg);

    或者：

        $dbcfg = array(
            'driver' => 'Pdo',
            'dsn' => 'mysql:dbname=test;host=localhost',
            'driver_options' => array(
                         PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
         ),
            'username' => 'root',
            'password' => '',
        );
        $adapter = new \Zend\Db\Adapter\Adapter($dbcfg);

    以下代码展示了，连接一个sqlite数据库，并且在sqlite数据库中创建表、向表中插入数据、查询表数据的操作：

class TestController extends AbstractActionController {
    public function otherdbAction() {
        $dbcfg = array(
            'driver' => 'Pdo',
            'dsn' => 'sqlite:' . __DIR__ . '/sqlite.db',
        );
        $adapter = new \Zend\Db\Adapter\Adapter($dbcfg);
        //通过sql创建表，写数据入表，从表查出数据
        $sqlStr = "CREATE TABLE IF NOT EXISTS `test1` ( "
                . "`name` varchar(40) NOT NULL, "
                . "`content` varchar(255) NOT NULL , PRIMARY KEY (`name`) )";
        $adapter->query($sqlStr, $adapter::QUERY_MODE_EXECUTE);
        $sqlStr = "INSERT INTO `test1`(`name`,`content`) VALUES('test1name', 'test1content');";
        $adapter->query($sqlStr, $adapter::QUERY_MODE_EXECUTE);
        $sqlStr = "SELECT * FROM `test1`";
        $ret1 = $adapter->query($sqlStr, $adapter::QUERY_MODE_EXECUTE)->toArray()[0];
        //通过ZF2类库创建表，写数据入表，从表查出数据
        $createTable = new \Zend\Db\Sql\Ddl\CreateTable('test2');
        $createTable->addColumn(new \Zend\Db\Sql\Ddl\Column\Varchar('name', 40));
        $createTable->addColumn(new \Zend\Db\Sql\Ddl\Column\Varchar('content', 255));
        $createTable->addConstraint(new \Zend\Db\Sql\Ddl\Constraint\PrimaryKey('name'));
        $sql = new \Zend\Db\Sql\Sql($adapter);
        $adapter->query($sql->getSqlStringForSqlObject($createTable), \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
        $insert = new \Zend\Db\Sql\Insert('test2');
        $insert->values(array(
            'name' => 'test2name',
            'content' => 'test2content',
        ));
        $statement = $adapter->getDriver()->createStatement();
        $insert->prepareStatement($adapter, $statement);
        $statement->execute();
        $select = new \Zend\Db\Sql\Select('test2');
        $statement = $adapter->getDriver()->createStatement();
        $select->prepareStatement($adapter, $statement);
        $ret2 = $adapter->query($select->getSqlString(), $adapter::QUERY_MODE_EXECUTE)->toArray()[0];
        
        return new ViewModel(array(
            'ret1' => $ret1,
            'ret2' => $ret2,
        ));
    }
}

    在模板文件view/test/test/otherdb.phtml总可以打印出查询到的数据：

foreach ($this->ret1 as $fieldName => $fieldValue) {
    echo $fieldName . '---' . $fieldValue . '<br/>';
}

echo '==============================================<br/>';


foreach ($this->ret2 as $key => $value) {
    echo $fieldName . '---' . $fieldValue . '<br/>';
}