OcFMDB+SQL语句

FMDB使用介绍
iOS中原生的SQLite API在使用上相当不友好，在使用时，非常不便。于是，就出现了一系列将SQLite API进行封装的库FMDB (https://github.com/ccgus/fmdb) 是一款简洁、易用的封装库，这一篇文章简单介绍下FMDB的使用。
一、简单说明
1.什么是FMDB
FMDB是iOS平台的SQLite数据库框架
FMDB以OC的方式封装了SQLite的C语言API

2.FMDB的优点
使用起来更加面向对象，省去了很多麻烦、冗余的c语言代码
对比苹果自带的Core Data框架，更加轻量级和灵活
提供了多线程安全的数据库操作方法，有效地防止数据混乱

FMDB PK Sqlite
优点:
对多线程的并发操作进行处理，所以是线程安全的；
以OC的方式封装了SQLite的C语言API，使用起来更加的方便；
FMDB是轻量级的框架，使用灵活。

缺点:
因为它是OC的语言封装的，只能在ios开发的时候使用，所以在实现跨平台操作的时候存在局限性。

3.FMDB的github地址
https://github.com/ccgus/fmdb

二、核心类
FMDB有三个主要的类
（1）FMDatabase
一个FMDatabase对象就代表一个单独的SQLite数据库
用来执行SQL语句

（2）FMResultSet
使用FMDatabase执行查询后的结果集

（3）FMDatabaseQueue
用于在多线程中执行多个查询或更新，它是线程安全的

20170807125929613.gif
20170807130237340.png

1.AppDelegate.m 中的代码

#import "AppDelegate.h"
#import "ViewController.h"//主控制器
@interface AppDelegate ()
@end
@implementation AppDelegate
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
//初始化
ViewController *vc = [[ViewController alloc]init];
//导航控制器
UINavigationController *nvc = [[UINavigationController alloc]initWithRootViewController:vc];
//添加图
self.window.rootViewCOntroller= nvc;
return YES;
}


2.导入第三方数据库 FMDB 框架包
下载地址:https://github.com/ccgus/fmdb
** 注意:要导入 libsqlite3.tbd 包 (详情请看上面图3)**

3.在 Other 模块中添加业务处理层处理 SQLite 语句
注意:如果现在拷贝到项目会报错&＃8211;>原因是其他 MVC 模块中的类没有实现

控制器1 .h:

#import
#import "Model.h"
#import "FMDatabase.h"
@interface LoadData : NSObject
//单列类
+(instancetype)sharlLoadData;
//添加元素
-(void)AddsharlLoadData:(Model *)model;
//查询
-(NSMutableArray *)Marr;
//删除元素
-(void)deleteharlLoadData:(Model *)model;
//修改元素
-(void)UPsharlLoadData:(Model *)model;
@end


控制器1 .M:

#import "LoadData.h"
static LoadData *ld =nil;
static FMDatabase *fate;
@implementation LoadData
//单列类
+(instancetype)sharlLoadData{
//静态
staticdispatch_once_t oneet;
//初始化
dispatch_once(&oneet, ^{
ld = [[LoadData alloc]init];
//定义初始化
[ld initA];
});
//返回值
return ld;
}
//初始化
+(instancetype)allocWithZone:(struct_NSZone *)zone{
if (!ld) {
//初始化
ld = [super allocWithZone:zone];
}
return ld;
}
//浅复制
-(id)copy{
return self;
}
//深复制
-(id)mutableCopy{
return self;
}
//初始化数据库
-(void)initA{
//创建沙盒
NSString *Ste = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask,YES)objectAtIndex:0];
//定义文件名
NSString *path = [Ste stringByAppendingPathComponent:@"HousingInfo.sqlite"];
//初始化
fate = [[FMDatabase alloc]initWithPath:path];
//判断
if ([fate open]) {
//初始化
[fate executeUpdate:@"create table class (ID integer primary key, fieldMail text, fieldAddress text , fieldConsignee text , fieldPhone text , fieldNote text)"];
[fate close];
NSLog(@"成功");
}else{
NSLog(@"失败");
}
}
//添加元素
-(void)AddsharlLoadData:(Model *)model{
//开始
[fate open];
//初始化
NSString *str = [NSString stringWithFormat:@"insert into class values (null , '%@','%@','%@','%@','%@')",model.fieldMail,model.fieldAddress,
model.fieldConsignee,model.fieldPhone,model.fieldNote];
//BOOL值接受
BOOL ii = [fate executeUpdate:str];
//判断
if (ii) {
NSLog(@"成功");
}else{
NSLog(@"失败");
}
//关闭
[fate close];
}
//查询
-(NSMutableArray *)Marr{
//初始化
NSMutableArray *marr = [NSMutableArray new];
//开始
[fateopen];
//初始化
FMResultSet *Set = [[FMResultSet alloc]init];
//使用set接受
Set = [fate executeQuery:@"select * from class"];
//判断
while ([Setnext]) {
//初始化
Model *mm = [Modelnew];
//链接
mm.fieldMail = [Set stringForColumn:@"fieldMail"];
mm.fieldAddress = [Set stringForColumn:@"fieldAddress"];
mm.fieldCOnsignee= [Set stringForColumn:@"fieldConsignee"];
mm.fieldPhOne= [Set stringForColumn:@"fieldPhone"];
mm.fieldNote = [Set stringForColumn:@"fieldNote"];
mm.ID = [Set intForColumn:@"ID"];
//添加到数组
[marr addObject:mm];
}
//关闭
[fate close];
//返回值
return marr;
}
//删除元素
-(void)deleteharlLoadData:(Model *)model{
//开始
[fate open];
//初始化
NSString *str = [NSString stringWithFormat:@"delete from class where ID = '%ld' ",model.ID];
//BOOL值接受
BOOL ii = [fate executeUpdate:str];
//判断
if (ii) {
NSLog(@"成功");
}else{
NSLog(@"失败");
}
//关闭
[fate close];
}
//修改元素
-(void)UPsharlLoadData:(Model *)model{
//开始
[fate open];
//初始化
NSString *str = [NSString stringWithFormat:@"update class set fieldMail = '%@',fieldAddress = '%@',fieldCOnsignee= '%@',fieldPhOne= '%@',fieldNote = '%@' where ID = '%ld'",model.fieldMail,model.fieldAddress,model.fieldConsignee,
model.fieldPhone,model.fieldNote,model.ID]
//BOOL值接受
BOOL ii = [fate executeUpdate:str];
//判断
if (ii) {
NSLog(@"成功");
}else{
NSLog(@"失败");
}
//关闭
[fate close];
}
@end


4.MVC&＃8211;>C模块
控制器1 -主控制器 .m:

