IOS FMDB

● FMDB的优点

● 使用起来更加面向对象,省去了很多麻烦、冗余的C语言代码

● 对比苹果自带的Core Data框架,更加轻量级和灵活

● 提供了多线程安全的数据库操作方法,有效地防止数据混乱

● FMDB的github地址

● https://github.com/ccgus/fmdb

● FMDB有三个主要的类

● FMDatabase
• 一个FMDatabase对象就代表一个单独的SQLite数据库 • 用来执行SQL语句

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

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

打开数据库

● 通过指定SQLite数据库文件路径来创建FMDatabase对象 FMDatabase *db = [FMDatabase databaseWithPath:path]; if (![db open]) {

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

● 文件路径有三种情况
● 具体文件路径
• 如果不存在会自动创建

● 空字符串@""
• 会在临时目录创建一个空的数据库
• 当FMDatabase连接关闭时,数据库文件也被删除

● nil
• 会创建一个内存中临时数据库,当FMDatabase连接关闭时,数据库会被销毁

执行查询

● 查询方法

● -(FMResultSet*)executeQuery:(NSString*)sql,...

● -(FMResultSet*)executeQueryWithFormat:(NSString*)format,...

● -(FMResultSet*)executeQuery:(NSString*)sql withArgumentsInArray:(NSArray *)arguments

● 示例
// 查询数据
FMResultSet *rs = [db executeQuery:@"SELECT * FROM t_student"];

// 遍历结果集
while ([rs next]) {

   NSString *name = [rs stringForColumn:@"name"];
   int age = [rs intForColumn:@"age"];
   double score = [rs doubleForColumn:@"score"];

}

FMDatabaseQueue

● FMDatabase这个类是线程不安全的,如果在多个线程中同时使用一个FMDatabase实例,会造成数据混乱等问题

● 为了保证线程安全,FMDB提供方便快捷的FMDatabaseQueue类 ● FMDatabaseQueue的创建

FMDatabaseQueue *queue = [FMDatabaseQueue
databaseQueueWithPath:path];

● 简单使用
[queue inDatabase:^(FMDatabase *db) {

   [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];
   [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];
   [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];

   FMResultSet *rs = [db executeQuery:@"select * from t_student"];
   while ([rs next]) {

// ...

} }];

●使用事务

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {

   [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];
   [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];
   [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];

   FMResultSet *rs = [db executeQuery:@"select * from t_student"];
   while ([rs next]) {

// ...

} }];

● 事务回滚 *rollback = YES;

1.打开数据库
2.创建表
3.查询表

#import "HMShopTool.h"
#import "FMDB.h"
#import "HMShop.h"

@implementation HMShopTool

static FMDatabase *_db;

+ (void)initialize
{
    // 1.打开数据库
    NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"shops.sqlite"];
    _db = [FMDatabase databaseWithPath:path];
    [_db open];
    
    // 2.创表
    [_db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real);"];
}

+ (void)addShop:(HMShop *)shop
{
    [_db executeUpdateWithFormat:@"INSERT INTO t_shop(name, price) VALUES (%@, %f);", shop.name, shop.price];
}

+ (NSArray *)shops
{// 得到结果集
    FMResultSet *set = [_db executeQuery:@"SELECT * FROM t_shop;"];
    
    // 不断往下取数据
    NSMutableArray *shops = [NSMutableArray array];
    while (set.next) {
        // 获得当前所指向的数据
        HMShop *shop = [[HMShop alloc] init];
        shop.name = [set stringForColumn:@"name"];
        shop.price = [set doubleForColumn:@"price"];
        [shops addObject:shop];
    }
    return shops;
}
@end

View Code

#import "HMViewController.h"
#import "FMDB.h"
#import "HMShop.h"
#import "HMShopTool.h"

@interface HMViewController ()
//@property (nonatomic, strong) FMDatabase *db;
@end

@implementation HMViewController

- (void)viewDidLoad
{
    [super viewDidLoad];

    // 1.打开数据库
    NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"shops.sqlite"];
    self.db = [FMDatabase databaseWithPath:path];
    [self.db open];
//
//    // 2.创表
    [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real);"];
    // executeQuery:查询数据
    [self.db executeQuery:<#(NSString *), ...#>];
    
    // executeUpdate:除查询数据以外的其他操作
    [self.db executeUpdate:<#(NSString *), ...#>];
}

- (void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event
{
//    for (int i = 0; i<100; i++) {
//        HMShop *shop = [[HMShop alloc] init];
//        shop.name = [NSString stringWithFormat:@"枕头--%d", i];
//        shop.price = arc4random() % 200;
//        [HMShopTool addShop:shop];
//    }
    
    NSArray *shops = [HMShopTool shops];
    for (HMShop *shop in shops) {
        NSLog(@"%@ %f", shop.name, shop.price);
    }
    
    [self.db executeUpdate:@"DELETE FROM t_shop WHERE price < 800;"];
    
    [self query];
}

- (void)query
{
    // 得到结果集
    FMResultSet *set = [self.db executeQuery:@"SELECT * FROM t_shop;"];
    
    // 不断往下取数据
    while (set.next) {
        // 获得当前所指向的数据
        NSString *name = [set stringForColumn:@"name"];
        double price = [set doubleForColumn:@"price"];
        NSLog(@"%@ %f", name, price);
    }
}

- (void)insert
{
    for (int i = 0; i<100; i++) {
        NSString *name = [NSString stringWithFormat:@"手机-%d", i];
#warning 这里的字符串不用再加上''
        [self.db executeUpdateWithFormat:@"INSERT INTO t_shop(name, price) VALUES (%@, %d);", name, arc4random()%1000];
    }
}

View Code