iOS数据库之使用FMDB的实际操作（简单，易懂）

2016-11-09 本文已影响323人艳晓

数据库简介

数据库：存储数据的仓库(以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗长度、与应用程序彼此独立的数据集合)
最常用的数据库, 关系型数据库, 比如My SQL, Qracle, SQL
移动端考虑到硬件配置, 常使用轻量级数据库, 比如SQLite, 它的体积小, 大部分的管理操作都支持

一、添加框架及所需库文件

数据库：添加框架及所需库文件

添加必须库

二、实际情况分析

1、需要从url获取数据存入本地数据库。
2、本地数据库中数据可以进行更新、替换。
3、条数众多，目前只用一个简单待测的格式，已经确定是数组-字典格式

三、实际使用

创建一个必须的model，方便存储、获取数据
创建一个数据库单例子，对外提供，增删改查的四个方法，方法中具体的语句有我们根据实际情况确定。
注：使用FMDB，语句是不需要改变的，还是sql语句

模型
PhotoModel.h
#import <Foundation/Foundation.h>

@interface PhotoModel : NSObject

@property (nonatomic, assign) NSInteger   ID;
@property (nonatomic, copy) NSString * CSM;
@property (nonatomic, copy) NSString * CSZ;
@property (nonatomic, copy) NSString * CSSM;
@property (nonatomic, copy) NSString * ZPLX;
@property (nonatomic, copy) NSString * DQDM;
@property (nonatomic, copy) NSString * SFYX;

@end

PhotoModel.m
#import "PhotoModel.h"
#import "MJExtension.h"

@implementation PhotoModel

//一句话将所有的子文件全部归档反归档(MJExtension)
MJCodingImplementation

@end

数据库
ZYXDatabase.h
#import <Foundation/Foundation.h>
#import "PhotoModel.h"
#define PhotoDatabase [ZYXDatabase shareInstance]

// 网络要求的数据库
@interface ZYXDatabase : NSObject

+(instancetype)shareInstance;

//创建照片标准表
-(void)createStandardsDatabase;
//增
-(void)addPhotoModel:(PhotoModel *)photoModel;
//删
-(void)deletePhotoModel:(PhotoModel *)photoModel;
- (void)deleteAll;
//改
-(void)updatePhotoModel:(PhotoModel *)photoModel;
//查
-(PhotoModel *)getPhotoModel:(PhotoModel *)photoModel;

@end

ZYXDatabase.m
#import "ZYXDatabase.h"
#import "FMDB.h"

@interface ZYXDatabase ()
@property(nonatomic, strong) NSString *dataBasePath;
@property(nonatomic, strong) FMDatabase *standardDatabase;
@end

@implementation ZYXDatabase

+(instancetype)shareInstance
{
    static dispatch_once_t onceToken;
    static ZYXDatabase * standardDatabase = nil;
    dispatch_once(&onceToken, ^{
    
        standardDatabase = [[ZYXDatabase alloc]init];
    
        [standardDatabase createStandardsDatabase];
    
    });

    return standardDatabase;

}

//数据库地址
-(NSString *)dataBasePath{

    if (_dataBasePath == nil) {
    
//        NSString *pathes = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
        NSString * pathes = @"/Users/gonganxinxi/Desktop";

        _dataBasePath = [pathes stringByAppendingPathComponent:@"standardDatabase.sqlite"];
    
    }

    return _dataBasePath;

}

//创建照片标准表
-(void)createStandardsDatabase
{
    self.standardDatabase = [FMDatabase databaseWithPath:self.dataBasePath];

    if ([self.standardDatabase open]) {
    
        NSLog(@"数据库打开成功");
    
        NSString *sqString = @"create table if not exists standard (s_id integer primary key autoincrement not NULL, s_clcsID integer, s_clcsCSM text,s_clcsCSZ text,s_clcsCSSM text,s_clcsZPLX text,s_clcsDQDM text,s_clcsSFYX text,s_clcxPhotoModel blob)";
    
    
        BOOL result = [self.standardDatabase executeUpdate:sqString];
    
        if (result) {
        
            NSLog(@"创建表成功");
        
        }else{
            NSLog(@"创建表失败");
        
        }
    
    }

}

//增
-(void)addPhotoModel:(PhotoModel *)photoModel
{

    PhotoModel * model = photoModel;

    NSLog(@"数据库%ld, %@, %@, %@, %@, %@, %@", (long)model.ID, model.CSM, model.CSZ, model.CSSM, model.ZPLX,model.DQDM,model.SFYX);
    NSData *data = [NSKeyedArchiver archivedDataWithRootObject:photoModel];

    BOOL result = [self.standardDatabase executeUpdateWithFormat:@"insert into standard (s_clcsID, s_clcsCSM, s_clcsCSZ, s_clcsCSSM , s_clcsZPLX, s_clcsDQDM , s_clcsSFYX , s_clcxPhotoModel) values (%d, %@, %@, %@, %@, %@, %@, %@)", photoModel.ID, photoModel.CSM, photoModel.CSZ, photoModel.CSSM, photoModel.ZPLX,photoModel.DQDM,photoModel.SFYX,data];


    if (result) {
        NSLog(@"插入数据成功");
    }else{
        NSLog(@"插入数据失败");
    
    }
}
//删

