这是最终的效果图
创建并打开数据库
#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]; }]; } |
