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