数据库（FMDB）-城市数据的缓存

这是最终的效果图

city.PNG

创建并打开数据库

#pragma mark -- 数据库处理
//创建并打开数据库
- (void)openSqlData{
    NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSLog(@"sql路径===%@",doc);
    NSString *fileName = [doc stringByAppendingPathComponent:@"city.sqlite"];
    FMDatabaseQueue *dbQueue = [FMDatabaseQueue databaseQueueWithPath:fileName];
    [dbQueue inDatabase:^(FMDatabase *db) {
        BOOL result = [db executeUpdate:@"create table if not exists t_city (id integer primary key autoincrement,pid integer not null,name text not null,pinyin text not null,firstletter text not null,hotcity integer not null);"];
        if (result) {
            NSLog(@"打开成功");
        }else{
            NSLog(@"打开失败");
        }
    }];
    self.dbQueue = dbQueue;
}

传入模型数组，向数据库中插入数据

//根据模型数组插入数据库中
- (void)insertData:(CityModel *)cityModel{
    DefineWeakSelf;
    //删除数据库数据
    __block NSString *insertStr = @"";
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *deleteStr = [NSString stringWithFormat:@"delete from t_city"];
        if (![db executeUpdate:deleteStr]) {
            NSLog(@"删除数据库记录失败。。。");
        }
        //开启事务
        [db beginTransaction];
        BOOL isRollBack = NO;
        @try {
            [cityModel.positionList enumerateObjectsUsingBlock:^(CityEntity *entity, NSUInteger idx, BOOL * _Nonnull stop) {
                insertStr =[NSString stringWithFormat:@"insert into t_city(pid,name,pinyin,firstletter,hotcity) values ('%@','%@','%@','%@','%@');",@(entity.pid),entity.name,entity.pinyin,entity.firstLetter,@(0)];
                if (![db executeUpdate:insertStr]) {
                    NSLog(@"插入数据失败");
                }
            }];
        } @catch (NSException *exception) {
        //插入出错，自动回滚
            isRollBack = YES;
            [db rollback];
        } @finally {
        //提交事务
            if (!isRollBack) {
                [db commit];
            }
        }
    }];
}

查询数据，并存到字典中

//查询并处理数据
- (void)queryData{
    DefineWeakSelf;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *firstStr = @"a";
        //执行查询语句 并以升序排列
        FMResultSet *resultSet = [db executeQuery:@"select * from t_city order by firstletter asc"];
        //遍历结果
        while ([resultSet next]) {
            NSString *title = [resultSet stringForColumn:@"firstletter"];
            NSString *name = [resultSet stringForColumn:@"name"];
            NSInteger hotcity = [resultSet intForColumn:@"hotcity"];
            if (hotcity == 1) {
                [wSelf.hotcityArray addObject:name];
            }
            if ([title isEqualToString:firstStr]) {
                [wSelf.cityArray addObject:name];
            }else{
                [wSelf.titleArray addObject:[firstStr uppercaseString]];
                wSelf.cityDictionary[firstStr] = wSelf.cityArray;
                firstStr = title;
                wSelf.cityArray = [NSMutableArray array];
                [wSelf.cityArray addObject:name];
            }
        }
        if (![firstStr isEqualToString:@"a"]) {
            [wSelf.titleArray addObject:[firstStr uppercaseString]];
            wSelf.cityDictionary[firstStr] = wSelf.cityArray;
        }
    }];
    [self.cityChooseTableView reloadData];
}

模糊搜索

//模糊查询
- (void)queryByString:(NSString *)queryStr{
    DefineWeakSelf;
    _isSearch = YES;
    _searchArr = [NSMutableArray array];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        //根据城市拼音或名字 模糊搜索
        NSString *sql = [NSString stringWithFormat:@"select * from t_city where pinyin like '%@%%' or name like '%@%%';",[queryStr lowercaseString],queryStr];
        FMResultSet *resultSet = [db executeQuery:sql];
        while ([resultSet next]) {
            NSLog(@"%@",[resultSet stringForColumn:@"name"]);
            [wSelf.searchArr addObject:[resultSet stringForColumn:@"name"]];
        }
        [wSelf.cityChooseTableView reloadData];
    }];
}