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