最近項目中用到本地數(shù)據(jù)庫存儲數(shù)據(jù)，將具體的實現(xiàn)記錄一下。
1.數(shù)據(jù)庫的創(chuàng)建，創(chuàng)建了一個單例文件。.h文件代碼如下。
//查詢數(shù)據(jù)庫是否存在
-(BOOL)selectDBByName:(NSString*)cardno;
//刪除數(shù)據(jù)庫
-(void)detDBByName:(NSString*)cardno;
//獲取數(shù)據(jù)庫位置
-(NSString *)databaseFilePathByCardno:(NSString*) cardno;
//創(chuàng)建數(shù)據(jù)庫
-(void)creatDatabaseByCardno:(NSString*) cardno;
//創(chuàng)建表
-(BOOL)createTableByCardno:(NSString*)cardno;
//插入用戶數(shù)據(jù)
-(void) insertToUserTableByModel:(MainCardModel*) model Cardno:(NSString*)cardno Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block;
//更新用戶數(shù)據(jù)
-(void) updateToUserTableByModel:(MainCardModel*) model ID:(NSInteger)ID Cardno:(NSString*)cardno Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block;
//根據(jù)表的名字判斷表是否存在
-(BOOL)isTableExistWithTableName:(NSString *)tableName;
//清空所有表
-(void) deleteAllTableByCardno:(NSString*)cardno;
//刪除某表某數(shù)據(jù)
-(void) deleteTableByCardno:(NSString*)cardno TableName:(NSString*)tableName FieldKey:(NSString*)fieldkey FieldValue:(NSString*)fieldValue Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block ;
//刪除某表數(shù)據(jù)
-(void) deleteSomeTableByCardno:(NSString*)cardno TableName:(NSString*)tableName;
//刪除數(shù)據(jù)庫
- (void)deleteDatabse:(NSString*)cardno;
//關(guān)閉數(shù)據(jù)庫
-(void) dbClose;
2. .m是具體的實現(xiàn)方法。如下：
//查詢數(shù)據(jù)庫是否存在
-(BOOL)selectDBByName:(NSString*)cardno{
NSString *porjectDir = [NSString stringWithFormat:@"%@/Documents/%@db.sqlite", NSHomeDirectory(),cardno];
BOOL isDir = NO;
NSFileManager *fileManager = [NSFileManager defaultManager];
BOOL existed = [fileManager fileExistsAtPath:porjectDir isDirectory:&isDir];
if ( !(isDir == YES && existed == YES) )
{
return YES;
}else{
return NO;
}
}
//刪除數(shù)據(jù)庫
-(void)detDBByName:(NSString*)cardno{
NSString *porjectDir = [NSString stringWithFormat:@"%@/Documents/%@db.sqlite", NSHomeDirectory(), cardno];
NSFileManager *fileManager = [NSFileManager defaultManager];
[fileManager removeItemAtPath:porjectDir error:nil];
}
//獲取數(shù)據(jù)庫位置
-(NSString *)databaseFilePathByCardno:(NSString*) cardno{
NSArray *filePath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentPath = [filePath objectAtIndex:0];
NSString *dbFilePath = [documentPath stringByAppendingPathComponent:[NSString stringWithFormat:@"%@db.sqlite",cardno]];
NSLog(@"fmdb數(shù)據(jù)庫地址:%@",dbFilePath);
return dbFilePath;
}
//創(chuàng)建數(shù)據(jù)庫
-(void)creatDatabaseByCardno:(NSString*) cardno
{
self.db = [FMDatabase databaseWithPath:[self databaseFilePathByCardno:cardno]];
}
//根據(jù)表的名字判斷表是否存在
-(BOOL)isTableExistWithTableName:(NSString *)tableName
{
FMResultSet *set = [self.db executeQuery:@"select count(*) as count from sqlite_master where type = 'table' and name = ?",tableName];
if ([set next]) {
NSInteger count = [set intForColumn:@"count"];
if (0 == count) {
return NO;
}
return YES;
}
return NO;
}
// 刪除數(shù)據(jù)庫
- (void)deleteDatabse:(NSString*)cardno
{
if (!self.db) {
[self creatDatabaseByCardno:cardno];
}
if ([self.db open]) {
BOOL res = [self.db executeUpdate:@"drop table user"];
if (!res) {
NSLog(@"error when droping db table");
} else {
NSLog(@"success to droping db table");
}
[self.db executeUpdate:@"drop table user"];
}
}
//創(chuàng)建表
-(BOOL)createTableByCardno:(NSString*)cardno{
//先判斷數(shù)據(jù)庫是否存在，如果不存在，創(chuàng)建數(shù)據(jù)庫
if (!self.db) {
[self creatDatabaseByCardno:cardno];
}
//為數(shù)據(jù)庫設(shè)置緩存，提高查詢效率
[self.db setShouldCacheStatements:YES];
if ([self.db open]) {
//判斷數(shù)據(jù)庫中是否已經(jīng)存在這個表，如果不存在則創(chuàng)建該表
if(![self isTableExistWithTableName:@"user"]){
//用戶詳情表
NSString* userCreate = @"CREATE table user(CustomerName TEXT, position TEXT,name TEXT,phone TEXT,pictureName TEXT,fanpictureName TEXT,id INTEGER PRIMARY KEY AUTOINCREMENT)";
BOOL re = [self.db executeUpdate:userCreate];
if (!re) {
NSLog(@"error when creating db user");
} else {
NSLog(@"success to creating db user");
}
return NO;
}
}
return YES;
}
//插入用戶數(shù)據(jù)
-(void) insertToUserTableByModel:(MainCardModel*) model Cardno:(NSString*)cardno Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block{
//先判斷數(shù)據(jù)庫是否存在，如果不存在，創(chuàng)建數(shù)據(jù)庫
if (!self.db) {
[self creatDatabaseByCardno:cardno];
}
if ([self.db open]) {
NSString* sqlInsert = [NSString stringWithFormat:@"INSERT INTO user(CustomerName, position,name,phone,pictureName,fanpictureName) VALUES('%@','%@','%@','%@','%@','%@')",model.CustomerName,model.position,model.name,model.phone,model.pictureName,model.fanpictureName];
BOOL res = [self.db executeUpdate:sqlInsert];
if (!res) {
NSLog(@"error when insert db user");
NSInteger result = 2;
NSString * errmeg = @"客戶數(shù)據(jù)插入失敗";
if (block) {
block(errmeg,result,nil);
}
} else {
NSLog(@"success to insert db user");
NSInteger result = 1;
NSString * errmeg = @"客戶數(shù)據(jù)插入成功";
if (block) {
block(errmeg,result,nil);
}
}
}
}
//更新用戶數(shù)據(jù)
-(void) updateToUserTableByModel:(MainCardModel*) model ID:(NSInteger)ID Cardno:(NSString*)cardno Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block
{
//先判斷數(shù)據(jù)庫是否存在，如果不存在，創(chuàng)建數(shù)據(jù)庫
if (!self.db) {
[self creatDatabaseByCardno:cardno];
}
if ([self.db open]) {
NSString* sqlInsert = [NSString stringWithFormat:@"update? user set CustomerName = '%@', position = '%@',name = '%@' ,phone = '%@' ,pictureName = '%@' ,fanpictureName = '%@' where id = %ld? ",model.CustomerName,model.position,model.name,model.phone,model.pictureName,model.fanpictureName,ID];
BOOL res = [self.db executeUpdate:sqlInsert];
if (!res) {
NSLog(@"error when update db user");
NSInteger result = 2;
NSString * errmeg = @"客戶數(shù)據(jù)更新失敗";
if (block) {
block(errmeg,result,nil);
}
} else {
NSLog(@"success to update db user");
NSInteger result = 1;
NSString * errmeg = @"客戶數(shù)據(jù)更新成功";
if (block) {
block(errmeg,result,nil);
}
}
}
}
//清空所有表
-(void) deleteAllTableByCardno:(NSString*)cardno{
if (!self.db) {
[self creatDatabaseByCardno:cardno];
}
if ([self.db open]) {
//用戶表
[self.db executeUpdate:@"delete from user"];
}
}
//刪除某表某數(shù)據(jù)
-(void) deleteTableByCardno:(NSString*)cardno TableName:(NSString*)tableName FieldKey:(NSString*)fieldkey FieldValue:(NSString*)fieldValue? Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block {
if (!self.db) {
[self creatDatabaseByCardno:cardno];
}
if ([self.db open]) {
NSString* delSql = [NSString stringWithFormat:@"delete from %@ where %@ = %@",tableName,fieldkey,fieldValue];
BOOL res = [self.db executeUpdate:delSql];
if (!res) {
NSLog(@"error when delete db user");
NSInteger result = 2;
NSString * errmeg = @"客戶數(shù)據(jù)刪除失敗";
if (block) {
block(errmeg,result,nil);
}
}else
{
NSLog(@"success to delete db user");
NSInteger result = 1;
NSString * errmeg = @"客戶數(shù)據(jù)刪除成功";
if (block) {
block(errmeg,result,nil);
}
}
}
}
//刪除某表數(shù)據(jù)
-(void) deleteSomeTableByCardno:(NSString*)cardno TableName:(NSString*)tableName{
if (!self.db) {
[self creatDatabaseByCardno:cardno];
}
if ([self.db open]) {
NSString* delSql = [NSString stringWithFormat:@"delete from %@",tableName];
[self.db executeUpdate:delSql];
}
}
//關(guān)閉數(shù)據(jù)庫
-(void) dbClose{
[self.db close];
}
3.調(diào)用方法對數(shù)據(jù)庫進(jìn)行操作。
（1）創(chuàng)建數(shù)據(jù)庫
[manger? creatDatabaseByCardno:@"001"];
? (2)創(chuàng)建表
[manger createTableByCardno:@"001"];
（3）調(diào)用新增方法，插入數(shù)據(jù)到數(shù)據(jù)庫中。
[manger insertToUserTableByModel:model Cardno:@"001" Block:^(NSString *errorMsg, NSInteger result, NSError *error) {
if (result == 1) {
NSLog(@"插入成功");
}else{
NSLog(@"插入失敗");
}
}]
(4)查詢數(shù)據(jù)庫的數(shù)據(jù)
-(NSMutableArray*)getUserInfo:(NSString*)str{
if (!manger.db) {
[manger creatDatabaseByCardno:@"001"];
}
NSMutableArray *ary ;
if ([manger.db open]){
NSString* selectSql = @"";
if (str.length>0) {
selectSql = [NSString stringWithFormat:@"select * from user where CustomerName like '%%%@%%' or position like? '%%%@%%'? or? name like '%%%@%%' ",str,str,str];
}else{
selectSql = @"select * from user";
}
ary = [[NSMutableArray alloc]init];
FMResultSet * rs = [manger.db executeQuery:selectSql];
while ([rs next]) {
MainCardModel* model = [[MainCardModel alloc] init];
model.ID = [[rs stringForColumn:@"id"]integerValue];
model.CustomerName = [rs stringForColumn:@"CustomerName"];
model.position = [rs stringForColumn:@"position"];
model.phone = [rs stringForColumn:@"phone"];
model.name = [rs stringForColumn:@"name"];
model.pictureName = [rs stringForColumn:@"pictureName"];
model.fanpictureName = [rs stringForColumn:@"fanpictureName"];
[ary addObject:model];
}
}
return ary;
}
（5）根據(jù)ID刪除某一條數(shù)據(jù)
[manger deleteTableByCardno:@"001" TableName:@"user" FieldKey:@"ID" FieldValue:str Block:^(NSString *errorMsg, NSInteger result, NSError *error) {
if (result == 1) {
NSLog(@"刪除成功");
}];
}else
{
NSLog(@"刪除失敗");
}
