FMDatabase 的使用方法

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

FMDatabase　的使用方法

 

以下是FMDB的一些基本使用，FMDB框架其实只是一层很薄的封装，主要的类也就两个：FMDatabase和FMResultSet;

其中的FMResultSet对象让我想起了android中sqlite的cursor集合啊。

FMDB的github地址是，https://github.com/ccgus/fmdb。

补充：是导入FMDB之后，还要记得导入iOS的sqlite3Framework，libsqlite3.0.dylib,PS：我发现网上有些转载我的博文的，我很欢迎，毕竟我的一些博文也是总结别人的经验的。但是有个网站，51CTO，我觉得是让人又爱又恨的，它引用的文章从来没有过注明出处。都是佚名。这让人很无语，这是我偶然之间发现我写的东西竟然被标为佚名?

1、首先得实例化一个FMDatabase对象，这跟Sqlitepersistentobjects派生一个子类进行操作是不同。接着打开一个数据库(如果没有会创建一个数据库)

//paths： ios下Document路径，Document为中ios可读写的文件夹

//创建数据库实例 db 这里说明下:如果路径中不存在”Test.db”的文件,sqlite会自动创建”Test.db”

1. NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); 
2. NSString *documentDirectory = [paths objectAtIndex:0]; 
3. //dbPath： 数据库路径，在Document中。 
4. NSString *dbPath = [documentDirectory stringByAppendingPathComponent:@"Test.db"]; 
5.    
6. FMDatabase *db= [FMDatabase databaseWithPath:dbPath] ; 
7. if (![db open]) { 
8. NSLog(@“Could not open db.”); 
9. return ;  
10. } 

接下来，我们可以通过这个数据库对象进行操作了。操作主要是update和queries。

首先是创建表。

//创建一个名为User的表，有两个字段分别为string类型的Name，integer类型的 Age

1. [db executeUpdate:@"CREATE TABLE User (Name text, Age integer)"]; 

这样我们就有了一张表了。接下我们对表进行操作。插入数据!注意插入的数据使用了通配符，这跟iphone直接使用sqlite借口的绑定变量是一样的，后面的通配符匹配的数据。

//插入数据使用OC中的类型 text对应为NSString integer对应为NSNumber的整形

1. [db executeUpdate:@"INSERT INTO User (Name,Age) VALUES (?,?)",@"老婆",[NSNumber numberWithInt:20]] ; 

接下来是更新数据。

1. //更新数据 将“老婆”更改为“宝贝” 
2. [db executeUpdate:@"UPDATE User SET Name = ? WHERE Name = ? ",@"老婆",@"宝贝"]; 

再接下来，就是删除数据啦。

1. //删除数据  
2. [db executeUpdate:@"DELETE FROM User WHERE Name = ?",@"老婆"]; 

update的基本操作就这几个，接下来是queries!

1. //返回数据库中第一条满足条件的结果  
2. NSString *aa=[db stringForQuery:@"SELECT Name FROM User WHERE Age = ?",@"20"]; 

这样我们就查询返回了一条数据，那当我们想要查询放返回多条数据怎么办呢?不用愁，之前我就提到了FMDB中的另外一个主要的类，FMResultSet，这是一个结果集!返回多条数据时FMDB会将数据放在这个结果集中，然后我们在对这个结果集进行查询操作!很简单。

1. FMResultSet *rs=[db executeQuery:@"SELECT * FROM User"]; 
2. rs=[db executeQuery:@"SELECT * FROM User WHERE Age = ?",@"20"]; 
3. while ([rs next]){ 
4. NSLog(@“%@ %@”,[rs stringForColumn:@"Name"],[rs stringForColumn:@"Age"]); 
5. } 

更多的FMResultSet方法有：

intForColumn:

longForColumn:

longLongIntForColumn:

boolForColumn:

doubleForColumn:

stringForColumn:

dateForColumn:

dataForColumn:

dataNoCopyForColumn:

UTF8StringForColumnIndex:

objectForColumn:

- (NSString*)getPath {

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

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

}

１.创建数据库

-(void)CreateTable;

{

dataBase = [FMDatabasedatabaseWithPath:[selfgetPath]];

    if (![dataBase open])

     NSLog(@"OPEN FAIL");

    }

       

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

    [dataBaseclose];

}

２.查询数据

-(void)QueryData

{

//获取数据

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

          OneRecord.bb =[rs stringForColumn:@"bb"];

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

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

          OneRecord.ee =[rs stringForColumn:@"ee"];

         [recordArrayaddObject: OneRecord];

          [OneRecordrelease];

       }

       [rs close];

       [dbclose];

}

３.更新数据

-(void)UpdateData

{

if ([dbopen]){

       [dbbeginTransaction];

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

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

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

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

        [dbcommit];

        db close];

   }

}

４。插入数据

-(void)insertData

{

//插入数据库

   [dbbeginTransaction];

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

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

    db commit];

    db close];

}

５。官方的使用方法为：

Usage

There are three main classes in FMDB:

1. FMDatabase - Represents a single SQLitedatabase. Used for executing SQL statements.
2. FMResultSet - Represents the results ofexecuting a query on anFMDatabase.
3. FMDatabaseQueue - If you're wanting toperform queries and updates on multiple threads, you'll want to usethis class. It's described in the "Thread Safety" sectionbelow.

