FMDatabase 的使用方法

原创 2012年03月21日 17:29:07

FMDatabase 的使用方法

- (NSString*) getPath {

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

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





dataBase = [FMDatabasedatabaseWithPath:[selfgetPath]];

    if (![dataBase open])

       NSLog(@"OPEN FAIL");



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







   recordArray = [[NSMutableArrayalloc]init];


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


    if ([db open]) {

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

        while ([rs next]){

            OneRecord = [[OneRecord alloc]init];

            OneRecord.aa = [NSNumbernumberWithFloat:[rs doubleForColumn:@"aa"]];

   = [rs stringForColumn:@"bb"];

   = [NSNumbernumberWithInt:[rs intForColumn:@"cc"]];

            OneRecord.dd = [NSNumbernumberWithInt:[rs intForColumn:@"dd"]];

   = [rs stringForColumn:@"ee"];

            [recordArrayaddObject: OneRecord];



        [rs close];






if ([dbopen]) {


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


         db close];








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

    NSNumber numberWithFloat:aa],bb,cc,dd,ee];

    db commit];

    db close];




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:

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 includes CREATE, PRAGMA,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 withSELECT, 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. 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 an FMResultSet object if successful, and nil 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 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:

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


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


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 an NSArray,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 parameters must 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 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:@"INSERT INTO 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%%.

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;



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



iOS中原生的SQLite API在使用上相当不友好,在使用时,非常不便。于是,就出现了一系列将SQLite API进行封装的库,例如FMDB、PlausibleDatabase、sqlitepers...
  • u012890196
  • u012890196
  • 2014年07月24日 12:14
  • 1332


本人以后都会不定时的写一些关于IOS开发的教程,大家希望可提些意见,大家希望写些什么,对大家有帮助的,希望帮到大家: FMDatabase其实是一个很轻级的sqlite数据库封装库,用Object-...
  • u012460084
  • u012460084
  • 2014年07月05日 10:40
  • 2308

FMDatabase 的使用方法 FMDatabase 的使用方法   以下是FMDB的一些基本使用,FM...
  • u010889390
  • u010889390
  • 2013年12月04日 14:51
  • 773


#import #import "FMDatabase.h" #import "FMDatabaseAdditions.h" @interface wiDBRoot : NSObject @pro...
  • liuyinghui523
  • liuyinghui523
  • 2015年09月10日 08:38
  • 823


对于没得什么SQL基础的来说,弄起来还是很无助的,所以有就记录下把。 // 判断是否存在表 - (BOOL) isTableOK:(NSString *)tableName ...
  • liwuking
  • liwuking
  • 2014年05月07日 11:50
  • 1259


通常一次 sqlite3_exec 就是一次事务,假如你要对数据库中的Stutent表插入新数据,那么该事务的具体过程是:开始新事物->插入数据->提交事务,那么当我们要往该表内插入500条数据,如果...
  • x32sky
  • x32sky
  • 2014年02月07日 16:42
  • 6803

fmdb使用心得(fmdatabase execute update error withargumentsinarray)

用了一会会儿的sqlite自带的函数接口,貌似有点坑。 就用了fmdb,确实好用,但是要注意,插入的时候,变量应该是对象,而不能是基本数据类型。否则,报错,虐。 error:"fmdatabase...
  • cos_sin_tan
  • cos_sin_tan
  • 2015年10月02日 00:34
  • 1029

iOS FMDatabase 本地数据库的创建和几个基本使用方法

使用数据库之前当然要先在网上下载FMDB的库,然后添加到自己的工程里面去。没有的请点击下面的来下载 fmdb 在FrameWork里添加“libsqulite3.0.dylib”,不然库托进去后会引起...
  • I_am_JoJo
  • I_am_JoJo
  • 2015年08月13日 10:36
  • 2206


以下是FMDB的一些基本使用,FMDB框架其实只是一层很薄的封装,主要的类也就两个:FMDatabase和FMResultSet; 其中的FMResultSet对象让我想起了android中sq...
  • xiaoclove
  • xiaoclove
  • 2013年11月26日 17:06
  • 686


一、前言如上一章所讲,FMDB源码主要有以下几个文件组成: FMResultSet : 表示FMDatabase执行查询之后的结果集。 FMDatabase : 表示一个单独的SQLite数据库操作实...
  • yixiangboy
  • yixiangboy
  • 2016年04月28日 14:45
  • 4528
您举报文章:FMDatabase 的使用方法