php数据库操作类库doctrine使用全攻略

首先composer安装

{
    "require": {
        "doctrine/dbal":"2.5.12"
    }
}

表结构如下

CREATE TABLE `test_databases` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `db_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '库名',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '测试用户id',
  `created_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `updated_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_databases_db_name_user_id_unique` (`db_name`,`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 

示例代码：

<?php
require ("../vendor/autoload.php");

$config = new \Doctrine\DBAL\Configuration();
//..
$connectionParams = array(
		'dbname' => 'test1',
		'user' => 'root',
		'password' => 'root',
		'host' => 'localhost',
		'driver' => 'pdo_mysql',
                'charset'=>'UTF8',
);
$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);

echo "<h1>fetchAll（获取全部）, fetchAssoc（获取行）, fetchColumn（获取单个）示例</h1>";
$users = $conn->fetchAll('SELECT * FROM test_databases limit 2');
var_dump($users);
echo "<hr>";
$sql = 'SELECT * FROM test_databases';
$result = $conn->fetchAssoc($sql);

var_dump($result);
echo "<hr>";
$sql = 'SELECT db_name FROM test_databases limit 3';
$result = $conn->fetchColumn($sql);

var_dump($result);
echo "<hr>";
// ，没有单独的取某列的值。好遗憾。
$sql = 'SELECT db_name FROM test_databases order by id asc limit 3';
$result = $conn->fetchAll($sql);

var_dump($result);
echo "<hr>";

echo "<h1>迭代获取全部，适用于数据量超大的场合</h1>";
//迭代获取全部，适用于数据量超大的场合
$stat = $conn->query('SELECT * FROM test_databases limit 2');
while ($row= $stat->fetch()) {
	var_dump($row);
}

//var_dump($users);

echo "<hr>";
// 参数绑定
echo "<h1>动态参数绑定，也可以使用另外几个fetch函数</h1>";
$sql = 'SELECT * FROM test_databases WHERE db_name = ? limit 2';
$statement = $conn->executeQuery($sql, array('db1'));
$result = $statement->fetchAll();
var_dump($result);
echo "<hr>";

echo "<h1>查询构造器使用示例，也可以使用另外几个fetch函数</h1>";
// 下面是查询构造器使用示例
$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
	->select('id', 'db_name')
	->from('test_databases')
	->where("db_name = :db_name")
	->setParameter('db_name', 'db1');

$sql = $queryBuilder->getSQL();
var_dump($sql);
echo "<hr>";
$result = $queryBuilder->execute()->fetchAll(); //返回 executeQuery对象。

var_dump($result);

增删改

$conn->insert('user', array('username' => 'jwage'));
$conn->lastInsertId();// 得到最后插入的行

$conn->delete('user', array('id' => 1));
$conn->update('user', array('username' => 'jwage'), array('id' => 1));

浏览器显示