#import "ViewController.h"
#import "LoadData.h"//业务处理 SQLite
#import "Model.h" //保存数据
#import "MyTableViewCell.h"//主控制器 cell
#import "UpViewController.h"//修改信息控制器
#import "AddViewController.h"//添加信息控制器
@interface ViewController (){
UITableView *tabele;
NSMutableArray *marr;
}
@end
@implementation ViewController
//将要显示
-(void)viewWillAppear:(BOOL)animated{
//查询
marr = [[LoadData sharlLoadData]Marr];
//刷新
[tabele reloadData ];
}
- (void)viewDidLoad {
[super viewDidLoad];
//定义标题
self.title =@"信息查询系统";
//初始化
tabele = [[UITableView alloc]initWithFrame:self.view.framestyle:
UITableViewStylePlain];
//添加协议
tabele.delegate =self;
tabele.dataSource =self;
//添加到试图上
[self.view addSubview:tabele];
//定义按钮
UIBarButtonItem *right = [[UIBarButtonItem alloc]
initWithTitle:@"添加"style:UIBarButtonItemStylePlain
target:selfaction:@selector(click)];
//添加到导航调试
self.navigationItem.rightBarButtOnItem= right;}
-(void)click{
//初始化
AddViewController *add = [AddViewController new];
//跳转
[self.navigationController pushViewController:addanimated:YES];
}
//行数
-(NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section{
returnmarr.count;
}
-(UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath{
static NSString *cellId = @"CELLID";
//初始化
MyTableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:cellId];
//复用池
if (!cell) {
//初始化‘
cell = [[MyTableViewCell alloc]initWithStyle:
UITableViewCellStyleSubtitlereuseIdentifier:cellId];
}
//初始化
Model *mm =marr[indexPath.row];
//添加到表格上
cell.fieldMail.text = mm.fieldMail;
cell.fieldAddress.text = mm.fieldAddress;
cell.fieldConsignee.text = mm.fieldConsignee;
cell.fieldPhone.text = mm.fieldPhone;
cell.fieldNote.text = mm.fieldNote;
//返回值
return cell;
}
//删除
-(void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath{
//添加
Model *mm =marr[ indexPath.row];
//删除
[[LoadData sharlLoadData]deleteharlLoadData:mm];
[marr removeObjectAtIndex:indexPath.row];
//刷新
[tabele reloadData];
}
//跳转
-(void)tableView:(UITableView *)tableView
didSelectRowAtIndexPath:(NSIndexPath *)indexPath{
//初始化
UpViewController *up = [UpViewController new];
//添加
up.mm = marr[indexPath.row];
//修改
[[LoadData sharlLoadData]UPsharlLoadData:up.mm];
//跳转
[self.navigationController pushViewController:up animated:YES];
}
@end


控制器2&＃8211;添加信息控制器 .m:

#import "AddViewController.h"
#import "Model.h"
#import "LoadData.h"
@interface AddViewController ()
//邮件
@property (strong,nonatomic)IBOutletUITextField *fieldMail;
//收货地址
@property (strong,nonatomic)IBOutletUITextField *fieldAddress;
//收货人
@property (strong,nonatomic)IBOutletUITextField *fieldConsignee;
//电话
@property (strong,nonatomic)IBOutletUITextField *fieldPhone;
//备注
@property (strong,nonatomic)IBOutletUITextField *fieldNote;
//点击添加信息
- (IBAction)sss:(id)sender;
@end
@implementation AddViewController
- (void)viewDidLoad {
[superviewDidLoad];
}
- (IBAction)sss:(id)sender {
//初始化
Model *mm = [Model new];
//链接
mm.fieldMail =self.fieldMail.text;
mm.fieldAddress =self.fieldAddress.text;
mm.fieldCOnsignee=self.fieldConsignee.text;
mm.fieldPhOne=self.fieldPhone.text;
mm.fieldNote =self.fieldNote.text; //添加到数据库
[[LoadData sharlLoadData]AddsharlLoadData:mm];
//跳转
[self.navigationController popViewControllerAnimated:YES];}
@end


控制器3&＃8211;修改信息控制器 .h:

#import
@class Model;
@interface UpViewController : UIViewController
//定义属性
@property (nonatomic ,strong)Model *mm;
@end


控制器3&＃8211;修改信息控制器 .m:

#import "UpViewController.h"
#import "LoadData.h"
#import "Model.h"
@interface UpViewController ()
//邮件
@property (strong,nonatomic)IBOutletUITextField *fieldMail;
//收货地址
@property (strong,nonatomic)IBOutletUITextField *fieldAddress;
//收货人
@property (strong,nonatomic)IBOutletUITextField *fieldConsignee;
//电话
@property (strong,nonatomic)IBOutletUITextField *fieldPhone;
//备注
@property (strong,nonatomic)IBOutletUITextField *fieldNote;
//点击修改信息
- (IBAction)ssss:(id)sender;
@end
@implementation UpViewController
- (void)viewDidLoad {
[superviewDidLoad];
//将数据添加到修改页面
self.fieldMail.text =self.mm.fieldMail;
self.fieldAddress.text =self.mm.fieldAddress;
self.fieldConsignee.text =self.mm.fieldConsignee;
self.fieldPhone.text =self.mm.fieldPhone;
self.fieldNote.text =self.mm.fieldNote;
}
- (IBAction)ssss:(id)sender {
//初始化
Model *mm = self.mm;
//链接
mm.fieldMail = self.fieldMail.text;
mm.fieldAddress = self.fieldAddress.text;
mm.fieldCOnsignee= self.fieldConsignee.text;
mm.fieldPhOne= self.fieldPhone.text;
mm.fieldNote = self.fieldNote.text;
//添加
[[LoadData sharlLoadData]UPsharlLoadData:mm];
//跳转
[self.navigationController popViewControllerAnimated:YES];
}
@end


5.MVC&＃8211;>V模块

控制器1&＃8211; .h:

#import
@interface MyTableViewCell : UITableViewCell
//邮件
@property (nonatomic ,strong)UILabel *fieldMail;
//收货地址
@property (nonatomic ,strong)UILabel *fieldAddress;
//收货人
@property (nonatomic ,strong)UILabel *fieldConsignee;
//电话
@property (nonatomic ,strong)UILabel *fieldPhone;
//备注
@property (nonatomic ,strong)UILabel *fieldNote;
@end


控制器1&＃8211; .m:

#import "MyTableViewCell.h"
@implementation MyTableViewCell
//重写父类方法
-(instancetype)initWithStyle:(UITableViewCellStyle)style
reuseIdentifier:(NSString *)reuseIdentifier{
//判断
if ([superinitWithStyle:stylereuseIdentifier:reuseIdentifier]) {
//添加到
[self.contentView addSubview:self.fieldMail];
[self.contentView addSubview:self.fieldAddress];
[self.contentView addSubview:self.fieldConsignee];
[self.contentView addSubview:self.fieldPhone];
[self.contentView addSubview:self.fieldNote];
}
//返回值
returnself;
}
//懒加载-//邮件
-(UILabel *)fieldMail{
//判断
if (!_fieldMail) {
//初始化
_fieldMail = [[UILabel alloc]initWithFrame:CGRectMake(5,5,80,44)];
}
//返回值
return_fieldMail;
}
//懒加载-//收货地址
-(UILabel *)fieldAddress{
//判断
if (!_fieldAddress) {
//初始化
_fieldAddress = [[UILabel alloc]initWithFrame:CGRectMake(80,5,80,44)];
}
//返回值
return_fieldAddress;
}
//懒加载=//收货人
-(UILabel *)fieldConsignee{
//判断
if (!_fieldConsignee) {
//初始化
_fieldCOnsignee= [[UILabel alloc]initWithFrame:CGRectMake(155,5,80,44)];
}
//返回值
return_fieldConsignee;
}
//懒加载--//电话
-(UILabel *)fieldPhone{
//判断
if (!_fieldPhone) {
//初始化
_fieldPhOne= [[UILabel alloc]initWithFrame:CGRectMake(220,5,80,44)];
}
//返回值
return _fieldPhone;
}
//懒加载
-(UILabel *)fieldNote{
//判断
if (!_fieldNote) {
//初始化
_fieldNote = [[UILabel alloc]initWithFrame:CGRectMake(305,5,80,44)];
}
//返回值
return _fieldNote;
}
- (void)awakeFromNib {
[super awakeFromNib];
}
- (void)setSelected:(BOOL)selected animated:(BOOL)animated {
[super setSelected:selected animated:animated];
}
@end


6.MVC&＃8211;>M模块

控制器1&＃8211; .h:

#import
@interface Model : NSObject
//定义属性
//邮箱
@property (nonatomic ,copy)NSString *fieldMail;
//收货地址
@property (nonatomic ,copy)NSString *fieldAddress;
//收货人
@property (nonatomic ,copy)NSString *fieldConsignee;
//电话
@property (nonatomic ,copy)NSString *fieldPhone;
//备注
@property (nonatomic ,copy)NSString *fieldNote;
//
@property (nonatomic ,assign)NSInteger ID;
@end


控制器1&＃8211; .m:

#import "Model.h"
@implementation Model
@end