最近项目中用到本地数据库存储数据，将具体的实现记录一下。
1.数据库的创建，创建了一个单例文件。.h文件代码如下。
//查询数据库是否存在
-(BOOL)selectDBByName:(NSString*)cardno;
//删除数据库
-(void)detDBByName:(NSString*)cardno;
//获取数据库位置
-(NSString *)databaseFilePathByCardno:(NSString*) cardno;
//创建数据库
-(void)creatDatabaseByCardno:(NSString*) cardno;
//创建表
-(BOOL)createTableByCardno:(NSString*)cardno;
//插入用户数据
-(void) insertToUserTableByModel:(MainCardModel*) model Cardno:(NSString*)cardno Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block;
//更新用户数据
-(void) updateToUserTableByModel:(MainCardModel*) model ID:(NSInteger)ID Cardno:(NSString*)cardno Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block;
//根据表的名字判断表是否存在
-(BOOL)isTableExistWithTableName:(NSString *)tableName;
//清空所有表
-(void) deleteAllTableByCardno:(NSString*)cardno;
//删除某表某数据
-(void) deleteTableByCardno:(NSString*)cardno TableName:(NSString*)tableName FieldKey:(NSString*)fieldkey FieldValue:(NSString*)fieldValue Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block ;
//删除某表数据
-(void) deleteSomeTableByCardno:(NSString*)cardno TableName:(NSString*)tableName;
//删除数据库
- (void)deleteDatabse:(NSString*)cardno;
//关闭数据库
-(void) dbClose;
2. .m是具体的实现方法。如下：
//查询数据库是否存在
-(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;
}
}
//删除数据库
-(void)detDBByName:(NSString*)cardno{
NSString *porjectDir = [NSString stringWithFormat:@"%@/Documents/%@db.sqlite", NSHomeDirectory(), cardno];
NSFileManager *fileManager = [NSFileManager defaultManager];
[fileManager removeItemAtPath:porjectDir error:nil];
}
//获取数据库位置
-(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数据库地址:%@",dbFilePath);
return dbFilePath;
}
//创建数据库
-(void)creatDatabaseByCardno:(NSString*) cardno
{
self.db = [FMDatabase databaseWithPath:[self databaseFilePathByCardno:cardno]];
}
//根据表的名字判断表是否存在
-(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;
}
// 删除数据库
- (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"];
}
}
//创建表
-(BOOL)createTableByCardno:(NSString*)cardno{
//先判断数据库是否存在，如果不存在，创建数据库
if (!self.db) {
[self creatDatabaseByCardno:cardno];
}
//为数据库设置缓存，提高查询效率
[self.db setShouldCacheStatements:YES];
if ([self.db open]) {
//判断数据库中是否已经存在这个表，如果不存在则创建该表
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;
}
//插入用户数据
-(void) insertToUserTableByModel:(MainCardModel*) model Cardno:(NSString*)cardno Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block{
//先判断数据库是否存在，如果不存在，创建数据库
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 = @"客户数据插入失败";
if (block) {
block(errmeg,result,nil);
}
} else {
NSLog(@"success to insert db user");
NSInteger result = 1;
NSString * errmeg = @"客户数据插入成功";
if (block) {
block(errmeg,result,nil);
}
}
}
}
//更新用户数据
-(void) updateToUserTableByModel:(MainCardModel*) model ID:(NSInteger)ID Cardno:(NSString*)cardno Block:(void (^)(NSString *errorMsg,NSInteger result, NSError *error))block
{
//先判断数据库是否存在，如果不存在，创建数据库
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 = @"客户数据更新失败";
if (block) {
block(errmeg,result,nil);
}
} else {
NSLog(@"success to update db user");
NSInteger result = 1;
NSString * errmeg = @"客户数据更新成功";
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"];
}
}
//删除某表某数据
-(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 = @"客户数据删除失败";
if (block) {
block(errmeg,result,nil);
}
}else
{
NSLog(@"success to delete db user");
NSInteger result = 1;
NSString * errmeg = @"客户数据删除成功";
if (block) {
block(errmeg,result,nil);
}
}
}
}
//删除某表数据
-(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];
}
}
//关闭数据库
-(void) dbClose{
[self.db close];
}
3.调用方法对数据库进行操作。
（1）创建数据库
[manger creatDatabaseByCardno:@"001"];
(2)创建表
[manger createTableByCardno:@"001"];
（3）调用新增方法，插入数据到数据库中。
[manger insertToUserTableByModel:model Cardno:@"001" Block:^(NSString *errorMsg, NSInteger result, NSError *error) {
if (result == 1) {
NSLog(@"插入成功");
}else{
NSLog(@"插入失败");
}
}]
(4)查询数据库的数据
-(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）根据ID删除某一条数据
[manger deleteTableByCardno:@"001" TableName:@"user" FieldKey:@"ID" FieldValue:str Block:^(NSString *errorMsg, NSInteger result, NSError *error) {
if (result == 1) {
NSLog(@"删除成功");
}];
}else
{
NSLog(@"删除失败");
}
