FMDB

一、主要类

• FMDatabase－代表一个独立的SQLite数据库，执行SQL语句。
• FMResultSet－代表FMDatebase查询的结果集。
• FMDatabaseQueue－如果你想要在多线程中查询和更新，你应该使用这个类。

二、优势

• 使用起来更加面向对象，省去了很多麻烦、冗余的C语言代码
• 对比苹果自带的CoreData框架，更加轻量级和灵活
• 提供多线程安全，有效地防止数据混乱，原来的SQLite不是线程安全的

三、创建

• 1、创建对应路径下的数据库
• 2、打开数据库
• 3、在数据库中创建表
• 4、执行更新操作
• 5、关闭数据库

四、EMDB 使用

4.1、FMDBbase类创建数据库

#import "LGDBManager.h"
#import <FMDB/FMDB.h>

@interface LGDBManager ()
@property (nonatomic, copy) NSString *dbPath;
@property (nonatomic, strong) FMDatabase *db;
@end

@implementation LGDBManager

static LGDBManager *manager = nil;
+ (instancetype)shareInstense {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[LGDBManager alloc] init];
    });
    return manager;
}

+ (id)allocWithZone:(struct _NSZone *)zone{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [super allocWithZone:zone];
    });
    return manager;
}

//MARK: - 数据库表路径
- (NSString *)dbPath{
    NSString *docuPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    NSString *dbPath = [docuPath stringByAppendingPathComponent:@"lgKody.db"];
    NSLog(@"!!!dbPath = %@",dbPath);
    return dbPath;
}
@end

• 1、使用FMDBbase类创建数据库

- (void)initDataBase{
    // 1.创建对应路径下数据库
    self.db = [FMDatabase databaseWithPath:self.dbPath];
    // 2.在数据库中进行增删改查操作时，需要判断数据库是否open，如果open失败，可能是权限或者资源不足，数据库操作完成通常使用close关闭数据库
    if (![self.db open]) {
        self.db = nil;
        NSLog(@"当前数据库打开失败,可能是权限或者资源不足....");
        return;
    }
    NSLog(@"数据库打开成功了");
    
    // 3.数据库中创建表（可创建多张）
    NSString *sql = @"create table if not exists t_person ('userID' INTEGER PRIMARY KEY AUTOINCREMENT,'name' TEXT NOT NULL, 'classString' TEXT NOT NULL,'timeString' INTEGER NOT NULL)";
    // 4.执行更新操作 此处database直接操作，不考虑多线程问题，多线程问题，用FMDatabaseQueue 每次数据库操作之后都会返回bool数值，YES，表示success，NO，表示fail,可以通过 @see lastError @see lastErrorCode @see lastErrorMessage
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        NSLog(@"创建表: t_person 成功了");
    }
    // 5: 使用完毕记得关闭
    [self.db close];
}

• 2、增删改查
• 增删改查中 除了查询（executeQuery），其余操作都用（executeUpdate）

- (void)insertObject:(LGPerson *)person{
    
    [self.db open];
    //1.executeUpdate:不确定的参数用？来占位（后面参数必须是oc对象，；代表语句结束）
    BOOL result = [_db executeUpdate:@"INSERT INTO t_person (name, classString, timeString) VALUES (?,?,?)",person.name,person.classString,person.timeString];
    //2.executeUpdateWithForamat：不确定的参数用%@，%d等来占位 （参数为原始数据类型，执行语句不区分大小写）
    //    BOOL result = [_db executeUpdateWithFormat:@"insert into t_person (name, classString, timeString) values (%@,%i,%@)",person.name,person.classString,person.timeString];
    //3.参数是数组的使用方式
    //    BOOL result = [_db executeUpdate:@"INSERT INTO t_person (name, classString, timeString) VALUES  (?,?,?);" withArgumentsInArray:@[person.name,person.classString,person.timeString]];
    //4. 参数是字典的使用方式
    //    BOOL result = [db executeUpdate:@"insert into 't_person' (name,classString,timeString) values(:name,:classString,:timeString)" withParameterDictionary:@{@"name":person.name,@"classString":person.classString,@"timeString":person.timeString}];
    if (result) {
        NSLog(@"往 't_person' 插入数据成功");
    } else {
        NSLog(@"往 't_person' 插入数据失败: %@",[self.db lastError].description);
    }
    [self.db close];
    
}

• 3、删除元素

