[iOS学习笔记]·FMDB：第三方数据库处理框架·用法示例篇(附源码Demo)

陈满iOS

发布于 2018-09-10 11:13:03

8920

发布于 2018-09-10 11:13:03

文章被收录于专栏：陈满iOS陈满iOS

关于学习使用FMDB，除了笔者整理的官方文档翻译版，更重要的是实践和代码练习。笔者曾查阅过FMDB示例用法的一些文献，一部分有的只讲到FMDatabase类，另一部分只讲专为多线程操作的FMDatabaseQueue类，很少有同时介绍两者用法区别的文章。这里，笔者做一个尝试。

这里提供我整理的源码GigHub下载链接，觉得有需要的朋友可以直接下载Demo，而不用看本篇文章介绍。欢迎作出进一步优化与分享，笔者这个尝试也是得益于两个大神的文章。
关于FMDatabase，官方文档说：它表示一个单独的SQLite数据库，用来执行SQLite的命令。而关于FMDatabaseQueue，官方文档说：如果你想在多线程中执行多个查询或更新，你应该使用该类。这是线程安全的。
然而，你看完还是不知道FMDatabaseQueue是什么。在字面Queue意义上，它只是个队列？那还需要自己另外建一个DataBase吗？其实FMDatabaseQueue这个类的对象已经把一个数据库和有关事务操作的方法封装在一起了。一个FMDatabaseQueue实例对象里面包含一个FMDatabase数据库对象。
Demo演示效果图：

1. 单线程生物：FMDatabase类

1.1 对FMDB的FMDataBase类进行一层封装

1.1.1 源码

DataBase.h

//
//  DataBase.h
//  FMDBDemo

#import <Foundation/Foundation.h>

@class Person;
@class Car;

@interface DataBase : NSObject

@property(nonatomic,strong) Person *person;

+ (instancetype)sharedDataBase;

#pragma mark - Person
/**
 *  添加person
 *
 */
- (void)addPerson:(Person *)person;
/**
 *  删除person
 *
 */
- (void)deletePerson:(Person *)person;
/**
 *  更新person
 *
 */
- (void)updatePerson:(Person *)person;

/**
 *  获取所有数据
 *
 */
- (NSMutableArray *)getAllPerson;

#pragma mark - Car

/**
 *  给person添加车辆
 *
 */
- (void)addCar:(Car *)car toPerson:(Person *)person;
/**
 *  给person删除车辆
 *
 */
- (void)deleteCar:(Car *)car fromPerson:(Person *)person;
/**
 *  获取person的所有车辆
 *
 */
- (NSMutableArray *)getAllCarsFromPerson:(Person *)person;
/**
 *  删除person的所有车辆
 *
 */
- (void)deleteAllCarsFromPerson:(Person *)person;

@end

DataBase.m

//
//  DataBase.m
//  FMDBDemo

#import "DataBase.h"
#import <FMDB.h>

#import "Person.h"
#import "Car.h"

static DataBase *_DBCtl = nil;

@interface DataBase()<NSCopying,NSMutableCopying>{
    FMDatabase  *_db;
}

@end

@implementation DataBase

#pragma mark - 单例操作
+(instancetype)sharedDataBase{
    
    @synchronized(self) {
    
        if (_DBCtl == nil) {
            
            _DBCtl = [[DataBase alloc] init];
            
            [_DBCtl initDataBase];
            
        }
    }
    
    return _DBCtl;
}

+(instancetype)allocWithZone:(struct _NSZone *)zone{
    
    @synchronized(self) {
        if (_DBCtl == nil) {
            
            _DBCtl = [super allocWithZone:zone];
            
        }
    }
    return _DBCtl;  
}

-(id)copy{
    return self;
}

-(id)mutableCopy{
    return self; 
}

-(id)copyWithZone:(NSZone *)zone{
    return self;
}

-(id)mutableCopyWithZone:(NSZone *)zone{
    return self;
}

-(void)initDataBase{
    
    // 获得Documents目录路径
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    // 文件路径
    
    NSString *filePath = [documentsPath stringByAppendingPathComponent:@"DB1.sqlite"];
    
    // 实例化FMDataBase对象
    
    _db = [FMDatabase databaseWithPath:filePath];
    
    [_db open];
    
    // 初始化数据表
    NSString *personSql = @"CREATE TABLE 'person' ('id' INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,'person_id' VARCHAR(255),'person_name' VARCHAR(255),'person_age' VARCHAR(255),'person_number'VARCHAR(255)) ";
    NSString *carSql = @"CREATE TABLE 'car' ('id' INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,'own_id' VARCHAR(255),'car_id' VARCHAR(255),'car_brand' VARCHAR(255),'car_price'VARCHAR(255)) ";
    
    [_db executeUpdate:personSql];
    [_db executeUpdate:carSql];
    [_db close];
}

