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 新建數(shù)據(jù)庫
????????使用資料庫的第一件事，就是建立一個資料庫。要注意的是，在iOS環(huán)境下，只有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的變數(shù)有關(guān)，所以在string裡使用?號來代表這些變數(shù)。
[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。該做的轉(zhuǎn)換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 刪除
/刪除數(shù)據(jù)
[db executeUpdate: @"DELETE FROM User WHERE Name = ?",@"張三"];
1.2.6 查詢
1.2.6.1 普通查詢
????????取得特定的資料，則需使用FMResultSet物件接收傳回的內(nèi)容：
??? 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裡對應的數(shù)值，并用[rs stringForColumn:]或[rs intForColumn:]等方法把值轉(zhuǎn)成Object-C的型態(tài)。取用完資料后則用[rs close]把結(jié)果關(guān)閉。
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函數(shù)，也沒有CONCAT函數(shù)。
sqldf? SQLITE中文模糊查詢的支持問題及解決方案
http://blog.sina.com.cn/s/blog_62b37bfe0101hygk.html
1.2.7 快速查詢
????????在有些時候，只會query某一個row裡特定的一個數(shù)值（比方只是要找John的年齡），F(xiàn)MDB提供了幾個比較簡便的方法。這些方法定義在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 使用事務批量插入數(shù)據(jù)
[_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開發(fā)常用方法集錦#FMDB數(shù)據(jù)庫操作
http://www.cnphp6.com/archives/62009
sqlite第三方類庫：FMDB使用（轉(zhuǎn)載）
http://www.cnblogs.com/wuhenke/archive/2012/02/07/2341656.html
使用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多線程訪問數(shù)據(jù)庫 及databaseislocked的問題
