// // SDJKDBManager.m // SDJK // // Created by 张嵘 on 2021/8/26. // #import "SDJKDBManager.h" @interface SDJKDBManager () @property (nonatomic, readwrite, strong) FMDatabase *database; @property (nonatomic, readwrite, strong) FMDatabaseQueue *databaseQueue; @end @implementation SDJKDBManager static id rq_sdjkDBManager = nil; #pragma mark - init + (instancetype)sharedInstance { static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ rq_sdjkDBManager = [[self alloc] init]; }); return rq_sdjkDBManager; } - (instancetype)init { if (self = [super init]) { NSError *error; NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:[NSString stringWithFormat:@"sdjk.db"]]; NSString *resourcePath = [[NSBundle mainBundle] pathForResource:@"sdjk" ofType:@"db"]; if ([RQFileManager isPathExist:dbPath]) { self.database = [FMDatabase databaseWithPath:dbPath]; self.databaseQueue = [FMDatabaseQueue databaseQueueWithPath:dbPath]; NSLog(@"数据库path-----%@",dbPath); [_database open]; } else { [[RQFileManager fileManager] copyItemAtPath:resourcePath toPath:[[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:[NSString stringWithFormat:@"sdjk.db"]] error:&error]; self.database = [FMDatabase databaseWithPath:dbPath]; self.databaseQueue = [FMDatabaseQueue databaseQueueWithPath:dbPath]; NSLog(@"数据库path-----%@",dbPath); [_database open]; } } return self; } #pragma mark - Public Method #pragma mark - WrongList - (void)addWrongRecordWithRQWrongModel:(RQWrongModel *)wrongModel { if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { BOOL isExist; FMResultSet *isExistResultSet = [db executeQuery:[NSString stringWithFormat:@"select COUNT(questionId) AS countNum from wrongList where questionId = %ld", (long)wrongModel.questionId]]; while ([isExistResultSet next]) { NSInteger count = [isExistResultSet intForColumn:@"countNum"]; isExist = count > 0; NSString * timeStr = wrongModel.createTime; NSDateFormatter *dateFormatter = [NSDateFormatter rq_defaultDateFormatter]; //需要设置为和字符串相同的格式 NSDate *localDate = [dateFormatter dateFromString:timeStr]; NSString *localDateStr = [NSString stringWithFormat:@"%.f",[localDate timeIntervalSince1970]]; if (isExist) { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:wrongModel.questionId].issue]); if (!RQObjectIsNil(@(wrongModel.id)) && !RQObjectIsNil(@(wrongModel.userId)) && !RQObjectIsNil(@(wrongModel.questionId)) && RQStringIsNotEmpty(wrongModel.createTime)) { BOOL updateResultSet = [db executeUpdate:[NSString stringWithFormat:@"update wrongList set id = %ld, userId = %ld, km = '%@', carType = '%@', createTime = %ld where questionId = %ld", (long)wrongModel.id, (long)wrongModel.userId, wrongModel.km, wrongModel.carType, (long)[localDateStr integerValue], (long)wrongModel.questionId]]; if (updateResultSet) { NSLog(@"更新数据成功"); } else { NSLog(@"更新数据失败"); } } } else { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表不存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:wrongModel.questionId].issue]); BOOL insertResultSet = [db executeUpdate:[NSString stringWithFormat:@"insert into wrongList (id, questionId, userId, km, carType, createTime) values(%ld, %ld, %ld, '%@', '%@', %ld) ", (long)wrongModel.id, (long)wrongModel.questionId, (long)wrongModel.userId, wrongModel.km, wrongModel.carType, (long)[localDateStr integerValue]]]; if (insertResultSet) { NSLog(@"插入数据成功"); } else { NSLog(@"插入数据失败"); } } } }]; } } - (void)addWrongRecordWithRQWrongModelArr:(NSArray *)wrongModelArr isClearOldData:(BOOL)isClearOldData complete:(VoidBlock)complete { if (_databaseQueue) { [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) { @try { if (isClearOldData) { BOOL resultSet = [db executeUpdate:@"DELETE FROM wrongList"]; if (resultSet) { NSLog(@"删除成功"); } else { NSLog(@"删除失败"); } } [[wrongModelArr.rac_sequence.signal deliverOnMainThread] subscribeNext:^(RQWrongModel *wrongModel) { BOOL isExist; FMResultSet *isExistResultSet = [db executeQuery:[NSString stringWithFormat:@"select COUNT(questionId) AS countNum from wrongList where questionId = %ld", (long)wrongModel.questionId]]; while ([isExistResultSet next]) { NSInteger count = [isExistResultSet intForColumn:@"countNum"]; isExist = count > 0; NSString * timeStr = wrongModel.createTime; NSDateFormatter *dateFormatter = [NSDateFormatter rq_defaultDateFormatter]; //需要设置为和字符串相同的格式 NSDate *localDate = [dateFormatter dateFromString:timeStr]; NSString *localDateStr = [NSString stringWithFormat:@"%.f",[localDate timeIntervalSince1970]]; if (isExist) { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:wrongModel.questionId].issue]); if (!RQObjectIsNil(@(wrongModel.id)) && !RQObjectIsNil(@(wrongModel.userId)) && !RQObjectIsNil(@(wrongModel.questionId)) && RQStringIsNotEmpty(wrongModel.createTime)) { BOOL updateResultSet = [db executeUpdate:[NSString stringWithFormat:@"update wrongList set id = %ld, userId = %ld, km = '%@', carType = '%@', createTime = %ld where questionId = %ld", (long)wrongModel.id, (long)wrongModel.userId, wrongModel.km, wrongModel.carType, (long)[localDateStr integerValue], (long)wrongModel.questionId]]; if (updateResultSet) { NSLog(@"更新数据成功"); } else { NSLog(@"更新数据失败"); } } } else { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表不存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:wrongModel.questionId].issue]); BOOL insertResultSet = [db executeUpdate:[NSString stringWithFormat:@"insert into wrongList (id, questionId, userId, km, carType, createTime) values(%ld, %ld, %ld, '%@', '%@', %ld) ", (long)wrongModel.id, (long)wrongModel.questionId, (long)wrongModel.userId, wrongModel.km, wrongModel.carType, (long)[localDateStr integerValue]]]; if (insertResultSet) { NSLog(@"插入数据成功"); } else { NSLog(@"插入数据失败"); } } } } completed:^{ if (complete) { complete(); } }]; } @catch (NSException *exception) { *rollback = YES; } @finally { *rollback = NO; } }]; } } - (void)updateWrongRecordWithRQWrongModel:(RQWrongModel *)wrongModel { NSString * timeStr = wrongModel.createTime; NSDateFormatter *dateFormatter = [NSDateFormatter rq_defaultDateFormatter]; //需要设置为和字符串相同的格式 NSDate *localDate = [dateFormatter dateFromString:timeStr]; NSString *localDateStr = [NSString stringWithFormat:@"%.f",[localDate timeIntervalSince1970]]; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { BOOL resultSet; resultSet = [db executeUpdate:[NSString stringWithFormat:@"update wrongList set id = %ld, userId = %ld, km = '%@', carType = '%@', createTime = %ld where questionId = %ld", (long)wrongModel.id, (long)wrongModel.userId, wrongModel.km, wrongModel.carType, (long)[localDateStr integerValue], (long)wrongModel.questionId]]; if (resultSet) { NSLog(@"更新数据成功"); } else { NSLog(@"更新数据失败"); } }]; } } - (void)deleteWrongRecordWithQuestionId:(NSInteger)questionId { if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { BOOL resultSet = [db executeUpdate:[NSString stringWithFormat:@"delete from wrongList where questionId = %ld", (long)questionId]]; if (resultSet) { NSLog(@"删除数据成功"); } else { NSLog(@"删除数据失败"); } }]; } } - (void)deleteWrongRecordWithIdArr:(NSArray *)idArr { if (_databaseQueue) { [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) { @try { [idArr.rac_sequence.signal subscribeNext:^(NSString *idStr) { BOOL res = [_database executeUpdate:[NSString stringWithFormat:@"delete from wrongList where id = %@", idStr]]; if(!res){ NSLog(@"删除数据失败"); } else { NSLog(@"删除数据成功"); } }]; } @catch (NSException *exception) { *rollback = YES; } @finally { *rollback = NO; } }]; } } - (BOOL)isExistWithRQWrongModel:(RQWrongModel *)wrongModel { __block BOOL isExist = false; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select * from wrongList where questionId = %ld", (long)wrongModel.questionId]]; while ([resultSet next]) { NSInteger questionId = [resultSet intForColumn:@"questionId"]; isExist = questionId == wrongModel.questionId; if (isExist) { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:wrongModel.questionId].issue]); } else { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表不存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:wrongModel.questionId].issue]); } } }]; } return isExist; } - (BOOL)isSelectWithRQWrongModel:(RQWrongModel *)wrongModel { __block BOOL isSelect = false; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select isSelected from wrongList where questionId = %ld", (long)wrongModel.questionId]]; while ([resultSet next]) { isSelect = [resultSet boolForColumn:@"isSelected"]; if (isSelect) { NSLog(@"选中"); } else { NSLog(@"不选中"); } } }]; } return isSelect; } - (NSArray *)setSelectWithRQWrongModel:(RQWrongModel *)wrongModel carType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType isSelect:(BOOL)isSelect limitNum:(NSInteger)limit { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { NSNumber *isSelectValue = [NSNumber numberWithBool:isSelect]; BOOL isSelectResultSet = [db executeUpdate:[NSString stringWithFormat:@"update wrongList set isSelected = %@ where questionId = %ld", isSelectValue, (long)wrongModel.questionId]]; if (isSelectResultSet) { NSLog(isSelect? @"设置选中成功" : @"设置未选中成功"); } else { NSLog(isSelect? @"设置选中失败" : @"设置未选中失败"); } FMResultSet *resultSet; resultSet = [_database executeQuery:[NSString stringWithFormat:@"select id from wrongList where isSelected = 1 and (select count(createTime) from wrongList) >= %ld and createTime in (select createTime from wrongList order by createTime ASC limit %ld) and km = '%@' and carType = '%@'",(long)limit,limit,[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType], [RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"id"]; [arr addObject:questionIdStr]; } }]; } return arr.copy; } - (NSArray *)setSelectWithRQWrongModelArr:(NSArray *)wrongModelArr carType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType isSelect:(BOOL)isSelect limitNum:(NSInteger)limit complete:(VoidBlock)complete { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) { @try { [wrongModelArr.rac_sequence.signal subscribeNext:^(RQWrongModel *wrongModel) { NSNumber *isSelectValue = [NSNumber numberWithBool:isSelect]; BOOL isSelectResultSet = [db executeUpdate:[NSString stringWithFormat:@"update wrongList set isSelected = %@ where questionId = %ld", isSelectValue, (long)wrongModel.questionId]]; if (isSelectResultSet) { NSLog(isSelect? @"设置选中成功" : @"设置未选中成功"); } else { NSLog(isSelect? @"设置选中失败" : @"设置未选中失败"); } FMResultSet *resultSet; resultSet = [_database executeQuery:[NSString stringWithFormat:@"select id from wrongList where isSelected = 1 and (select count(createTime) from wrongList) >= %ld and createTime in (select createTime from wrongList order by createTime ASC limit %ld) and km = '%@' and carType = '%@'",(long)limit,limit,[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType], [RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"id"]; [arr addObject:questionIdStr]; } } completed:^{ complete(); }]; } @catch (NSException *exception) { *rollback = YES; } @finally { *rollback = NO; } }]; } return arr.copy; } - (NSArray *)querySelectedWrongModelsWithLimitNum:(NSInteger)limit carType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select id from wrongList where isSelected = 1 and (select count(createTime) from wrongList) >= %ld and createTime in (select createTime from wrongList order by createTime ASC limit %ld) and km = '%@' and carType = '%@'",(long)limit,limit,[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType], [RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"id"]; [arr addObject:questionIdStr]; } }]; } return arr.copy; } - (NSArray *)querySelectedWrongModelsQuestionIdArrWithLimitNum:(NSInteger)limit carType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select questionId from wrongList where isSelected = 1 and (select count(createTime) from wrongList) >= %ld and createTime in (select createTime from wrongList order by createTime ASC limit %ld) and km = '%@' and carType = '%@'",(long)limit,limit,[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType], [RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"questionId"]; [arr addObject:questionIdStr]; } }]; } return arr.copy; } - (void)deleteAllWrongModelsWithComplete:(VoidBlock_int)complete { if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { BOOL resultSet = [db executeUpdate:@"DELETE FROM wrongList"]; if (resultSet) { NSLog(@"删除成功"); } else { NSLog(@"删除失败"); } complete(resultSet); }]; } } - (NSInteger)getAllWrongModelsCount { __block NSInteger count = 0; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:@"SELECT COUNT(*) FROM wrongList"]; while ([resultSet next]) { count = [resultSet intForColumn:@"count"]; } }]; } return count; } - (void)queryWrongModelQuestionIdArrWithCarType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType complete:(VoidBlock_id)complete { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select questionId from wrongList where km = '%@' and carType = '%@'",[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType] ,[RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"questionId"]; [arr addObject:questionIdStr]; } if (complete) { complete(arr.copy); } }]; } } #pragma mark - CollectionList - (void)addCollectionRecordWithRQCollectionModel:(RQCollectionModel *)collectionModel { if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { BOOL isExist; FMResultSet *isExistResultSet = [db executeQuery:[NSString stringWithFormat:@"select COUNT(questionId) AS countNum from collectionList where questionId = %ld", (long)collectionModel.questionId]]; while ([isExistResultSet next]) { NSInteger count = [isExistResultSet intForColumn:@"countNum"]; isExist = count > 0; NSString * timeStr = collectionModel.createTime; NSDateFormatter *dateFormatter = [NSDateFormatter rq_defaultDateFormatter]; //需要设置为和字符串相同的格式 NSDate *localDate = [dateFormatter dateFromString:timeStr]; NSString *localDateStr = [NSString stringWithFormat:@"%.f",[localDate timeIntervalSince1970]]; if (isExist) { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:collectionModel.questionId].issue]); if (!RQObjectIsNil(@(collectionModel.id)) && !RQObjectIsNil(@(collectionModel.userId)) && !RQObjectIsNil(@(collectionModel.questionId)) && RQStringIsNotEmpty(collectionModel.createTime)) { BOOL updateResultSet = [db executeUpdate:[NSString stringWithFormat:@"update collectionList set id = %ld, userId = %ld, km = '%@', carType = '%@', createTime = %ld where questionId = %ld", (long)collectionModel.id, (long)collectionModel.userId, collectionModel.km, collectionModel.carType, (long)[localDateStr integerValue], (long)collectionModel.questionId]]; if (updateResultSet) { NSLog(@"更新数据成功"); } else { NSLog(@"更新数据失败"); } } } else { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表不存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:collectionModel.questionId].issue]); BOOL insertResultSet = [db executeUpdate:[NSString stringWithFormat:@"insert into collectionList (id, questionId, userId, km, carType, createTime) values(%ld, %ld, %ld, '%@', '%@', %ld) ", (long)collectionModel.id, (long)collectionModel.questionId, (long)collectionModel.userId, collectionModel.km, collectionModel.carType, (long)[localDateStr integerValue]]]; if (insertResultSet) { NSLog(@"插入数据成功"); } else { NSLog(@"插入数据失败"); } } } }]; } } - (void)addCollectionRecordWithQuestionIdArr:(NSArray *)questionIdArr { if (_databaseQueue) { [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) { @try { [questionIdArr.rac_sequence.signal subscribeNext:^(NSString *questionIdStr) { BOOL isExist; FMResultSet *isExistResultSet = [db executeQuery:[NSString stringWithFormat:@"select COUNT(questionId) AS countNum from collectionList where questionId = %ld", (long)questionIdStr.integerValue]]; while ([isExistResultSet next]) { NSInteger count = [isExistResultSet intForColumn:@"countNum"]; isExist = count > 0; if (isExist) { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:questionIdStr.integerValue].issue]); } else { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表不存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:questionIdStr.integerValue].issue]); BOOL insertResultSet = [db executeUpdate:[NSString stringWithFormat:@"insert into collectionList (id, questionId, userId, km, carType, createTime) values(%d, %ld, %d, '%@', '%@', %d) ", 0, (long)questionIdStr.integerValue, 0, @"", @"", 0]]; if (insertResultSet) { NSLog(@"插入数据成功"); } else { NSLog(@"插入数据失败"); } } } }]; } @catch (NSException *exception) { *rollback = YES; } @finally { *rollback = NO; } }]; } } - (void)addCollectionRecordWithRQCollectionModelArr:(NSArray *)collectionModelArr isClearOldData:(BOOL)isClearOldData complete:(VoidBlock)complete { if (_databaseQueue) { [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) { @try { if (isClearOldData) { BOOL resultSet = [db executeUpdate:@"DELETE FROM collectionList"]; if (resultSet) { NSLog(@"删除成功"); } else { NSLog(@"删除失败"); } } [[collectionModelArr.rac_sequence.signal deliverOnMainThread] subscribeNext:^(RQCollectionModel *collectionModel) { BOOL isExist; FMResultSet *isExistResultSet = [db executeQuery:[NSString stringWithFormat:@"select COUNT(questionId) AS countNum from collectionList where questionId = %ld", (long)collectionModel.questionId]]; while ([isExistResultSet next]) { NSInteger count = [isExistResultSet intForColumn:@"countNum"]; isExist = count > 0; NSString * timeStr = collectionModel.createTime; NSDateFormatter *dateFormatter = [NSDateFormatter rq_defaultDateFormatter]; //需要设置为和字符串相同的格式 NSDate *localDate = [dateFormatter dateFromString:timeStr]; NSString *localDateStr = [NSString stringWithFormat:@"%.f",[localDate timeIntervalSince1970]]; if (isExist) { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:collectionModel.questionId].issue]); if (!RQObjectIsNil(@(collectionModel.id)) && !RQObjectIsNil(@(collectionModel.userId)) && !RQObjectIsNil(@(collectionModel.questionId)) && RQStringIsNotEmpty(collectionModel.createTime)) { BOOL updateResultSet = [db executeUpdate:[NSString stringWithFormat:@"update collectionList set id = %ld, userId = %ld, km = '%@', carType = '%@', createTime = %ld where questionId = %ld", (long)collectionModel.id, (long)collectionModel.userId, collectionModel.km, collectionModel.carType, (long)[localDateStr integerValue], (long)collectionModel.questionId]]; if (updateResultSet) { NSLog(@"更新数据成功"); } else { NSLog(@"更新数据失败"); } } } else { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表不存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:collectionModel.questionId].issue]); BOOL insertResultSet = [db executeUpdate:[NSString stringWithFormat:@"insert into collectionList (id, questionId, userId, km, carType, createTime) values(%ld, %ld, %ld, '%@', '%@', %ld) ", (long)collectionModel.id, (long)collectionModel.questionId, (long)collectionModel.userId, collectionModel.km, collectionModel.carType, (long)[localDateStr integerValue]]]; if (insertResultSet) { NSLog(@"插入数据成功"); } else { NSLog(@"插入数据失败"); } } } } completed:^{ if (complete) { complete(); } }]; } @catch (NSException *exception) { *rollback = YES; } @finally { *rollback = NO; } }]; } } - (void)updateCollectionRecordWithRQCollectionModel:(RQCollectionModel *)collectionModel { NSString * timeStr = collectionModel.createTime; NSDateFormatter *dateFormatter = [NSDateFormatter rq_defaultDateFormatter]; //需要设置为和字符串相同的格式 NSDate *localDate = [dateFormatter dateFromString:timeStr]; NSString *localDateStr = [NSString stringWithFormat:@"%.f",[localDate timeIntervalSince1970]]; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { BOOL resultSet; resultSet = [db executeUpdate:[NSString stringWithFormat:@"update collectionList set id = %ld, userId = %ld, km = '%@', carType = '%@', createTime = %ld where questionId = %ld", (long)collectionModel.id, (long)collectionModel.userId, collectionModel.km, collectionModel.carType, (long)[localDateStr integerValue], (long)collectionModel.questionId]]; if (resultSet) { NSLog(@"更新数据成功"); } else { NSLog(@"更新数据失败"); } }]; } } - (void)deleteCollectionRecordWithQuestionId:(NSInteger)questionId { if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { BOOL resultSet = [db executeUpdate:[NSString stringWithFormat:@"delete from collectionList where questionId = %ld", (long)questionId]]; if (resultSet) { NSLog(@"删除数据成功"); } else { NSLog(@"删除数据失败"); } }]; } } - (void)deleteCollectionRecordWithIdArr:(NSArray *)idArr { if (_databaseQueue) { [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) { @try { [idArr.rac_sequence.signal subscribeNext:^(NSString *idStr) { BOOL res = [_database executeUpdate:[NSString stringWithFormat:@"delete from collectionList where id = %@", idStr]]; if(!res){ NSLog(@"删除数据失败"); } else { NSLog(@"删除数据成功"); } }]; } @catch (NSException *exception) { *rollback = YES; } @finally { *rollback = NO; } }]; } } - (BOOL)isExistWithRQCollectionModel:(RQCollectionModel *)collectionModel { __block BOOL isExist = false; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select * from collectionList where questionId = %ld", (long)collectionModel.questionId]]; while ([resultSet next]) { NSInteger questionId = [resultSet intForColumn:@"questionId"]; isExist = questionId == collectionModel.questionId; if (isExist) { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:collectionModel.questionId].issue]); } else { NSLog(@"%@",[NSString stringWithFormat:@"%@--------数据库表不存在该条数据",[RQ_QUESTION_DB_MANAGER getQuestionWithQuestionId:collectionModel.questionId].issue]); } } }]; } return isExist; } - (BOOL)isSelectWithRQCollectionModel:(RQCollectionModel *)collectionModel { __block BOOL isSelect = false; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select isSelected from collectionList where questionId = %ld", (long)collectionModel.questionId]]; while ([resultSet next]) { isSelect = [resultSet boolForColumn:@"isSelected"]; if (isSelect) { NSLog(@"选中"); } else { NSLog(@"不选中"); } } }]; } return isSelect; } - (NSArray *)setSelectWithRQCollectionModel:(RQCollectionModel *)collectionModel carType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType isSelect:(BOOL)isSelect limitNum:(NSInteger)limit { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { NSNumber *isSelectValue = [NSNumber numberWithBool:isSelect]; BOOL isSelectResultSet = [db executeUpdate:[NSString stringWithFormat:@"update collectionList set isSelected = %@ where questionId = %ld", isSelectValue, (long)collectionModel.questionId]]; if (isSelectResultSet) { NSLog(isSelect? @"设置选中成功" : @"设置未选中成功"); } else { NSLog(isSelect? @"设置选中失败" : @"设置未选中失败"); } FMResultSet *resultSet; resultSet = [_database executeQuery:[NSString stringWithFormat:@"select id from collectionList where isSelected = 1 and (select count(createTime) from collectionList) >= %ld and createTime in (select createTime from collectionList order by createTime ASC limit %ld) and km = '%@' and carType = '%@'",(long)limit,limit,[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType], [RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"id"]; [arr addObject:questionIdStr]; } }]; } return arr.copy; } - (NSArray *)setSelectWithRQCollectionModelArr:(NSArray *)collectionModelArr carType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType isSelect:(BOOL)isSelect limitNum:(NSInteger)limit complete:(VoidBlock)complete { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) { @try { [collectionModelArr.rac_sequence.signal subscribeNext:^(RQCollectionModel *collectionModel) { NSNumber *isSelectValue = [NSNumber numberWithBool:isSelect]; BOOL isSelectResultSet = [db executeUpdate:[NSString stringWithFormat:@"update collectionList set isSelected = %@ where questionId = %ld", isSelectValue, (long)collectionModel.questionId]]; if (isSelectResultSet) { NSLog(isSelect? @"设置选中成功" : @"设置未选中成功"); } else { NSLog(isSelect? @"设置选中失败" : @"设置未选中失败"); } FMResultSet *resultSet; resultSet = [_database executeQuery:[NSString stringWithFormat:@"select id from collectionList where isSelected = 1 and (select count(createTime) from collectionList) >= %ld and createTime in (select createTime from collectionList order by createTime ASC limit %ld) and km = '%@' and carType = '%@'",(long)limit,limit,[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType], [RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"id"]; [arr addObject:questionIdStr]; } } completed:^{ complete(); }]; } @catch (NSException *exception) { *rollback = YES; } @finally { *rollback = NO; } }]; } return arr.copy; } - (NSArray *)querySelectedCollectionModelsWithLimitNum:(NSInteger)limit carType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select id from collectionList where isSelected = 1 and (select count(createTime) from collectionList) >= %ld and createTime in (select createTime from collectionList order by createTime ASC limit %ld) and km = '%@' and carType = '%@'",(long)limit,limit,[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType], [RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"id"]; [arr addObject:questionIdStr]; } }]; } return arr.copy; } - (void)deleteAllCollectionModelsWithComplete:(VoidBlock_int)complete { if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { BOOL resultSet = [db executeUpdate:@"DELETE FROM collectionList"]; if (resultSet) { NSLog(@"删除成功"); } else { NSLog(@"删除失败"); } complete(resultSet); }]; } } - (NSInteger)getAllCollectionModelsCount { __block NSInteger count = 0; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:@"SELECT COUNT(*) FROM collectionList"]; while ([resultSet next]) { count = [resultSet intForColumn:@"count"]; } }]; } return count; } - (void)queryCollectionModelQuestionIdArrWithCarType:(RQHomePageCarType)carType subjectType:(RQHomePageSubjectType)subjectType complete:(VoidBlock_id)complete { NSMutableArray *arr = @[].mutableCopy; if (_databaseQueue) { [_databaseQueue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *resultSet; resultSet = [db executeQuery:[NSString stringWithFormat:@"select questionId from collectionList where km = '%@' and carType = '%@'",[RQ_COMMON_MANAGER getSubjectTypeStrWithSubjectType:subjectType] ,[RQ_COMMON_MANAGER getCarTypeStrWithCarType:carType]]]; while ([resultSet next]) { NSString *questionIdStr = [resultSet stringForColumn:@"questionId"]; [arr addObject:questionIdStr]; } if (complete) { complete(arr.copy); } }]; } } #pragma mark - Private Method @end