数据库数据交互方式‘_NotORM使数据库交互变得容易-CSDN博客

数据库数据交互方式‘

Object Relational Mappers (ORMs) are cool. They help you to rapidly create your application without worrying about writing raw SQL queries. The idea is to simplify database interaction and avoid possible errors in writing complex queries. In fact, modern ORMs can generate Models/Entities from the database, and vise versa.

对象关系映射器(ORM)很酷。它们帮助您快速创建应用程序，而不必担心编写原始SQL查询。这样做的目的是简化数据库交互，并避免在编写复杂查询时可能出现的错误。实际上，现代ORM可以从数据库生成模型/实体，反之亦然。

But the reality of working with any ORM is that using it is only simple if you already have experience using it. To make the most out of it, you should have a deep understanding of the concepts. And there’s a steep learning curve associated with any ORM.

但是，与任何ORM一起工作的现实是，只有您已经有使用它的经验，使用它才简单。要充分利用它，您应该对概念有深刻的理解。任何ORM都有一个陡峭的学习曲线。

If all you are developing is a simple application with a few tables, using a full-fledged ORM is probably overkill. In this case, you may want to consider using NotORM. NotORM is easy to learn and simple to use because it provides an intuitive API to interact with the database. In this article I’ll be teaching you how to use NotORM.

如果您正在开发的只是一个带有几个表的简单应用程序，那么使用功能强大的ORM可能会过头了。在这种情况下，您可能要考虑使用NotORM。 NotORM易于学习且易于使用，因为它提供了与数据库交互的直观API。在本文中，我将教您如何使用NotORM。

Before we get started though, here’s the database layout which I’ll be using throughout the article.

在开始之前，这是我在整篇文章中都会使用的数据库布局。

Table: author
+----+------------------------+
| id | name                   |
+----+------------------------+
|  1 | Khalil Gibran          |
|  2 | Sir Arthur Conan Doyle |
|  3 | Paulo Coelho           |
+----+------------------------+

Table: book
+----+-----------------+-----------+
| id | title           | author_id |
+----+-----------------+-----------+
|  1 | The Prophet     |         1 |
|  3 | Sherlock Holmes |         2 |
|  4 | The Alchemist   |         3 |
+----+-----------------+-----------+

Table: category
+----+------------+
| id | category   |
+----+------------+
|  1 | poem       |
|  2 | article    |
|  3 | tutorials  |
|  4 | philosophy |
|  5 | essays     |
|  6 | story      |
+----+------------+

Table: book_category
+----+---------+-------------+
| id | book_id | category_id |
+----+---------+-------------+
|  1 |       1 |           4 |
|  3 |       3 |           6 |
|  4 |       4 |           4 |
+----+---------+-------------+

连接到数据库 (Connecting to the Database)

The first step to using NotORM is to create an instance of the NotORM object which uses an active PDO connection to interface with the database.

使用NotORM的第一步是创建NotORM对象的实例，该实例使用活动的PDO连接与数据库接口。

<?php
$dsn = "mysql:dbname=library;host=127.0.0.1";
$pdo = new PDO($dsn, "dbuser", "dbpassword");
$library = new NotORM($pdo);

A Data Source Name (DSN) is a common way of describing a database connection. It contains the name of the database driver use, database name, and host address. The PDO constructor accepts a DSN and database username and password to connect. Once connected, the PDO object is passed to NotORM. We will use this NotORM instance throughout this article.

数据源名称(DSN)是描述数据库连接的常用方法。它包含数据库驱动程序使用的名称，数据库名称和主机地址。 PDO构造函数接受DSN和数据库用户名和密码进行连接。连接后，PDO对象将传递到NotORM。在本文中，我们将使用此NotORM实例。

基本检索 (Basic Retrieval)

Now that we are connected through NotORM, let’s list all of the books in the database.

现在，我们已经通过NotORM进行了连接，下面列出数据库中的所有书籍。

<?php
$books = $library->book();
foreach ($books as $book) {
	echo $book["id"] . " " . $book["title"] . "<br>";
}

The result is:

结果是：

1 The Prophet
3 Sherlock Holmes
4 The Alchemist

It’s as simple as that! $library is the NotORM object and book is the table name where our book information is stored. The book() method returns an multi-dimensional array with the table’s primary key column as the first-level index. Within the foreach, $book is a representation of a single book record, an array with keys named after the table’s column names. $book["title"] returns the value from the title column for that record.

