FMDB简易封装，可以进行多表操作，动态创建表

最新推荐文章于 2021-02-23 18:59:35 发布

J.Reno

最新推荐文章于 2021-02-23 18:59:35 发布

阅读量2.2k

点赞数

分类专栏： iOS 文章标签： FMDB iOS

本文链接：https://blog.csdn.net/JReno/article/details/87888846

版权

iOS 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

由于项目中用到多用户登录，需要根据不同的用户创建本地的数据库表，我的做法就是根据用户ID判断，创建属于该ID的不同的数据库表，包括个人信息，聊天，语音，图片，下载文件等等。

XMDataModelHelp

这个用来根据传入的参数来创建表，需要传入的参数为一个字典，{“tableId”:“123”,“tablePath”:path,“tableName”:name} 分别为表id,表的存储路径跟表名。

#import <Foundation/Foundation.h>
#import "FMDB.h"
@interface XMDataModelHelp : NSObject
@property (nonatomic, retain, readonly) FMDatabaseQueue *dbQueue;

/**
 {"tableId":"123","tablePath":path,"tableName":name}
 */
@property (nonatomic, strong) NSDictionary *tableParams;
/**
 用于动态创建数据库
 
 @param tableParams {"tableId":"123","tablePath":path,"tableName":name}
 @return path
 */
+ (NSString *)dbPathWithTableID:(NSDictionary *)tableParams;
@end

#import "XMDataModelHelp.h"

@interface XMDataModelHelp()

@property (nonatomic, retain) FMDatabaseQueue *dbQueue;

@end

@implementation XMDataModelHelp
+ (NSString *)dbPathWithTableID:(NSDictionary *)tableParams{
    NSString *docsdir = tableParams[@"tablePath"];
    NSFileManager *filemanage = [NSFileManager defaultManager];
    BOOL isDir;
    BOOL exit =[filemanage fileExistsAtPath:docsdir isDirectory:&isDir];
    if (!exit || !isDir) {
        [filemanage createDirectoryAtPath:docsdir withIntermediateDirectories:YES attributes:nil error:nil];
    }
    NSString *dbpath = [NSString stringWithFormat:@"%@/%@_%@.sqlite",docsdir,tableParams[@"tableName"],tableParams[@"tableId"]];
    return dbpath;
}

- (FMDatabaseQueue *)dbQueue{
    self.dbQueue = [[FMDatabaseQueue alloc] initWithPath:[self.class dbPathWithTableID:self.tableParams]];
    return _dbQueue;
}
@end

XMDataModel

所有创建的模型model都可以继承于此model，并且可以进行增删查改操作。

#import <Foundation/Foundation.h>

/** SQLite五种数据类型 */
#define SQLTEXT     @"TEXT"
#define SQLINTEGER  @"INTEGER"
#define SQLREAL     @"REAL"
#define SQLBLOB     @"BLOB"
#define SQLNULL     @"NULL"
#define PrimaryKey  @"primary key"
#define primaryId   @"pk"

@interface XMDataModel : NSObject
/** 主键 id */
@property (nonatomic, assign)   int  pk;
/** 列名 */
@property (retain, readonly, nonatomic) NSMutableArray *columeNames;
/** 列类型 */
@property (retain, readonly, nonatomic) NSMutableArray *columeTypes;

/**
 *  获取该类的所有属性
 */
+ (NSDictionary *)getPropertys;

/** 获取所有属性，包括主键 */
+ (NSDictionary *)getAllProperties;

/** 数据库中是否存在表 */
+ (BOOL)isExistInTableWithID:(NSDictionary *)tableParams;

/** 表中的字段*/
+ (NSArray *)getColumnsWithID:(NSDictionary *)tableParams;

/** 保存或更新
 * 如果不存在主键，保存，
 * 有主键，则更新
 */
