1. 創(chuàng)建 XSqliteTool 類鸠按，對 SQLite 基本操作進行封裝
- 打開數(shù)據(jù)庫
#pragma mark - 打開數(shù)據(jù)庫
+ (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;
}
- 關(guān)閉數(shù)據(jù)庫
#pragma mark - 關(guān)閉數(shù)據(jù)庫
+ (void)closeDB {
sqlite3_close(ppDb);
}
- 執(zhí)行語句
#pragma mark - 執(zhí)行數(shù)據(jù)庫
+ (BOOL)dealSql: (NSString *)sql uid: (NSString *)uid {
if (![self openDB:uid]) {
NSLog(@"打開數(shù)據(jù)庫失敗");
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(@"打開數(shù)據(jù)庫失敗");
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. 創(chuàng)建 XSqliteModelTool 類 動態(tài)創(chuàng)建表
基本創(chuàng)建概要
- 拼接完整的sql語句
- 表名：以類的名字定義
- 字段名稱：類的成員變量
- 主鍵：通過協(xié)議介汹，讓類遵守協(xié)議仰担，實現(xiàn)主鍵方法
- 需要忽略的字段：通過協(xié)議咱娶，讓類遵守協(xié)議躯肌，實現(xiàn)忽略字段的方法XSqliteModelTool 中創(chuàng)建表的方法
+ (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(@"請先實現(xiàn)+ 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 服務(wù)谍失，實現(xiàn)了如下方法
// 獲取表名
+ (NSString *)tableName: (Class)cls;
// 獲取成員變量和成員變量的類型 字典
+ (NSDictionary *)classIvarNameAndTypeDict: (Class)cls;
// 獲取類的成員變量和成員變量的類型映射成sqlite的類型 字典
+ (NSDictionary *)classIvarNameAndSqliteTypeDict: (Class)cls;
// 獲取表的字段及類型
+ (NSString *)columnNameAndTypeStr: (Class)cls;
// 所有排好序的表的字段
+ (NSArray *)tableSortedIvarNames: (Class)cls;
3. 動態(tài)更新表
檢測表格是否需要更新，需要更新的情況如下：
- 修改了字段名稱
- 新增了字段
- 刪除了字段動態(tài)的遷移數(shù)據(jù)
1. 創(chuàng)建新的臨時表格
2. 以新表為基準莹汤，從舊表中快鱼，取數(shù)據(jù)進行填充
2.0 根據(jù)主鍵，插入主鍵的數(shù)據(jù)
2.1 按照新表的有效字段（即是 舊表中包含的字段）纲岭，從舊表中更新數(shù)據(jù)到新表
3. 刪除舊表
4. 修改臨時表格的名稱為新表對字段改名的處理
- 通過協(xié)議獲取改名的映射字典
- 遷移數(shù)據(jù)時進行過濾：沒有被舊表的字段包含抹竹，且更改后的字段名也沒有被舊表包含主要代碼如下
#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;
}
// 創(chuàng)建正確結(jié)構(gòu)的臨時表
NSMutableArray *sqls = [NSMutableArray array];
NSString *tableName = [XModelTool tableName:cls];
// 1.創(chuàng)建臨時表
NSString *tempTableName = [XModelTool tempTableName:cls];
NSString *columnNameAndType = [XModelTool columnNameAndTypeStr:cls];
if (![cls respondsToSelector:@selector(primaryKey)]) {
NSLog(@"請先實現(xiàn)+ 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.插入舊表中的主鍵數(shù)據(jù)到臨時表
NSString *insertPKeySql = [NSString stringWithFormat:@"insert into %@(%@) select %@ from %@", tempTableName, primaryKey, primaryKey, tableName];
[sqls addObject:insertPKeySql];
// 根據(jù)主鍵更新新表內(nèi)容
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;
}
創(chuàng)建 XTableModelTool 類，用于存放如下方法
// 判斷表格是否存在
+ (BOOL)isTableExists: (Class)cls uid: (NSString *)uid;
// 獲取排好序的表名
+ (NSArray *)tableSortedNames: (Class)cls uid: (NSString *)uid;
多條語句的處理
- 在 XSqliteTool 類中增加方法
+ (BOOL)dealSqls: (NSArray *)sqls uid: (NSString *)uid;
- 運用事物多條語句的處理進行干預
1. 在執(zhí)行語句前止潮，打開事務(wù)
2. 如果有語句執(zhí)行結(jié)果失敗窃判，則回滾事務(wù)
3. 所有結(jié)果都成功，就提交事務(wù)
模型操作-保存／更新模型
操作步驟
1. 檢查表格是否存在喇闸，沒有則創(chuàng)建
2. 檢查表格是否需要更新袄琳，需要則更新
3. 插入或者更新
- 根據(jù)主鍵判斷記錄是否存在：
- 存在询件，則拼接更新語句；不存在唆樊，則拼接插入語句代碼
+ (BOOL)saveOrUpdateModel:(id)model uid:(NSString *)uid {
// 判斷表格是否存在宛琅，不存在就創(chuàng)建
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(@"請先實現(xiàn)+ primaryKey 方法");
return nil;
}
NSString *primaryKey = [cls primaryKey];
id primaryValue = [model valueForKeyPath:primaryKey];
// 根據(jù)主鍵的值判斷是更新還是保存（有值-更新逗旁，無值-保存
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];
}
刪除模型夯秃、查詢模型
- 此處均根據(jù) 主鍵 進行刪除或查詢操作。也可以根據(jù)不同情況進行多個條件刪除或查詢痢艺，此處不做處理
#pragma mark - 通過操作模型 刪除數(shù)據(jù)
+ (BOOL)deleteModel:(id)model uid:(NSString *)uid {
Class cls = [model class];
NSString *tableName = [XModelTool tableName:cls];
// 獲取主鍵
if (![cls respondsToSelector:@selector(primaryKey)]) {
NSLog(@"請先實現(xiàn)+ 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 - 通過操作模型 查找數(shù)據(jù)
+ (NSArray *)queryModel:(id)model uid:(NSString *)uid {
Class cls = [model class];
NSString *tableName = [XModelTool tableName:cls];
// 獲取主鍵
if (![cls respondsToSelector:@selector(primaryKey)]) {
NSLog(@"請先實現(xiàn)+ 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;
}
