iOS数据库的使用——FMDB

一般的app中，都会有本地数据库，来存储一些数据或者缓存。虽然我们项目中也用到了数据库，但是以前都没怎么研究，昨天专门研究了一下这一块儿。在iOS中，数据库的调用我用的是FMDB。FMDB可以在gitHub上下载：https://github.com/ccgus/fmdb。下面我们来看看怎么使用FMDB。

1. 添加必要的文件

将下载下来的项目解压，然后将src文件夹下的除了fmdb.m文件之外的所有文件都添加到自己的项目工程中。

然后再在项目中添加libsqlite3.dylib。如图

2. 编写必要的代码

在这里我新建了一个FMDBManager单例。用来操作FMDB。下面我们来看看怎么操作

①.初始化Database

#define CREATE_TABLE_Student @"CREATE TABLE IF NOT EXISTS student(id integer primary key autoincrement , name TEXT(100) , number INTEGER , sex INTEGER , score INTEGER)"

- (BOOL)createDB
{
//    根据path获取db。一定要retain。否则会自动释放
    _db = [[FMDatabase databaseWithPath:[self dbPath]] retain];
    
//    打开db
    if ([_db open])
    {
//        新建Table
        if ([_db executeUpdate:CREATE_TABLE_Student])
        {
            NSLog(@"create table successed");
            return YES;
        }
        {
            NSLog(@"create table faild");
        }
    }else
    {
        NSLog(@"db open faild");
    }
    return NO;
}

//获取db的路径
- (NSString*)dbPath
{
    NSArray* ary = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString* documentDic = [ary objectAtIndex:0];
    NSString* dbPath = [documentDic stringByAppendingString:@"/database.db"];
    return dbPath;
}

②. 创建单例

+ (FMDBManager*)shareInstance
{
    static FMDBManager* manager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[FMDBManager alloc] initWithDB];
    });
    return manager;
}

- (id)initWithDB
{
    if (self = [super init])
    {
        [self createDB];
    }
    return self;
}

③. 使用DataBase

- (BOOL)saveStudentInDB:(Student*)stu
{
    NSString* sql = @"INSERT INTO student(name, number, sex, score) VALUES(?,?,?,?)";
    if ([_db executeUpdate:sql,
         stu.name,
         [NSNumber numberWithInteger:stu.number],
         [NSNumber numberWithBool:stu.sex],
         [NSNumber numberWithInteger:stu.score]])
    {
        NSLog(@"save stu successed");
        return YES;
    }else
    {
        NSLog(@"save stu faild");
        return NO;
    }
}

- (NSMutableArray*)getStudentFromDBByName:(NSString*)name
{
    NSMutableArray* ary = [NSMutableArray array];
    NSString* sql = @"SELECT * FROM student WHERE name = ?";
    FMResultSet* result = [_db executeQuery:sql,name];
    while ([result next])
    {
        Student* stu = [Student studentWithResult:result];
        [ary addObject:stu];
    }
    return ary;
}

- (NSMutableArray*)getStudentFromDBByNumber:(NSInteger)number
{
    NSMutableArray* ary = [NSMutableArray array];
    NSString* sql = [NSString stringWithFormat:@"SELECT * FROM student WHERE number = %d",number];
    FMResultSet* result = [_db executeQuery:sql];
    while ([result next])
    {
        Student* stu = [Student studentWithResult:result];
        [ary addObject:stu];
    }
    return ary;
}

- (NSMutableArray*)getStudentFromDBByScoreAboveNumber:(NSInteger)number
{
    NSMutableArray* ary = [NSMutableArray array];
    NSString* sql = [NSString stringWithFormat:@"SELECT * FROM student WHERE score > %d",number];
    FMResultSet* result = [_db executeQuery:sql];
    while ([result next])
    {
        Student* stu = [Student studentWithResult:result];
        [ary addObject:stu];
    }
    return ary;
}

- (NSMutableArray*)getStudentFromDBByScoreUnderNumber:(NSInteger)number
{
    NSMutableArray* ary = [NSMutableArray array];
    NSString* sql = [NSString stringWithFormat:@"SELECT * FROM student WHERE score < ?"];
    FMResultSet* result = [_db executeQuery:sql,[NSNumber numberWithInteger:number]];
    while ([result next])
    {
        Student* stu = [Student studentWithResult:result];
        [ary addObject:stu];
    }
    return ary;
}

- (NSMutableArray*)getAllStudentFromDB
{
    NSMutableArray* ary = [NSMutableArray array];
    NSString* sql = @"SELECT * FROM student";
    FMResultSet* result = [_db executeQuery:sql];
    while ([result next]) {
        Student* stu = [Student studentWithResult:result];
        [ary addObject:stu];
    }
    return ary;
}

- (BOOL)deleteStudentInDBByNumber:(NSInteger)number
{
    NSString* sql = @"DELETE FROM student WHERE number = ?";
    if ([_db executeUpdate:sql,[NSNumber numberWithInteger:number]])
    {
        NSLog(@"delete successed");
        return YES;
    }else
    {
        NSLog(@"delete faild");
        return NO;
    }
}

具体demo见：点击打开链接http://download.csdn.net/detail/u011394507/7009999