- (BOOL)saveOrUpdateWithID:(NSDictionary *)tableParams;
/** 保存单个数据 */
- (BOOL)saveWithID:(NSDictionary *)tableParams;
/** 批量保存数据 */
+ (BOOL)saveObjects:(NSArray *)array withID:(NSDictionary *)tableParams;
/** 更新单个数据 */
- (BOOL)updateWithID:(NSDictionary *)tableParams;
/** 批量更新数据*/
+ (BOOL)updateObjects:(NSArray *)array withID:(NSDictionary *)tableParams;
/** 删除单个数据 */
- (BOOL)deleteObjectWithID:(NSDictionary *)tableParams;
/** 批量删除数据 */
+ (BOOL)deleteObjects:(NSArray *)array withID:(NSDictionary *)tableParams;
/** 通过条件删除数据 */
+ (BOOL)deleteObjectsByCriteria:(NSString *)criteria withID:(NSDictionary *)tableParams;
/** 清空表 */
+ (BOOL)clearTableWithID:(NSDictionary *)tableParams;

/** 查询全部数据 */
+ (NSArray *)findAllWithID:(NSDictionary *)tableParams;

/** 通过主键查询 */
+ (instancetype)findByPK:(int)inPk withID:(NSDictionary *)tableParams;

/** 查找某条数据 */
+ (instancetype)findFirstByCriteria:(NSString *)criteria withID:(NSDictionary *)tableParams;

/** 通过条件查找数据
 * 这样可以进行分页查询 @" WHERE pk > 5 limit 10"
 */
+ (NSArray *)findByCriteria:(NSString *)criteria withID:(NSDictionary *)tableParams;

#pragma mark - must be override method

/**
 * 创建表
 * 如果已经创建，返回YES
 */
+ (BOOL)createTableWithID:(NSDictionary *)tableParams;

/** 如果子类中有一些property不需要创建数据库字段，那么这个方法必须在子类中重写
 */
+ (NSArray *)transients;

/**
 *数据库中的数据条数
 */
+ (int)numOfMessagesWithID:(NSDictionary *)tableParams;


/**
 给数据库新增加字段
 
 @param column 字段
 @param type 字段类型
 @param tableParams {"tableId":"123","tablePath":path,"tableName":name}
 @return 添加结果
 */
+ (BOOL)addColumn:(NSString *)column type:(NSString *)type tableId:(NSDictionary *)tableParams;

/**
 查找某条数据的最大值最小值，平均值
 @param criteria 条件用语
 (1)查找最大值 select max(age) maxAge from workTable
 (2)查找平均值 select avg(age) avgAge from workTable
 (3)查找数据之和 select sum(age) sumAge from workTable
 (4)查找最小值 select min(age) minAge from workTable
 @param tableParams tableParams {"tableId":"123","tablePath":path,"tableName":name}
 @return self
 */
+ (instancetype)findOneByCriteria:(NSString *)criteria withID:(NSDictionary *)tableParams;
@end

#import "XMDataModel.h"
#import "XMDataModelHelp.h"
@implementation XMDataModel
- (instancetype)init
{
    self = [super init];
    if (self) {
        NSDictionary *dic = [self.class getAllProperties];
        _columeNames = [[NSMutableArray alloc] initWithArray:[dic objectForKey:@"name"]];
        _columeTypes = [[NSMutableArray alloc] initWithArray:[dic objectForKey:@"type"]];
    }
    
    return self;
}
#pragma mark - base method
/**
 *  获取该类的所有属性
 */
+ (NSDictionary *)getPropertys
{
    NSMutableArray *proNames = [NSMutableArray array];
    NSMutableArray *proTypes = [NSMutableArray array];
    NSArray *theTransients = [[self class] transients];
    unsigned int outCount, i;
    objc_property_t *properties = class_copyPropertyList([self class], &outCount);
    for (i = 0; i < outCount; i++) {
        objc_property_t property = properties[i];
        //获取属性名
        NSString *propertyName = [NSString stringWithCString:property_getName(property) encoding:NSUTF8StringEncoding];
        if ([theTransients containsObject:propertyName]) {
            continue;
        }
        [proNames addObject:propertyName];
        //获取属性类型等参数
        NSString *propertyType = [NSString stringWithCString: property_getAttributes(property) encoding:NSUTF8StringEncoding];
        /*
         c char         C unsigned char
         i int          I unsigned int
         l long         L unsigned long
         s short        S unsigned short
         d double       D unsigned double
         f float        F unsigned float
         q long long    Q unsigned long long
         B BOOL
         @ 对象类型 //指针 对象类型 如NSString 是@“NSString”
         64位下long 和long long 都是Tq
         SQLite 默认支持五种数据类型TEXT、INTEGER、REAL、BLOB、NULL
         */
        if ([propertyType hasPrefix:@"T@"]) {
            [proTypes addObject:SQLTEXT];
        } else if ([propertyType hasPrefix:@"Ti"]||[propertyType hasPrefix:@"TI"]||[propertyType hasPrefix:@"Ts"]||[propertyType hasPrefix:@"TS"]||[propertyType hasPrefix:@"TB"]) {
            [proTypes addObject:SQLINTEGER];
        } else {
            [proTypes addObject:SQLREAL];
        }
        
    }
    free(properties);
    
    return [NSDictionary dictionaryWithObjectsAndKeys:proNames,@"name",proTypes,@"type",nil];
}