#pragma mark - 接口
#pragma mark - 对人操作
//添加人
- (void)addPerson:(Person *)person{
    [_db open];
    
    NSNumber *maxID = @(0);
    
    FMResultSet *res = [_db executeQuery:@"SELECT * FROM person "];
    //获取数据库中最大的ID
    while ([res next]) {
        if ([maxID integerValue] < [[res stringForColumn:@"person_id"] integerValue]) {
            maxID = @([[res stringForColumn:@"person_id"] integerValue] ) ;
        }
    }
    maxID = @([maxID integerValue] + 1);
    
    [_db executeUpdate:@"INSERT INTO person(person_id,person_name,person_age,person_number)VALUES(?,?,?,?)",maxID,person.name,@(person.age),@(person.number)];

    [_db close];
}
//删除人
- (void)deletePerson:(Person *)person{
    [_db open];
    
    [_db executeUpdate:@"DELETE FROM person WHERE person_id = ?",person.ID];

    [_db close];
}
//更新人
- (void)updatePerson:(Person *)person{
    
    [_db open];
    
    [_db executeUpdate:@"UPDATE 'person' SET person_name = ?  WHERE person_id = ? ",person.name,person.ID];
    [_db executeUpdate:@"UPDATE 'person' SET person_age = ?  WHERE person_id = ? ",@(person.age),person.ID];
    [_db executeUpdate:@"UPDATE 'person' SET person_number = ?  WHERE person_id = ? ",@(person.number + 1),person.ID];

    [_db close];
}
//获取所有人
- (NSMutableArray *)getAllPerson{
    
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    
    if ([_db open]) {
        
        FMResultSet *res = [_db executeQuery:@"SELECT * FROM person"];
        
        while ([res next]) {
            
            Person *person = [[Person alloc] init];
            person.ID = @([[res stringForColumn:@"person_id"] integerValue]);
            person.name = [res stringForColumn:@"person_name"];
            person.age = [[res stringForColumn:@"person_age"] integerValue];
            person.number = [[res stringForColumn:@"person_number"] integerValue];
            
            [dataArray addObject:person];
        }
    };
    
    [_db close];
    
    return dataArray;
}

#pragma mark - 对人的车操作
/**
 *  给person添加车辆
 *
 */
- (void)addCar:(Car *)car toPerson:(Person *)person{
    [_db open];
    
    //根据person是否拥有car来添加car_id
    NSNumber *maxID = @(0);
    
    FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"SELECT * FROM car where own_id = %@ ",person.ID]];
    
    while ([res next]) {
        if ([maxID integerValue] < [[res stringForColumn:@"car_id"] integerValue]) {
             maxID = @([[res stringForColumn:@"car_id"] integerValue]);
        }
    }
     maxID = @([maxID integerValue] + 1);
    
    [_db executeUpdate:@"INSERT INTO car(own_id,car_id,car_brand,car_price)VALUES(?,?,?,?)",person.ID,maxID,car.brand,@(car.price)];
    [_db close];
}

/**
 *  给person删除车辆
 *
 */
- (void)deleteCar:(Car *)car fromPerson:(Person *)person{
    [_db open];
    [_db executeUpdate:@"DELETE FROM car WHERE own_id = ?  and car_id = ? ",person.ID,car.car_id];
    [_db close];
}

/**
 *  获取person的所有车辆
 *
 */
- (NSMutableArray *)getAllCarsFromPerson:(Person *)person{
    
    [_db open];
    NSMutableArray  *carArray = [[NSMutableArray alloc] init];
    
    FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"SELECT * FROM car where own_id = %@",person.ID]];
    while ([res next]) {
        Car *car = [[Car alloc] init];
        car.own_id = person.ID;
        car.car_id = @([[res stringForColumn:@"car_id"] integerValue]);
        car.brand = [res stringForColumn:@"car_brand"];
        car.price = [[res stringForColumn:@"car_price"] integerValue];
        [carArray addObject:car];  
    }
    [_db close];
    return carArray;
}

