【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

PersonModel.m

　　创建一个PeopleService演示一下

#import <Foundation/Foundation.h>

@interface PeopleService : NSObject

+ (instancetype)shareInstance;

- (void)createTable;

- (void)insertOnePerson;

- (NSArray *)query;

@end

PeopleService.h

#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.m

　　测试

    [[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：http://files.cnblogs.com/files/bomo/FmdbDemo.zip

相关阅读:
freopen
字符
 map映射
 P3512 [POI2010]PIL-Pilots-洛谷luogu
快读
 单调队列&单调栈
 简写
 邻接表&链式前向星
 mysql参数详解
 网络管理指南
原文地址：https://www.cnblogs.com/bomo/p/4665376.html