/** 获取所有属性，包含主键pk */
+ (NSDictionary *)getAllProperties
{
    NSDictionary *dict = [self.class getPropertys];
    
    NSMutableArray *proNames = [NSMutableArray array];
    NSMutableArray *proTypes = [NSMutableArray array];
    [proNames addObject:primaryId];
    [proTypes addObject:[NSString stringWithFormat:@"%@ %@",SQLINTEGER,PrimaryKey]];
    [proNames addObjectsFromArray:[dict objectForKey:@"name"]];
    [proTypes addObjectsFromArray:[dict objectForKey:@"type"]];
    
    return [NSDictionary dictionaryWithObjectsAndKeys:proNames,@"name",proTypes,@"type",nil];
}

/** 数据库中是否存在表 */
+ (BOOL)isExistInTableWithID:(NSDictionary *)tableParams
{
    __block BOOL res = NO;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [self.class getTableName:tableParams];
        res = [db tableExists:tableName];
    }];
    return res;
}

+ (NSArray *)getColumnsWithID:(NSDictionary *)tableParams
{
    WEAKSELF;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    NSMutableArray *columns = [NSMutableArray array];
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
        FMResultSet *resultSet = [db getTableSchema:tableName];
        while ([resultSet next]) {
            NSString *column = [resultSet stringForColumn:@"name"];
            [columns addObject:column];
        }
    }];
    return [columns copy];
}

/**
 * 创建表
 * 如果已经创建，返回YES
 */
+ (BOOL)createTableWithID:(NSDictionary *)tableParams
{
    FMDatabase *db = [FMDatabase databaseWithPath:[XMDataModelHelp dbPathWithTableID:tableParams]];
    if (![db open]) {
        AppLog(@"数据库打开失败!");
        return NO;
    }
    NSString *tableName = [self.class getTableName:tableParams];
    NSString *columeAndType = [self.class getColumeAndTypeString];
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@);",tableName,columeAndType];
    if (![db executeUpdate:sql]) {
        return NO;
    }
    
    NSMutableArray *columns = [NSMutableArray array];
    FMResultSet *resultSet = [db getTableSchema:tableName];
    while ([resultSet next]) {
        NSString *column = [resultSet stringForColumn:@"name"];
        [columns addObject:column];
    }
    NSDictionary *dict = [self.class getAllProperties];
    NSArray *properties = [dict objectForKey:@"name"];
    NSPredicate *filterPredicate = [NSPredicate predicateWithFormat:@"NOT (SELF IN %@)",columns];
    //过滤数组
    NSArray *resultArray = [properties filteredArrayUsingPredicate:filterPredicate];
    
    for (NSString *column in resultArray) {
        NSUInteger index = [properties indexOfObject:column];
        NSString *proType = [[dict objectForKey:@"type"] objectAtIndex:index];
        NSString *fieldSql = [NSString stringWithFormat:@"%@ %@",column,proType];
        NSString *sql = [NSString stringWithFormat:@"ALTER TABLE %@ ADD COLUMN %@ ",[self.class getTableName:tableParams],fieldSql];
        if (![db executeUpdate:sql]) {
            return NO;
        }
    }
    [db close];
    return YES;
}

- (BOOL)saveOrUpdateWithID:(NSDictionary *)tableParams
{
    id primaryValue = [self valueForKey:primaryId];
    if ([primaryValue intValue] <= 0) {
        return [self saveWithID:tableParams];
    }
    return [self updateWithID:tableParams];
}

