SQLite封装

作者: sajiner | 来源:发表于2017-03-19 17:51 被阅读33次

1. 创建 XSqliteTool 类，对 SQLite 基本操作进行封装

打开数据库

#pragma mark - 打开数据库
+ (BOOL)openDB: (NSString *)uid {
    NSString *dbName = @"common.sqlite";
    if (uid) {
        dbName = [NSString stringWithFormat:@"%@.sqlite", uid];
    }
    NSString *fileName = [kPathName stringByAppendingPathComponent:dbName];
    return sqlite3_open(fileName.UTF8String, &ppDb) == SQLITE_OK;
}

关闭数据库

#pragma mark - 关闭数据库
+ (void)closeDB {
    sqlite3_close(ppDb);
}

执行语句

#pragma mark - 执行数据库
+ (BOOL)dealSql: (NSString *)sql uid: (NSString *)uid {
    if (![self openDB:uid]) {
        NSLog(@"打开数据库失败");
        return false;
    }
    BOOL result = sqlite3_exec(ppDb, sql.UTF8String, nil, nil, nil) == SQLITE_OK;
    [self closeDB];
    return result;
}

查询语句

#pragma mark -  查询操作
+ (NSMutableArray<NSMutableDictionary *> *)querySql:(NSString *)sql uid:(NSString *)uid {
    if (![self openDB:uid]) {
        NSLog(@"打开数据库失败");
        return false;
    }
    sqlite3_stmt *stmt = nil;
    if (sqlite3_prepare_v2(ppDb, sql.UTF8String, -1, &stmt, nil) != SQLITE_OK) {
        NSLog(@"准备语句失败");
    }
    NSMutableArray *resultArrM = [NSMutableArray array];
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        NSMutableDictionary *dictM = [NSMutableDictionary dictionary];
        int count = sqlite3_column_count(stmt);
        for (int i = 0; i < count; i++) {
            const char *columnNameC = sqlite3_column_name(stmt, i);
            NSString *columnName = [NSString stringWithUTF8String:columnNameC];  
            int type = sqlite3_column_type(stmt, i);
            id value = nil;
            switch (type) {
                case SQLITE_TEXT:
                    value = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, i)];
                    break;
                case SQLITE_INTEGER:
                    value = @(sqlite3_column_int(stmt, i));
                    break;
                case SQLITE_FLOAT:
                    value = @(sqlite3_column_double(stmt, i));
                    break;
                case SQLITE_BLOB:
                    value = CFBridgingRelease(sqlite3_column_blob(stmt, i));
                    break;
                default:
                    break;
            }
            [dictM setValue:value forKey:columnName];
        }
        [resultArrM addObject:dictM];
    }
    sqlite3_finalize(stmt);
    [self closeDB];
    return resultArrM;
}

2. 创建 XSqliteModelTool 类动态创建表

基本创建概要
- 拼接完整的sql语句
- 表名：以类的名字定义
- 字段名称：类的成员变量
- 主键：通过协议，让类遵守协议，实现主键方法
- 需要忽略的字段：通过协议，让类遵守协议，实现忽略字段的方法
XSqliteModelTool 中创建表的方法

+ (BOOL)createTable:(Class)cls uid:(NSString *)uid {
    // create table if not exists tableName(字段1 类型，字段2 类型 。。。)
    NSString *tableName = [XModelTool tableName:cls];
    NSString *columnNameAndType = [XModelTool columnNameAndTypeStr:cls];
    
    if (![cls respondsToSelector:@selector(primaryKey)]) {
        NSLog(@"请先实现+ primaryKey 方法");
        return NO;
    }
    NSString *primaryKey = [cls primaryKey];
    NSString *sql = [NSString stringWithFormat:@"create table if not exists %@(%@, primary key(%@))", tableName, columnNameAndType, primaryKey];
    
    return [XSqliteTool dealSql:sql uid:uid];
}

