FMDatabase　的使用方法

SQLite (http://www.sqlite.org/docs.html) 是一個輕量級的關聯式資料庫。iOS SDK很早就支援了SQLite，在使用時，只需要加入 libsqlite3.dylib 依賴以及引入 sqlite3.h 標頭檔案即可。但是，原生的SQLite API在使用上相當不友好，在使用時，非常不便。於是，開源社群中就出現了一系列將SQLite API進行封裝的庫，而FMDB (https://github.com/ccgus/fmdb) 則是開源社群中的優秀者。

用FMDB後的資料庫程式碼清晰明瞭，比原生的API優雅多了。另外，FMDB同時相容ARC和非ARC工程，會自動根據工程配置來調整相關的記憶體管理程式碼。

使用說明

該使用說明主要翻譯自fmdb的github專案說明文件: https://github.com/ccgus/fmdb

引入相關檔案

首先將FMDB從github上clone下來，然後將以下檔案copy到你的工程中：

1
2
3
4
5
6
7
8
9
10

FMDatabase.h
FMDatabase.m
FMDatabaseAdditions.h
FMDatabaseAdditions.m
FMDatabasePool.h
FMDatabasePool.m
FMDatabaseQueue.h
FMDatabaseQueue.m
FMResultSet.h
FMResultSet.m

工具

為了檢視Sqlite中的資料，一個好的圖形化介面的資料庫管理程式是必不可少的。mysql有phpMyAdmin，那麼sqlite呢？

我主要使用的是Firefox的一個名為SQLite Manager的外掛，安裝此外掛後，可以直接開啟字尾名為sqlite的資料庫檔案。SQLite Manager提供一個圖形化的介面來執行資料查詢或更改操作。如下圖所示：

FMDB將SQLite API進行了很友好的封裝，使用上非常方便，對於那些使用純Sqlite API來進行資料庫操作的app，可以考慮將其遷移到基於FMDB上，這對於以後資料庫相關功能的開發維護，可以提高不少效率。

使用方法

- (NSString*) getPath {

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

   return [[pathsobjectAtIndex:0]stringByAppendingPathComponent:@"MyTable"] ;

}

１.建立資料庫

-(void)CreateTable;

{

dataBase = [FMDatabasedatabaseWithPath:[selfgetPath]];

    if (![dataBaseopen])

       NSLog(@"OPEN FAIL");

    }

    [dataBaseexecuteUpdate:@"CREATE TABLE IF NOT EXISTS MyTable(aa float,bb text,cc integer,dd integer,ee text)"];

    [dataBaseclose];

}

２.查詢資料

-(void)QueryData

{

//獲取資料

   recordArray = [[NSMutableArrayalloc]init];

   db = [[FMDatabasealloc]initWithPath:[selfgetPath]];

    if ([dbopen]) {

       FMResultSet *rs = [dbexecuteQuery:@"SELECT * FROM MyTable"];

        while ([rsnext]){

            OneRecord = [[OneRecordalloc]init];

            OneRecord.aa = [NSNumbernumberWithFloat:[rsdoubleForColumn:@"aa"]];

            OneRecord.bb = [rsstringForColumn:@"bb"];

            OneRecord.cc = [NSNumbernumberWithInt:[rsintForColumn:@"cc"]];

            OneRecord.dd = [NSNumbernumberWithInt:[rsintForColumn:@"dd"]];

            OneRecord.ee = [rsstringForColumn:@"ee"];

            [recordArrayaddObject:OneRecord];

            [OneRecordrelease];

        }

        [rs close];

        [dbclose];

}

３.更新資料

-(void)UpdateData

{

if ([dbopen]) {

         [dbbeginTransaction];

         [dbexecuteUpdate:@"UPDATE MyTable SET aa = ? WHERE date = ?",aa1,aa2];

         [dbexecuteUpdate:@"UPDATE MyTable SET bb = ? WHERE date = ?",bb1,bb2];

         [dbexecuteUpdate:@"UPDATE MyTable SET cc = ? WHERE date = ?",cc1,cc2];

         [dbexecuteUpdate:@"UPDATE MyTable SET dd = ? WHERE date = ?",dd1,dd2];

         [dbcommit];

         dbclose];

   }

}

４。插入資料

-(void)insertData

{

//插入資料庫

    [dbbeginTransaction];

    [dbexecuteUpdate:@"INSERT INTO MyTable (aa,bb,cc,dd,ee) VALUES (?,?,?,?,?)",

    NSNumbernumberWithFloat:aa],bb,cc,dd,ee];

    db commit];

    db close];

}

５。官方的使用方法為：

Usage

There are three main classes in FMDB:

1. FMDatabase - Represents a single SQLite database. Used for executing SQL statements.
2. FMResultSet - Represents the results of executing a query on anFMDatabase.
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.

Database Creation

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

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 theFMDatabase connection is closed.
3. NULL. An in-memory database is created. This database will be destroyed with theFMDatabase 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"];

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;

}

Executing Updates

Any sort of SQL statement which is not aSELECT statement qualifies as an update. This includesCREATE,PRAGMA,UPDATE,INSERT,ALTER,COMMIT,BEGIN,DETACH,DELETE,DROP,END,EXPLAIN,VACUUM, andREPLACE statements (plus many more). Basically, if your SQL statement does not begin withSELECT, it is an update statement.

Executing updates returns a single value, aBOOL. A return value ofYES means the update was successfully executed, and a return value ofNO means that some error was encountered. If you use the-[FMDatabase executeUpdate:error:withArgumentsInArray:orVAList:] method to execute an update, you may supply anNSError ** that will be filled in if execution fails. Otherwise you may invoke the-lastErrorMessage and-lastErrorCode methods to retrieve more information.

Executing Queries

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

Executing queries returns anFMResultSet object if successful, andnil upon failure. Like executing updates, there is a variant that accepts anNSError ** parameter. Otherwise 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 awhile() 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:

• intForColumn:
• longForColumn:
• longLongIntForColumn:
• boolForColumn:
• doubleForColumn:
• stringForColumn:
• dateForColumn:
• dataForColumn:
• dataNoCopyForColumn:
• UTF8StringForColumnIndex:
• objectForColumn:

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 anFMResultSet yourself, since that happens when either the result set is deallocated, or the parent database is closed.

Closing

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

[db close];

Transactions

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

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 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 anNSArray,NSDictionary, or ava_list), which are properly escaped for you.

Alternatively, you may use named parameters syntax:

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

The parametersmust start with a colon. SQLite itself supports other characters, but internally the Dictionary keys are prefixed with a colon, donot 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 ava_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 anNSNumber object:

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

Alternatively, you can use the-execute*WithFormat: variant to useNSString-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%%.

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

        return;

    }

    // etc…

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

}];

FMDatabaseQueue will make a serialized GCD queue in the background and execute the blocks you pass to the GCD queue. This means if you call your FMDatabaseQueue's methods from multiple threads at the same time GDC will execute them in the order they are received. This means queries and updates won't step on each other's toes, and every one is happy.

轉自:http://blog.csdn.net/ch_soft/article/details/7380218

http://blog.csdn.net/kingkong1024/article/details/7744285