- (void)deleteAllCarsFromPerson:(Person *)person{
    [_db open];
    
    [_db executeUpdate:@"DELETE FROM car WHERE own_id = ?",person.ID];
    [_db close];
}

@end

1.1.2 知识点总结

静态变量(这里是静态实例)
- http://www.jianshu.com/p/aec2e85b9e84
单例类
SQL语言
- http://www.jianshu.com/p/3b0d0469cf3e
- http://www.jianshu.com/p/2e206a02476c

1.1.3 说明

FMDatabase的实例对象_db在执行SQL语句的时候采取类似下面的代码。其中，有两种风格，一种在executeUpdate方法后面直接写上字符串的字面量语法。另一种在执行方法的后面基于SQL字符串初始化一个NSString对象。

//删除
[_db executeUpdate:@"DELETE FROM person WHERE person_id = ?",person.ID];

[_db executeUpdate:[NSString stringWithFormat:@"delete from %@ where name = '%@'",KTable_UserName, dto.name]];

上面注意?和'%@'的区别！但对于也仅仅对于SELECT查询操作，可以省掉'%@'两边的''噢。DEMO代码中也是这样做的。

//查询
FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"SELECT * FROM car where own_id = '%@'",person.ID]];
//去掉''
FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"SELECT * FROM car where own_id = %@",person.ID]];

1.2 模型层

1.2.1 源码

Person.h

//
//  Person.h
//  FMDBDemo

#import <Foundation/Foundation.h>

@interface Person : NSObject

@property(nonatomic,strong) NSNumber *ID;


@property(nonatomic,copy) NSString *name;

@property(nonatomic,assign) NSInteger age;

@property(nonatomic,assign) NSInteger number;
/**
 *  一个人可以拥有多辆车
 */
@property(nonatomic,strong) NSMutableArray *carArray;

@end

Car.h

//
//  Car.h
//  FMDBDemo


#import <Foundation/Foundation.h>

@interface Car : NSObject
/**
 *  所有者
 */
@property(nonatomic,strong ) NSNumber *own_id;

/**
 *  车的ID
 */
@property(nonatomic,strong) NSNumber *car_id;


@property(nonatomic,copy) NSString *brand;

@property(nonatomic,assign) NSInteger price;


@end

1.3 调用层

类名
- FMViewController.m
示例方法: 添加一个人

#pragma mark - Action
/**
 *  添加数据到数据库
 */
- (void)addData{
    
    NSLog(@"addData");
    
    int nameRandom = arc4random_uniform(1000);
    NSInteger ageRandom  = arc4random_uniform(100) + 1;
    
    
    NSString *name = [NSString stringWithFormat:@"person_%d号",nameRandom];
    NSInteger age = ageRandom;
    
    Person *person = [[Person alloc] init];
    person.name = name;
    person.age = age;

    [[DataBase sharedDataBase] addPerson:person];
    
    self.dataArray = [[DataBase sharedDataBase] getAllPerson];

    [self.tableView reloadData];
 
}

知识点
- arc4random_uniform(uint32_t)会随机返回一个0到上界之间（不含上界）的整数。另外，arc4random(void)这个全局函数会生成9位数的随机整数。具体参考 http://www.jianshu.com/p/51269165c3e0 。
- [[DataBase sharedDataBase] addPerson:person];中的sharedDataBase方法返回一个该类的静态实例static DataBase *_DBCtl。如上重写了sharedDataBase和allocWithZone等方法，DataBase是一个单例类。

2. 多线程生物：FMDatabaseQueue类

2.1 封装

2.1.1 单例类：负责创建FMDatabaseQueue和它的数据库

DataBaseManager.m

//
//  DataBaseManager.m

#import "DataBaseManager.h"

@implementation DataBaseManager

//单例
+(instancetype)sharedManager{
    
    static DataBaseManager *manager = nil;
    static dispatch_once_t once;
    dispatch_once(&once, ^{
        
        manager = [[DataBaseManager alloc] init];
        
    });
    return manager;
}

- (instancetype)init{
    
    if (self = [super init]) {
        
        NSString *docDir = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
        NSString *filePath = [docDir stringByAppendingPathComponent:@"DB2.sqlite"];
        
        NSLog(@"路径 = %@",filePath);
        
        _databaseQueue = [FMDatabaseQueue databaseQueueWithPath:filePath];
    }
    return self;
}

@end

2.1.2 数据库访问类·基本类(DAO)：负责创建表

DAO.m

//
//  DAO.m

#import "DAO.h"

@implementation DAO

- (FMDatabaseQueue *)databaseQueue{
    
    return [DataBaseManager sharedManager].databaseQueue;;
}

+ (void)creatTableIfNeed{
    
    //【注意】：以后做sql语句，()外面的参数用字符串拼，()里面的参数放在执行方法里写
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, score REAL,arr BLOB,dic BLOB,book BLOB,date,img BLOB)",KTable_UserName];
    
    [[DataBaseManager sharedManager].databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if ([db executeUpdate:sql]) {
            
            NSLog(@"创建表成功");
        }
    }];
}

@end

2.1.3 数据库访问类·用户类(UserDAO)：负责增删改查

UserDAO.m

//
//  UserDAO.m

#import "UserDAO.h"
#import "UserDTO.h"

@implementation UserDAO

///单例
+(instancetype)sharedInstance{
    
    static UserDAO *instance = nil;
    static dispatch_once_t once;
    dispatch_once(&once, ^{
        //1.
        instance = [[UserDAO alloc] init];
        
    });
    return instance;
}

/*!
 *  插入数据
 *  【注意】：？？？
 */
- (BOOL)insertUserDTO:(UserDTO *)dto{
    
    __block BOOL success = NO;

    NSString *sql = [NSString stringWithFormat:@"insert into %@ (name,age,score,arr,dic,book,date,img) values (?,?,?,?,?,?,?,?)",KTable_UserName];
    
    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if (![db executeUpdate:sql,
              dto.name,
              @(dto.age),
              @(dto.score),
              [NSKeyedArchiver archivedDataWithRootObject:dto.arr],
              [NSKeyedArchiver archivedDataWithRootObject:dto.dic],
              [NSKeyedArchiver archivedDataWithRootObject:dto.book],
              dto.date,
               UIImagePNGRepresentation(dto.img)]) {
            
            *rollback = YES;
            return ;
        }
        
        success = YES;
    }];
    
    return success;
}

/*!
 *  获取数据
 */
- (NSMutableArray *)loadUserData{

    NSString *sql = [NSString stringWithFormat:@"select * from %@",KTable_UserName];
    
    NSMutableArray *arrM = [NSMutableArray array];
    
    [self.databaseQueue inDatabase:^(FMDatabase *db) {
        
       FMResultSet *re = [db executeQuery:sql];
        
        while ([re next]) {
            
            UserDTO *dto = [[UserDTO alloc] init];
            dto.name = [re stringForColumn:@"name"];
            dto.age = [re intForColumn:@"age"];
            dto.score = [re doubleForColumn:@"score"];
            dto.arr = [NSKeyedUnarchiver unarchiveObjectWithData:[re dataForColumn:@"arr"]];
            dto.dic = [NSKeyedUnarchiver unarchiveObjectWithData:[re dataForColumn:@"dic"]];
            dto.book = [NSKeyedUnarchiver unarchiveObjectWithData:[re dataForColumn:@"book"]];
            dto.date = [re dateForColumn:@"date"];
            dto.img = [UIImage imageWithData:[re dataForColumn:@"img"]];
            
            [arrM addObject:dto];
            
             NSLog(@"名字 = %@",dto.name);
             NSLog(@"数组 = %@",dto.arr);
             NSLog(@"字典 = %@",dto.dic);
             NSLog(@"BOOK = %@",dto.book);
             NSLog(@"时间 = %@",dto.date);
        }
    }];

    return arrM;
}

/*!
 *  修改数据
 * 【注意】
    1.这个参数，可要又不要，最好要，然后把新的模型赋值过去
    2.修改两个值的格式是： set a = 'a' , b = 'b'  一定不要少了中间的逗号
    3.是单引号，不可使用双引号，所有的都是
 */
- (BOOL)updateUserDTO:(UserDTO *)dto{

    __block BOOL success = NO;
    
    NSString *sql = [NSString stringWithFormat:@"update %@ set name = '%@',score = '%@'",KTable_UserName,@"郭美美",@(100)];

    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if (![db executeUpdate:sql]) {
            
            *rollback = YES;
            return ;
        }
        
        success = YES;
    }];

    return success;
}

/*!
 *  删除某个模型  （简单）
 */
- (BOOL)deleteUserDTO:(UserDTO *)dto{

    __block BOOL success = NO;
    
    NSString *sql = [NSString stringWithFormat:@"delete from %@ where name = '%@'",KTable_UserName, dto.name];
    
    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if (![db executeUpdate:sql]) {
            
            *rollback = YES;
            return ;
        }
        
        success = YES;
    }];
    
    return success;
}


