FMDB 的使用

经常用FMDB，但是没怎么研究，正好有时间仔细看一看。怕看过的东西过段时间忘记了，所以记录一下。
FMDB GitHub：https://github.com/ccgus/fmdb

9375A8F4-6DFD-42BC-A3C6-62227307CF16.png

README.markdown中有Usage模块，就在这开始。

BAD576EE-2142-4843-8891-40192EA671E6.png

Usage下又分了几个模块：
1，Database Creation（建库）
2，Opening（打开连接）
3，Executing Updates（更新）
4，Executing Queries（查询）
5，Closing（关闭连接）
6，Transactions（事物）
7，Multiple Statements and Batch Stuff（批处理）
8，Data Sanitization（数据清理）

一：建库
通过一个SQLite数据库文件的路径创建，这条路径可以是：
（1）文件的系统路径。
（2）一个空字符串@“”。
（3）NULL。

NSString *path = [NSTemporaryDirectory()   stringByAppendingPathComponent:@"tmp.db"];
FMDatabase *db = [FMDatabase databaseWithPath:path];

临时和内存数据库的更多信息,这方面的阅读sqlite文档:http://www.sqlite.org/inmemorydb.html

二：打开连接

if (![db open]) {
    db = nil;
    return;
}

三：更新
Any sort of SQL statement which is not a SELECT statement qualifies as an update. This includes CREATE, UPDATE, INSERT, ALTER, COMMIT, BEGIN, DETACH, DELETE, DROP, END, EXPLAIN, VACUUM, and REPLACE statements (plus many more). Basically, if your SQL statement does not begin with SELECT, it is an update statement.
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 information.
大概意思是除了‘SELECT’开头的SQL语句，其他都属于更新语句。更新语句的执行返回值类型为BOOL。

四：查询
executequery查询方法执行成功返回一个FMResultSet对象，在失败时返回“nil”。
遍历查询结果，就算只想得到一个结果也要始终调用-[FMResultSet next]方法。

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

只有一个的情况这样取值

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

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. 根据位置而不是名字检索数据。

五：关闭连接

[db close];

关闭连接，释放资源。

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

七：批处理
You can use FMDatabase's executeStatements:withResultBlock: to do multiple statements in a string:

NSString *sql = @"create table bulktest1 (id integer primary key autoincrement, x text);"
                 "create table bulktest2 (id integer primary key autoincrement, y text);"
                 "create table bulktest3 (id integer primary key autoincrement, z text);"
                 "insert into bulktest1 (x) values ('XXX');"
                 "insert into bulktest2 (y) values ('YYY');"
                 "insert into bulktest3 (z) values ('ZZZ');";

success = [db executeStatements:sql];

sql = @"select count(*) as count from bulktest1;"
       "select count(*) as count from bulktest2;"
       "select count(*) as count from bulktest3;";

success = [self.db executeStatements:sql withResultBlock:^int(NSDictionary *dictionary) {
    NSInteger count = [dictionary[@"count"] integerValue];
    XCTAssertEqual(count, 1, @"expected one record for dictionary %@", dictionary);
    return 0;
}];

八：数据清理
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:

INSERT INTO myTable VALUES (?, ?, ?, ?)

最后，又说了多线程使用FMDB “Using FMDatabaseQueue and Thread Safety”
So don't instantiate a single FMDatabase object and use it across multiple threads。Instead, use FMDatabaseQueue. Instantiate a single FMDatabaseQueue and use it across multiple threads. The FMDatabaseQueue object will synchronize and coordinate access across the multiple threads. 不要在多个线程中调用FMDB的单例。
在多线程中要使用FMDatabaseQueue。
FMDatabaseQueuewill run the blocks on a serialized queue (hence the name of the class). So if you callFMDatabaseQueue'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. The calls toFMDatabaseQueue`'s methods are blocking. So even though you are passing along blocks, they will not be run on another thread.这几句话解释了FMDatabaseQueue线程安全的原因。

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

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

需要用到事务的时候

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

    if (whoopsSomethingWrongHappened) {
        *rollback = YES;
        return;
    }
}];

未完待续。。。