FMDB简单使用
1、增删改查://注意:dataWithPath中的路径参数一般会选择保存到沙箱中的Documents目录中;
//如果这个参数设置为nil则数据库会在内存中创建; //如果设置为@””则会在沙箱中的临时目录创建,应用程序关闭则文件删除 //增 - (void)openDB:(NSString *)dbname{ NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject]; path = [path stringByAppendingPathComponent:dbname]; NSLog(@"path--->%@",path); FMDatabase *db = [FMDatabase databaseWithPath:path]; _fmdb = db; if ([db open]) { NSLog(@"数据库(%@)打开成功",dbname); //在FMDB中,除查询以外的所有操作,都称为“更新” //create、drop、insert、update、delete等 //建表 BOOL result = [db executeUpdate:[NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"]]; if (result) { NSLog(@"创表成功"); }else{ NSLog(@"创表失败"); } }else{ NSLog(@"数据库(%@)打开失败",dbname); } } //改 - (void)insert{ for (int i = 0; i<10; i++) { NSString *name = [NSString stringWithFormat:@"jack_%d",i]; [self.fmdb executeUpdate:@"INSERT INTO t_student (name, age) VALUES (?, ?);", name, @(arc4random_uniform(40))]; } } //删 - (void)delete{ [self.fmdb executeUpdate:@"DROP TABLE IF EXISTS t_student;"]; [self.fmdb executeUpdate:@"CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"]; } //查 - (void)query { // 1.执行查询语句 FMResultSet *resultSet = [self.fmdb executeQuery:@"SELECT * FROM t_student"]; // 2.遍历结果 while ([resultSet next]) { int ID = [resultSet intForColumn:@"id"]; NSString *name = [resultSet stringForColumn:@"name"]; int age = [resultSet intForColumn:@"age"]; NSLog(@"%d %@ %d", ID, name, age); }
//此处是调用下面的自定义models集合
NSArray *aaa = [self executeQuery:resultSet.query withArgumentsInArray:_arr modelClass:[Person class] performBlock:^(id model, FMResultSet *rs) { rs = resultSet; }];
if (aaa.count !=0 ) {
Person *p = aaa[0];
NSLog(@"%@--%@--%d",aaa,p.name,p.age);
}else{
NSLog(@"查询生成的模型数组为空");
}
}
2、自定义构造models集合
/** * 执行查询操作,自定构造models集合 * * @param sql sql语句 * @param args sql参数 * @param modelClass 结果集model类型 * @param block 对model执行自定义操作 * * @return 查询结果集 */ - (NSArray *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)args modelClass:(Class)modelClass performBlock:(void (^)(id model, FMResultSet *rs))block { __block NSMutableArray *models = [NSMutableArray array]; [_queue inDatabase:^(FMDatabase *db) { NSDictionary *mapping = nil; FMResultSet *rs = [db executeQuery:sql withArgumentsInArray:args]; while ([rs next]) { id model = [[modelClass alloc] init]; if(!mapping && [model conformsToProtocol:@protocol(ColumnPropertyMappingDelegate)]) { //实现了列-属性转换协议 mapping = [model columnPropertyMapping]; } for (int i = 0; i < [rs columnCount]; i++) { //列名 NSString *columnName = [rs columnNameForIndex:i]; //进行数据库列名到model之间的映射转换,拿到属性名 NSString *propertyName; if(mapping) { propertyName = mapping[columnName]; if (propertyName == nil) { //如果映射未定义,则视为相同 propertyName = columnName; } } else { propertyName = columnName; } objc_property_t objProperty = class_getProperty(modelClass, propertyName.UTF8String); //如果属性不存在,则不操作 if (objProperty) { if(![rs columnIndexIsNull:i]) { [self setProperty:model value:rs columnName:columnName propertyName:propertyName property:objProperty]; } } NSAssert(![propertyName isEqualToString:@"description"], @"description为自带方法,不能对description进行赋值,请使用其他属性名或请ColumnPropertyMappingDelegate进行映射"); } //执行自定义操作 if (block) { block(model, rs); } [models addObject:model]; } [rs close]; }]; return models; } /** * 进行属性赋值 */ - (void)setProperty:(id)model value:(FMResultSet *)rs columnName:(NSString *)columnName propertyName:(NSString *)propertyName property:(objc_property_t)property { // @"f":@"float", // @"i":@"int", // @"d":@"double", // @"l":@"long", // @"c":@"BOOL", // @"s":@"short", // @"q":@"long", // @"I":@"NSInteger", // @"Q":@"NSUInteger", // @"B":@"BOOL", NSString *firstType = [[[[NSString stringWithUTF8String:property_getAttributes(property)] componentsSeparatedByString:@","] firstObject] substringFromIndex:1]; if ([firstType isEqualToString:@"f"]) { NSNumber *number = [rs objectForColumnName:columnName]; [model setValue:@(number.floatValue) forKey:propertyName]; } else if([firstType isEqualToString:@"i"]){ NSNumber *number = [rs objectForColumnName:columnName]; [model setValue:@(number.intValue) forKey:propertyName]; } else if([firstType isEqualToString:@"d"]){ [model setValue:[rs objectForColumnName:columnName] forKey:propertyName]; } else if([firstType isEqualToString:@"l"] || [firstType isEqualToString:@"q"]){ [model setValue:[rs objectForColumnName:columnName] forKey:propertyName]; } else if([firstType isEqualToString:@"c"] || [firstType isEqualToString:@"B"]){ NSNumber *number = [rs objectForColumnName:columnName]; [model setValue:@(number.boolValue) forKey:propertyName]; } else if([firstType isEqualToString:@"s"]){ NSNumber *number = [rs objectForColumnName:columnName]; [model setValue:@(number.shortValue) forKey:propertyName]; } else if([firstType isEqualToString:@"I"]){ NSNumber *number = [rs objectForColumnName:columnName]; [model setValue:@(number.integerValue) forKey:propertyName]; } else if([firstType isEqualToString:@"Q"]){ NSNumber *number = [rs objectForColumnName:columnName]; [model setValue:@(number.unsignedIntegerValue) forKey:propertyName]; } else if([firstType isEqualToString:@"@\"NSData\""]){ NSData *value = [rs dataForColumn:columnName]; [model setValue:value forKey:propertyName]; } else if([firstType isEqualToString:@"@\"NSDate\""]){ NSDate *value = [rs dateForColumn:columnName]; [model setValue:value forKey:propertyName]; } else if([firstType isEqualToString:@"@\"NSString\""]){ NSString *value = [rs stringForColumn:columnName]; [model setValue:value forKey:propertyName]; } else { [model setValue:[rs objectForColumnName:columnName] forKey:propertyName]; } }
相关链接: