数据库简介
数据库：存储数据的仓库(以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗长度、与应用程序彼此独立的数据集合)
最常用的数据库, 关系型数据库, 比如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];
}
四、查看数据库是否存在数据
下图，是我们查看数据库中存在的数据截图、
一、我们直接使用SQLiteManager查看数据库文件
SQLiteManager
使用SQLiteManager查看数据库文件
二、使用fireFox浏览器中的插件
fireFox浏览器
查找使用SQLite Manager
使用SQLite Manager找到数据库文件，选取并打开
查看数据表中内容1
查看数据表中内容2
