帮酷LOGO
0 0 评论
文章标签:Core Data  nette  Layer  函数  functional  数据库抽象  FUNC  CORE  

Database Core

NetteDatabase Core是数据库抽象层,提供核心功能。

连接和配置

要连接到数据库,只需创建netteDatabaseConnection类的一个实例:

$database = new NetteDatabaseConnection($dsn, $user, $password);

$dsn (数据源名称)参数是PDO使用的相同,例如,host=127.0.0.1;dbname=test ,在失败的情况下,它抛出NetteDatabaseConnectionException

然而更复杂的方法应用程序配置 ,我们将添加一个database部分,它在Tracybar 中创建所需的对象和一个数据库面板。


database:
 dsn: 'mysql:host=127.0.0.1;dbname=test'
 user: root
 password: password

我们是从DI容器接收服务的连接对象,例如:


class Model
{
 private $database;

 // pass NetteDatabaseContext to work with the Database Explorer layer
 public function __construct(NetteDatabaseConnection $database)
 {
 $this->database = $database;
 }
}

有关更多信息,请参见数据库配置

查询

查询数据库使用返回的query()方法resultSet


$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
 echo $row->id;
 echo $row->name;
}

echo $result->getRowCount(); // returns the number of rows if is known

通过ResultSet可以循环访问一次,如果需要重复多次,则必须通过fetchAll()方法将结果转换。

你可以轻松地向查询添加参数,注意问号:


$database->query('SELECT * FROM users WHERE name = ?', $name);

$database->query('SELECT * FROM users WHERE name = ? AND active = ?', $name, $active);

$database->query('SELECT * FROM users WHERE id IN (?)', $ids); // $ids is array

警告:永远不要连接字符串以避免SQL注入漏洞!

$db->query('SELECT * FROM users WHERE name = ' . $name); // WRONG!!!

在出现故障时query()会引发NetteDatabaseDriverException或它的一个后,代:

除了query()之外,还有其他有用的方法:


// returns the associative array id => name
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// returns all rows as array
$rows = $database->fetchAll('SELECT * FROM users');

// returns single row
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// return single field
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);

在出现故障时所有这些方法都抛出NetteDatabaseDriverException.

插入、更新和删除

我们插入到SQL查询中的参数也可以是数组(在这种情况下,可以跳过通配符?),这对于INSERT语句很有用:


$database->query('INSERT INTO users ?', [ // here can be omitted question mark
 'name' => $name,
 'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // returns the auto-increment of inserted row

$id = $database->getInsertId($sequence); // or sequence value

多重插入:


$database->query('INSERT INTO users', [
 [
 'name' => 'Jim',
 'year' => 1978,
 ], [
 'name' => 'Jack',
 'year' => 1987,
 ]
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)

我们还可以传递文件或DateTime对象:


$database->query('INSERT INTO users', [
 'name' => $name,
 'created' => new DateTime, // or $database::literal('NOW()')
 'avatar' => fopen('image.gif', 'r'), // inserts file contents
]);

更新行:


$result = $database->query('UPDATE users SET', [
 'name' => $name,
 'year' => $year,
], 'WHERE id = ?', $id);
// UPDATE users SET `name` = 'Jim', `year` = 1978 WHERE id = 123

echo $result->getRowCount(); // returns the number of affected rows

对于更新,我们可以使用运算符+=-=


$database->query('UPDATE users SET', [
 'age+=' => 1, // note +=
], 'WHERE id = ?', $id);
// UPDATE users SET `age` = `age` + 1

删除:


$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // returns the number of affected rows

高级查询

插入或更新,如果已经存在:


$database->query('INSERT INTO users', [
 'id' => $id,
 'name' => $name,
 'year' => $year,
], 'ON DUPLICATE KEY UPDATE', [
 'name' => $name,
 'year' => $year,
]);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
// ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

注意,Nette数据库识别插入数组参数的SQL上下文,并相应地构建SQL代码,所以从第一个数组他生成了 (id, name, year) VALUES (123, 'Jim', 1978) ,而第二个转换为 name = 'Jim', year = 1978

还可以使用数组描述排序,键是列名称,值是确定是否按升序排序的布尔值:


$database->query('SELECT id FROM author ORDER BY', [
 'id' => true, // ascending
 'name' => false, // descending
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

如果检测无效,则可以使用通配符?指定程序集的形式,然后指定提示,支持以下提示:

?values (key1 ,key2 ,...) VALUES (value1 ,value2 ,... )
?set key1 = value1 ,key2 = value2 .. .
?and key1 = value1 or key2 = value2. . .
?or key1 = value1 and key2 = value2. . .
?order key1 ASC ,key2 DESC

where子句使用?and运算符,因此条件由AND链接:


$result = $database->query('SELECT * FROM users WHERE', [
 'name' => $name,
 'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND `year` = 1978

通过使用?or通配符,可以很容易地将它更改为OR


$result = $database->query('SELECT * FROM users WHERE ?or', [
 'name' => $name,
 'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' OR `year` = 1978

我们可以在条件中使用运算符:


$result = $database->query('SELECT * FROM users WHERE', [
 'name <>' => $name,
 'year >' => $year,
]);
// SELECT * FROM users WHERE `name` <> 'Jim' AND `year` > 1978

还有枚举:


$result = $database->query('SELECT * FROM users WHERE', [
 'name' => ['Jim', 'Jack'],
 'role NOT IN' => ['admin', 'owner'], // enumeration + operator NOT IN
]);
// SELECT * FROM users WHERE
// `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')

我们还可以使用SQL文本来包含一个定制的SQL代码:


$result = $database->query('SELECT * FROM users WHERE', [
 'name' => $name,
 'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

或者:


$result = $database->query('SELECT * FROM users WHERE', [
 'name' => $name,
 $database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

SQL文本也可以有其参数:


$result = $database->query('SELECT * FROM users WHERE', [
 'name' => $name,
 $database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

感谢我们可以创建有趣的组合:


$result = $database->query('SELECT * FROM users WHERE', [
 'name' => $name,
 $database::literal('?or', [
 'active' => true,
 'role' => $role,
 ]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

变量名称

如果表名称或列名为变量,则会使用?name通配符,(注意,不允许用户操作此类变量的内容):


$table = 'blog.users';
$column = 'name';
$database->query('SELECT * FROM ?name WHERE ?name = ?', $table, $column, $name);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'

事务

有三种处理事务的方法:


$database->beginTransaction();

$database->commit();

$database->rollback();


文章标签:数据  FUNC  函数  CORE  functional  Layer  Functionality  Core Data  

Copyright © 2011 HelpLib All rights reserved.    知识分享协议 京ICP备05059198号-3  |  如果智培  |  酷兔英语