- (BOOL)saveWithID:(NSDictionary *)tableParams
{
    WEAKSELF;
    if (![self.class isExistInTableWithID:tableParams]) {//判断表是不是存在
        [self.class createTableWithID:tableParams];
    }
    [self updateColumnEnable:tableParams];
    NSString *tableName = [self.class getTableName:tableParams];
    NSMutableString *keyString = [NSMutableString string];
    NSMutableString *valueString = [NSMutableString string];
    NSMutableArray *insertValues = [NSMutableArray  array];
    for (int i = 0; i < self.columeNames.count; i++) {
        NSString *proname = [self.columeNames objectAtIndex:i];
        if ([proname isEqualToString:primaryId]) {
            continue;
        }
        [keyString appendFormat:@"%@,", proname];
        [valueString appendString:@"?,"];
        id value = [self valueForKey:proname];
        if (!value) {
            value = @"";
        }
        [insertValues addObject:value];
    }
    
    [keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
    [valueString deleteCharactersInRange:NSMakeRange(valueString.length - 1, 1)];
    
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    __block BOOL res = NO;
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@(%@) VALUES (%@);", tableName, keyString, valueString];
        res = [db executeUpdate:sql withArgumentsInArray:insertValues];
        weakSelf.pk = res?[NSNumber numberWithLongLong:db.lastInsertRowId].intValue:0;
        AppLog(res?@"插入成功":@"插入失败");
    }];
    AppLog(@"数据库存储位置%@",[XMDataModelHelp dbPathWithTableID:tableParams]);
    return res;
}

/** 批量保存用户对象 */
+ (BOOL)saveObjects:(NSArray *)array withID:(NSDictionary *)tableParams
{
    WEAKSELF;
    if (![self.class isExistInTableWithID:tableParams]) {//判断表是不是存在
        [self.class createTableWithID:tableParams];
    }
    //判断是否是XMBaseModel的子类
    for (XMDataModel *model in array) {
        if (![model isKindOfClass:[XMDataModel class]]) {
            return NO;
        }
    }
    __block BOOL res = YES;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    // 如果要支持事务
    [XMDB.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        for (XMDataModel *model in array) {
            [model updateColumnEnable:tableParams];
            NSString *tableName = [weakSelf getTableName:tableParams];
            NSMutableString *keyString = [NSMutableString string];
            NSMutableString *valueString = [NSMutableString string];
            NSMutableArray *insertValues = [NSMutableArray  array];
            for (int i = 0; i < model.columeNames.count; i++) {
                NSString *proname = [model.columeNames objectAtIndex:i];
                if ([proname isEqualToString:primaryId]) {
                    continue;
                }
                [keyString appendFormat:@"%@,", proname];
                [valueString appendString:@"?,"];
                id value = [model valueForKey:proname];
                if (!value) {
                    value = @"";
                }
                [insertValues addObject:value];
            }
            [keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
            [valueString deleteCharactersInRange:NSMakeRange(valueString.length - 1, 1)];
            
            NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@(%@) VALUES (%@);", tableName, keyString, valueString];
            BOOL flag = [db executeUpdate:sql withArgumentsInArray:insertValues];
            model.pk = flag?[NSNumber numberWithLongLong:db.lastInsertRowId].intValue:0;
            AppLog(flag?@"插入成功":@"插入失败");
            if (!flag) {
                res = NO;
                *rollback = YES;
                return;
            }
        }
    }];
    return res;
}

/** 更新单个对象 */
- (BOOL)updateWithID:(NSDictionary *)tableParams
{
    [self updateColumnEnable:tableParams];
    WEAKSELF;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    __block BOOL res = NO;
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
        id primaryValue = [weakSelf valueForKey:primaryId];
        if (!primaryValue || primaryValue <= 0) {
            return ;
        }
        NSMutableString *keyString = [NSMutableString string];
        NSMutableArray *updateValues = [NSMutableArray  array];
        for (int i = 0; i < weakSelf.columeNames.count; i++) {
            NSString *proname = [weakSelf.columeNames objectAtIndex:i];
            if ([proname isEqualToString:primaryId]) {
                continue;
            }
            [keyString appendFormat:@" %@=?,", proname];
            id value = [weakSelf valueForKey:proname];
            if (!value) {
                value = @"";
            }
            [updateValues addObject:value];
        }
        //删除最后那个逗号
        [keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
        NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE %@ = ?;", tableName, keyString, primaryId];
        [updateValues addObject:primaryValue];
        res = [db executeUpdate:sql withArgumentsInArray:updateValues];
        AppLog(res?@"更新成功":@"更新失败");
    }];
    return res;
}