-(void)deletePhotoModel:(PhotoModel *)photoModel
{   
    PhotoModel * model = photoModel;

    BOOL result = [self.standardDatabase executeUpdateWithFormat:@"delete from standard where s_clcsZPLX = %@ and s_clcsDQDM = %@ ",  photoModel.ZPLX,photoModel.DQDM];

    if (result) {
        NSLog(@"删除数据成功");
    }else{
        NSLog(@"删除数据失败");
    }
}

- (void)deleteAll
{
    BOOL result = [self.standardDatabase executeUpdateWithFormat:@"delete from standard"];

    if (result) {
        NSLog(@"删除全部数据成功");
    }else{
        NSLog(@"删除全部数据失败");        
    }    
}

- (void)deletetable
{
    BOOL result = [self.standardDatabase executeUpdateWithFormat:@"drop table if exists standard"];
    
    if (result) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");    
    }
}

//改
-(void)updatePhotoModel:(PhotoModel *)photoModel
{
    NSData *data = [NSKeyedArchiver archivedDataWithRootObject:photoModel];

    BOOL result = [self.standardDatabase executeUpdateWithFormat:@"update standard set s_clcsID = %d, s_clcsCSM = %@,s_clcsCSZ = %@, s_clcsCSSM  = %@,s_clcsDQDM  = %@,s_clcsSFYX  = %@,s_clcsPhotoModel = %@ where s_clcsZPLX = 2 and s_clcsCSM = %@", photoModel.ID, photoModel.CSM, photoModel.CSZ, photoModel.CSSM, photoModel.DQDM,photoModel.SFYX,photoModel.ZPLX,data,photoModel.CSM];
     
    if (result) {
        NSLog(@"更改数据成功");
    }else{
        NSLog(@"更改数据失败");        
    }

}

//查 : 根据需要，查到响应的model返回，我们可以从中获取model的属性值
-(PhotoModel *)selectPhotoModel:(PhotoModel *)photoModel
{
    PhotoModel * returnModel = nil;

    FMResultSet *result = [self.standardDatabase executeQueryWithFormat:@"select s_clcxPhotoModel from standard where s_clcsZPLX = %@ ", photoModel.ZPLX];

    while ([result next]) {
    
        NSData *data = [result dataForColumn:@"s_clcxPhotoModel"];
    
        returnModel = [NSKeyedUnarchiver unarchiveObjectWithData:data];
           
    }
    
    return returnModel;
}

@end

ViewController.m实际应用

@property (nonatomic, strong) ZYXDatabase * db;

首先创建数据库，创建表

self.db = PhotoDatabase;

其次，根据实际需要，在应该的位置上进行数据的插入、删除、更改、查询

- (void)touchesBegan:(NSSet<UITouch *> *)touches withEvent:(UIEvent *)event
{
    [self getPhotoRequires];
}

// 网络请求参数信息
- (void)getPhotoRequires{

   //    [self.db deleteAll];
    NSString * urlString = @"http://***.com/getparaforapp/getClcsData";
    NSURL * url = [NSURL URLWithString:urlString];
    NSMutableURLRequest * request = [NSMutableURLRequest requestWithURL:url];
    NSString * bodyStr  = [NSString stringWithFormat:@"dqdm=360100&zplx=2"];
    [request setHTTPMethod:@"post"];  
    NSData * bodyData = [bodyStr dataUsingEncoding:NSUTF8StringEncoding];// 和服务器之间传的是data,需要将字符串转化成data
    [request setHTTPBody:bodyData];
    NSURLSession * session = [NSURLSession sharedSession];
    NSURLSessionDataTask * task = [session dataTaskWithRequest:request completionHandler:^(NSData * _Nullable data, NSURLResponse * _Nullable response, NSError * _Nullable error) {

        NSArray * array = [NSJSONSerialization JSONObjectWithData:data options:(NSJSONReadingAllowFragments) error:nil];

        for (NSDictionary *dict in array) {
        
            PhotoModel * model = [[PhotoModel alloc] init];
            [model setValuesForKeysWithDictionary:dict];
            NSLog(@"测试1：%ld, %@, %@, %@, %@, %@, %@", (long)model.ID, model.CSM, model.CSZ, model.CSSM, model.ZPLX,model.DQDM,model.SFYX);
        
            // 删除
            [self.db deletePhotoModel:model];
            // 插入
            [self.db addPhotoModel:model];
            // 查询
            PhotoModel * model1 = [self.db selectPhotoModel:model];
            NSLog(@"查询测试：%ld, %@, %@, %@, %@, %@, %@", (long)model1.ID, model1.CSM, model1.CSZ, model1.CSSM, model1.ZPLX,model1.DQDM,model1.SFYX);
            // 更改
            [self.db updatePhotoModel:model];

        }
    }];

    //恢复
    [task resume];

}