iOS 第三方框架之FMDB的基本使用

1.首先导入FMDB的第三方框架

2.添加sqllite框架

3.使用FMDB的DataBase

3.1添加属性和方法

@property (nonatomic,strong) FMDatabase *database;
- (IBAction)insert;
- (IBAction)update;
- (IBAction)delete;
- (IBAction)query;

3.2 加载界面时,创建数据库,打开数据库,建表

- (void)viewDidLoad
{
    [super viewDidLoad];
    // 创建数据库文件,打开数据库,建表
    [self myDatabase];
}

// 创建数据库文件,打开数据库,建表的方法
- (void)myDatabase
{
    // 获得沙盒中的数据库文件名
    NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *fileName = [doc stringByAppendingPathComponent:@"student.sqlite"];
    
    // 创建数据库实例对象
    self.database = [FMDatabase databaseWithPath:fileName];
    
    // 打开数据库
    if ([self.database open]) {
        NSLog(@"数据库打开成功");
        // 创表
        BOOL result = [self.database executeUpdate:@"create table if not exists t_student(id integer primary key autoincrement , name text , age integer);"];
        
        if (result) {
            NSLog(@"创表成功");
        }else{
            NSLog(@"创表失败");
        }
    }else{
        NSLog(@"数据库打开失败");
    }
}

3.2更新操作,在FMDB了,插入,更新,删除都是更新操作

// 插入
- (IBAction)insert {
    
    for (int i = 0; i < 40; i++) {
        NSString *name= [NSString stringWithFormat:@"rose_%d", arc4random()%100 + 1];
        NSNumber *age = [NSNumber numberWithInt:arc4random()%100 + 1];
        [self.database executeUpdate:@"insert into t_student(name,age) values (?,?);",name,age];
    }
}

// 更新
- (IBAction)update{
    [self.database executeUpdate:@"update t_student set age = ? where name = ? ;",@30,@"rose_14"];
    
}

// 删除
- (IBAction)delete{
    [self.database executeUpdate:@"delete from t_student;"];
    
}

3.3 查询操作

// 查询
- (IBAction)query{
    FMResultSet *result = [self.database executeQuery:@"select * from t_student where age > ?;",@20];
    while(result.next){
        int ID = [result intForColumn:@"id"];
        NSString *name = [result stringForColumn:@"name"];
        int age = [result intForColumn:@"age"];
        NSLog(@"ID:%d ,name:%@ , age:%d",ID,name,age);
    }
}

4.使用FMDB的DataBaseQueue

4.1 新增属性和4个方法

@property (nonatomic,strong) FMDatabaseQueue *dbq;
- (IBAction)insertDBQ;
- (IBAction)updateDBQ;
- (IBAction)deleteDBQ;
- (IBAction)queryDBQ;

4.2 创建数据库,打开数据库,创建表

#pragma mark - databaseQueue
// 创建数据库文件,打开数据库,建表
- (void)myDatabaseQueue
{
    NSString *fileName = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingString:@"studentDBQ.sqlite"];
    self.dbq = [FMDatabaseQueue databaseQueueWithPath:fileName];
   [self.dbq inDatabase:^(FMDatabase *db) {
   BOOL result = [db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement,name text ,age integer );"];
       
       if (result) {
           NSLog(@"创表成功");
       }else{
           NSLog(@"创表失败");
       };
   }];
    
}

4.3 更新操作

// 插入 databaseQueue
- (IBAction)insertDBQ {
    [self.dbq inDatabase:^(FMDatabase *db) {
        for (int i = 0; i < 50; i ++) {
            NSString *name =[ NSString stringWithFormat:@"jack_%d",arc4random()%1000];
            NSNumber *age = @(arc4random()%100 + 1);
            [db executeUpdate:@"insert into t_student (name,age) values(?,?)",name,age ];
        }
        

    }];
}
// 更新表<span style="font-family: Menlo;">databaseQueue</span>
- (IBAction)updateDBQ{
   
    
    [self.dbq inDatabase:^(FMDatabase *db) {
        // 开启事务
        // [db executeUpdate:@"begin transaction;"]; 或者下面这一句
        // [db beginTransaction];
        
        [db executeUpdate:@"update t_student set age = ? ;", @20];
        [db executeUpdate:@"update t_student set age = ? ;", @20];
      
         [self.database executeUpdate:@"update t_student set age = ? ;",@30];
        //        if (发现情况不对){
        //            // 回滚事务
        //            [db rollback];
        //            [db executeUpdate:@"rollback transaction;"];或者上面这一句
        //        }
        
        
       [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];
        
        // 提交事务
        // [db commit];
        // [db executeUpdate:@"commit transaction;"];或者换用上面这一句
    }];
}
// 删除
- (IBAction)deleteDBQ{
    [self.dbq inDatabase:^(FMDatabase *db) {
        [db executeUpdate:@"delete from t_student;"];
    }];
    
}

4.4 查询操作

// 查询
- (IBAction)queryDBQ{
    [self.dbq inDatabase:^(FMDatabase *db) {
        FMResultSet *rs = [db executeQuery:@"select * from t_student where age > ?",@50];
        while (rs.next) {
            NSString *name = [rs stringForColumn:@"name"];
            int age = [rs intForColumn:@"age"];
            NSLog(@"name:%@ , age:%d",name ,age);
        }
    }];
}