就这么简单！ $library是NotORM对象，而book是存储我们的图书信息的表名。 book()方法返回一个多维数组，该数组具有表的主键列作为第一级索引。在foreach ， $book是单个书记录的表示，该记录具有以表的列名命名的键的数组。 $book["title"]从标题列返回该记录的值。

In most cases you won’t be interested in retrieving all columns from the table. You can specify just the columns you are interested in through the select() method. For instance, if you only want the title, you could re-write the example as:

在大多数情况下，您对从表中检索所有列都不会感兴趣。您可以通过select()方法仅指定您感兴趣的列。例如，如果只想要标题，则可以将示例重写为：

<?php
$books = $library->book()->select("title");
foreach ($books as $book) {
	echo $book["title"] . "<br>";
}

Retrieving specific columns is especially desirable on tables that have a large number of columns, this way you don’t have to waste time and memory retrieving and storing values you won’t use.

在具有大量列的表上，检索特定列尤为可取，这样您就不必浪费时间和内存来检索和存储您不会使用的值。

To fetch a single record from the table using its primary key, we reference the primary key in a query’s WHERE clause when writing SQL. In NotORM, There are many ways we can accomplish the task; the easiest way is to use the primary key as an index to the table property.

为了使用表的主键从表中获取一条记录，我们在编写SQL时在查询的WHERE子句中引用了主键。在NotORM中，我们可以通过多种方式完成任务。最简单的方法是使用主键作为table属性的索引。

<?php
$book = $library->book[1]; 
echo $book["title"];

This will simply retrieve the get the book details from the record with ID 1.

这将简单地从ID为1的记录中检索获取书籍的详细信息。

过滤列值 (Filtering on Column Values)

NotORM allows for filtering results on conditions that would be specified in the query’s WHERE clause using the where() method. To illustrate, let’s search the table for books with a title containing “Alchemist”.

NotORM允许使用where()方法在查询的WHERE子句中指定的条件下过滤结果。为了说明这一点，让我们在表格中搜索标题为“ Alchemist”的书籍。

<?php
$books = $library->book->where("title LIKE ?", "%Alchemist%");
foreach ($books as $book) {
	echo $book["id"] . " " . $book["title"] . "<br>";
}

The result is:

结果是：

4 The Alchemist

If you are not familiar with prepared statements, you may be wondering what that question mark means. This is a way of binding paremeters to the queries executed by PDO so that you can execute the same query many times just by changing the values. A question mark, or a variable like :title, acts like a value place holder. You can read more about PDO’s prepared statements in the PHP manual.

如果您不熟悉准备好的语句，您可能想知道问号的含义。这是将参数与PDO执行的查询绑定的一种方法，因此您只需更改值即可多次执行同一查询。问号或类似变量:title作用类似于值占位符。您可以在PHP手册中阅读有关PDO准备好的语句的更多信息。

NotORM also allows you to chain where() methods to apply more than one condition.

NotORM还允许您链接where()方法以应用多个条件。

<?php
$books = $library->book->where("title LIKE ?", "%The%")
				   ->where("id < ?", 5);
foreach ($books as $book) {
	echo $book["id"] . " " . $book["title"] . "<br>";
}

1 The Prophet
4 The Alchemist

The statement issued by NotORM for the above example is equivalent to the following SQL query:

NotORM针对上述示例发出的语句等效于以下SQL查询：

SELECT * FROM book WHERE title LIKE "%The%" AND id < 5

排序结果 (Sorting Results)

Chances are rare that you’ll have straight forward queries with a single table throughout your whole application. In real life applications, you’ll need to join many tables, order records based on the values in different columns, limit the number of records retrieved, and so on.

很少有机会在整个应用程序中使用单个表进行直接查询。在现实生活中的应用程序中，您将需要连接许多表，根据不同列中的值对记录进行排序，限制检索到的记录数，等等。

You can order results based on one or more columns, ascending or descending order. The example given below will return books in descending order of their IDs.

您可以基于一列或多列(升序或降序)对结果进行排序。下面给出的示例将按ID降序返回书籍。

<?php
$books = $library->book->order("id desc");
foreach ($books as $id => $book) {
	echo $id . " " . $book["title"] . "<br>";
}

4 The Alchemist
3 Sherlock Holmes
1 The Prophet

If you want to order the results based on more than one column, you can specify them separated by commas.

