一直想对FMDB 进行封装一下,却不知道怎么去封装,今天闲来没事,就简单封装了一下,如果有什么不对的地方请大家指出
首先自己定义了一个单例MySqlTool,在其.h文件中实现
#import <Foundation/Foundation.h>
#import "FMDatabase.h"
@interface MySqlTool : NSObject
@property (nonatomic,strong)FMDatabase *db;
+ (MySqlTool *)sharedInstance;
- (void)creatTable:(NSString*)tableName; //创建表
- (void)insertData:(NSDictionary*)dic intoTable:(NSString *)tableName; //插入数据
- (void)deleteData:(NSDictionary *)dic fromTable:(NSString *)tableName; //删除单个数据
- (NSDictionary *)queryDataFromTable:(NSString *)tableName;//获取表tableName中所有的数据
@end
并在.m文件中实现一下方法
#import "MySqlTool.h"
#import "FMDatabaseAdditions.h"
static MySqlTool *mySqlTool = nil;
@implementation MySqlTool
@synthesize db = db;
+ (MySqlTool *)sharedInstance
{
static dispatch_once_t onceDispatch;
dispatch_once(&onceDispatch,^{
mySqlTool = [[MySqlTool alloc]init];
});
return mySqlTool;
}
//获得存放数据库文件的沙盒地址
- (NSString *)databaseFilePath
{
NSArray *filePath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentPath = [filePath objectAtIndex:0];
NSLog(@"%@",filePath);
NSString *dbFilePath = [documentPath stringByAppendingPathComponent:@"db.sqlite"];
return dbFilePath;
}
//创建数据库的操作
- (void)creatDatabase
{
db = [[FMDatabase databaseWithPath:[self databaseFilePath]]retain];
}
- (void)creatTable:(NSString*)tableName
{
//先判断数据库是否存在,如果不存在,创建数据库
if (!db) {
[self creatDatabase];
}
//判断数据库是否已经打开,如果没有打开,提示失败
if (![db open]) {
NSLog(@"数据库打开失败");
return;
}
//为数据库设置缓存,提高查询效率
[db setShouldCacheStatements:YES];
//判断数据库中是否已经存在这个表,如果不存在则创建该表
if ([db open]) {
if (![db tableExists:tableName]) {
NSString *sql = [NSString stringWithFormat:@"CREATE TABLE '%@' ('name' CHAR(30) PRIMARY KEY, 'age' INTEGER)",tableName];
BOOL res = [db executeUpdate:sql];
if (!res) {
debugLog(@"error when creating db table");
} else {
debugLog(@"succ to creating db table");
}
[db close];
}else{
debugLog(@"table exist!!!!!");
}
} else {
debugLog(@"error when open db");
}
}
- (void)insertData:(NSDictionary*)dic intoTable:(NSString *)tableName
{
//判断是否有数据库
if (!db) {
[self creatDatabase];
}
if (![db open]) {
NSLog(@"db open error");
return;
}
//判断数据库中是否已经存在这个表,如果不存在则创建该表
if (![db tableExists:tableName]) {
[self creatTable:tableName];
}
// 查询表中是否有该条数据,有则修改,无则插入
NSString *seachStr = [NSString stringWithFormat:@"select * from %@ where name = ?",tableName];
FMResultSet *rs = [db executeQuery:seachStr,[dic objectForKey:@"name"]];
if ([rs next]) {
NSString *updateStr = [NSString stringWithFormat:@"update %@ set age = ? where name = ?",tableName];
BOOL res = [db executeUpdate:updateStr,[dic objectForKey:@"age"],[dic objectForKey:@"name"]];
if (res) {
NSLog(@"data update success!");
}else{
NSLog(@"data update fail!");
}
}else{
NSString *insertStr = [NSString stringWithFormat:@"INSERT INTO %@ (name, age) VALUES (?,?)",tableName];
BOOL res = [db executeUpdate:insertStr,[dic objectForKey:@"name"],[dic objectForKey:@"age"]];
if (res) {
NSLog(@"data insert success!");
}else{
NSLog(@"data insert fail!");
}
}
[db close];
}
- (void)deleteData:(NSDictionary *)dic fromTable:(NSString *)tableName
{
//判断是否有数据库
if (!db) {
[self creatDatabase];
}
if (![db open]) {
NSLog(@"db open error");
return;
}
//判断数据库中是否已经存在这个表,如果不存在则创建该表
if (![db tableExists:tableName]) {
[self creatTable:tableName];
}
//判断数据库中是否有这条数据,有则删除
NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where name = ?",tableName];
FMResultSet *rs = [ db executeQuery:sqlStr,[dic objectForKey:@"name"]];
if ([rs next]) {
NSString *deleteStr = [NSString stringWithFormat:@"delete from %@ where name = ?",tableName];
BOOL res = [db executeUpdate:deleteStr,[dic objectForKey:@"name"]];
if (res ) {
NSLog(@"delete data sucess!!!!");
}else{
NSLog(@"delete data failure!");
}
}
[db close];
}
- (NSDictionary *)queryDataFromTable:(NSString *)tableName
{
//判断是否有数据库
if (!db) {
[self creatDatabase];
}
if (![db open]) {
NSLog(@"db open error");
return nil;
}
//判断数据库中是否已经存在这个表,如果不存在则创建该表
if (![db tableExists:tableName]) {
[self creatTable:tableName];
}
if ([db open]) {
NSString * sql = [NSString stringWithFormat:@"select * from %@",tableName];
FMResultSet * rs = [db executeQuery:sql];
while ([rs next]) {
NSString * name = [rs stringForColumn:@"name"];
int age = [rs intForColumn:@"age"];
NSLog(@"name = %@,age = %d",name,age);
NSDictionary *divc = [[NSDictionary alloc]initWithObjectsAndKeys:name,@"name",[NSNumber numberWithInt:age],@"age", nil];
return divc;
}
[db close];
}
}
@end