iOS开发之 - FMDB的简单使用

FMDB

下面使用的XLUserModel是自定义的model对象
.h文件的简单实现

@interface XLDataBase : NSObject

+ (XLDataBase *)standardDB;
// 插入一条数据
- (BOOL)insertIntoDataBaseWithModel:(XLUserModel *)model;
// 修改某一条数据
- (BOOL)updateDataBaseWithModel:(XLUserModel *)model;
// 删除某一条数据
- (BOOL)deleteFromDataBaseByIdentifier:(NSString *)identifier;
// 查询某一条数据是否存在
- (XLUserModel *)queryFromDataBaseByIdentifier:(NSString *)identifier;
// 清空当前数据库
- (BOOL)deleteFromDataBase;
// 查询所有数据
- (NSMutableArray *)selectAllFromDataBase;
// 获取数据库路径
- (NSString *)dataBasePath;

@end

.m文件的实现

@interface XLDataBase ()
@property (nonatomic, strong) FMDatabase *fmdb;
@property (nonatomic, strong) NSMutableArray *dataArray;
@end

@implementation XLDataBase
// 初始化单例
static XLDataBase *xldb = nil;
+ (XLDataBase *)standardDB {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        xldb = [[XLDataBase alloc] init];
        [xldb creatDataBase]; // 创建数据库
        [xldb createTableInDataBase]; // 创建表
    });
    return xldb;
}
- (NSMutableArray *)dataArray {
    if (!_dataArray) {
        self.dataArray = [NSMutableArray arrayWithCapacity:1];
    }
    return _dataArray;
}

// 创建数据库
- (void)creatDataBase {
    self.fmdb = [FMDatabase databaseWithPath:[xldb dataBasePath]];
}
// 创建表
- (void)createTableInDataBase {
    // 打开数据库
    if (![xldb.fmdb open]) {
        return;
    }
    // 操作数据库 -- 创建表 
    [xldb.fmdb executeUpdate:@"create table if not exists friends(item_id integer primary key autoincrement, friend_id text, group_id text, reamrk text, session_id text, user_id text, userName text)"];
    // 关闭数据库
    [xldb.fmdb close];
}

// 增 -- 插入
- (BOOL)insertIntoDataBaseWithModel:(XLUserModel *)model {
    // 打开数据库
    if (![xldb.fmdb open]) {
        return NO;
    }
    // 操作数据库 -- 插入数据库
    BOOL isSuccess = [xldb.fmdb executeUpdate:@"insert into friends(friend_id, group_id, reamrk, session_id, user_id, userName) values(?, ?, ?, ?, ?, ?)", model.friendId, model.groupId, model.remark, model.sessionId, model.userId, model.userName];
    /**
        因为主键item_id是由数据库自动生成的, 所以在插入数据之前, model对象中没有item_id, 所以当数据库把item_id生成好之后, 需要同步给model对象
     */
    model.item_id =xldb.fmdb.lastInsertRowId;
    // 关闭数据库
    [xldb.fmdb close];
    return isSuccess;
}

// 删
- (BOOL)deleteFromDataBaseByIdentifier:(NSString *)identifier {
    // 打开数据库
    if (![xldb.fmdb open]) {
        return NO;
    }
    // 操作数据库 -- 删除一条数据
    BOOL isSuccess = [xldb.fmdb executeUpdate:@"delete from friends where friend_id = ?", identifier];
    // 关闭数据库
    [xldb.fmdb close];
    return isSuccess;
}