其中的 XModelTool 类为 XSqliteModelTool 服务，实现了如下方法
// 获取表名
+ (NSString *)tableName: (Class)cls;
// 获取成员变量和成员变量的类型字典
+ (NSDictionary *)classIvarNameAndTypeDict: (Class)cls;
// 获取类的成员变量和成员变量的类型映射成sqlite的类型字典
+ (NSDictionary *)classIvarNameAndSqliteTypeDict: (Class)cls;
// 获取表的字段及类型
+ (NSString *)columnNameAndTypeStr: (Class)cls;
// 所有排好序的表的字段
+ (NSArray *)tableSortedIvarNames: (Class)cls;

3. 动态更新表

检测表格是否需要更新，需要更新的情况如下：
- 修改了字段名称
- 新增了字段
- 删除了字段
动态的迁移数据
1. 创建新的临时表格
2. 以新表为基准，从旧表中，取数据进行填充
2.0 根据主键，插入主键的数据
2.1 按照新表的有效字段（即是旧表中包含的字段），从旧表中更新数据到新表
3. 删除旧表
4. 修改临时表格的名称为新表
对字段改名的处理
- 通过协议获取改名的映射字典
- 迁移数据时进行过滤：没有被旧表的字段包含，且更改后的字段名也没有被旧表包含
主要代码如下

#pragma mark - 判断是否需要更新表格
+ (BOOL)isTableRequiredUpdate: (Class)cls uid: (NSString *)uid {
    NSArray *tableSortedNames = [XTableModel tableSortedNames:cls uid:uid];
    NSArray *modelSortedNames = [XModelTool tableSortedIvarNames:cls];
    
    return ![tableSortedNames isEqualToArray:modelSortedNames];
}

#pragma mark - 是否更新成功
+ (BOOL)isSuccessUpdateTable:(Class)cls uid:(NSString *)uid {
    
    NSArray *sqls = [self udpateSqls:cls uid:uid];
    return [XSqliteTool dealSqls:sqls uid:uid];
}

#pragma mark - 返回所有需要操作的sql语句
+ (NSArray *)udpateSqls: (Class)cls uid: (NSString *)uid {
    if ([self isTableRequiredUpdate:cls uid:uid] == NO) {
        NSLog(@"不需要更新表");
        return nil;
    }
    // 创建正确结构的临时表
    NSMutableArray *sqls = [NSMutableArray array];
    NSString *tableName = [XModelTool tableName:cls];
    // 1.创建临时表
    NSString *tempTableName = [XModelTool tempTableName:cls];
    NSString *columnNameAndType = [XModelTool columnNameAndTypeStr:cls];
    
    if (![cls respondsToSelector:@selector(primaryKey)]) {
        NSLog(@"请先实现+ primaryKey 方法");
        return nil;
    }
    NSString *primaryKey = [cls primaryKey];
    NSString *tempSql = [NSString stringWithFormat:@"create table if not exists %@(%@, primary key(%@))", tempTableName, columnNameAndType, primaryKey];
    [sqls addObject:tempSql];
    
    // 2.插入旧表中的主键数据到临时表
    NSString *insertPKeySql = [NSString stringWithFormat:@"insert into %@(%@) select %@ from %@", tempTableName, primaryKey, primaryKey, tableName];
    [sqls addObject:insertPKeySql];
    
    // 根据主键更新新表内容
    NSDictionary *oldNameToNewNameDict = @{};
    if ([cls respondsToSelector:@selector(oldNameToNewName)]) {
        oldNameToNewNameDict = [cls oldNameToNewName];
    }
    NSArray *oldNames = [XTableModel tableSortedNames:cls uid:uid];
    NSArray *newNames = [XModelTool tableSortedIvarNames:cls];
    for (NSString *newName in newNames) {
        NSString *oldName = newName;
        if ([oldNameToNewNameDict[newName] length] != 0) {
            oldName = oldNameToNewNameDict[newName];
        }
        
        if (![oldNames containsObject:newName] && ![oldNames containsObject:oldName]) {
            continue;
        }
        NSString *updateSql = [NSString stringWithFormat:@"update %@ set %@ = (select %@ from %@ where %@.%@ = %@.%@)", tempTableName, newName, oldName, tableName, tableName, primaryKey, tempTableName, primaryKey];
        [sqls addObject:updateSql];
    }
    // 删除旧表
    NSString *dropSql = [NSString stringWithFormat:@"drop table if exists %@", tableName];
    [sqls addObject:dropSql];
    // 更新表明
    NSString *tableNameSql = [NSString stringWithFormat:@"alter table %@ rename to %@", tempTableName, tableName];
    [sqls addObject:tableNameSql];
    
    return sqls;
}

