FMDB的初体验及离线缓存

一、FMDB的简单使用

1、代码实例

1、准备工作
    需要导入sqlite3框架，下载fmdb类库
2、打开数据库与创建表
   //2.1存取的沙盒路径
 NSString *cache = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES).lastObject;
    NSString *filePath = [cache stringByAppendingPathComponent:@"class.sqlite"];
    //2.2打开数据库
    FMDatabase *base =[FMDatabase databaseWithPath:filePath];
    if([base open]){
        NSLog(@"打开成功");
    }else{
        NSLog(@"打开失败");
    }
    //2.3创建表
    NSString *sql = @"create table if not exists t_students (id integer primary key autoincrement,name text ,age integer,height real)";
    BOOL flag = [base executeUpdate:sql];
    if (flag) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
    _dataBase = base;  //赋值全局变量
3、凡是增、删、改都行executeUpdate执行语句。
4、插入数据
//插入数据，注意当数据为整型或浮点型时，必须转换成NSNumber类型的，@(),包含起来
    for (NSInteger i = 0; i< 10; i++) {
        NSString *name = [NSString stringWithFormat:@"TheYouth%ld",i];
        NSInteger age = [[NSString stringWithFormat:@"%ld",i+20] integerValue];
        float height = [[NSString stringWithFormat:@"%.1f",i+0.5] floatValue];
        
        BOOL isflag = [_dataBase executeUpdate:@"insert into t_students (name,age,height) values (?,?,?)",name,@(age),@(height)];
        if (isflag) {
            NSLog(@"插入表成功");
        }else{
            NSLog(@"插入表失败");
        }
    }
5、删除数据
 BOOL isflag = [_dataBase executeUpdate:@"delete from t_students where age > 25"];
    if (isflag) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
6、修改数据
BOOL isflag = [_dataBase executeUpdate:@"update t_students set age = 3.8,name='xingZai' where height < 3.5"];
    if (isflag) {
        NSLog(@"修改成功");
    }else{
        NSLog(@"修改失败");
    }
7、查询数据
 FMResultSet *result = [_dataBase executeQuery:@"select * from t_students"];
    while ([result next]) { //执行符合条件的下一行
        NSString *name = [result stringForColumnIndex:1]; //取出字符型属性
        NSInteger age = [result intForColumnIndex:2];  //取出整型
        CGFloat height = [result doubleForColumnIndex:3]; //取出浮点型数据
        NSLog(@"%@,%ld,%.1f",name,age,height);
    } 

二、FMDB多线程与事务

1、代码实例

//1.创建数据库，打开表
NSString *cachePath = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES)[0];
    //设置路径 
    NSString *filePath = [cachePath stringByAppendingPathComponent:@"student.sqlite"];
    // 创建数据库实例
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:filePath];
    _queue = queue;
    
    // 创建数据库表
    // 提供了一个多线程安全的数据库实例
    [queue inDatabase:^(FMDatabase *db) {
        
      BOOL flag =  [db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement,name text,money integer)"];
        if (flag) {
            NSLog(@"success");
        }else{
            NSLog(@"failure");
        }  
    }];
//2.插入数据
 [_queue inDatabase:^(FMDatabase *db) {
       BOOL flag = [db executeUpdate:@"insert into t_student (name,money) values (?,?)",@"a",@100];
        if (flag) {
            NSLog(@"success");
        }else{
            NSLog(@"failure");
        }
        [db executeUpdate:@"insert into t_student (name,money) values (?,?)",@"b",@50];
        
    }];
//3.删除所有数据
   [_queue inDatabase:^(FMDatabase *db) {
        BOOL flag = [db executeUpdate:@"delete from t_user;"];
        if (flag) {
            NSLog(@"success");
        }else{
            NSLog(@"failure");
        }
    }];
//4.开启一个事务，来处理多线程的问题，必须全部处理完才能结束(成功)，否则失败，然后回滚到原始状态
  [_queue inDatabase:^(FMDatabase *db) {
        // 开启事务
        [db beginTransaction];
       BOOL flag = [db executeUpdate:@"update t_user set money = ? where name = ?;",@500,@"a"];
        if (flag) {
            NSLog(@"success");
        }else{
            NSLog(@"failure");
            // 回滚
            [db rollback];
        }
        BOOL flag1 = [db executeUpdate:@"updat t_user set money = ? where name = ?;",@1000,@"b"];
        if (flag1) {
            NSLog(@"success");
        }else{
            NSLog(@"failure");
            [db rollback];
        }
        // 全部操作完成时候再去
        [db commit];
    }];

三、FMDB处理离线缓存

1、思维导图

2、代码实例

以微博数据为例
1、保存数据
+ (void)saveWithStatuses:(NSArray *)statuses
{//取出数据
    // 遍历模型数组
    for (NSDictionary *statusDict in statuses) {//遍历数组中的每个字典
        NSString *idstr = statusDict[@"idstr"]; //存取idstr，为了之后取数据
        NSString *accessToken = [CZAccountTool account].access_token; //存取用户token
        NSData *data = [NSKeyedArchiver archivedDataWithRootObject:statusDict]; //存取字典数据，转换成二进制流
       BOOL flag = [_db executeUpdate:@"insert into t_status (idstr,access_token,dict) values(?,?,?)",idstr,accessToken,data];//插入数据，字段有idstr，用户token，原始数据字典
        if (flag) {
            NSLog(@"插入成功");
        }else{
            NSLog(@"插入失败");
        }
    }
}
//2.存数据库中取数据，param状态模型，要不需要传很多参数
+ (NSArray *)statusesWithParam:(CZStatusParam *)param
{
    // 进入程序第一次获取的查询语句
    NSString *sql = [NSString stringWithFormat:@"select * from t_status where access_token = '%@' order by idstr desc limit 20;",param.access_token];
    if (param.since_id) { // 获取最新微博的查询语句
        sql = [NSString stringWithFormat:@"select * from t_status where access_token = '%@' and idstr > '%@' order by idstr desc limit 20;",param.access_token,param.since_id];
    }else if (param.max_id){  // 获取更多微博的查询语句
        sql = [NSString stringWithFormat:@"select * from t_status where access_token = '%@' and idstr <= '%@' order by idstr desc limit 20;",param.access_token,param.max_id];
    }
    FMResultSet *set = [_db executeQuery:sql];
    NSMutableArray *arrM = [NSMutableArray array];
    while ([set next]) {
        NSData *data = [set dataForColumn:@"dict"];
        NSDictionary *dict = [NSKeyedUnarchiver unarchiveObjectWithData:data];
        CZStatus *s = [CZStatus objectWithKeyValues:dict];
        [arrM addObject:s];
    }
    return arrM;
}