如果要基于多个列对结果进行排序，可以指定它们，并以逗号分隔。

<?php
$books = $library->book->order("id desc, title asc");
foreach ($books as $id => $book) {
	echo $id . " " . $book["title"] . "<br>";
}

The resulting records will be returned in descending order of their ID and if there’s more than one records with the same ID (of course there won’t be) it will be in ascending order of the title.

结果记录将按其ID的降序返回，如果有多个具有相同ID的记录(当然不会)，则将按标题的升序排列。

NotORM supports limiting the results, too. Let’s limit the result set to two records, starting from offset 0:

NotORM也支持限制结果。让我们将结果集限制为两条记录，从偏移量0开始：

<?php
$books = $library->book->limit(2, 0);
foreach ($books as $id => $book) {
	echo $id . " " . $book["title"] . "<br>";
}

1 The Prophet
3 Sherlock Holmes

联接表 (Joining Tables)

So far we were discussing about listing books or getting NotORM work with only one table. Now we want to move further, like finding out who authored the book and so on.

到目前为止，我们正在讨论有关列出书籍或仅使用一张桌子来使NotORM工作的问题。现在，我们想进一步发展，例如找出谁写了这本书。

Let’s try to list the books along with their authors. In our library database, the book table has a foreign key author_id which references the author table (each book can have only one author in this set-up).

让我们尝试列出书籍及其作者。在我们的图书馆数据库中，book表具有一个外键author_id ，它引用了author表(在此设置中，每本书只能有一位作者)。

<table>
 <tr><th>Book</th><th>Author</th></tr>
<?php
$books = $library->book();
foreach ($books as $book) {
    echo "<tr>";
    echo "<td>" . $book["title"] . "</td>";
    echo "<td>" . $book->author["name"] . "</td>";
    echo "</tr>";
}
?>
</table>

The output of above code is:

上面代码的输出是：

Book             Author
The Prophet      Khalil Gibran
Sherlock Holmes  Sir Arthur Conan Doyle
The Alchemist    Paulo Coelho

When you call $book->author["name"], NotORM automagically links the book table with the author table using the author_id column and retrieves the author details for the book record. This is a case of a one-to-one relationship (a record in parent table will be linked to only one record in child table) relations.

当您调用$book->author["name"] ，NotORM使用author_id列自动将book表与author表链接，并检索该书记录的作者详细信息。这是一对一关系(父表中的记录将仅链接到子表中的一条记录)关系的情况。

In the case of one-to-many relations, the secondary table will have more than one record corresponding to a single row in the primary table. For example, let’s assume we can write reviews for book, so for each book there will be zero or more reviews which which are stored in another table. For each each book then you will need another loop to display its reviews, if any.

在一对多关系的情况下，辅助表将具有多个记录，这些记录对应于主表中的单个行。例如，假设我们可以为书撰写评论，那么对于每本书，将有零个或多个评论，这些评论存储在另一个表中。对于每本书，您将需要另一个循环来显示其评论(如果有)。

For many-to-many relationship, there will be a third table linking the primary and secondary tables. We have a category table to keep book categories in, and a book can have zero or more categories associated with it. The link is maintained using the book_category table.

对于多对多关系，将有第三个表链接主表和辅助表。我们有一个category表来保留书籍类别，并且书籍可以具有零个或多个与之关联的类别。使用book_category表维护该链接。

<table>
 <tr><th>Book</th><th>Author</th><th>Categories</th></tr>
<?php
foreach ($books as $book) {
    echo "<tr>";
    echo "<td>" . $book["title"] . "</td>";
    echo "<td>" . $book["author"] . "</td>";
    // book_category table joins book and category
    $categories = array();
    foreach ($book->book_category() as $book_category) {
        $categories[] = $book_category->category["category"];
    }
    echo "<td>" . join(", ", $categories) . "</td>";
    echo "</tr>";
}
?>
 </tr>
</table>

Book             Author                 Categories
The Prophet      Khalil Gibran           philosophy
Sherlock Holmes  Sir Arthur Conan Doyle  story
The Alchemist    Paulo Coelho            philosophy, story

When you call the book_category() method, it will get the records from the book_category table, which in turn will connect to the category table using $book_category->category["category"].

当您调用book_category()方法时，它将从book_category表中获取记录，该记录又将使用$book_category->category["category"]连接到category表。