创建 XTableModelTool 类，用于存放如下方法

// 判断表格是否存在
+ (BOOL)isTableExists: (Class)cls uid: (NSString *)uid;
// 获取排好序的表名
+ (NSArray *)tableSortedNames: (Class)cls uid: (NSString *)uid;

多条语句的处理

- 在 XSqliteTool 类中增加方法
     + (BOOL)dealSqls: (NSArray *)sqls uid: (NSString *)uid;
- 运用事物多条语句的处理进行干预
     1. 在执行语句前，打开事务
     2. 如果有语句执行结果失败，则回滚事务
     3. 所有结果都成功，就提交事务

模型操作-保存／更新模型

操作步骤
1. 检查表格是否存在，没有则创建
2. 检查表格是否需要更新，需要则更新
3. 插入或者更新
- 根据主键判断记录是否存在：
- 存在，则拼接更新语句；不存在，则拼接插入语句
代码

+ (BOOL)saveOrUpdateModel:(id)model uid:(NSString *)uid {
    // 判断表格是否存在，不存在就创建
    Class cls = [model class];
    if (![XTableModel isTableExists:cls uid:uid]) {
        NSLog(@"表不存在");
        [self createTable:cls uid:uid];
    }
    // 判断是否需要更新, 需要，就更新
    if ([self isTableRequiredUpdate:cls uid:uid]) {
        BOOL result = [self isSuccessUpdateTable:cls uid:uid];
        if (!result) {
            NSLog(@"更新表格失败");
            return NO;
        }
    }
    NSString *tableName = [XModelTool tableName:cls];
    // 获取主键
    if (![cls respondsToSelector:@selector(primaryKey)]) {
        NSLog(@"请先实现+ primaryKey 方法");
        return nil;
    }
    NSString *primaryKey = [cls primaryKey];
    id primaryValue = [model valueForKeyPath:primaryKey];
    // 根据主键的值判断是更新还是保存（有值-更新，无值-保存
    NSString *checkSql = [NSString stringWithFormat:@"select * from %@ where %@ = '%@'", tableName, primaryKey, primaryValue];
    NSArray *result = [XSqliteTool querySql:checkSql uid:uid];
    
    NSArray *columnNames = [XModelTool classIvarNameAndTypeDict:cls].allKeys;
    NSMutableArray *setValueArray = [NSMutableArray array];
    NSMutableArray *values = [NSMutableArray array];
    
    for (NSString *columnName in columnNames) {
        id value = [model valueForKeyPath:columnName];
        if ([value isKindOfClass:[NSArray class]] || [value isKindOfClass:[NSDictionary class]]) {
            NSData *data = [NSJSONSerialization dataWithJSONObject:value options:NSJSONWritingPrettyPrinted error:nil];
            value = [[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding];
        }
        
        [values addObject:value];
        
        NSString *str = [NSString stringWithFormat:@"%@='%@'", columnName, value];
        [setValueArray addObject:str];
    }
    
    NSString *execSql;
    // 更新
    if (result.count > 0) {
        execSql = [NSString stringWithFormat:@"update %@ set %@ where %@ = %@", tableName, [setValueArray componentsJoinedByString:@","], primaryKey, primaryValue];
    } else { // 插入
        execSql = [NSString stringWithFormat:@"insert into %@(%@) values('%@')", tableName, [columnNames componentsJoinedByString:@","], [values componentsJoinedByString:@"','"]];
    }
    return [XSqliteTool dealSql:execSql uid:uid];
}

删除模型、查询模型

此处均根据主键进行删除或查询操作。也可以根据不同情况进行多个条件删除或查询，此处不做处理

#pragma mark - 通过操作模型 删除数据
+ (BOOL)deleteModel:(id)model uid:(NSString *)uid {
    Class cls = [model class];
    NSString *tableName = [XModelTool tableName:cls];
    // 获取主键
    if (![cls respondsToSelector:@selector(primaryKey)]) {
        NSLog(@"请先实现+ primaryKey 方法");
        return nil;
    }
    NSString *primaryKey = [cls primaryKey];
    id primaryValue = [model valueForKeyPath:primaryKey];
   
    NSString *execSql = [NSString stringWithFormat:@"delete from %@ where %@ = '%@'", tableName, primaryKey, primaryValue];
    return [XSqliteTool dealSql:execSql uid:uid];
}

#pragma mark -  通过操作模型 查找数据
+ (NSArray *)queryModel:(id)model uid:(NSString *)uid {
    Class cls = [model class];
    NSString *tableName = [XModelTool tableName:cls];
    // 获取主键
    if (![cls respondsToSelector:@selector(primaryKey)]) {
        NSLog(@"请先实现+ primaryKey 方法");
        return nil;
    }
    NSString *primaryKey = [cls primaryKey];
    id primaryValue = [model valueForKeyPath:primaryKey];
    
    NSString *execSql = [NSString stringWithFormat:@"select * from %@ where %@ = '%@'", tableName, primaryKey, primaryValue];
    NSArray *resultArr = [XSqliteTool querySql:execSql uid:uid];
    return [self parseResults:resultArr withClass:cls];
}

+ (NSArray *)parseResults: (NSArray <NSDictionary *>*)results withClass:(Class)cls {
    NSDictionary *nameTypeDict = [XModelTool classIvarNameAndTypeDict:cls];
    
    NSMutableArray *models = [NSMutableArray array];
    for (NSDictionary *dict in results) {
        id model = [[cls alloc] init];
        [models addObject:model];
        [dict enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
            NSString *type = nameTypeDict[key];
            id resultValue = obj;
            if ([type isEqualToString:@"NSArray"] || [type isEqualToString:@"NSDictionary"]) {
                NSData *data = [obj dataUsingEncoding:NSUTF8StringEncoding];
                resultValue = [NSJSONSerialization JSONObjectWithData:data options:kNilOptions error:nil];
            } else if ([type isEqualToString:@"NSMutableArray"] || [type isEqualToString:@"NSMutableDictionary"]) {
                NSData *data = [obj dataUsingEncoding:NSUTF8StringEncoding];
                resultValue = [NSJSONSerialization JSONObjectWithData:data options:NSJSONReadingMutableContainers error:nil];
            }
            [model setValue:resultValue forKeyPath:key];
        }];
    }
    NSLog(@"%@", models);
    return models;
}

SQLite封装

1. 创建 XSqliteTool 类，对 SQLite 基本操作进行封装

2. 创建 XSqliteModelTool 类动态创建表

3. 动态更新表

创建 XTableModelTool 类，用于存放如下方法

多条语句的处理

模型操作-保存／更新模型

删除模型、查询模型

代码请点击 Demo

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

SQLite封装

1. 创建 XSqliteTool 类，对 SQLite 基本操作进行封装

2. 创建 XSqliteModelTool 类 动态创建表

3. 动态更新表

创建 XTableModelTool 类，用于存放如下方法

多条语句的处理

模型操作-保存／更新模型

删除模型、查询模型

代码请点击 Demo

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

2. 创建 XSqliteModelTool 类动态创建表