[翻译] FMDB

苹果电脑 1

苹果电脑 2



This is an Objective-C wrapper around SQLite: http://sqlite.org/

这是对 SQLite

Read the SQLite FAQ:


Since FMDB is built on top of SQLite, you’re going to want to read this
page top to bottom at least once. And while you’re there, make sure to
bookmark the SQLite Documentation
page: http://www.sqlite.org/docs.html

因为 FMDB 建立在 SQLite
之上,所以,你足足应当把 SQLite 文书档案看一回。看的时候,将
http://www.sqlite.org/docs.html 网址添加到你的浏览器书签中呢。

FMDB Class Reference:


Automatic Reference Counting (ARC) or Manual Memory Management?

You can use either style in your Cocoa project. FMDB Will figure out
which you are using at compile time and do the right thing.

FMDB 支持 ARC 与 非ARC 。


There are three main classes in FMDB:

FMDB 包涵了 3 个重点的类:

  1. FMDatabase – Represents a single SQLite database. Used for
    executing SQL statements.
  2. FMResultSet – Represents the results of executing a query on
    an FMDatabase.
  3. FMDatabaseQueue – If you’re wanting to perform queries and updates
    on multiple threads, you’ll want to use this class. It’s described
    in the “Thread Safety” section below.
  4. FMDatabase – 代表了单纯性的一个 SQLite
    数据库。用来进行 SQL 语句的。
  5. FMResultSet – 在 FMDatabase
  6. FMDatabaseQueue –

Database Creation

An FMDatabase is created with a path to a SQLite database file. This
path can be one of these three:

通过给定八个 SQLite 数据库文件的路线来创造FMDatabase,这些文件路径可由上面 3 种艺术提供:

  1. A file system path. The file does not have to exist on disk. If it
    does not exist, it is created for you.
  2. An empty string (@""). An empty database is created at a temporary
    location. This database is deleted with the FMDatabase connection
    is closed.
  3. NULL. An in-memory database is created. This database will be
    destroyed with the FMDatabaseconnection is closed.
  4. 一个系统文件路径,这几个文件路径不肯定须要存在,倘使不设有,它会自动为你创制。
  5. 一个空的字符串
    @“”,它会为您创制三个一时的数据库文件。当 FMDatabase
  6. 如若参数为 NULL
    ,这就会在内部存款和储蓄器中创设壹个数据库,当 FMDatabase

(For more information on temporary and in-memory databases, read the
sqlite documentation on the

FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];


Before you can interact with the database, it must be opened. Opening
fails if there are insufficient resources or permissions to open and/or
create the database.


if (![db open]) {
    [db release];

Executing Updates(执行更新)

Any sort of SQL statement which is not a SELECT statement qualifies as
an update. This
and REPLACEstatements (plus many more). Basically, if your SQL
statement does not begin with SELECT, it is an update statement.

其余 SQL
语句,尽管不是 SELECT 语句都会被视作是叁个 updata
以及 REPLACE。所以请注意,如果你的 SQL 语句不是以 SELECT 开始的,那是一个更新操作哦。

Executing updates returns a single value, a BOOL. A return value
of YES means the update was successfully executed, and a return value
of NO means that some error was encountered. You may invoke
the -lastErrorMessage and -lastErrorCode methods to retrieve more

实践更新操作会再次来到3个 BOOL 值。YES
代表出现了一些错误。你能够调用 -lastErrorMessage 和 -lastErrorCode 方法来寻找错误的信息。

Executing Queries(执行查询)

SELECT statement is a query and is executed via one of
the -executeQuery... methods.

查询语句 SELECT 通过 -executeQuery..

Executing queries returns an FMResultSet object if successful,
and nil upon failure. Like executing updates, there is a variant that
accepts an NSError ** parameter. Otherwise you should use
the -lastErrorMessageand -lastErrorCode methods to determine why a
query failed.

询问成功会回来八个 FMResultSet
对象,退步重临 nil。与实施更新操作一样,有叁个变量来收取三个 NSError
参数。当然,你也足以使用 -lastErrorMessage 和 -lastErrorCode

In order to iterate through the results of your query, you use
while() loop. You also need to “step” from one record to the other.
With FMDB, the easiest way to do that is like this:

为了能迭代检索处全体你的查询结果,你能够采纳while() 循环。你须求使用 “step” 来从一条记下跳到其它一条记下。在 FMDB

FMResultSet *s = [db executeQuery:@"SELECT * FROM myTable"];
while ([s next]) {
    //retrieve values for each record

You must always invoke -[FMResultSet next] before attempting to access
the values returned in a query, even if you’re only expecting one:

您不可能不平时调用方法 -[FMResultSet next] ,哪怕你只是查找一条记录。

FMResultSet *s = [db executeQuery:@"SELECT COUNT(*) FROM myTable"];
if ([s next]) {
    int totalCount = [s intForColumnIndex:0];

FMResultSet has many methods to retrieve data in an appropriate


  • intForColumn:
  • longForColumn:
  • longLongIntForColumn:
  • boolForColumn:
  • doubleForColumn:
  • stringForColumn:
  • dateForColumn:
  • dataForColumn:
  • dataNoCopyForColumn:
  • UTF8StringForColumnName:
  • objectForColumnName:

Each of these methods also has a {type}ForColumnIndex: variant that is
used to retrieve the data based on the position of the column in the
results, as opposed to the column’s name.

地方的每3个措施中都有叁个 {type}ForColumnIndex:自己的版本,用来匹配数据,基于查询结果中的记录。

Typically, there’s no need to -close an FMResultSet yourself, since
that happens when either the result set is deallocated, or the parent
database is closed.

更进一步令人瞩目,你绝不自己关闭 FMResultSet


When you have finished executing queries and updates on the database,
you should -close the FMDatabaseconnection so that SQLite will
relinquish any resources it has acquired during the course of its

-close 来关闭数据库的连接,让 SQLite

[db close];


FMDatabase can begin and commit a transaction by invoking one of the
appropriate methods or executing a begin/end transaction statement.

FMDatabase 能够付出八个作业,通过调用
begin/end 事务语句。

Data Sanitization(数据处理)

When providing a SQL statement to FMDB, you should not attempt to
“sanitize” any values before insertion. Instead, you should use the
standard SQLite binding syntax:

当你提供一条 SQL 语句给 FMDB
时,你绝不一向给语句中的变量附上值。你应有运用正规的 SQLite

INSERT INTO myTable VALUES (?, ?, ?)

The ? character is recognized by SQLite as a placeholder for a value
to be inserted. The execution methods all accept a variable number of
arguments (or a representation of those arguments, such as
an NSArrayNSDictionary, or a va_list), which are properly
escaped for you.

NSArrayNSDictionary 恐怕三个 va_list),你会欣赏的。

Alternatively, you may use named parameters syntax:


INSERT INTO myTable VALUES (:id, :name, :value)

The parameters must start with a colon. SQLite itself supports other
characters, but internally the Dictionary keys are prefixed with a
colon, do not include the colon in your dictionary keys.

参数在此之前必须抬高冒号。 SQLite
本人协助很各个字符,不过 OC 中字典对象的前缀正是冒号,故而,不要在你的字典 key

NSDictionary *argsDict = [NSDictionary dictionaryWithObjectsAndKeys:@"My Name", @"name", nil];
[db executeUpdate:@"INSERT INTO myTable (name) VALUES (:name)" withParameterDictionary:argsDict];

Thus, you SHOULD NOT do this (or anything like this):


[db executeUpdate:[NSString stringWithFormat:@"INSERT INTO myTable VALUES (%@)", @"this has \" lots of ' bizarre \" quotes '"]];

Instead, you SHOULD do:


[db executeUpdate:@"INSERT INTO myTable VALUES (?)", @"this has \" lots of ' bizarre \" quotes '"];

All arguments provided to the -executeUpdate: method (or any of the
variants that accept a va_list as a parameter) must be objects. The
following will not work (and will result in a crash):


[db executeUpdate:@"INSERT INTO myTable VALUES (?)", 42];

The proper way to insert a number is to box it in an NSNumber object:

插入二个数字类型的,供给运用 NSNumber

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:42]];

