自定义封装简单的基于FMDB以及数据model的数据库操作
.h 文件
#import <Foundation/Foundation.h>
//@class FeedModel;
//通用数据库封装类
//可以根据模型自动建表，实现增删改查
//模型类名就是表名，模型属性名就是表的字段名
@interface QFDatebase : NSObject
+(QFDatebase *)sharedDatabase;
//将一条记录插入到表中(数组中保存的是所有模型的类名字符串)
-(void)createTable:(NSArray *)namesArray;
//将一条记录插入表中
-(void)insertModel:(id)model;
//将多条记录插入表中
-(void)insertArray:(NSArray * )array;
//从指定表中读取startIndex开始的count条记录，model标识要返回的对象类型，查询需要的参数，读取表的信息 where为model的某一个属性名
-(NSArray *)selectArray:(NSInteger)startIndex count:(NSInteger)count model:(id)model where:(NSString *)where;
@end
.m文件
#import "QFDatebase.h"
#import "FMDatabase.h"
#import "FeedModel.h"
#import "NSObject+DHF.h"
@implementation QFDatebase
{
FMDatabase * _db;
}
//uid,name,address
//uid text, name text, address text
//根据模型，返回和它匹配的字符串
-(NSString *)sqlFromModel:(id)model
{
//获取model对象的属性列表
NSDictionary * dict = [model propertyList:NO];
NSArray * keyArray = [dict allKeys];
NSString * sql = [keyArray componentsJoinedByString:@" text,"];
sql = [sql stringByAppendingString:@" text"];
NSLog(@"...............sql = %@", sql);
return sql;
}
-(void)createTable:(NSArray *)namesArray
{
// NSArray * sqlArray = [NSArray arrayWithObjects:@"create table if not exists %@ (id INTEGER PRIMARY KEY AUTOINCREMENT,%@)", nil];//语句有问题
NSString * srcSql = @"create table if not exists %@ (id integer primary key autoincrement, %@)";
for (NSString * modelName in namesArray) {
Class newClass = NSClassFromString(modelName);
id model = [[newClass alloc]init];
//格式化此模型的建表语句
NSString * sql = [NSString stringWithFormat:srcSql,[modelName lowercaseString],[self sqlFromModel:model]];
NSLog(@"execute sql:%@",sql);
BOOL success = [_db executeUpdate:sql];
//fmdb我们常用的方法只有两个,
//executeUpdate对应建表，删除，修改，插入
//executeQuery对应查询
//这两个方法是变参方法
if (!success) {
NSLog(@"创建表格失败:%@",[_db lastErrorMessage]);
}
}
}
+(QFDatebase *)sharedDatabase
{
static QFDatebase * db;
if (!db) {
db = [[[self class] alloc] init];
}
return db;
}
-(void)insertModel:(id)model
{
//格式化插入sql语句
NSString * sql = @"insert into %@ (%@) values(%@)";
//获得model对象的属性名和属性值组成的字典
NSDictionary * dict = [model propertyList:YES];//yes能够得到他的属性名和值
//获得所有属性名组成的字符串，逗号分隔
NSArray * array = [dict allKeys];
NSString * namelist = [array componentsJoinedByString:@","];
NSMutableString * valuelist = [NSMutableString string];
for (NSInteger i = 0 ; i < array.count; i++) {
if (i==0) {
[valuelist appendFormat:@"?"];
}else{
[valuelist appendFormat:@",?"];
}
}
//格式化最终的插入语句
sql = [NSString stringWithFormat:sql, NSStringFromClass([model class]),namelist,valuelist];
NSLog(@"insert sql:%@",sql);
//变参方法，动态绑定数据
BOOL success = [_db executeUpdate:sql withArgumentsInArray:[dict allValues]];
if (!success) {
NSLog(@"插入失败：%@",[_db lastErrorMessage]);
}
}
-(void)insertArray:(NSArray *)array
{
//捕获异常
@try {
//开始事务
[_db beginTransaction];
for (id model in array) {
[self insertModel:model];
}
}
@catch (NSException *exception) {
//回滚数据（此次提前全部取消）
[_db rollback];
}
@finally {
//提交数据
[_db commit];
}
}
-(NSArray *)selectArray:(NSInteger)startIndex count:(NSInteger)count model:(id)model where:(NSString *)where
{
NSMutableArray * array = [NSMutableArray array];
//查询指定表中从startindex条记录开始的count条记录
NSString * sql = @"select %@ from %@";
//如果有查询条件
if (where) {
sql = [sql stringByAppendingFormat:@" where %@=?", where];//where是段名
}
//如果需要读取限制
if (count!=0) {
sql = [sql stringByAppendingFormat:@" limit %d,%d", startIndex, count];
}
//获得查询的字段列表字符串
NSDictionary * dict = [model propertyList:NO];
NSLog(@"-----------dict = %@",dict);
NSString * namelist = [[dict allKeys] componentsJoinedByString:@","];
//格式化查询语句
sql = [NSString stringWithFormat:sql,namelist,NSStringFromClass( [model class])];
//执行查询，结果为FMResultSet
NSLog(@"select sql:%@",sql);
//执行查询
FMResultSet * rs;
if (where) {
rs = [_db executeQuery:sql,[model valueForKey:where]];
}else{
rs = [_db executeQuery:sql];
}
//便利结果集，一次或得一条记录
while ([rs next]) {
//根据传入的模型创建相同类型对象
id newModel = [[[model class] alloc]init];
NSLog(@"+++++++++++class : %@", newModel);
//获得当前记录数据字典
NSDictionary * dict = [rs resultDictionary];//当前记录转换为字典
//保存数据到模型对象中（kvc 的方法）
[newModel setValuesForKeysWithDictionary:dict];
[array addObject:newModel];
}
if ([array count] == 0)
{
NSLog(@"读取失败:%@",[_db lastErrorMessage]);
}
return array;
}
-(instancetype)init
{
if (self = [super init]) {
NSString * path = NSHomeDirectory();
path = [path stringByAppendingPathComponent:@"Documents/data.db"];
NSLog(@"path:%@", path);
//创建数据库，如果不存在
_db = [[FMDatabase alloc]initWithPath:path];
//尝试打开数据库
if ([_db open]) {
//使用者必须要调用一次此方法，传入需要创建的所有表名
[self createTable:nil];
}
}
return self;
}
@end