/** 批量更新用户对象*/
+ (BOOL)updateObjects:(NSArray *)array withID:(NSDictionary *)tableParams
{
    WEAKSELF;
    for (XMDataModel *model in array) {
        if (![model isKindOfClass:[XMDataModel class]]) {
            return NO;
        }
    }
    __block BOOL res = YES;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    // 如果要支持事务
    [XMDB.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        for (XMDataModel *model in array) {
            [model updateColumnEnable:tableParams];
            NSString *tableName = [weakSelf getTableName:tableParams];
            id primaryValue = [model valueForKey:primaryId];
            if (!primaryValue || primaryValue <= 0) {
                res = NO;
                *rollback = YES;
                return;
            }
            
            NSMutableString *keyString = [NSMutableString string];
            NSMutableArray *updateValues = [NSMutableArray  array];
            for (int i = 0; i < model.columeNames.count; i++) {
                NSString *proname = [model.columeNames objectAtIndex:i];
                if ([proname isEqualToString:primaryId]) {
                    continue;
                }
                [keyString appendFormat:@" %@=?,", proname];
                id value = [model valueForKey:proname];
                if (!value) {
                    value = @"";
                }
                [updateValues addObject:value];
            }
            
            //删除最后那个逗号
            [keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
            NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE %@=?;", tableName, keyString, primaryId];
            [updateValues addObject:primaryValue];
            BOOL flag = [db executeUpdate:sql withArgumentsInArray:updateValues];
            AppLog(flag?@"更新成功":@"更新失败");
            if (!flag) {
                res = NO;
                *rollback = YES;
                return;
            }
        }
    }];
    
    return res;
}

/** 删除单个对象 */
- (BOOL)deleteObjectWithID:(NSDictionary *)tableParams
{
    [self updateColumnEnable:tableParams];
    WEAKSELF;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    __block BOOL res = NO;
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
        id primaryValue = [weakSelf valueForKey:primaryId];
        if (!primaryValue || primaryValue <= 0) {
            return ;
        }
        NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?",tableName,primaryId];
        res = [db executeUpdate:sql withArgumentsInArray:@[primaryValue]];
        AppLog(res?@"删除成功":@"删除失败");
        //delete from tab where (select count(Id) from tab)> 50 and Id in (select Id from tab order by insertTime desc limit (select count(Id) from tab) offset 50 );
    }];
    return res;
}

/** 批量删除用户对象 */
+ (BOOL)deleteObjects:(NSArray *)array withID:(NSDictionary *)tableParams
{
    WEAKSELF;
    [array enumerateObjectsUsingBlock:^(id  _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
        if (![obj isKindOfClass:[XMDataModel class]]) {
            return;
        }
    }];
    __block BOOL res = YES;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    // 如果要支持事务
    [XMDB.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        for (XMDataModel *model in array) {
            [model updateColumnEnable:tableParams];
            NSString *tableName = [weakSelf getTableName:tableParams];
            id primaryValue = [model valueForKey:primaryId];
            if (!primaryValue || primaryValue <= 0) {
                return ;
            }
            
            NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?",tableName,primaryId];
            BOOL flag = [db executeUpdate:sql withArgumentsInArray:@[primaryValue]];
            AppLog(flag?@"删除成功":@"删除失败");
            if (!flag) {
                res = NO;
                *rollback = YES;
                return;
            }
        }
    }];
    return res;
}

/** 通过条件删除数据 */
+ (BOOL)deleteObjectsByCriteria:(NSString *)criteria withID:(NSDictionary *)tableParams
{
    WEAKSELF
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    __block BOOL res = NO;
    XMDB.tableParams = tableParams;
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
        NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ %@ ",tableName,criteria];
        res = [db executeUpdate:sql];
        AppLog(res?@"删除成功":@"删除失败");
    }];
    return res;
}

