[TOC]
# 基础 C.R.U.D. 操作
本章节, 我们将学习如何使用Propel对您的数据库进行基础的 C.R.U.D. (增、删、改、查)操作.
## 新增数据
要在数据库增加新的数据, 首先实例化一个Propel自动生成的对象, 然后调用其中的save()方法, 实例化的对象会自动生成合适的INSERT SQL语句.
稍等, 在增加数据之前, 您可能会想定义一下列的值. 为了实现这一目的, Propel已经在对象里为表中的每一列生成了 setXXX() 方法.因此，在最简单的形式中，插入一个新行就像下面的内容：
~~~
<?php
/* initialize Propel, etc. */
$author = new Author();
$author->setFirstName('Jane');
$author->setLastName('Austen');
$author->save();
~~~
setXXX() 方法中的 XXX 对应您在schema文件中为 \<column\> 定义的phpName属性值, 如果phpName没有被定义的话, XXX则会是驼峰风格(CamelCase)的列名
在后台, 调用的save()方法会在数据库中执行以下SQL:
~~~
INSERT INTO author (first_name, last_name) VALUES ('Jane', 'Austen');
~~~
## 读取对象的属性
Propel 生成的对象属性映射了表中对应的列值. 每个属性您都可以使用一个已经自动生成的get方法来获取它.
~~~
<?php
echo $author->getId(); // 1
echo $author->getFirstName(); // 'Jane'
echo $author->getLastName(); // 'Austen'
~~~
在schema.xml文件中定义id列为自增键的话, id列会被数据库自动设置. 一旦对象被保存，这个值就很容易被检索：只需要调用getPhpName()。
这些调用不会发出数据库查询，因为对象已经加载到内存中了。
您还可以通过调用下列方法之一导出对象的所有属性： toArray(), toXML(), toYAML(), toJSON(), toCSV(), and __toString():
~~~
<?php
echo $author->toJSON();
// {"Id":1,"FirstName":"Jane","LastName":"Austen"}
~~~
> Tip
> 对于每个导出方法，Propel还提供一个导入方法对应方法。因此，可以使用fromArray()从数组中轻松填充对象，从字符串中导入则可以使用 fromXML(), fromYAML(), fromJSON(), and fromCSV().
由生成的对象提供了许多更有用的方法。您可以在[活动记录引用](http://propelorm.org/documentation/reference/active-record.html)中找到这些方法的一个广泛列表。
## 检索记录
从数据库中检索对象，也称为吸水化对象(hydrating objects)，本质上是对数据库执行select查询, 并使用每个返回行的内容填充适当对象。
### 按主键检索
从数据库中检索行的最简单方法是使用生成的findPK()方法。它只需要检索要检索的行的主键的值。
~~~
<?php
$q = new AuthorQuery();
$firstAuthor = $q->findPK(1);
// now $firstAuthor is an Author object, or NULL if no match was found.
~~~
这会构造一个简单的选择查询。以mysql为例：
~~~
SELECT author.id, author.first_name, author.last_name
FROM `author`
WHERE author.id = 1
LIMIT 1;
~~~
当主键由多个列组成时，findPK()接受多个参数，对每个主键列都接受一个参数。
> Tip
> 每个生成的查询对象都提供了名为create()的工厂方法。此方法将创建查询的新实例，并允许您在一行中写入查询：
> ~~~
> <?php
> $firstAuthor = AuthorQuery::create()->findPK(1);
> ~~~
您还可以调用生成的findPKs()方法，基于主键选择多个对象。这个方法将主键数组作为参数：
~~~
<?php
$selectedAuthors = AuthorQuery::create()->findPKs(array(1,2,3,4,5,6,7));
// $selectedAuthors is a collection of Author objects
~~~
### 查询数据库
若要检索除主键之外的行，请使用find()方法。
不带任何条件的空查询对象将返回表的所有行。
~~~
<?php
$authors = AuthorQuery::create()->find();
// $authors contains a collection of Author objects
// one object for every row of the author table
foreach($authors as $author) {
echo $author->getFirstName();
}
~~~
若要在给定列上添加简单条件，请使用查询对象中生成的filterByXXX()方法之一，其中xxx是列的phpName。由于filterByXXX()方法返回当前查询对象，因此可以继续添加条件，或者调用获取结果方法从而结束查询。例如，以名称进行筛选：
~~~
<?php
$authors = AuthorQuery::create()
->filterByFirstName('Jane')
->find();
~~~
当将值传递给filterByXXX()方法时，Propel使用列类型来转义pdo中的值。这将保护您免受sql注入风险。
> filterByXXX()是用于创建查询的首选方法。它非常的灵活，并接受使用通配符的参数，以及用于查询更复杂结果的数组参数。有关详细信息，请参阅[列筛选器方法](http://propelorm.org/documentation/reference/model-criteria.html#column_filter_methods)。
您还可以轻松地对查询结果进行限制和排序。再次强调，查询方法会返回当前的查询对象，这样您就可以轻松地将它们连接起来使用：
~~~
<?php
$authors = AuthorQuery::create()
->orderByLastName()
->limit(10)
->find();
~~~
find()总是返回对象的集合，即使只有一个结果。如果您只需要一个结果，那么使用findOne()而不是find()。它将添加限制并返回一个对象而不是数组：
~~~
<?php
$author = AuthorQuery::create()
->filterByFirstName('Jane')
->findOne();
~~~
> Tip
> Propel 为这个简单的用例提供了魔术方法。因此，可以将上面的查询写成：
~~~
<?php
$author = AuthorQuery::create()->findOneByFirstName('Jane');
~~~
Propel的查询api非常强大。下一章将教您如何使用它来为相关对象添加条件。可以在API文档[ API reference](http://propelorm.org/documentation/reference/model-criteria.html)中查阅相关信息.
#### 使用自定义sql
Query类提供了一种相对简单的构造查询的方法。在表达一些常见查询的时候，使用Query类是一个很好的选择。但是，对于一个非常复杂的查询，使用自定义的SQL查询来填充Propel对象也许是更轻松有效的方法。
Propel使用PDO来查询底层数据库，因此您可以使用PDO语法编写自定义查询语句。例如，如果需要使用子选择：
~~~
<?php
use Propel\Runtime\Propel;
$con = Propel::getWriteConnection(\Map\BookTableMap::DATABASE_NAME);
$sql = "SELECT * FROM book WHERE id NOT IN "
."(SELECT book_review.book_id FROM book_review"
." INNER JOIN author ON (book_review.author_id=author.ID)"
." WHERE author.last_name = :name)";
$stmt = $con->prepare($sql);
$stmt->execute(array(':name' => 'Austen'));
~~~
只需要稍微多一点点步骤，您还可以从结果语句填充book对象。Create a new ObjectFormatter for the Book model, and call the format() method using the DataFetcher instance of the current connection with the pdo statement:
~~~
<?php
use Propel\Runtime\Formatter\ObjectFormatter;
$con = Propel::getWriteConnection(\Map\BookTableMap::DATABASE_NAME);
$formatter = new ObjectFormatter();
$formatter->setClass('\Book'); //full qualified class name
$books = $formatter->format($con->getDataFetcher($stmt));
// $books contains a collection of Book objects
~~~
在使用Propel的自定义sql填充时，有几件重要的事情需要记住：
待填充的结果必须使用数字索引。
待填充的结果必须包含表的所有列(延迟加载的列除外)
待填充的结果必须与schema.xml文件中定义的列顺序相同
## Updating Objects
Updating database rows basically involves retrieving objects, modifying the contents, and then saving them. In practice, for Propel, this is a combination of what you’ve already seen in the previous sections:
~~~
<?php
$author = AuthorQuery::create()->findOneByFirstName('Jane');
$author->setLastName('Austen');
$author->save();
~~~
Alternatively, you can update several rows based on a Query using the query object’s update() method:
~~~
<?php
AuthorQuery::create()
->filterByFirstName('Jane')
->update(array('LastName' => 'Austen'));
~~~
This last method is better for updating several rows at once, or if you didn’t retrieve the objects before.
## Deleting Objects
Deleting objects works the same as updating them. You can either delete an existing object:
~~~
<?php
$author = AuthorQuery::create()->findOneByFirstName('Jane');
$author->delete();
~~~
Or use the delete() method in the query:
~~~
<?php
AuthorQuery::create()
->filterByFirstName('Jane')
->delete();
~~~
> Tip
> A deleted object still lives in the PHP code. It is marked as deleted and cannot be saved anymore, but you can still read its properties:
~~~
<?php
echo $author->isDeleted(); // true
echo $author->getFirstName(); // 'Jane'
~~~
## Query Termination Methods
The Query methods that don’t return the current query object are called “Termination Methods”. You’ve already seen some of them: find(), findOne(), update(), delete(). There are two more termination methods that you should know about:
count() returns the number of results of the query.
~~~
<?php
$nbAuthors = AuthorQuery::create()->count();
~~~
You could also count the number of results from a find(), but that would be less effective, since it implies hydrating objects just to count them.
paginate() returns a paginated list of results:
~~~
<?php
$authorPager = AuthorQuery::create()->paginate($page = 1, $maxPerPage = 10);
// This method will compute an offset and a limit
// based on the number of the page and the max number of results per page.
// The result is a PropelModelPager object, over which you can iterate:
foreach ($authorPager as $author) {
echo $author->getFirstName();
}
~~~
A pager object gives more information:
~~~
<?php
echo $pager->getNbResults(); // total number of results if not paginated
echo $pager->haveToPaginate(); // return true if the total number of results exceeds the maximum per page
echo $pager->getFirstIndex(); // index of the first result in the page
echo $pager->getLastIndex(); // index of the last result in the page
$links = $pager->getLinks(5); // array of page numbers around the current page; useful to display pagination controls
~~~
## Collections And On-Demand Hydration
The find() method of generated Model Query objects returns a PropelCollection object. You can use this object just like an array of model objects, iterate over it using foreach, access the objects by key, etc.
~~~
<?php
$authors = AuthorQuery::create()
->limit(5)
->find();
foreach ($authors as $author) {
echo $author->getFirstName();
}
~~~
The advantage of using a collection instead of an array is that Propel can hydrate model objects on demand. Using this feature, you’ll never fall short of memory when retrieving a large number of results. Available through the setFormatter() method of Model Queries, on-demand hydration is very easy to trigger:
~~~
<?php
$authors = AuthorQuery::create()
->limit(50000)
->setFormatter(ModelCriteria::FORMAT_ON_DEMAND) // just add this line
->find();
foreach ($authors as $author) {
echo $author->getFirstName();
}
~~~
In this example, Propel will hydrate the Author objects row by row, after the foreach call, and reuse the memory between each iteration. The consequence is that the above code won’t use more memory when the query returns 50,000 results than when it returns 5.
ModelCriteria::FORMAT_ON_DEMAND is one of the many formatters provided by the Query objects. You can also get a collection of associative arrays instead of objects, if you don’t need any of the logic stored in your model object, by using ModelCriteria::FORMAT_ARRAY.
The ModelCriteria Query API reference describes each formatter, and how to use it.
## Propel Instance Pool
Propel keeps a list of the objects that you already retrieved in memory to avoid calling the same request twice in a PHP script. This list is called the instance pool, and is automatically populated from your past requests:
~~~
<?php
// first call
$author1 = AuthorQuery::create()->findPk(1);
// Issues a SELECT query
...
// second call
$author2 = AuthorQuery::create()->findPk(1);
// Skips the SQL query and returns the existing $author1 object
~~~
