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

　　这是终的效果图

　　创建并打开数据库
#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];
}];
}