一，创建一个单例管理FMDB
单例头文件包含
#import <Foundation/Foundation.h>
#import "FMDB.h" // FMDB头文件
#import "PAStudentModel.h" // 需要做存储属性Model
@interface PAFMDBManager : NSObject
/**
数据库线程 增删改查都在这个线层做处理 保证线层安全和数据操作有效性 如在外部线层做数据存储太过频繁出现闪退 可能是抢占资源造成的
*/
@property(nonatomic,strong) FMDatabaseQueue *dbQueue;
@end
******************************一道华丽的分割线来到PAFMDBManager.m***************************
#import "PAFMDBManager.h"
// 数据库-----表名 根据业务需要，创建一张或者多张表 这里避免对一张表操作多故分成两张表
// 表名1 用来保存要识别成功考生
#define kStudentTB2 @"StudentTable2"
// 表名1 用做上传识别考生
#define kUploadStudentTB @"UploadStudentTB"
// 数据库-----字段
#define P_VECTOR @"P_F1" //特征向量
#define kName @"name" // 学生姓名
#define kCardID @"CardID"
#define kcandidate_num @"candidate_num"
#define kseat_num @"seat_num"
#define kRoomID @"RoomID"
#define klocation_ID @"location_ID"
#define kExamination_no @"Examination_no"
#define kStudentImage @"StudentImage" // 表2学生现场照
#define kCardImage @"CardImage" //表1证件照
#define kCardImageNamePath @"CardImageNamePath" // 证件照路径
#define kImageTime @"ImageTime"
#define kStudentKey @"StudentKey"
#define kStudentState @"StudentState"
#define kexam_time @"exam_time"
#define kexam_date @"exam_date"
#define ksimilarity @"similarity" // 学生相似度
@implementation PAFMDBManager
/**
* 单例的初始化
*/
+ (instancetype)shareInstance{
static PAFMDBManager *manager = nil;
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
manager = [[PAFMDBManager alloc] init];
});
return manager;
}
/**
* 在初始化 init中 创建数据表
*/
- (instancetype)init{
if (self = [super init]) {
// 1. 获取沙盒路径
NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
// 拼接数据的名称
path = [path stringByAppendingPathComponent:@"StudentSqlDB.db"];
self.dbQueue = [FMDatabaseQueue databaseQueueWithPath:path];
// 会通过block传递队列中创建好的数据库 里面字段不必理会根据业务需求字段比较多 需要注意的是 这里面不同字段类型怎样存储 --------> 如kStudentState 用 INTEGER 这个类型存储,kCardImage ------> blob 这个image 用data类型来存储
[self.dbQueue inDatabase:^(FMDatabase *db) {
// 4. 创建表 计数表
NSString *sql2 = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@ TEXT PRIMARY KEY NOT NULL,%@ TEXT , %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT,%@ TEXT,%@ TEXT, %@ TEXT,%@ blob, %@ INTEGER,%@ blob, %@ TEXT, %@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT)",kStudentTB2,kStudentKey, kCardID,P_VECTOR, kName,kcandidate_num,kseat_num,kRoomID,klocation_ID,kImageTime,kStudentImage,kexam_time,kexam_date,kExamination_no,kStudentState,kCardImage,kBatch,room_id,ksimilarity,kCardImageNamePath,kwgclqt];
// 执行语句
if (![db executeUpdate:sql2]) {
NSLog(@"建表2失败");
}else{
NSLog(@"建表2成功! 存储现场采集学生照片");
}
// 上传表
NSString *sql3 = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@ TEXT PRIMARY KEY NOT NULL,%@ TEXT ,%@ TEXT , %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT,%@ TEXT,%@ TEXT, %@ TEXT,%@ blob, %@ INTEGER,%@ blob,%@ INTEGER,%@ INTEGER,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT)",kUploadStudentTB,kTimesKey,kStudentKey, kCardID,P_VECTOR, kName,kcandidate_num,kseat_num,kRoomID,klocation_ID,kImageTime,kStudentImage,kexam_time,kexam_date,kExamination_no,kStudentState,kCardImage,kuploadState,kStudentIndex,kdisciplineType,kdisciplineCode,kdisciplineStr,room_id,kwgclh,kwgclqt,ksimilarity,kCardImageNamePath,kBatch];
// 执行语句
if (![db executeUpdate:sql3]) {
NSLog(@"建表3失败");
}else{
NSLog(@"建表3成功! 上传数据表！");
}
}];
}
return self;
}
@end
二，下面开始增删改查 基本操作
#pragma mark 增 -----> 外部调用传入需要存储的学生Model
- (void)insert1:(NSString *)tableName WithStudent:(PAStudentModel *)student{
if ([tableName isEqualToString:kStudentTB2]){
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSData* imgData = UIImageJPEGRepresentation(student.StudentImage, 0.3);
NSData *imageCardDate = UIImageJPEGRepresentation(student.CardImage, 0.3);
NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ ( %@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@) VALUES ( ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?, ?, ?, ?,?,?,?)", kStudentTB2,kStudentKey, kCardID,P_VECTOR, kName,kcandidate_num,kseat_num,kRoomID,klocation_ID,kImageTime,kexam_time,kexam_date,kStudentImage,kExamination_no,kStudentState,kCardImage,kBatch,room_id,ksimilarity,kCardImageNamePath];
if (![db executeUpdate:sql, student.StudentKey,student.CardID,student.vector,student.StudentName,student.candidate_num,student.seat_num,student.RoomID,student.location_ID,student.ImageTime,student.exam_time,student.exam_date,imgData,student.Examination_no,@(student.StudentState),imageCardDate,student.Batch,student.roomid,student.similarity,student.CardImageNamePath]) {
NSLog(@"插入表2失败");
}else{
NSLog(@"插入表2成功");
}
}];
}
}
#pragma mark 改------> 更新表中 学生状态 存储的学生图片
- (void)updateStudent2Table:(NSString *)TableName WithStudent:(PAStudentModel *)Model State:(NSInteger)StateIndex {
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = ?,%@ = ?,%@ = ?, %@ = ? WHERE %@ = ?", kStudentTB2, kStudentState,kStudentImage,kImageTime ,ksimilarity,kStudentKey];
NSData* imgData = UIImageJPEGRepresentation(Model.StudentImage, 0.3);
if (![db executeUpdate:sql, @(StateIndex),imgData, Model.ImageTime,Model.similarity,Model.StudentKey]) {
NSLog(@"%@更新状态失败",kStudentTB2);
}else{
NSLog(@"%@更新状态成功",kStudentTB2);
}
}];
}
#pragma mark 查-------> 查找某个考室中状态 1 和 3 的所有学生 返回学生模型便于外部逻辑处理
- (NSMutableArray *)findAllStudentExamination_no:(NSString *)Examination_no StudentRomm_ID:(NSString *)Room_ID exam_Date:(NSString *)exam_date
{
__block NSMutableArray *studentList = [[NSMutableArray alloc] init];
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM UploadStudentTB where %@ = '%@' and %@ = '%@' and %@ = '%@' and StudentState in (1, 3) group by CardID order by imagetime ", kRoomID, Room_ID, kExamination_no, Examination_no,kexam_date,exam_date];
FMResultSet *result = [db executeQuery:sql];
while ([result next]){
PAStudentModel *student = [[PAStudentModel alloc] init];
student.StudentName = [result stringForColumn:kName];
student.CardID = [result stringForColumn:kCardID];
student.vector = [result stringForColumn:P_VECTOR];
student.seat_num = [result stringForColumn:kseat_num];
student.candidate_num = [result stringForColumn:kcandidate_num];
student.location_ID = [result stringForColumn:klocation_ID];
student.RoomID = [result stringForColumn:kRoomID];
student.ImageTime = [result stringForColumn:kImageTime];
NSData *imageData = [result dataForColumn:kStudentImage];
student.StudentImage = [UIImage imageWithData:imageData];
student.StudentKey = [result stringForColumn:kStudentKey];
student.StudentState = [result intForColumn:kStudentState];
NSData *CardimageData = [result dataForColumn:kCardImage];
student.CardImage = [UIImage imageWithData:CardimageData];
student.Examination_no = [result stringForColumn:kExamination_no];
student.exam_date = [result stringForColumn:kexam_date];
student.exam_time = [result stringForColumn:kexam_time];
// student.Batch = [result stringForColumn:kBatch];
student.roomid = [result stringForColumn:room_id];
student.similarity = [result stringForColumn:ksimilarity];
student.CardImageNamePath = [result stringForColumn:kCardImageNamePath];
student.wgclqt = [result stringForColumn:kwgclqt];
[studentList addObject:student];
}
[result close];
}];
return studentList;
}
#pragma mark 查找计数表 某个考室中状态 1 和 3 的最近的7位学生 最新录入的7个学生
- (NSMutableArray *)findAllStudentInStudentExamination_no:(NSString *)Examination_no StudentRomm_ID:(NSString *)Room_ID exam_Date:(NSString *)exam_date
{
__block NSMutableArray *studentList = [[NSMutableArray alloc] init];
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM StudentTable2 where %@ = '%@' and %@ = '%@' and %@ = '%@' and StudentState in (1, 3) group by CardID order by imagetime desc LIMIT 7", kRoomID, Room_ID, kExamination_no, Examination_no,kexam_date,exam_date];
FMResultSet *result = [db executeQuery:sql];
while ([result next]){
// 创建新的模型，每行数据就是一个模型
PAStudentModel *student = [[PAStudentModel alloc] init];
student.StudentName = [result stringForColumn:kName];
student.CardID = [result stringForColumn:kCardID];
student.vector = [result stringForColumn:P_VECTOR];
student.seat_num = [result stringForColumn:kseat_num];
student.candidate_num = [result stringForColumn:kcandidate_num];
student.location_ID = [result stringForColumn:klocation_ID];
student.RoomID = [result stringForColumn:kRoomID];
student.ImageTime = [result stringForColumn:kImageTime];
NSData *imageData = [result dataForColumn:kStudentImage];
student.StudentImage = [UIImage imageWithData:imageData];
student.StudentKey = [result stringForColumn:kStudentKey];
student.StudentState = [result intForColumn:kStudentState];
NSData *CardimageData = [result dataForColumn:kCardImage];
student.CardImage = [UIImage imageWithData:CardimageData];
student.Examination_no = [result stringForColumn:kExamination_no];
student.exam_date = [result stringForColumn:kexam_date];
student.exam_time = [result stringForColumn:kexam_time];
// student.Batch = [result stringForColumn:kBatch];
student.roomid = [result stringForColumn:room_id];
student.similarity = [result stringForColumn:ksimilarity];
student.CardImageNamePath = [result stringForColumn:kCardImageNamePath];
student.wgclqt = [result stringForColumn:kwgclqt];
[studentList addObject:student];
}
[result close];
}];
return studentList;
}
#pragma mark 删除某张表 也可以根据创建表时 每条数据的唯一key来删除某一个学生
- (void)removeTable:(NSString *)TableName {
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@", TableName];
if (![db executeUpdate:sql]) {
NSLog(@"删除失败");
}else{
NSLog(@"删除成功");
}
}];
}
#pragma mark 在判断某个学生是否存在摸张表中 也可以根据创建表时 每条数据的唯一key来判断
- (BOOL)isExistenceUploadStudentTB:(NSString *)TableName WithColumnName:(NSString *)ColumnName {
__block BOOL isExist = NO;
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?", TableName,kStudentKey];
FMResultSet *set = [db executeQuery:sql,ColumnName];
isExist = [set next];
[set close];
}];
return isExist;
}
#pragma mark 根据key 找到唯一学生
- (NSMutableArray *)findStudentCount:(NSString *)TabelName WithStudent:(NSString *)studentKey{
__block NSMutableArray *studentList = [[NSMutableArray alloc] init];
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?", TabelName, kStudentKey];
FMResultSet *result = [db executeQuery:sql,studentKey];
while ([result next]){
// 创建新的模型，每行数据就是一个模型
PAStudentModel *student = [[PAStudentModel alloc] init];
student.StudentName = [result stringForColumn:kName];
student.CardID = [result stringForColumn:kCardID];
student.vector = [result stringForColumn:P_VECTOR];
student.seat_num = [result stringForColumn:kseat_num];
student.candidate_num = [result stringForColumn:kcandidate_num];
student.location_ID = [result stringForColumn:klocation_ID];
student.RoomID = [result stringForColumn:kRoomID];
student.ImageTime = [result stringForColumn:kImageTime];
NSData *imageData = [result dataForColumn:kStudentImage];
student.StudentImage = [UIImage imageWithData:imageData];
student.StudentKey = [result stringForColumn:kStudentKey];
student.StudentState = [result intForColumn:kStudentState];
NSData *CardimageData = [result dataForColumn:kCardImage];
student.CardImage = [UIImage imageWithData:CardimageData];
student.Examination_no = [result stringForColumn:kExamination_no];
student.exam_date = [result stringForColumn:kexam_date];
student.exam_time = [result stringForColumn:kexam_time];
student.wgclqt = [result stringForColumn:kwgclqt];
student.wgclh = [result stringForColumn:kwgclh];
student.similarity = [result stringForColumn:ksimilarity];
student.CardImageNamePath = [result stringForColumn:kCardImageNamePath];
student.wgclqt = [result stringForColumn:kwgclqt];
[studentList addObject:student];
}
[result close];
}];
return studentList;
}
#pragma mark 找到表 中所有学生
- (NSMutableArray *)findUploadStudentTBAllStudents
{
__block NSMutableArray *studentList = [[NSMutableArray alloc] init];
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?", kUploadStudentTB,kuploadState];
FMResultSet *result = [db executeQuery:sql,@(0)];
while ([result next]){
PAStudentModel *student = [[PAStudentModel alloc] init];
student.StudentName = [result stringForColumn:kName];
student.CardID = [result stringForColumn:kCardID];
student.vector = [result stringForColumn:P_VECTOR];
student.seat_num = [result stringForColumn:kseat_num];
student.candidate_num = [result stringForColumn:kcandidate_num];
student.location_ID = [result stringForColumn:klocation_ID];
student.RoomID = [result stringForColumn:kRoomID];
student.ImageTime = [result stringForColumn:kImageTime];
student.StudentKey = [result stringForColumn:kStudentKey];
student.StudentState = [result intForColumn:kStudentState];
student.roomid = [result stringForColumn:room_id];
NSData *CardimageData = [result dataForColumn:kCardImage];
student.CardImage = [UIImage imageWithData:CardimageData];
NSData *imageData = [result dataForColumn:kStudentImage];
student.StudentImage = [UIImage imageWithData:imageData];
student.Batch = [result stringForColumn:kBatch];
student.Examination_no = [result stringForColumn:kExamination_no];
student.exam_date = [result stringForColumn:kexam_date];
student.exam_time = [result stringForColumn:kexam_time];
student.index = [result intForColumn:kStudentIndex];
student.disciplineCode = [result stringForColumn:kdisciplineCode];
student.disciplineType = [result stringForColumn:kdisciplineType];
student.disciplineStr = [result stringForColumn:kdisciplineStr];
student.wgclqt = [result stringForColumn:kwgclqt];
student.wgclh = [result stringForColumn:kwgclh];
student.similarity = [result stringForColumn:ksimilarity];
student.CardImageNamePath = [result stringForColumn:kCardImageNamePath];
[studentList addObject:student];
}
[result close];
}];
return studentList;
}
/**
* 批量 添加数组数据 并使它们的操作在一个事务中完成 这里是批量操作 不要频繁打开和关闭一个事务 在一个中完成就好 非常省时
*/
- (void)InsterTokStudentTB2:(NSMutableArray *)StudentArr
{
[self.dbQueue inDatabase:^(FMDatabase *db) {
NSDate *startTime = [NSDate date];
[db beginTransaction];
BOOL isRollBack = NO;
@try
{
for (int i = 0; i<StudentArr.count; i++)
{
PAStudentModel *student = StudentArr[i];
// 判断是否已存在数据库
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?", kStudentTB2,kStudentKey];
FMResultSet *set = [db executeQuery:sql,student.StudentKey];
bool isExist = [set next];
[set close];
if (!isExist) {
// [self insert1:@"StudentTable2" WithStudent:student];
NSData* imgData = UIImageJPEGRepresentation(student.StudentImage, 0.3);
NSData *imageCardDate = UIImageJPEGRepresentation(student.CardImage, 0.3);
NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ ( %@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@,%@) VALUES ( ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?, ?, ?, ?,?,?,?,?)", kStudentTB2,kStudentKey, kCardID,P_VECTOR, kName,kcandidate_num,kseat_num,kRoomID,klocation_ID,kImageTime,kexam_time,kexam_date,kStudentImage,kExamination_no,kStudentState,kCardImage,kBatch,room_id,ksimilarity,kCardImageNamePath,kwgclqt];
if (![db executeUpdate:sql, student.StudentKey,student.CardID,student.vector,student.StudentName,student.candidate_num,student.seat_num,student.RoomID,student.location_ID,student.ImageTime,student.exam_time,student.exam_date,imgData,student.Examination_no,@(student.StudentState),imageCardDate,student.Batch,student.roomid,student.similarity,student.CardImageNamePath]) {
NSLog(@"插入表2失败");
}else{
// NSLog(@"插入表2成功");
}
}else{
// NSLog(@"该考生已经存在数据库！");
}
}
NSDate *endTime = [NSDate date];
NSTimeInterval a = [endTime timeIntervalSince1970] - [startTime timeIntervalSince1970];
NSLog(@"使用事务插入%ld条数据用时%.3f秒",StudentArr.count,a);
}
@catch (NSException *exception)
{
isRollBack = YES;
[db rollback];
}
@finally
{
if (!isRollBack)
{
[db commit];
}
}
}];
}
网友评论