【IOS开发进阶系列】FMDB专题

1 FMDB简介

1.1 使用方法

        首先到这裡下载FMDB的source code，接著在解开的档案裡，把src资料夹下除了fmdb.m的档案加入到自己的iOS专案，最后在专案中加入libsqlite3.dylib这个函式库就可以了。啥？有人问為什麼不用加入fmdb.m？简单讲，这个档案是fmdb的使用说明。裡面的註解清楚，范例又简单，如果有兴趣，直接看fmdb.m，大概就会用fmdb了。

http://ccgus.github.io/fmdb/html/index.html


1.2 常用命令

1.2.1 新建数据库

        使用资料库的第一件事，就是建立一个资料库。要注意的是，在iOS环境下，只有document directory 是可以进行读写的。在写程式时用的那个Resource资料夹底下的东西都是read-only。因此，建立的资料库要放在document 资料夹下。方法如下：

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *documentDirectory = [paths objectAtIndex: 0];

    NSString *dbPath = [documentDirectory stringByAppendingPathComponent: @"MyDatabase.db"];

    FMDatabase *db = [FMDatabase databaseWithPath: dbPath] ;


    if(![db open]) {

        NSLog(@"Could not open db.");

        return;

    }


1.2.2 建表

        如果是新建的资料库档，一开始是没有table的。建立table的方式很简单：

[db executeUpdate: @"CREATE TABLE PersonList (Name text, Age integer, Sex integer, Phone text, Address text, Photo blob)"];


        这是FMDB裡很常用的指令，[FMDatabase_object executeUpdate:]后面用NSString塞入SQLite语法，就解决了。因為这篇主要是在讲FMDB，所以SQLite的语法就不多说了，上述程式码建立了一个名為PersonList的table，裡面有姓名、年龄、性别、电话、地址和照片。


1.2.3 插入记录

        插入资料跟前面一样，用executeUpdate后面加语法就可以了。比较不同的是，因為插入的资料会跟Objective-C的变数有关，所以在string裡使用?号来代表这些变数。

[db executeUpdate: @"INSERT INTO PersonList (Name, Age, Sex, Phone, Address, Photo) VALUES (?,?,?,?,?,?)", @"Jone", [NSNumber numberWithInt: 20], [NSNumber numberWithInt: 0], @"091234567", @"Taiwan, R.O.C", [NSData dataWithContentsOfFile: filepath]];

        其中，在SQLite中的text对应到的是NSString，integer对应NSNumber，blob则是NSData。该做的转换FMDB都做好了，只要了解SQLite语法，应该没有什麼问题才是。


1.2.4 更新

太简单了，不想讲，请看范例：

 [_dbQueue inDatabase:^(FMDatabase*db) {

       NSString * updateStr = [NSString stringWithFormat: @"UPDATE AQCityInfoTable SET province = \'%@\', cityName_Pinyin = \'%@\', cityName_Pinyin_Short = \'%@\', lastAQI = %d, lastUpdate = \'%@\', isPopCity = %d, latitude = %lf, longitude = %lf WHERE name = \'%@\'", obj.province, obj.cityName_Pinyin, obj.cityName_Pinyin_Short, 200, [NSDate getStringFromDate: obj.lastUpdate Format: AQDATEFORMAT], obj.isPopCity, obj.latitude, obj.longitude, obj.cityName];

       BOOL f = TRUE;

       f = [db executeUpdate: updateStr];

}];


或者：

f = [db executeUpdate: @"UPDATE AQCityInfoTable SET province = ?, cityName_Pinyin = ?, cityName_Pinyin_Short = ?, lastAQI = ?, lastUpdate = ?, isPopCity = ?, latitude = ?, longitude = ? WHERE name = ?", obj.province, obj.cityName_Pinyin, obj.cityName_Pinyin_Short, [NSNumber numberWithInteger: obj.lastAQI], [NSDate getStringFromDate: obj.lastUpdate Format: AQDATEFORMAT], [NSNumber numberWithBool: obj.isPopCity], [NSNumber numberWithDouble: obj.latitude], [NSNumber numberWithDouble: obj.longitude], obj.cityName];


