#import <Foundation/Foundation.h>
#import "FMDB.h"
@interface CustomDatabase : NSObject
{
}
@property (nonatomic, strong) FMDatabase *db;
//查询数据库结果
@property (nonatomic,strong) NSMutableArray *resultSet;
- (void)createSQL;
- (void)createTab:(NSString *)table CreatTable:(NSString*)sql;
- (void)deleteTab:(NSString *)table;
- (BOOL)insertTab:(NSString *)table CreatTable:(NSString *)setup SQL:(NSString*)sql, ...;
- (BOOL)updateTab:(NSString *)table CreatTable:(NSString *)setup SQL:(NSString *)sql;
- (void)queryTab: (NSString *)table CreatTable:(NSString *)setup SQL:(NSString *)sql;
@end
//
// CustomDatabase.m
// DB
//
// Created by 龙凤 on 15/4/11.
// Copyright (c) 2015年 William. All rights reserved.
//
#import "CustomDatabase.h"
@implementation CustomDatabase
//创建(打开)数据库
- (void)createSQL
{
//设置数据库文件夹
NSArray *library = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
NSString *strSQL = [[library objectAtIndex:0] stringByAppendingPathComponent:@"sqlite"];
BOOL directory = YES;
if (![[NSFileManager defaultManager] fileExistsAtPath:strSQL isDirectory:&directory])
{
[[NSFileManager defaultManager] createDirectoryAtPath:strSQL withIntermediateDirectories:NO attributes:nil error:nil];
}
//获取并打开数据库(没有时自动创建)
if (!self.db)
{
//设置数据库文件
NSString *strTab = [strSQL stringByAppendingPathComponent:@"commodity1.db"];
self.db = [FMDatabase databaseWithPath:strTab];
}
[self.db setShouldCacheStatements:YES];//一般设置数据库使用缓存,提高效率
self.resultSet = [NSMutableArray array];
}
//创建表
- (void)createTab:(NSString *)table CreatTable:(NSString*)sql
{
if (!self.db) [self createSQL];//创建数据库
if (![self.db open]) return;
//创建表(FMDB中只有update和query操作,除了查询其他都是update操作)
if (!sql) return;
if (![self.db tableExists:table])
{
[self.db executeUpdate:sql];
NSLog(@"创建表成功");
}
else
{
NSLog(@"表已经存在");
}
}
//删除
- (void)deleteTab:(NSString *)table
{
if (!self.db) [self createSQL];//创建数据库
if (![self.db open]) return;
if (![self.db tableExists:table]) return;
//删除数据
BOOL delete = [self.db executeUpdate:[NSString stringWithFormat:@"delete from %@",table]];
if (delete) {
NSLog(@"删除表成功");
}
[self.db close];//关闭数据库
}
//插入
- (BOOL)insertTab:(NSString *)table CreatTable:(NSString *)setup SQL:(NSString*)sql, ...
{
if (!self.db) [self createSQL];//创建数据库
if (![self.db open]) return NO;
if (![self.db tableExists:table]) [self createTab:table CreatTable:setup];
//插入数据
if (!sql) return NO;
NSMutableArray *argsArray = [[NSMutableArray alloc] init];
id arg;
va_list arglist;
va_start(arglist, sql);
NSString *formatStr = [[NSString alloc] initWithFormat:sql arguments:arglist];
for (arg=va_arg(arglist,id); arg!=nil;)
{
[argsArray addObject:arg];
(arg = va_arg(arglist,id));
}
va_end(arglist);
BOOL inseart = NO;
switch (argsArray.count) {
case 2:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1]];
break;
case 3:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2]];
break;
case 4:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2],argsArray[3]];
break;
case 5:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2],argsArray[3],argsArray[4]];
break;
case 6:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2],argsArray[3],argsArray[4],argsArray[5]];
break;
case 7:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2],argsArray[3],argsArray[4],argsArray[5],argsArray[6]];
break;
case 8:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2],argsArray[3],argsArray[4],argsArray[5],argsArray[6],argsArray[7]];
break;
case 9:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2],argsArray[3],argsArray[4],argsArray[5],argsArray[6],argsArray[7],argsArray[8]];
break;
case 10:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2],argsArray[3],argsArray[4],argsArray[5],argsArray[6],argsArray[7],argsArray[8],argsArray[9]];
break;
case 11:
inseart = [self.db executeUpdate:formatStr,argsArray[0],argsArray[1],argsArray[2],argsArray[3],argsArray[4],argsArray[5],argsArray[6],argsArray[7],argsArray[8],argsArray[9],argsArray[10]];
break;
default:
break;
}
[self.db close];//关闭数据库
return inseart;
}
//修改
- (BOOL)updateTab:(NSString *)table CreatTable:(NSString *)setup SQL:(NSString *)sql
{
if (!self.db) [self createSQL];//创建数据库
if (![self.db open]) return NO;
if (![self.db tableExists:table]) [self createTab:table CreatTable:setup];
//修改数据
BOOL update = [self.db executeUpdate:sql];
[self.db close];//关闭数据库
return update;
}
//查询
- (void)queryTab:(NSString *)table CreatTable:(NSString *)setup SQL:(NSString *)sql
{
if (!self.db) [self createSQL];//创建数据库
if (![self.db open]) return;
if (![self.db tableExists:table]) [self createTab:table CreatTable:setup];
NSLog(@"-------------------------------------------------------------------------------------------");
//查询数据
[self.resultSet removeAllObjects];
FMResultSet *resultSet = [self.db executeQuery:sql];
while ([resultSet next])
{
[self.resultSet addObject:[resultSet resultDictionary]];
}
[self.db close];//关闭数据库
}
@end