iOS下FMDB的多线程操作（一）

来源：互联网发布：水冷机箱推荐知乎编辑：程序博客网时间：2024/05/29 10:36

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];    });}

0 0