Alternatively, you can use the -execute*WithFormat: variant to
use NSString-style substitution:

-exexute*WithFormat:来交付 NSString 形式的口舌:

[db executeUpdateWithFormat:@"INSERT INTO myTable VALUES (%d)", 42];

Internally, the -execute*WithFormat: methods are properly boxing
things for you. The following percent modifiers are
recognized: %@%c%s%d%D%i%u%U%hi%hu%qi%qu%f%g%ld%lu%lld,
and %llu. Using a modifier other than those will have unpredictable
results. If, for some reason, you need the % character to appear in
your SQL statement, you should use %%.

本质上,-excute*WithFormat:方法也是3个?的法子。上面包车型客车百分号的办法是能够被识其余: %@%c%s%d%D%i%u%U%hi%hu%qi%qu%f%g%ld%lu%lld,
和 %llu。你使用了其他修饰语而不是这些会导致预料不到的结果。由于某些原因,你需要使用 % 出现在你的 SQL 语句中,你应该使用 %%。

Using FMDatabaseQueue and Thread Safety.(使用数据库队列以及线程安全)

Using a single instance of FMDatabase from multiple threads at once is a
bad idea. It has always been OK to make a FMDatabase object per
. Just don’t share a single instance across threads, and
definitely not across multiple threads at the same time. Bad things will
eventually happen and you’ll eventually get something to crash, or maybe
get an exception, or maybe meteorites will fall out of the sky and hit
your Mac Pro. This would suck.

FMDatabase 对象是高枕无忧的。请不要创建数据库单例,并在不相同的线程中走访,假诺实在可怜,也并非让差异的线程同时做客那1个数据库对象。一经你坚持不渝这么做,那不时就会产出有的莫明其妙的倒台,万分,大概从天而降的流星砸坏你的苹果电脑,别这么干哦。

So don’t instantiate a single FMDatabase object and use it across
multiple threads.

据此,不要创设三个 FMDatabase

Instead, use FMDatabaseQueue. It’s your friend and it’s here to help.
Here’s how to use it:


First, make your queue.


FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];

Then use it like so:


[queue inDatabase:^(FMDatabase *db) {
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    FMResultSet *rs = [db executeQuery:@"select * from foo"];
    while ([rs next]) {

An easy way to wrap things up in a transaction can be done like this:


[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    if (whoopsSomethingWrongHappened) {
        *rollback = YES;
    // etc…
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:4]];

FMDatabaseQueue will run the blocks on a serialized queue (hence the
name of the class). So if you call FMDatabaseQueue’s methods from
multiple threads at the same time, they will be executed in the order
they are received. This way queries and updates won’t step on each
other’s toes, and every one is happy.

运转在三个串行队列在那之中。所以,当你在分歧的线程中调用了 FMDatabaseQueue

Note: The calls to FMDatabaseQueue’s methods are blocking. So even
though you are passing along blocks, they will not be run on another

留意:调用 FMDatabaseQueue 方法是一个block 。就算你在 block 中采纳了 block,它也不会在别的线程中运转。

Making custom sqlite functions, based on blocks.(定制 sqlite 函数,基于 blocks)

苹果电脑,You can do this! For an example, look for “makeFunctionNamed:” in main.m

你能够定制哦!你在 main.m



































No Comments, Be The First!