- (void)deleteObject:(LGPerson *)person{
    [self.db open];
    BOOL result = [self.db executeUpdate:@"delete from 't_person' where name = ? and timeString = ?" withArgumentsInArray:@[person.name,person.timeString]];
    if (result) {
        NSLog(@"删除元素成功");
    } else {
        NSLog(@"删除元素失败: %@",[self.db lastError].description);
    }
    [self.db close];
}

• 4、更新数据

- (void)updateObject:(LGPerson *)person{
    [self.db open];
    BOOL result = [self.db executeUpdate:@"update 't_person' set timeString = ? where name = ?" withArgumentsInArray:@[person.timeString, person.name]];

    if (result) {
        NSLog(@"更新数据成功");
    } else {
        NSLog(@"更新数据失败: %@",[self.db lastError].description);
    }
    [self.db close];
}

• 5、查询数据

- (NSMutableArray *)queryData{
    [self.db open];
    FMResultSet *result = [self.db executeQuery:@"select * from 't_person'"];
    NSMutableArray *arr = [NSMutableArray array];
    while ([result next]) {
        LGPerson *person = [LGPerson new];
        person.userID   = [result intForColumn:@"userID"];
        person.name     = [result stringForColumn:@"name"];
        person.classString = [result stringForColumn:@"classString"];
        person.timeString  = [result stringForColumn:@"timeString"];
        [arr addObject:person];
        NSLog(@"从数据库查询到的人员 %@",person.name);
    }
    [self.db close];
    return arr;
}

• 6、清空数据

- (void)clearData{
    [self.db open];

    BOOL result1 = [self.db executeUpdate:@"DELETE FROM 't_database'"];
    if (result1) {
        NSLog(@"删除表内容成功");
        BOOL result2 = [self.db executeUpdate:@"UPDATE sqlite_sequence set seq=0 where name='t_database'"];
        
        if (result2) {
            NSLog(@"清空自增内容成功");
        } else {
            NSLog(@"清空自增内容失败: %@",[self.db lastError].description);
        }
    } else {
        NSLog(@"删除表内容数据失败: %@",[self.db lastError].description);
    }
    [self.db close];
}

• 7、更新整个数据

- (void)saveData:(NSArray *)array{
    [self.db open];
    
    BOOL result1 = [self.db executeUpdate:@"DELETE FROM 't_person'"];
    if (result1) {
        NSLog(@"删除表内容成功");
        BOOL result2 = [self.db executeUpdate:@"UPDATE sqlite_sequence set seq=0 where name='t_person'"];
        
        if (result2) {
            NSLog(@"清空自增内容成功");
        } else {
            NSLog(@"清空自增内容失败: %@",[self.db lastError].description);
        }
    } else {
        NSLog(@"删除表内容数据失败: %@",[self.db lastError].description);
    }
    
    for (LGPerson *person in array) {
        BOOL result3 = [_db executeUpdate:@"INSERT INTO t_person (name, classString, timeString) VALUES (?,?,?)",person.name,person.classString,person.timeString];
        if (result3) {
            NSLog(@"往 't_person' 插入数据成功");
        } else {
            NSLog(@"往 't_person' 插入数据失败: %@",[self.db lastError].description);
        }
    }
    
    [self.db close];
}

4.2、事务处理

• 1、在事务中处理事情
• 事务：原子性、效率更高

- (void)handleTransaction {
    
    self.db = [FMDatabase databaseWithPath:self.dbPath];
    if (![self.db open]) {
        self.db = nil;
        NSLog(@"当前数据库打开失败,可能是权限或者资源不足....");
        return;
    }
    NSLog(@"数据库打开成功了");
    
    NSString *sql = @"create table if not exists t_student1 ('userID' INTEGER PRIMARY KEY AUTOINCREMENT,'name' TEXT NOT NULL, 'number' TEXT NOT NULL)";
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        NSLog(@"创建表: t_student 成功了");
    }
    
    [self.db beginTransaction];
    NSDate * begin = [NSDate date];
    BOOL rollBack = NO;
    
    @try {
        for (int i= 0; i< 1000; i++) {
            NSString * name = [NSString stringWithFormat:@"LGStudent_%d",i];
            NSInteger number = i + 10;
            NSInteger userID = i;
            NSString * insertSql = [NSString stringWithFormat:@"insert into 't_student1' (userID,name,number) values(%ld,'%@',%ld)",userID,name,number];
            if (i == 200 ) {
                insertSql = [NSString stringWithFormat:@"insert into 't_student1' (userID,name,number) values(%d,'%@',%ld)",1,name,number];
            }
            BOOL result = [self.db executeUpdate:insertSql];
            if (!result) {
                NSLog(@"插入失败");
                rollBack = YES;
                return;
            }
        }
    } @catch (NSException *exception) {
        rollBack = YES;
    } @finally {
        // 只有都执行成功了才执行，如果有一条不成功就“回滚”

        if (!rollBack) {
            // 提交事务
            [self.db commit];
        }else{
            // 回滚事务
            [self.db rollback];
        }
    }
    
    NSDate * end = [NSDate date];
    NSTimeInterval time = [end timeIntervalSinceDate:begin];
    NSLog(@"在事务中执行插入任务所需时间 === %f",time);
    
}