/*!
 *  删除某个模型  （简单）
 */
- (BOOL)deleteAllUserDTO{
    
    __block BOOL success = NO;
    
    NSString *sql = [NSString stringWithFormat:@"delete from %@",KTable_UserName];
    
    [self.databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if (![db executeUpdate:sql]) {
            
            *rollback = YES;
            return ;
        }
        
        success = YES;
    }];
    
    return success;
}

@end

2.2 模型层

2.2.1 用户数据操作对象UserDTO

UserDTO.h

//
//  UserDTO.h

#import <Foundation/Foundation.h>
#import <UIKit/UIKit.h>
@class BookDTO;

@interface UserDTO : NSObject

/** 基本属性 */
@property (nonatomic, copy) NSString *name;

@property (nonatomic, assign) NSInteger age;

@property (nonatomic, assign) float score;

/** 数组 */
@property (nonatomic, strong) NSArray *arr;

/** 字典 */
@property (nonatomic, strong) NSDictionary *dic;

/** 自定义模型 */
@property (nonatomic, strong) BookDTO *book;

/** 时间 */
@property (nonatomic, strong) NSDate *date;

/** 图片 */
@property (nonatomic, strong) UIImage *img;


///构造器1
+ (UserDTO *)userWithUserName:(NSString *)userName
                      userAge:(NSInteger)userAge
                    userScore:(float)userScore
                          arr:(NSArray *)arr
                          dic:(NSDictionary *)dic
                         book:(BookDTO *)book
                         date:(NSDate *)date
                        image:(UIImage *)img;

@end

2.2.2 书本数据操作对象BookDTO

BookDTO.h

//
//  BookDTO.h

#import <Foundation/Foundation.h>

@interface BookDTO : NSObject

@property (nonatomic, copy) NSString *bookName;

@property (nonatomic, assign) NSInteger bookId;

/** 创建book */
+ (BookDTO *)bookWithBookName:(NSString *)bookName bookId:(NSInteger)bookId;


@end

2.3 调用层

类名
- DBQViewController.m
示例方法：

#pragma mark - Action
/**
 *  添加数据到数据库
 */
- (void)addData{
    
    NSLog(@"addData");
    
    int nameRandom = arc4random_uniform(1000);
    NSInteger ageRandom  = arc4random_uniform(100) + 1;

    NSString *name = [NSString stringWithFormat:@"person_%d号",nameRandom];
    NSInteger age = ageRandom;

    NSArray *arrr = @[@"t1",@"t2",@"t3"];
    
    NSDictionary *dic = @{@"key":@"t123"};
    
    BookDTO *bDto = [BookDTO bookWithBookName:@"NewBook" bookId:0011];
    
    UIImage *imag = [UIImage imageNamed:@"1"];
    
    NSLog(@"date = %@",[NSDate date]);
    //2016-05-11 10:04:50 +0000
    //2016-05-11 10:05:40 +0000  13
    //2016-05-11 10:06:01 +0000    31
    
    UserDTO *dto = [UserDTO userWithUserName:name userAge:age userScore:9 arr:arrr dic:dic book:bDto date:[NSDate date] image:imag];
    
    if ([[UserDAO sharedInstance] insertUserDTO:dto]) {
        
        NSLog(@"插入成功");
    }else{
        
        NSLog(@"插入失败");
    }
    
    self.dataArray = [[UserDAO sharedInstance] loadUserData];
    [self.tableView reloadData];
}

3. 小结

FMDatabase创建数据库的路径代码为：

// 获得Documents目录路径
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    // 文件路径
    
    NSString *filePath = [documentsPath stringByAppendingPathComponent:@"DB1.sqlite"];

FMDatabaseQueue创建数据库的路径代码为：

if (self = [super init]) {
        
        NSString *docDir = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
        NSString *filePath = [docDir stringByAppendingPathComponent:@"DB2.sqlite"];
        
        NSLog(@"路径 = %@",filePath);
        
        _databaseQueue = [FMDatabaseQueue databaseQueueWithPath:filePath];
    }
    return self;

运行的时候，你可以打个断点，打印出数据库的路径，类似如下：

/Users/ChenMan/Library/Developer/CoreSimulator/Devices/12B1701D-AFF5-4D6B-9923-86CE8AE0C387/data/Containers/Data/Application/6512C7E8-BD84-47FC-9284-00E7BBBD67D8/Documents/