There are some default conventions for table and column names that, if you follow, can make working with table relationships easier in NotORM.

有一些表和列名的默认约定，如果您遵循这些约定，可以使它们在NotORM中更轻松地处理表关系。

The table name should be singular, i.e. use book for the name of a table to store book details.
表格名称应为单数，即使用book作为表格名称以存储书籍详细信息。
Use id as the primary key for your tables. It’s not necessary to have primary keys for all tables, but it’s a good idea.
使用id作为表的主键。不必为所有表都具有主键，但这是一个好主意。
Foreign keys in a table should be named as table_id.
表中的外键应命名为table_id 。

Like I said, these are only default conventions. You can follow different convention if you want, but then you need to inform NotORM of the conventions it should follow. The library has a class, NotORM_Structure_Convention for defining the naming convention. Here’s a simple example of how to use, which I copied from the NotORM website.

就像我说的那样，这些只是默认约定。如果需要，可以遵循不同的约定，但是随后需要将其应遵循的约定告知NotORM。该库具有一个NotORM_Structure_Convention类，用于定义命名约定。这是我从NotORM网站复制的简单用法示例。

<?php
$structure = new NotORM_Structure_Convention(
    $primary = "id_%s", // id_$table
    $foreign = "id_%s", // id_$table
    $table = "%ss", // {$table}s
    $prefix = "wp_" // wp_$table
);
$db = new NotORM($pdo, $structure);

The example changes all of the table names to plural and prefixed with “wp_” . The primary key and foreign keys have been changed to id_table.

该示例将所有表名更改为复数并以“ wp_”为前缀。主键和外键已更改为id_table 。

数据持久性 (Data Persistence)

Up until now we’ve discussed retrieving data that’s already in the database. Next we need to look at storing and updating the data as well.

到目前为止，我们已经讨论了检索数据库中已经存在的数据。接下来，我们还需要研究存储和更新数据。

Inserts and updates are pretty simple and straight forward. You only need to create an associative array with the column names as keys and pass it as an argument to the table’s insert() or update() method.

插入和更新非常简单直接。您只需要创建一个以列名作为键的关联数组，并将其作为参数传递给表的insert()或update()方法。

<?php
$book = $library->book();
$data = array(
    "title" => "Beginning PHP 5.3",
    "author_id" => 88
);
$result = $book->insert($data);

insert() will return the record if insertion was successful or false if it failed. From there, you can get the ID of the inserted record using $result["id"].

如果插入成功，则insert()将返回记录；如果插入失败，则将返回false。从那里，您可以使用$result["id"]获得插入记录的ID。

To update a book, fetch the book record from the database using its primary key and then pass the values that should be updated as an array to update().

要更新书籍，请使用其主键从数据库中获取书籍记录，然后将应更新的值作为数组传递给update() 。

<?php
$book = $library->book[1];
if ($book) {
    $data = array(
        "title" => "Pro PHP Patterns, Frameworks, Testing and More"
    );
    $result = $book->update($data);
}

update() will return true on success or false if the update failed.

如果更新成功，则update()将返回true，否则将返回false。

Similarly, you can delete a book by calling delete() on a book object.

同样，您可以通过在book对象上调用delete()来删除一本书。

<?php
$book = $library->book[10];
if ($book && $book->delete()) {
    echo "Book deleted successfully.";
}

It’s important to remember when you update or delete a row that you first make sure it exists in the database, otherwise your script will throw a fatal error.

重要的是要记住，在更新或删除行时，首先要确保该行已存在于数据库中，否则脚本将引发致命错误。

摘要 (Summary)

Through this article you’ve become familiarized with a simple library for interacting with your database. The reality is that as your application grows, the code using NotORM will become less manageable and so you’ll need to decide whether NotORM is suitable based on the expected size of your application and other factors. But when using NotORM, you don’t need to worry about writing raw SQL queries or creating entity classes with complex relationships. Rather, you can use the familiar object oriented notation to deal with tables and columns straight away.

通过本文，您已经熟悉了用于与数据库进行交互的简单库。现实情况是，随着应用程序的增长，使用NotORM的代码将变得难以管理，因此您需要根据应用程序的预期大小和其他因素来决定NotORM是否合适。但是，使用NotORM时，您不必担心编写原始SQL查询或创建具有复杂关系的实体类。相反，您可以使用熟悉的面向对象的表示法立即处理表和列。

Image via Fotolia

图片来自Fotolia