• 2、未在事务中处理

- (void)handleNoTransaction {
    
    self.db = [FMDatabase databaseWithPath:self.dbPath];
    if (![self.db open]) {
        self.db = nil;
        NSLog(@"当前数据库打开失败,可能是权限或者资源不足....");
        return;
    }
    NSLog(@"数据库打开成功了");
    
    NSString *sql = @"create table if not exists t_teacher ('userID' INTEGER PRIMARY KEY AUTOINCREMENT,'name' TEXT NOT NULL, 'number' TEXT NOT NULL)";
    BOOL result = [self.db executeUpdate:sql];
    if (!result) {
        [self.db close];
    }
    NSDate * begin = [NSDate date];
    for (int i = 0; i < 1000; i ++) {
        NSString * name = [NSString stringWithFormat:@"LGStudent_%d",i];
        NSInteger number = i + 10;
        NSInteger userID = i;
        NSString * insertSql = [NSString stringWithFormat:@"insert into 't_teacher' (userID,name,number) values(%ld,'%@',%ld)",userID,name,number];
        BOOL result = [self.db executeUpdate:insertSql];
        if (!result) {
            NSLog(@"插入失败");
            return;
        }
    }
    NSDate * end = [NSDate date];
    NSTimeInterval  time = [end timeIntervalSinceDate:begin];
    NSLog(@"不在事务中执行插入任务所需时间===%f",time);

}

4.3、多线程处理

// dataBase 处理多线程BUG
- (void)buildDatabaseNotWithQueue{
    self.db = [FMDatabase databaseWithPath:self.dbPath];
    if (![self.db open]) {
        self.db = nil;
        NSLog(@"当前数据库打开失败,可能是权限或者资源不足....");
        return;
    }
    NSLog(@"数据库打开成功了");
    
    NSString *sql = @"create table if not exists t_database ('userID' INTEGER PRIMARY KEY AUTOINCREMENT,'name' TEXT NOT NULL, 'number' TEXT NOT NULL)";
    BOOL result = [self.db executeUpdate:sql];
    if (!result) {
        [self.db close];
    }
    
    dispatch_queue_t queuet1 = dispatch_queue_create("queuet1", DISPATCH_QUEUE_CONCURRENT);
    dispatch_async(queuet1, ^{
        NSLog(@"%@",[NSThread currentThread]);
        for (int i = 0; i < 50; i ++) {
            if ([self.db open]) {
//                BOOL result = [self.db executeUpdate:@"create table if not exists t_database (userID integer primary key autoincrement,name text not null,number integer not null);"];
                if (result) {
                    NSString * insertSql1 = [NSString stringWithFormat:@"insert into t_database (userID,name,number) values (%d,'Kody ——%d',%d)",i+50,i,i];
                    BOOL res = [self.db executeUpdate:insertSql1];
                    if (!res) {
                        NSLog(@"1插入失败 ----%d",i);
                    }
                }
            }
        }
    });
    
    dispatch_async(queuet1, ^{
        NSLog(@"%@",[NSThread currentThread]);
        for (int i = 0; i < 50;i ++) {
            if ([self.db open]) {
//                BOOL result = [self.db executeUpdate:@"create table if not exists t_database (userID integer primary key autoincrement,name text not null,number integer not null);"];
                if (result) {
                    NSString * insertSql2 = [NSString stringWithFormat:@"insert into t_database (userID,name,number) values (%d,'Kody ——%d',%d)",i,i,i];
                    BOOL res = [self.db executeUpdate:insertSql2];
                    if (!res) {
                        NSLog(@"2插入失败---%d",i);
                    }
                }
            }
        }
    });

}