1.2.5 删除

/删除数据

[db executeUpdate: @"DELETE FROM User WHERE Name = ?",@"张三"];


1.2.6 查询

1.2.6.1 普通查询

        取得特定的资料，则需使用FMResultSet物件接收传回的内容：

    FMResultSet *rs = [db executeQuery: @"SELECT Name, Age, FROM PersonList"];


    while ([rs next]) {

        NSString *name = [rs stringForColumn: @"Name"];

        int age = [rs intForColumn: @"Age"];

    }


    [rs close];

        用[rs next]可以轮询query回来的资料，每一次的next可以得到一个row裡对应的数值，并用[rs stringForColumn:]或[rs intForColumn:]等方法把值转成Object-C的型态。取用完资料后则用[rs close]把结果关闭。


1.2.6.2 模糊查询

select * from tb_name t ifnull(t.cloum) like '%a%' or ifnull(t.cloum) like '%b%' or ifnull(t.cloum) like '%c%'

sqlite3中没有isnull函数，也没有CONCAT函数。


sqldf  SQLITE中文模糊查询的支持问题及解决方案

http://blog.sina.com.cn/s/blog_62b37bfe0101hygk.html


1.2.7 快速查询

        在有些时候，只会query某一个row裡特定的一个数值（比方只是要找John的年龄），FMDB提供了几个比较简便的方法。这些方法定义在FMDatabaseAdditions.h，如果要使用，记得先import进来。

//找地址

    NSString *address = [db stringForQuery:@"SELECT Address FROM PersonList WHERE Name = ?",@"John"];


    //找年齡

    int age = [db intForQuery: @"SELECT Age FROM PersonList WHERE Name = ?", @"John"];


1.3 事务

1.3.1 使用事务批量插入数据

[_dataBase beginTransaction];

BOOL isRollBack = NO;

      @try{

            for(int i = fromIndex; i<500+fromIndex; i++) {

                NSString *nId = [NSString stringWithFormat: @"%d",i];

                NSString *strName = [[NSString alloc] initWithFormat: @"student_%d",i];

                NSString *sql = @"INSERT INTO Student (id, student_name) VALUES (?,?)";

                BOOL a = [_dataBase executeUpdate: sql, nId, strName];

                if(!a) {

                    NSLog(@"插入失败1");

                }

            }

        }

        @catch(NSException *exception) {

            isRollBack = YES;

            [_dataBase rollback];

        }

        @finally{

            if(!isRollBack) {

                [_dataBase commit];

            }

        }


1.3.2 多线程提交

1.3.2.1 初始化FMDatabaseQueue

- (id)init

{

    self = [super init];

    if(self)

    {

        self.dbFile = [DbFileManager dbFilePath];

        self.dbQueue = [FMDatabaseQueue databaseQueueWithPath: self.dbFile];

    }

    return self;

}


1.3.2.2 多线程更新

// 使用

[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]) {

        // …

    }

}];

[if !supportLists]1.3.2.3 [endif]使用事务批量提交

// 如果要支持事务

[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;

        return;

    }

    // etc…

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

}];


[FMDB]IOS多线程读写Sqlite问题解决

http://www.cnblogs.com/likwo/archive/2012/04/09/2438790.html


2 FMDB Class Reference

2.1 Main Usage Classes

There are three main classes in FMDB:

    FMDatabase- Represents a singleSQLite database. Used for executing SQL statements.

    FMResultSet - Represents the results of executing a query on an FMDatabase.

    FMDatabaseQueue- If you're wanting to perform queries andupdates on multiple threads, you'll want to use this class. It's described inthe "Thread Safety" section below.


2.2 Database Creation

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

    A file system path. The file does not have to exist on disk. If it does not exist, it is created for you.

    An empty string (@""). An empty database is created at a temporary location. This database is deleted with the FMDatabase connection is closed.

    NULL. An in-memory database is created. This database will be destroyed with the FMDatabase connection is closed.

        (For more information on temporary and in-memory databases, read the sqlite documentation on the subject: http://www.sqlite.org/inmemorydb.html)

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


2.3 Opening

        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];

    return;

}