Database Creation

An FMDatabase iscreated with a path to a SQLite database file. This path can be oneof these three:

1. A file system path. The file does not have to exist on disk. If itdoes not exist, it is created for you.
2. An empty string (@"").An empty database is created at a temporary location. This databaseis deleted with theFMDatabase connectionis closed.
3. NULL. An in-memory database is created. This database willbe destroyed with theFMDatabaseconnectionis closed.

(For more information on temporary and in-memory databases, readthe 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 toopen and/or create the database.

if (![db open]) {

    [db release];

    return;

}

Executing Updates

Any sort of SQL statement which is nota SELECT statementqualifies 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 beginwithSELECT,it is an update statement.

Executing updates returns a single value,a BOOL.A return value of YES meansthe update was successfully executed, and a return valueof NO meansthat some error was encountered. If you usethe -[FMDatabaseexecuteUpdate:error:withArgumentsInArray:orVAList:] methodto execute an update, you may supply anNSError** that will be filled in if executionfails. Otherwise you may invoke the-lastErrorMessage and-lastErrorCode methodsto retrieve more information.

Executing Queries

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

Executing queries returns an FMResultSet objectif successful, and nil uponfailure. Like executing updates, there is a variant that acceptsanNSError** parameter. Otherwise you should usethe-lastErrorMessageand-lastErrorCode methodsto determine why a query failed.

In order to iterate through the results of your query, you usea 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 eachrecord

}

You must always invoke -[FMResultSetnext] before attempting to access thevalues returned in a query, even if you're only expecting one:

FMResultSet *s = [db executeQuery:@"SELECT COUNT(*) FROMmyTable"];

if ([s next]) {

    int totalCount = [sintForColumnIndex:0];

}

FMResultSet has many methods to retrievedata in an appropriate format:

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

Each of these methods also has a {type}ForColumnIndex: variantthat is used to retrieve the data based on the position of thecolumn 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, orthe parent database is closed.

Closing

When you have finished executing queries and updates on thedatabase, you should -close the FMDatabaseconnectionso that SQLite will relinquish any resources it has acquired duringthe course of its operation.

[db close];

Transactions

FMDatabase can begin and commit atransaction by invoking one of the appropriate methods or executinga 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 thestandard SQLite binding syntax:

INSERT INTO myTable VALUES (?, ?, ?)

The ? characteris recognized by SQLite as a placeholder for a value to beinserted. The execution methods all accept a variable number ofarguments (or a representation of those arguments, such asanNSArray,NSDictionary,or ava_list),which are properly escaped for you.

Alternatively, you may use named parameters syntax:

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

Theparameters must startwith a colon. SQLite itself supports other characters, butinternally the Dictionary keys are prefixed with a colon,donot include the colon in yourdictionary keys.

NSDictionary *argsDict = [NSDictionarydictionaryWithObjectsAndKeys:@"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 myTableVALUES (%@)", @"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 asa parameter) must be objects. The following will not work (and willresult in a crash):

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

The proper way to insert a number is to box it inan NSNumber object:

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

Alternatively, you can use the -execute*WithFormat: variantto use NSString-stylesubstitution:

[db executeUpdateWithFormat:@"INSERT INTO myTable VALUES (%d)",42];

Internally, the -execute*WithFormat: methodsare properly boxing things for you. The following percent modifiersare 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% characterto appear in your SQL statement, you should use%%.

Using FMDatabaseQueue and Thread Safety.

Using a single instance of FMDatabase from multiple threads at onceis a bad idea. It has always been OK to make a FMDatabaseobjectper thread. Just don't share a single instanceacross threads, and definitely not across multiple threads at thesame time. Bad things will eventually happen and you'll eventuallyget something to crash, or maybe get an exception, or maybemeteorites will fall out of the sky and hit your MacPro. This would suck.

So don't instantiate a single FMDatabase object and use itacross multiple threads.

Instead, use FMDatabaseQueue. It's your friend and it's here tohelp. Here's how to use it:

First, make your queue.

FMDatabaseQueue *queue = [FMDatabaseQueuedatabaseQueueWithPath:aPath];

Then use it like so:

[queue inDatabase:^(FMDatabase *db) {

    [db executeUpdate:@"INSERTINTO myTable VALUES (?)", [NSNumber numberWithInt:1]];

    [db executeUpdate:@"INSERTINTO myTable VALUES (?)", [NSNumber numberWithInt:2]];

    [db executeUpdate:@"INSERTINTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    FMResultSet *rs = [dbexecuteQuery:@"select * from foo"];

    while ([rs next]) {

       …

    }

}];

An easy way to wrap things up in a transaction can be done likethis:

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {

    [db executeUpdate:@"INSERTINTO myTable VALUES (?)", [NSNumber numberWithInt:1]];

    [db executeUpdate:@"INSERTINTO myTable VALUES (?)", [NSNumber numberWithInt:2]];

    [db executeUpdate:@"INSERTINTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    if(whoopsSomethingWrongHappened) {

       *rollback = YES;

       return;

    }

    // etc…

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

}];

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