- (void)buildDatabaseQueue{
    
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
    
    dispatch_queue_t queuet1 = dispatch_queue_create("queuet1", DISPATCH_QUEUE_CONCURRENT);
    dispatch_async(queuet1, ^{
        NSLog(@"%@",[NSThread currentThread]);
        //多个任务并发的添加进来了，执行的顺序是顺序执行的
        for (int i = 0; i < 50; i ++) {
            [queue inDatabase:^(FMDatabase * _Nonnull db) {
                if ([db open]) {
                    BOOL result = [self.db executeUpdate:@"create table if not exists t_database_queue2 (userID integer primary key autoincrement,name text not null,number integer not null);"];
                    if (result) {
                        NSString * insertSql1 = [NSString stringWithFormat:@"insert into t_database_queue2 (userID,name,number) values (%d,'Kody——%d',%d)",i,i,i];
                        BOOL res = [self.db executeUpdate:insertSql1];
                        if (!res) {
                            NSLog(@"1插入失败 ----%d",i);
                        }
                    }
                }
            }];
        }
    });
    
   dispatch_async(queuet1, ^{
        NSLog(@"%@",[NSThread currentThread]);
        for (int i = 0; i < 50; i ++) {
            [queue inDatabase:^(FMDatabase * _Nonnull db) {
                if ([db open]) {
                    BOOL result = [self.db executeUpdate:@"create table if not exists t_database_queue2 (userID integer primary key autoincrement,name text not null,number integer not null);"];
                    if (result) {
                        NSString * insertSql1 = [NSString stringWithFormat:@"insert into t_database_queue2 (userID,name,number) values (%d,'Kody——%d',%d)",i+50,i,i];
                        BOOL res = [self.db executeUpdate:insertSql1];
                        if (!res) {
                            NSLog(@"1插入失败 ----%d",i);
                        }
                    }
                }
            }];
        }
    });
    
}

- (void)buildDatabaseQueueShowTime{
    NSDate * begin = [NSDate date];

    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
    
    dispatch_queue_t queuet1 = dispatch_queue_create("queuet1", DISPATCH_QUEUE_CONCURRENT);
    dispatch_group_t group = dispatch_group_create();

    dispatch_group_async(group, queuet1, ^{
        NSLog(@"%@",[NSThread currentThread]);
        for (int i = 0; i < 50; i ++) {
            [queue inDatabase:^(FMDatabase * _Nonnull db) {
                if ([db open]) {
                    BOOL result = [self.db executeUpdate:@"create table if not exists t_database_queue1 (userID integer primary key autoincrement,name text not null,number integer not null);"];
                    if (result) {
                        NSString * insertSql1 = [NSString stringWithFormat:@"insert into t_database_queue1 (userID,name,number) values (%d,'Kody——%d',%d)",i,i,i];
                        BOOL res = [self.db executeUpdate:insertSql1];
                        if (!res) {
                            NSLog(@"1插入失败 ----%d",i);
                        }
                    }
                }
            }];
        }
    });
    
    dispatch_group_async(group, queuet1, ^{
        NSLog(@"%@",[NSThread currentThread]);
        for (int i = 0; i < 50; i ++) {
            [queue inDatabase:^(FMDatabase * _Nonnull db) {
                if ([db open]) {
                    BOOL result = [self.db executeUpdate:@"create table if not exists t_database_queue1 (userID integer primary key autoincrement,name text not null,number integer not null);"];
                    if (result) {
                        NSString * insertSql1 = [NSString stringWithFormat:@"insert into t_database_queue1 (userID,name,number) values (%d,'Kody——%d',%d)",i+50,i,i];
                        BOOL res = [self.db executeUpdate:insertSql1];
                        if (!res) {
                            NSLog(@"1插入失败 ----%d",i);
                        }
                    }
                }
            }];
        }
    });
    
    dispatch_group_notify(group, queuet1, ^{
        NSDate * end = [NSDate date];
        NSTimeInterval time = [end timeIntervalSinceDate:begin];
        NSLog(@"在多线程执行插入100条用时%f",time);
    });
 
}

注意点

• open close 需要平凡的打开和关闭数据库。只操作一张表的时候，可以只打开一次，之后在关闭，但是还是不安全，也耗性能，所以一般还是关闭好。close 只要是 防止多表切换