iOS开发之

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;}