/** 清空表 */
+ (BOOL)clearTableWithID:(NSDictionary *)tableParams
{
    WEAKSELF;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    __block BOOL res = NO;
    XMDB.tableParams = tableParams;
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
        NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@",tableName];
        res = [db executeUpdate:sql];
        AppLog(res?@"清空成功":@"清空失败");
    }];
    return res;
}

/** 查询全部数据 */
+ (NSArray *)findAllWithID:(NSDictionary *)tableParams
{
    WEAKSELF
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    NSMutableArray *users = [NSMutableArray array];
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@",tableName];
        FMResultSet *resultSet = [db executeQuery:sql];
        while ([resultSet next]) {
            XMDataModel *model = [[weakSelf.class alloc] init];
            [model updateColumnEnable:tableParams];
            for (int i=0; i< model.columeNames.count; i++) {
                NSString *columeName = [model.columeNames objectAtIndex:i];
                NSString *columeType = [model.columeTypes objectAtIndex:i];
                if ([columeType isEqualToString:SQLTEXT]) {
                    [model setValue:[resultSet stringForColumn:columeName] forKey:columeName];
                } else {
                    [model setValue:[NSNumber numberWithLongLong:[resultSet longLongIntForColumn:columeName]] forKey:columeName];
                }
            }
            [users addObject:model];
            FMDBRelease(model);
        }
    }];
    
    return users;
}

/** 查找某条数据 */
+ (instancetype)findFirstByCriteria:(NSString *)criteria withID:(NSDictionary *)tableParams
{
    NSArray *results = [self.class findByCriteria:criteria withID:tableParams];
    if (results.count < 1) {
        return nil;
    }
    
    return [results firstObject];
}

+ (instancetype)findByPK:(int)inPk withID:(NSDictionary *)tableParams
{
    NSString *condition = [NSString stringWithFormat:@"WHERE %@=%d",primaryId,inPk];
    return [self findFirstByCriteria:condition withID:tableParams];
}

/** 通过条件查找数据 */
+ (NSArray *)findByCriteria:(NSString *)criteria withID:(NSDictionary *)tableParams
{
    WEAKSELF;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    NSMutableArray *users = [NSMutableArray array];
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ %@",tableName,criteria];
        FMResultSet *resultSet = [db executeQuery:sql];
        while ([resultSet next]) {
            XMDataModel *model = [[weakSelf.class alloc] init];
            [model updateColumnEnable:tableParams];
            for (int i=0; i< model.columeNames.count; i++) {
                NSString *columeName = [model.columeNames objectAtIndex:i];
                NSString *columeType = [model.columeTypes objectAtIndex:i];
                if ([columeType isEqualToString:SQLTEXT]) {
                    [model setValue:[resultSet stringForColumn:columeName] forKey:columeName];
                } else {
                    [model setValue:[NSNumber numberWithLongLong:[resultSet longLongIntForColumn:columeName]] forKey:columeName];
                }
            }
            [users addObject:model];
            FMDBRelease(model);
        }
    }];
    
    return users;
}

#pragma mark - util method
+ (NSString *)getColumeAndTypeString
{
    NSMutableString* pars = [NSMutableString string];
    NSDictionary *dict = [self.class getAllProperties];
    
    NSMutableArray *proNames = [dict objectForKey:@"name"];
    NSMutableArray *proTypes = [dict objectForKey:@"type"];
    
    for (int i=0; i< proNames.count; i++) {
        [pars appendFormat:@"%@ %@",[proNames objectAtIndex:i],[proTypes objectAtIndex:i]];
        if(i+1 != proNames.count)
        {
            [pars appendString:@","];
        }
    }
    return pars;
}

- (NSString *)description
{
    NSString *result = @"";
    NSDictionary *dict = [self.class getAllProperties];
    NSMutableArray *proNames = [dict objectForKey:@"name"];
    for (int i = 0; i < proNames.count; i++) {
        NSString *proName = [proNames objectAtIndex:i];
        id  proValue = [self valueForKey:proName];
        result = [result stringByAppendingFormat:@"%@:%@\n",proName,proValue];
    }
    return result;
}

#pragma mark - must be override method
/** 如果子类中有一些property不需要创建数据库字段，那么这个方法必须在子类中重写
 */
+ (NSArray *)transients
{
    return [NSArray array];
}


