iOS中一些时间比较长的操作都应该放在子线程中,以避免UI的卡顿。而sqlite 是非线程安全的,故在多线程中不能共用同一个数据库连接,否则会导致EXC_BAD_ACCESS。所以我们可以在子线程中创建一个新的db连接(新建一个db对象),然后再操作数据库。
如果选择FMDB,除了以上所说的方式外,还可以利用FMDatabaseQueue来解决多线程问题。
关于FMDB的使用网上教程比较多,内容基本都是与唐巧的这篇http://www.devtang.com/blog/2012/04/22/use-fmdb/差不多
这里记录一下多线程下的使用。
方式一:采用每次新建db的方式
db路径我是写了一个方法
+ (NSString *)getDBPath { NSString* docsdir = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES) lastObject]; NSFileManager *filemanage = [NSFileManager defaultManager]; docsdir = [docsdir stringByAppendingPathComponent:@"FMDBDemo"]; BOOL isDir; BOOL exit =[filemanage fileExistsAtPath:docsdir isDirectory:&isDir]; if (!exit || !isDir) { [filemanage createDirectoryAtPath:docsdir withIntermediateDirectories:YES attributes:nil error:nil]; } NSString *dbpath = [docsdir stringByAppendingPathComponent:@"myDB.sqlite"]; return dbpath; }
1、创建数据库表
/** 创建表 */ + (BOOL)createUserTableByDB { FMDatabase *db = [FMDatabase databaseWithPath:[self getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return NO; } NSString *sql = @"CREATE TABLE IF NOT EXISTS User(ID INTEGER PRIMARY KEY, name TEXT, age INTEGER, ID_No TEXT);"; BOOL res = [db executeUpdate:sql]; if (res) { NSLog(@"创建表格成功"); }else { NSLog(@"创建表格失败"); } return res; }2、插入数据
插入数据,我分了两种情形,一种是单条插入,一种是利用事务(利用事务插入多条时,时间会快很多,而且有任何一条插入失败,则不会提交到数据库)
/** 保存单个对象 */ - (BOOL)saveByDB { FMDatabase *db = [FMDatabase databaseWithPath:[User getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return NO; } NSString *sql = [NSString stringWithFormat:@"INSERT INTO User(name, age, ID_No) VALUES ('%@', '%d', '%@');", self.name, self.age, self.ID_no]; BOOL res = [db executeUpdate:sql]; [db close]; if (res) { NSLog(@"插入数据成功"); }else { NSLog(@"插入数据失败"); } return res; } /** 批量保存用户对象 */ + (BOOL)saveObjectsByDB:(NSArray *)array { FMDatabase *db = [FMDatabase databaseWithPath:[self getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return NO; } [db beginTransaction]; BOOL isRollBack = NO; @try { for (User *user in array) { NSString *sql = [NSString stringWithFormat:@"INSERT INTO User(name, age, ID_No) VALUES ('%@', '%d', '%@');", user.name, user.age, user.ID_no]; BOOL res = [db executeUpdate:sql]; if (!res) { NSLog(@"db事务插入失败"); }else { NSLog(@"db事务插入成功"); } } } @catch (NSException *exception) { isRollBack = YES; [db rollback]; } @finally { if (!isRollBack) { [db commit]; } } [db close]; return !isRollBack; }
更新与删除也分两种情形,与插入类似。
3、更新数据
/** 更新单个对象 */ - (BOOL)updateByDB { FMDatabase *db = [FMDatabase databaseWithPath:[User getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return NO; } NSString *sql = [NSString stringWithFormat:@"UPDATE User SET name = '%@',age = '%d',ID_no = '%@' WHERE ID = '%d';", self.name, self.age, self.ID_no, self.ID]; BOOL res = [db executeUpdate:sql]; [db close]; if (res) { NSLog(@"插入数据成功"); }else { NSLog(@"插入数据失败"); } return res; } /** 批量更新用户对象*/ + (BOOL)updateObjectsByDB:(NSArray *)array { FMDatabase *db = [FMDatabase databaseWithPath:[User getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return NO; } [db beginTransaction]; BOOL isRollBack = NO; @try { for (User *user in array) { NSString *sql = [NSString stringWithFormat:@"UPDATE User SET name = '%@',age = '%d',ID_no = '%@' WHERE ID = '%d';", user.name, user.age, user.ID_no, user.ID]; BOOL res = [db executeUpdate:sql]; if (!res) { NSLog(@"db事务更新失败"); }else { NSLog(@"db事务更新成功"); } } } @catch (NSException *exception) { isRollBack = YES; [db rollback]; } @finally { if (!isRollBack) { [db commit]; } } [db close]; return !isRollBack; }4、删除数据
/** 删除单个对象 */ - (BOOL)deleteObjectByDB { FMDatabase *db = [FMDatabase databaseWithPath:[User getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return NO; } NSString *sql = [NSString stringWithFormat:@"DELETE FROM User WHERE ID = '%d'",self.ID]; BOOL res = [db executeUpdate:sql]; [db close]; if (res) { NSLog(@"删除数据成功"); }else { NSLog(@"删除数据失败"); } return res; } /** 批量删除用户对象 */ + (BOOL)deleteObjectsByDB:(NSArray *)array { FMDatabase *db = [FMDatabase databaseWithPath:[self getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return NO; } [db beginTransaction]; BOOL isRollBack = NO; @try { for (User *user in array) { NSString *sql = [NSString stringWithFormat:@"DELETE FROM User WHERE ID = '%d'",user.ID]; BOOL res = [db executeUpdate:sql]; if (!res) { NSLog(@"db事务删除失败"); }else { NSLog(@"db事务删除成功"); } } } @catch (NSException *exception) { isRollBack = YES; [db rollback]; } @finally { if (!isRollBack) { [db commit]; } } [db close]; return !isRollBack; }
5、查询数据
查询就比较简单了。
/** 查询全部用户 */ + (NSArray *)findAllByDB { FMDatabase *db = [FMDatabase databaseWithPath:[self getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return nil; } NSMutableArray *users = [[NSMutableArray alloc] init]; NSString *sql = @"SELECT * FROM User"; FMResultSet *resultSet = [db executeQuery:sql]; while ([resultSet next]) { User *user = [[User alloc] init]; user.ID = [resultSet intForColumn:@"ID"]; user.name = [resultSet stringForColumn:@"name"]; user.age = [resultSet intForColumn:@"age"]; user.ID_no = [resultSet stringForColumn:@"ID_no"]; [users addObject:user]; [user release]; } NSLog(@"查询全部成功"); return [users autorelease]; } /** 查找某个用户 */ + (instancetype)findFirstBySqlByDB:(NSString *)sql { User *user = nil; NSArray *users = [self findBySqlByDB:sql]; if (users.count > 0) { user = [users firstObject]; } return user; } /** 查找用户 */ + (NSArray *)findBySqlByDB:(NSString *)sql { FMDatabase *db = [FMDatabase databaseWithPath:[self getDBPath]]; if (![db open]) { NSLog(@"数据库打开失败!"); return nil; } NSMutableArray *users = [[NSMutableArray alloc] init]; NSString *select = @"SELECT * FROM User "; if (sql) { select = [select stringByAppendingString:sql]; } FMResultSet *resultSet = [db executeQuery:select]; while ([resultSet next]) { User *user = [[User alloc] init]; user.ID = [resultSet intForColumn:@"ID"]; user.name = [resultSet stringForColumn:@"name"]; user.age = [resultSet intForColumn:@"age"]; user.ID_no = [resultSet stringForColumn:@"ID_no"]; [users addObject:user]; [user release]; } NSLog(@"条件查询成功"); return [users autorelease]; }
而多线程下的调用方式是这样的,其中上面的所有方法都是在对象类User中。
/** 用db插入User数据*/ - (IBAction)dbInsertData:(id)sender { //多线程插入数据 for (int i = 0; i < 5; i++) { dispatch_async(dispatch_get_global_queue(0, 0), ^{ User *user = [[User alloc] init]; user.name = @"dbName一"; user.ID_no = [NSString stringWithFormat:@"%d",55555+i]; user.age = 555+i; [user saveByDB]; }); } //利用事务插入数据 dispatch_async(dispatch_get_global_queue(0, 0), ^{ NSMutableArray *array = [NSMutableArray array]; for (int i = 0; i < 5; i++) { User *user = [[User alloc] init]; user.name = @"db事务"; user.ID_no = [NSString stringWithFormat:@"%d",66666+i]; user.age = 66+i; [array addObject:user]; [user release]; } [User saveObjectsByDB:array]; }); }