【iOS】FMDB封装，查询自动mapping
sqlite几乎所有的App都会用到，但是系统自带的sqlite API是用C语言写的，非常不友好，用起来非常不便，通常我们使用第三方封装好的工具，例如：FMDB（https://github.com/ccgus/fmdb）
FMDB的提供了一种更简单，方便的API，并且还提供了线程安全的队列FMDatabaseQueue用于数据库的读写，关于FMDB的使用，参见github上的描述
在使用FMDB查询表的时候的时候我们一般用下面方式
1、定义一个数据模型PersonModel
@interface PersonModel : NSObject @property (nonatomic, assign) NSInteger peopleId; @property (nonatomic, copy) NSString *name; @property (nonatomic, copy) NSString *gender; @property (nonatomic, assign) float weight; @property (nonatomic, assign) double height; @property (nonatomic, assign) short age; @property (nonatomic, assign) long score; @property (nonatomic, strong) NSDate *createTime; @property (nonatomic, assign) BOOL married; @property (nonatomic, strong) NSData *desc; @end
2、插入数据
NSString *sql = @"insert into People(name, gender, weight, height, age, score, createTime, married, desc) values(?,?,?,?,?,?,?,?,?)"; NSString *text = @"dataValue"; NSData *data = [text dataUsingEncoding:NSUTF8StringEncoding]; NSArray *param = @[@"bomo", @"male", @70, @175l, @22, @123, [NSDate date], @NO, data]; [_queue inDatabase:^(FMDatabase *db) { [db executeUpdate:sql withArgumentsInArray:param]; }];
3、查询
NSString *sql = @"select * from People"; __block NSMutableArray *people = [NSMutableArray array]; [_queue inDatabase:^(FMDatabase *db) { FMResultSet *rs = [db executeQuery:sql]; while ([rs next]) { person.name = [rs stringForColumn:@"name"]; person.gender = [rs stringForColumn:@"gender"]; person.height = [rs doubleForColumn:@"height"]; person.score = [rs longForColumn:@"score"]; person.createTime = [rs dateForColumn:@"createTime"]; person.married = [rs boolForColumn:@"married"]; person.desc = [rs dataForColumn:@"desc"]; //下面几种方式读取数据会导致数据类型不一致的问题 //person.peopleId = [rs intForColumn:@"peopleId"]; //person.age = [rs intForColumn:@"age"]; //person.weight = [rs doubleForColumn:@"weight"]; [people addObject:person]; } [rs close]; }];
这里的查询方法需要对每一个属性进行读取和赋值，并且可能有数据类型不一致的问题，比如 读取出来的int 赋值给NSInteger 类型，double类型赋值给float类型，下面我们对查询方法进行改造，让其变得更通用，可以自动映射查询结果到Model，并提供数据库列名到属性名之间的映射
1、定义映射协议
#import <Foundation/Foundation.h> //实现数据库列名到model属性名的映射 @protocol ColumnPropertyMappingDelegate <NSObject> @required - (NSDictionary *)columnPropertyMapping; @end
2、修改PersonModel模型
#import <Foundation/Foundation.h> #import "ColumnPropertyMappingDelegate.h" @interface PersonModel : NSObject <ColumnPropertyMappingDelegate> @property (nonatomic, assign) NSInteger peopleId; @property (nonatomic, copy) NSString *name; @property (nonatomic, copy) NSString *gender; @property (nonatomic, assign) float weight; @property (nonatomic, assign) double height; @property (nonatomic, assign) short age; @property (nonatomic, assign) long score; @property (nonatomic, strong) NSDate *createTime; @property (nonatomic, assign) BOOL married; @property (nonatomic, strong) NSData *desc; @end @implementation PersonModel - (NSDictionary *)columnPropertyMapping { return @{@"id": @"peopleId", @"str1": @"name", @"str2": @"gender", @"float1": @"weight", @"double1": @"height", @"short1": @"age", @"long1": @"score", @"date1": @"createTime", @"bool1": @"married", @"data1": @"desc"}; } @end
数据库的列名如果与Model的属性名不一致，可以通过改映射函数进行配置
3、查询函数，关键方法
/** * 执行查询操作，自定构造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]; } }
我们把数据库的查询和更新方法封装成DbService
#import <Foundation/Foundation.h> @class FMResultSet; @interface DbService : NSObject - (instancetype)initWithPath:(NSString *)path; /** * 查询第一行第一列的数据 */ - (id)executeScalar:(NSString *)sql param:(NSArray *)param; /** * 查询行数 */ - (NSInteger)rowCount:(NSString *)tableName; /** * 更新数据 */ - (BOOL)executeUpdate:(NSString *)sql param:(NSArray *)param; #pragma mark - 查询操作自动构建Model /** * 执行查询操作，自定构造models集合 * * @param sql sql语句 * @param args sql参数 * @param modelClass 结果集model类型 * * @return 查询结果集 */ - (NSArray *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)args modelClass:(Class)modelClass; /** * 执行查询操作，自定构造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; /** * 查询结果集取得model集合 * * @param rs 数据库查询结果集 * @param modelClass 结果集model类型 * * @return 查询结果集 */ - (NSArray *)resultForModels:(FMResultSet *)rs modelClass:(Class)modelClass; /** * 查询结果集取得model集合 * * @param rs 数据库查询结果集 * @param modelClass 结果集model类型 * @param block 对model执行自定义操作 * * @return 查询结果集 */ - (NSArray *)resultForModels:(FMResultSet *)rs modelClass:(Class)modelClass performBlock:(void (^)(id model, FMResultSet *rs))block; @end
#import "DbService.h" #import <objc/runtime.h> #import "FMDB.h" #import "ColumnPropertyMappingDelegate.h" #import "PersonModel.h" @interface DbService () { FMDatabaseQueue *_queue; } @end @implementation DbService - (instancetype)initWithPath:(NSString *)path { if (self = [super init]) { _queue = [FMDatabaseQueue databaseQueueWithPath:path]; } return self; } - (BOOL)executeUpdate:(NSString *)sql param:(NSArray *)param { __block BOOL result = NO; [_queue inDatabase:^(FMDatabase *db) { if (param && param.count > 0) { result = [db executeUpdate:sql withArgumentsInArray:param]; } else { result = [db executeUpdate:sql]; } }]; return result; } - (id)executeScalar:(NSString *)sql param:(NSArray *)param { __block id result; [_queue inDatabase:^(FMDatabase *db) { FMResultSet *rs = [db executeQuery:sql withArgumentsInArray:param]; if ([rs next]) { result = rs[0]; } else { result = 0; } }]; return result; } - (NSInteger)rowCount:(NSString *)tableName { NSNumber *number = (NSNumber *)[self executeScalar:[NSString stringWithFormat:@"SELECT COUNT(*) FROM %@", tableName] param:nil]; return [number longValue]; } #pragma mark - #pragma mark -- 自动创建model查询方法 - (NSArray *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)args modelClass:(Class)modelClass { return [self executeQuery:sql withArgumentsInArray:args modelClass:modelClass performBlock:nil]; } - (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; } /** * 解析结果集（models） */ - (NSArray *)resultForModels:(FMResultSet *)rs modelClass:(Class)modelClass { return [self resultForModels:rs modelClass:modelClass performBlock:nil]; } - (NSArray *)resultForModels:(FMResultSet *)rs modelClass:(Class)modelClass performBlock:(void (^)(id model, FMResultSet *rs))block; { NSDictionary *mapping = nil; NSMutableArray *models = [NSMutableArray array]; 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]; } } @end
创建一个PeopleService演示一下
#import <Foundation/Foundation.h> @interface PeopleService : NSObject + (instancetype)shareInstance; - (void)createTable; - (void)insertOnePerson; - (NSArray *)query; @end
#import "PersonModel.h" #import "PeopleService.h" #import "DbService.h" @interface PeopleService () { DbService *_dbService; } @end @implementation PeopleService + (instancetype)shareInstance { static id instance = nil; static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ instance = [[self alloc] init]; }); return instance; } - (instancetype)init { if (self = [super init]) { NSString *dbName = @"people.db"; NSString *directory = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) firstObject]; NSString *dbPath = [directory stringByAppendingPathComponent:dbName]; _dbService = [[DbService alloc] initWithPath:dbPath]; } return self; } - (void)createTable { NSString *sql = @"CREATE TABLE People ( \ id INTEGER PRIMARY KEY AUTOINCREMENT, \ str1 TEXT, \ str2 TEXT, \ float1 REAL, \ double1 INTEGER, \ short1 REAL, \ long1 REAL, \ date1 TEXT, \ bool1 INTEGER, \ data1 BLOB \ )"; [_dbService executeUpdate:sql param:nil]; } - (void)insertOnePerson { NSString *sql = @"insert into People(str1, str2, float1, double1, short1, long1, date1, bool1, data1) values(?,?,?,?,?,?,?,?,?)"; NSString *text = @"dataValue"; NSData *data = [text dataUsingEncoding:NSUTF8StringEncoding]; NSArray *param = @[@"bomo", @"male", @70, @175l, @22, @123, [NSDate date], @NO, data]; [_dbService executeUpdate:sql param:param]; } - (NSArray *)query { return [_dbService executeQuery:@"select * from People" withArgumentsInArray:nil modelClass:[PersonModel class]]; } @end
测试
[[PeopleService shareInstance] createTable]; [[PeopleService shareInstance] insertOnePerson]; NSArray *people = [[PeopleService shareInstance] query]; for (PersonModel *person in people) { NSLog(@"name = %@, age = %d", person.name, person.age); }
我们看一下数据库的表结构
查询操作只需要一行代码，其他的都交给DbService，减少代码量，减少人为错误，由于个人水平有限，如有疏漏或问题，欢迎回复，如果大家有更好的方式，可以一起讨论
Demo：https://files.cnblogs.com/files/bomo/FmdbDemo.zip