+ (int)numOfMessagesWithID:(NSDictionary *)tableParams{
    NSString *tableName = [self.class getTableName:tableParams];
    
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT Count(*) FROM %@",tableName];
    //
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    __block int  count = 0;
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        count = [db intForQuery:selectSQL];
        
    }];
    return count;
}
+ (NSString *)getTableName:(NSDictionary *)tableParams{
    NSString *className = NSStringFromClass(self.class);
    if ([className hasPrefix:@"SportPartner"]) {
        className = [className stringByReplacingOccurrencesOfString:@"SportPartner." withString:@""];
    }
    return [NSString stringWithFormat:@"%@%@",className,tableParams[@"tableId"]];
}

/**
 判断是不是要添加新字段
 
 @param tableParams 表id
 */
- (void)updateColumnEnable:(NSDictionary *)tableParams{
//    WEAKSELF;
//    //获取表中所有的字段
//    __block NSArray *allColumns = [self.class getColumnsWithID:tableParams];
//    [_columeNames enumerateObjectsUsingBlock:^(NSString *obj, NSUInteger idx, BOOL * _Nonnull stop) {
//        if (![allColumns containsObject:obj]) {//不存在,添加该字段
//            [weakSelf.class addColumn:obj type:weakSelf.columeTypes[idx] tableId:tableParams];
//        }
//    }];
}

+ (BOOL)addColumn:(NSString *)column type:(NSString *)type tableId:(NSDictionary *)tableParams{
    WEAKSELF;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    __block BOOL res = NO;
    [XMDB.dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
        NSString *sql = [NSString stringWithFormat:@"ALTER TABLE %@ ADD COLUMN %@ %@",tableName,column,type];
        res = [db executeUpdate:sql];
        AppLog(@"%@",res?@"字段更新成功":@"字段更新失败");
    }];
    return res;
}

+ (Class)getSelfClass{
    NSString *className = NSStringFromClass(self.class);
    if ([className hasPrefix:@"SportPartner"]) {
        className = [className stringByReplacingOccurrencesOfString:@"SportPartner." withString:@""];
    }
    return NSClassFromString(className);
}
- (Class)getSelfClass{
    NSString *className = NSStringFromClass(self.class);
    if ([className hasPrefix:@"SportPartner"]) {
        className = [className stringByReplacingOccurrencesOfString:@"SportPartner." withString:@""];
    }
    return NSClassFromString(className);
}

+ (instancetype)findOneByCriteria:(NSString *)criteria withID:(NSDictionary *)tableParams{
    WEAKSELF;
    XMDataModelHelp *XMDB = [[XMDataModelHelp alloc] init];
    XMDB.tableParams = tableParams;
    NSMutableArray *users = [NSMutableArray array];
    [XMDB.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableName = [weakSelf.class getTableName:tableParams];
//        select max(age) maxAge from workTable
        NSString *sql = [NSString stringWithFormat:@"SELECT %@ FROM  %@",criteria,tableName];
        FMResultSet *resultSet = [db executeQuery:sql];
        while ([resultSet next]) {
            XMDataModel *model = [[weakSelf.class alloc] init];
            [model updateColumnEnable:tableParams];
            for (int i=0; i< model.columeNames.count; i++) {
                NSString *columeName = [model.columeNames objectAtIndex:i];
                NSString *columeType = [model.columeTypes objectAtIndex:i];
                if ([columeType isEqualToString:SQLTEXT]) {
                    [model setValue:[resultSet stringForColumn:columeName] forKey:columeName];
                } else {
                    [model setValue:[NSNumber numberWithLongLong:[resultSet longLongIntForColumn:columeName]] forKey:columeName];
                }
            }
            [users addObject:model];
            FMDBRelease(model);
        }
    }];
    
    return users.firstObject;
}

@end

互相交流，如有问题，欢迎指正。

J.Reno

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
FMDB简易封装，可以进行多表操作，动态创建表

由于项目中用到多用户登录，需要根据不同的用户创建本地的数据库表，我的做法就是根据用户ID判断，创建属于该ID的不同的数据库表，包括个人信息，聊天，语音，图片，下载文件等等。XMDataModelHelp这个用来根据传入的参数来创建表，需要传入的参数为一个字典，{“tableId”:“123”,“tablePath”:path,“tableName”:name} 分别为表id,表的存储路径跟...
复制链接

扫一扫

专栏目录