2.4 Executing Updates

        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, aBOOL. 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.


2.5 Executing Queries

        A SELECT statement is a query and is executed via one of the -executeQuery...methods. Executing queries returns an FMResultSet object if successful, and nil upon failure. You should use the -lastErrorMessage and -lastErrorCode methods to determine why a query failed.

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

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 *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 format:

    1 intForColumn:

    2 longForColumn:

    3 longLongIntForColumn:

    4 boolForColumn:

    5 doubleForColumn:

    6 stringForColumn:

    7 dateForColumn:

    8 dataForColumn:

    9 dataNoCopyForColumn:

    10 UTF8StringForColumnName:

    11 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.

        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.


2.6 Closing

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

[db close];


2.7 Transactions

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


2.8 Multiple Statements and Batch Stuff

        You can use FMDatabase's executeStatements:withResultBlock: to do multiple statements in a string:

NSString *sql =@"create table bulktest1(id integer primary key autoincrement, xtext);"

                 "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 = @"selectcount(*) as count from bulktest1;"

       "select count(*) as count frombulktest2;"

       "select count(*) as count frombulktest3;";

success = [self.db executeStatements: sql withResultBlock: ^int(NSDictionary *dictionary) {

    NSInteger count = [dictionary[@"count"] integerValue];

    XCTAssertEqual(count, 1, @"expectedone record for dictionary %@", dictionary);

    return 0;

}];


2.9 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:

INSERT INTO myTableVALUES (?, ?, ?)

        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 NSArray, NSDictionary, or a va_list), which are properlyescaped for you.         Alternatively, you may use named parameters syntax:

INSERT INTO myTableVALUES (:id, :name, :value)

        The parameters muststart 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.

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:

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

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

[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 %%.


2.10 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 thread. 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.

        So don't instantiate a single FMDatabase object and use it across multiple threads. 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 myTableVALUES (?)", [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 thingsup 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;

        return;

    }

    // 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.

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


2.11 Making custom sqlite functions, based on blocks.

        You can do this! For an example, look for "makeFunctionNamed:" in main.m


3 参考链接

#IOS开发常用方法集锦#FMDB数据库操作

http://www.cnphp6.com/archives/62009


sqlite第三方类库：FMDB使用（转载）

http://www.cnblogs.com/wuhenke/archive/2012/02/07/2341656.html


https://github.com/ccgus/fmdb


使用FMDB(1)--基本操作

http://blog.sina.com.cn/s/blog_680a331e0100qkz3.html


FMDB官方使用文档-GCD的使用-提高性能（翻译）

http://www.cocoachina.com/industry/20130819/6821.html


支持多线程的FMDB

http://www.cocoachina.com/bbs/read.php?tid=99904


使用FMDB多线程访问数据库 及databaseislocked的问题

http://www.it165.net/pro/html/201407/18314.html

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,324评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,303评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,192评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,555评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,569评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,566评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,927评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,583评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,827评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,590评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,669评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,365评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,941评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,928评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,159评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,880评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,399评论 2赞 342

推荐阅读更多精彩内容

  • iOS--FMDB v2.6.2
    FMDB v2.6.2 这是个SQLite的OC封装。SQLite的地址：http://sqlite.org/ F...
    原鸣清阅读 2,273评论 0赞 3
  • iOS_阅读《iOS数据库应用高级编程》的总结
    阅读完本书，首先给我的感觉是内容有点对不起它的￥59.80定价，全书主要讲了两块内容，一块是SQLite3，...
    瑞小萌阅读 2,814评论 4赞 33
  • FMDB
    优秀的第三方库，README 也是很优秀的，理解了 README，会对使用带来很多便利。 ARC 和 MRC 项目...
    月上楼阁阅读 608评论 0赞 0
  • iOS FMDB 数据库
    FMDB 1、简述： * FMDB是iOS平台的SQLite数据库框架，是对libsqlite3框架的封装 * F...
    莦婼姑娘阅读 4,449评论 9赞 40
  • runLoop的应用
    runloop的应用：1.NSTimer2.ImageView显示3.PerformSelector4.常驻线程 ...
    tiGress阅读 236评论 0赞 1