使用FMDB操作SQLite数据库

       在进行iPhone开发的过程中，有时候我们需要使用数据库在本地存储一些信息，这时候我们就需要数据库来完成相应的功能。SQLite是一个不错的选择，虽然xCode自带的库可以进行数据库的各种操作，但是其操作是C语言形式的，这样我们在Objective-C中嵌入C势必会破坏整体的代码格式，所以我们这里推荐使用开源的SQLite操作库FMDB。

准备：

下载FMDB源文件，文件不多，将其添加到工程。需要的库文件是libsqlite3.0.dylib，需要引入的头文件：

#import "FMDatabase.h"
#import "FMResultSet.h"

示例：

- (void)openAndCreate
{
    //打开购物车数据库 创建表
    FMDatabase *db = [FMDatabase databaseWithPath:@"user.db"];
    if([db open])
    {
        [db setShouldCacheStatements:YES];
        NSLog(@"begin create table");
        [db beginTransaction];
        [db executeUpdate:@"create table shoppingcart(id INTEGER PRIMARY KEY,prodId VARCHAR(20),name VARCHAR(100),price NUMERIC(15,2),prodAmt NUMERIC(15,2),deliverSpecs VARCHAR(100),imgUrl NUMERIC(15,2),memberId VARCHAR(10))"];
        if([db hadError])
        {
            NSLog(@"Error %d : %@",[db lastErrorCode],[db lastErrorMessage]);
        }
        [db commit];
        [db close];
    }
    else
    {
        NSLog(@"open db failed");
        return NO;
    }
}

- (void)getData
{
    FMDatabase *db = db = [FMDatabase databaseWithPath:@"user.db"];
    if([db open])
    {
        [db setShouldCacheStatements:YES];
        //开始查询操作
        NSString *sql = [NSString stringWithString:@"SELECT * FROM shoppingcart"];
        FMResultSet *rs = [db executeQuery:sql];
        BOOL haveResult = NO;
        while([rs next])
        {
            haveResult = YES;
            ShoppingcartItemClass *shoppingItem = [[ShoppingcartItemClass alloc]init];
            shoppingItem.prodId = [rs stringForColumn:@"prodId"];
            shoppingItem.name = [rs stringForColumn:@"name"];
            shoppingItem.price = [rs doubleForColumn:@"price"];
            shoppingItem.prodAmt = [rs intForColumn:@"prodAmt"];
            shoppingItem.deliverSpecs = [rs stringForColumn:@"deliverSpecs"];
            shoppingItem.imgUrl = [rs stringForColumn:@"imgUrl"];
            shoppingItem.memberId = [rs stringForColumn:@"memberId"];
            [goodsList addObject:shoppingItem];
        }
        if(!haveResult)
        {
            Shoppingcart *shoppingcart = [[Shoppingcart alloc]init];
            [self.view addSubview:shoppingcart.view];
        }
        else
        {
            Shoppingcart *shoppingcart = [[Shoppingcart alloc]initWithArray:[goodsList copy] nav:self.navigationController item:self.navigationItem];
            [goodsList removeAllObjects];
           
            [self.view addSubview:shoppingcart.view];
        }
        if([db hadError])
        {
            NSLog(@"Error %d : %@",[db lastErrorCode],[db lastErrorMessage]);
        }
        [rs close];
        [db close];
    }
    else
    {
        NSLog(@"open db failed");
        return;
    }
}

- (void)deleteData
{
    FMDatabase *db = [FMDatabase databaseWithPath:@"user.db"];
    if([db open])
    {
        [db setShouldCacheStatements:YES];
        //开始删除操作
        [db beginTransaction];
        ShoppingItem *item = [list objectAtIndex:row];
        NSLog(@"%@",item.prodId);
        NSString *sql = [NSString stringWithFormat:@"DELETE FROM shoppingcart WHERE prodId = \"%@\"",item.prodId];
        [db executeUpdate:sql];
        if([db hadError])
        {
            NSLog(@"Error %d : %@",[db lastErrorCode],[db lastErrorMessage]);
            return;
        }
        [db commit];
        [db close];
    }
    else
    {
        NSLog(@"open db failed");
        if([db hadError])
        {
            NSLog(@"Error %d : %@",[db lastErrorCode],[db lastErrorMessage]);
        }
        return;
    }
}