FMDB第三方框架

FMDB是同AFN，SDWebImage同样好用的第三方框架，它以OC的方式封装了SQLite的C语言API，使得开发变得简单方便。

附上github链接https://github.com/ccgus/fmdb

FMDB有三个主要的类

（1）FMDatabase

一个FMDatabase对象就代表一个单独的SQLite数据库

用来执行SQL语句

（2）FMResultSet

使用FMDatabase执行查询后的结果集

（3）FMDatabaseQueue

用于在多线程中执行多个查询或更新，它是线程安全的

上demo

先导入libsqlite3.0.tbd库

将FMDB加入工程里，当然你也可以用cocopods导入FMDB

下面是创建数据库的代码

#import "ViewController.h"

#import "NSString+Tool.h"

#import "FMDB.h"

@interface ViewController ()

@property (nonatomic, strong) FMDatabase * database;

@end

@implementation ViewController

- (IBAction)selectAction:(id)sender {

if ([self.database open]) {

//返回查询数据的结果集

FMResultSet * rs = [self.database executeQuery:@"select * from t_student"];

//查询表中的每一个记录

while ([rs next]) {

NSString * name = [rs stringForColumn:@"name"];

NSUInteger age = [rs intForColumn:@"age"];

NSLog(@"name = %@, age = %@",name,@(age));

}

[self.database close];

}

- (IBAction)updateAction:(id)sender {

if ([self.database open]) {

BOOL flag = [self.database executeUpdate:@"update t_student set name = 'xiaohong' where name = 'dahuan'"];

if (flag) {

NSLog(@"修改成功");

}else {

NSLog(@"修改失败");

}

[self.database close];

}

- (IBAction)deleteAction:(id)sender {

if ([self.database open]) {

BOOL flag = [self.database executeUpdate:@"delete from t_student"];

if (flag) {

NSLog(@"删除成功");

}else {

NSLog(@"删除失败");

}

[self.database close];

}

- (IBAction)insertAction:(id)sender {

if ([self.database open]) {

BOOL flag = [self.database executeUpdate:@"insert into t_student (name,age) values (?,?)",@"dahuan",@18];

if (flag) {

NSLog(@"插入成功");

}else {

NSLog(@"插入失败");

}

[self.database close];

}

- (void)viewDidLoad {

[super viewDidLoad];

NSString * filename = [NSString cachaPathName:@"data.sqlite"];

NSLog(@"%@",filename);

//1、创建数据库

self.database = [[FMDatabase alloc] initWithPath:filename];

//2、打开数据库

if ([self.database open]) {

NSLog(@"打开数据库成功");

//3、创建表

BOOL flag = [self.database executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement ,name text, age integer);"];

if (flag) {

NSLog(@"创建表成功");

} else {

NSLog(@"创建表失败");

}

//4、关闭数据库

[self.database close];

} else {

NSLog(@"打开数据库失败");

}

- (void)didReceiveMemoryWarning {

[super didReceiveMemoryWarning];

// Dispose of any resources that can be recreated.

}

@end

同样我们封装一下FMDB的增删改查操作

FMDBManger.h


#import <Foundation/Foundation.h>

@interface FMDBManger : NSObject

/** 增、删、改的操作 */
+ (BOOL)executeUpdate:(NSString*)sql;

/** 查询的操作 */
+ (NSArray *)executeQuery:(NSString*)sql;

@end

FMDBManger.m



#import "FMDBManger.h"
#import "NSString+Tool.h"
#import "FMDB.h"

static FMDatabase *_dataBase;

@implementation FMDBManger

+ (void)initialize
{
    // 1.创建数据库
    NSString *fileName = [NSString cachaPathName:@"student.sqlite"];
    _dataBase = [FMDatabase databaseWithPath:fileName];
    
    // 2.打开数据库
    if ([_dataBase open]) {
        NSLog(@"打开数据库成功");
        // 3.创建表
        BOOL flag = [_dataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text, age integer);"];
        if (flag) {
            NSLog(@"创建表成功");
        }else{
            NSLog(@"创建表失败");
        }
        
        // 4.关闭数据库
        [_dataBase close];
    }else{
        NSLog(@"打开数据库失败");
    }
}

/** 增、删、改的操作 */
+ (BOOL)executeUpdate:(NSString*)sql
{
    NSLog(@"sql:%@", sql);
    // 1.打开数据库
    if ([_dataBase open]) {
        // 2.修改数据
        BOOL flag = [_dataBase executeUpdate:sql];
        
        // 3.关闭数据库
        [_dataBase close];
        
        return flag;
    }
    
    return NO;
}

/** 查询的操作 */
+ (NSArray *)executeQuery:(NSString *)sql
{
    // 0.初始化数组，用于存放查询数据
    NSMutableArray *dataList = [NSMutableArray array];
    // 1.打开数据库
    if ([_dataBase open]) {
        // 2.准备查询数据
        FMResultSet *resultSet = [_dataBase executeQuery:sql];
        // 3.查询符合条件的数据
        while ([resultSet next]) {
            // 4.获得数据
            // 4.1根据字段名来获取数据
            NSString *name = [resultSet stringForColumn:@"name"];
            NSInteger age = [resultSet intForColumn:@"age"];
            
            // 添加数据
            [dataList addObject:@{@"name": name, @"age" : @(age)}];
            
            NSLog(@"name:%@  age:%zi", name, age);
        }
        
        // 关闭数据库
        [_dataBase close];
    }
    return dataList;
}

@end

数据库有个基本概念，事务，生活中比如ATM取款要求是线程安全的，FMDB的以下很好的解决这个问题

#import "ViewController.h"
#import "NSString+Tool.h"
#import "FMDB.h"

@interface ViewController ()

@property (nonatomic, strong) FMDatabaseQueue * queue;

@end

@implementation ViewController

- (IBAction)insertAction:(id)sender {
    
    [self.queue inDatabase:^(FMDatabase *db) {
        
        if ([db open]) {
            
            [db executeUpdate:@"insert into t_person (name,money) values (?,?)",@"张三",@500];
            [db executeUpdate:@"insert into t_person (name,money) values (?,?)",@"李四",@1000];

            [db close];
        }
    }];
}

- (IBAction)deleteAction:(id)sender {
    
    [self.queue inDatabase:^(FMDatabase *db) {
        
        if ([db open]) {
            
            BOOL flag = [db executeUpdate:@"delete from t_person"];
            if (flag) {
                NSLog(@"删除成功");
            } else {
                NSLog(@"删除失败");
            }
            [db close];
        }
        
    }];
}

- (IBAction)updateAction:(id)sender {
    
    //事务的好处：存储大量数据
    
    [self.queue inDatabase:^(FMDatabase *db) {
        
        if ([db open]) {
            
            //添加事务操作
            [db beginTransaction];
            
            BOOL flag1 = [db executeUpdate:@"update t_person set monaaaey = 1000 where name = '张三'"];
            
            BOOL flag2 = [db executeUpdate:@"update t_person set money = 500 where name = '李四'"];
            
            if (!(flag1 && flag2)) {
                
                //事务回滚
                [db rollback];
            }
            
            //提交事务
            [db commit];
            
            [db close];
        }
    }];
}

- (IBAction)selectAction:(id)sender {
    
    [self.queue inDatabase:^(FMDatabase *db) {
        
        if ([db open]) {
            
            FMResultSet * rs = [db executeQuery:@"select * from t_person"];
            
            while ([rs next]) {
            
                NSString * name = [rs stringForColumn:@"name"];
                CGFloat money = [rs doubleForColumn:@"money"];
                NSLog(@"name = %@ money = %lf",name,money);
            }
 
            [db close];
        }
    }];
    
}

- (void)viewDidLoad {
    [super viewDidLoad];
    
    NSString * filename = [NSString cachaPathName:@"person.sqlite"];
    
    //创建FMDatabaseQueue实例，不需要再去打开数据库
    self.queue = [FMDatabaseQueue databaseQueueWithPath:filename];
    
    //在多线程安全的情况下操作数据
    [self.queue inDatabase:^(FMDatabase *db) {
        
        BOOL flag = [db executeUpdate:@"create table if not exists t_person (id integer primary key autoincrement, name text,money real);"];
        
        if (flag) {
            NSLog(@"创建成功");
        } else {
            NSLog(@"创建失败");
        }
        
        [db close];
    }];

}

@end