FMDB 的简单使用

// .h 文件实现

#import <Foundation/Foundation.h>

@class XLUserModel; // 定义的操作对象 模型类

@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 文件实现

#import "XLDataBase.h"

#import "XLUserModel.h"

#import "FMDB.h"

@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"]];

}

// 附上 XLUserModel 类的实现 

//  . h 文件

#import <Foundation/Foundation.h>

@interface XLUserModel : NSObject

@property (nonatomic, copy) NSString *friendId;

@property (nonatomic, copy) NSString *groupId;

@property (nonatomic, copy) NSString *remark;

@property (nonatomic, copy) NSString *sessionId;

@property (nonatomic, copy) NSString *userId;

@property (nonatomic, copy) NSString *userName;

@property (nonatomic, assign) NSInteger item_id;

- (id)initWithDictionary:(NSDictionary *)dic;

+ (id)xlModelWithDictionary:(NSDictionary *)dic;

- (id)initWithItemId:(NSInteger)itemId

            friendId:(NSString *)friendId

             groupId:(NSString *)groupId

              remark:(NSString *)remark

           sessionId:(NSString *)sessionId

              userId:(NSString *)userId

            userName:(NSString *)userName;

+ (id)xlModelWithItemId:(NSInteger)itemId

               friendId:(NSString *)friendId

                groupId:(NSString *)groupId

                 remark:(NSString *)remark

              sessionId:(NSString *)sessionId

                 userId:(NSString *)userId

               userName:(NSString *)userName;

@end