// 查询某一条数据
- (XLUserModel *)queryFromDataBaseByIdentifier:(NSString *)identifier {
    // 打开数据库
    if (![xldb.fmdb open]) {
        return nil;
    }
    // 操作数据库 -- 查询一条数据
    FMResultSet *result = [xldb.fmdb executeQuery:@"select * from friends where friend_id = ?", identifier];
    XLUserModel *model = nil;
    while ([result next]) {
        NSInteger item_id = [result intForColumn:@"item_id"];
        NSString *friend_id = [result stringForColumn:@"friend_id"];
        NSString *group_id = [result stringForColumn:@"group_id"];
        NSString *remark = [result stringForColumn:@"reamrk"];
        NSString *session_id = [result stringForColumn:@"session_id"];
        NSString *userName = [result stringForColumn:@"userName"];
        NSString *user_id = [result stringForColumn:@"user_id"];
        model = [XLUserModel xlModelWithItemId:item_id friendId:friend_id groupId:group_id remark:remark sessionId:session_id userId:user_id userName:userName];
    }
    // 关闭数据库
    [xldb.fmdb close];
    return model;
}
// 改
- (BOOL)updateDataBaseWithModel:(XLUserModel *)model {
    if (![xldb.fmdb open]) {
        return NO;
    }
    // 操作数据库 -- 修改一条数据
    BOOL isSuccess = [xldb.fmdb executeUpdate:@"update friends set group_id = ?, reamrk = ?, session_id = ?, userName = ? where friend_id = ?", model.groupId, model.remark, model.sessionId, model.userName, model.friendId];
    // 关闭数据库
    [xldb.fmdb close];
    return isSuccess;
}
// 查询所有数据
- (NSMutableArray *)selectAllFromDataBase {
    // 打开数据库
    if (![xldb.fmdb open]) {
        return nil;
    }
    xldb.dataArray = nil;
    // 操作数据库
    FMResultSet *result = [xldb.fmdb executeQuery:@"select * from friends"];
    while ([result next]) { // 查看是否还有下一条数据
        NSInteger item_id = [result intForColumn:@"item_id"];
        NSString *friend_id = [result stringForColumn:@"friend_id"];
        NSString *group_id = [result stringForColumn:@"group_id"];
        NSString *remark = [result stringForColumn:@"reamrk"];
        NSString *session_id = [result stringForColumn:@"session_id"];
        NSString *userName = [result stringForColumn:@"userName"];
        NSString *user_id = [result stringForColumn:@"user_id"];
        XLUserModel *model = [XLUserModel xlModelWithItemId:item_id friendId:friend_id groupId:group_id remark:remark sessionId:session_id userId:user_id userName:userName];
        [xldb.dataArray addObject:model];
    }

    // 关闭数据库
    [xldb.fmdb close];
    return self.dataArray;
}

// 删除所有数据
- (BOOL)deleteFromDataBase {
    // 打开数据库
    if (![xldb.fmdb open]) {
        return NO;
    }
    // 操作数据库 -- 删除当前表
    BOOL isSuccess = [xldb.fmdb executeUpdate:@"delete from friends"];
    // 关闭数据库
    [xldb.fmdb close];
    return isSuccess;
}

// 获取数据库文件路径
- (NSString *)dataBasePath {
    // 获取Documents文件夹路径
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
    // 拼接上数据库文件路径
    return [docPath stringByAppendingPathComponent:[NSString stringWithFormat:@"panda.sqlite"]];
}

FMDB另外使用使用方式

// MARK: - FMDatabaseQueue
- (void)xl_createFMDatabaseQueue {
    FMDatabaseQueue *databaseQueue = [FMDatabaseQueue databaseQueueWithPath:@""];
#pragma unused(databaseQueue) 
    [databaseQueue inDatabase:^(FMDatabase *db) {
        if (![self isTableOK:@"MYTABLE" withDB:db]) {
            NSString *createTableSQL = @"CREATE TABLE MYTABLE (id integer PRIMARY KEY autoincrement, userid text, name text, portraitUri text)";
            [db executeUpdate:createTableSQL];

            NSString *createIndexSQL = @"CREATE unique INDEX idx_userid ON MYTABLE(userid);";
            [db executeUpdate:createIndexSQL];
        }
    }];
}

/**
   FMResultSet *rs = [self executeQuery:@"select [sql] from sqlite_master where [type] = 'table' and lower(name) = ?", tableName];
 */
- (BOOL)isTableOK:(NSString *)tableName withDB:(FMDatabase *)db {
    BOOL isOK = NO;
    FMResultSet *resultSet = [db executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?", tableName];
    while ([resultSet next]) {
        NSInteger count = [resultSet intForColumn:@"count"];
        if (0 == count) {
            isOK =  NO;
        }
        else {
            isOK = YES;
        }
    }
    [resultSet close];
    return isOK;
}