最近給我媳婦寫一個(gè)微商用的app遭顶。主要功能就是
1。銷售記錄 添加 查詢 刪除 修改
2垦梆。進(jìn)貨記錄 添加 查詢 刪除 修改
3奈虾。常用廣告語 一鍵復(fù)制 增加 修改 查詢 刪除
4。財(cái)務(wù)記錄 各種進(jìn)出賬目統(tǒng)計(jì)
嗯 就是一個(gè)本地?cái)?shù)據(jù)庫(kù)app 不需要網(wǎng)絡(luò)請(qǐng)求【主要是因?yàn)槲也粫?huì)后臺(tái)菜职。青抛。〕旰耍】
好吧 廢話不多說 下面開始講解
在這里 必須要感謝一下 好基友【大劉】 嗯 幫我學(xué)習(xí)了FMDB 并且給我寫了一個(gè)demo 哈哈哈
1.加載FMDB
首先當(dāng)然是在程序中 加載FMDB了 不然怎么用
傳送門--點(diǎn)擊我跳轉(zhuǎn)下載鏈接
2.FMDB簡(jiǎn)單介紹
嗯 對(duì)于從來沒用過這個(gè)功能的朋友來說 只是簡(jiǎn)單知道 這是一個(gè)方便 處理數(shù)據(jù)存儲(chǔ)本地的第三方
其實(shí) 他只是給我們更好的封裝了 SQL語句系統(tǒng)自帶的存儲(chǔ)方法
SQL語句 是我寫iOS以來 遇到過第二惡心的東西 第一個(gè)是WebService
在 FMDB 中有三個(gè)重要的類：
FMDatabase：是一個(gè)提供 SQLite 數(shù)據(jù)庫(kù)的類蜜另，用于執(zhí)行 SQL 語句适室。
FMResultSet：用在 FMDatabase 中執(zhí)行查詢的結(jié)果的類。
FMDatabaseQueue：在多線程下查詢和更新數(shù)據(jù)庫(kù)用到的類举瑰。
3. 下面根據(jù)demo講解 FMDB
//創(chuàng)建數(shù)據(jù)庫(kù)
@interface FMDBForSell ()
{
FMDatabase *dateBase;
}
@end
- (BOOL)createDataBaseWithName:(NSString *)name{
//創(chuàng)建數(shù)據(jù)庫(kù)
NSString *path=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
dateBase = [FMDatabase databaseWithPath:[path stringByAppendingString:[NSString stringWithFormat:@"/%@",name]]];
return [dateBase open];
}
//創(chuàng)建表
- (BOOL)createTableWithDic:(NSArray *)arr{
NSMutableString *creat = [NSMutableString stringWithString:@"create table if not exists t_user(id integer primary key)"];
for (NSString *per in arr) {
[creat insertString:[NSString stringWithFormat:@",%@ varchar", per] atIndex:creat.length-1];
}
return [dateBase executeUpdate:creat];
}
首先第一步肯定是創(chuàng)建數(shù)據(jù)庫(kù) 并且在數(shù)據(jù)庫(kù)中創(chuàng)建表格
使用方法:
-(void)addFMDB
{
manager = [[FMDBForSell alloc] init];
//創(chuàng)建數(shù)據(jù)庫(kù)
BOOL open = [manager createDataBaseWithName:@"abc.splist"];
//打開數(shù)據(jù)庫(kù)
if (open) {
NSLog(@"數(shù)據(jù)庫(kù)打開成功");
}
}
好 下面開始講解一下 這個(gè) 創(chuàng)建數(shù)據(jù)庫(kù) 跟 創(chuàng)建表的內(nèi)容
我們可以看到 創(chuàng)建表 中 有一段
@"create table if not exists t_user(id integer primary key)"
這樣的內(nèi)容 那么這句話是什么意思呢捣辆？
首先要拆分一下
create table if not exists t_ 這句話 是前綴 不用更改，我只知道需要這么寫 至于為什么 不好意思 我也不懂
t_user是表名此迅，表示user表汽畴，table的縮寫
(id integer primary key) id是integer類型，primary key表示是主鍵
這里 我把 創(chuàng)建表代碼跟創(chuàng)建表的打印信息 標(biāo)記出來
//創(chuàng)建表
//這里傳的參數(shù) 就是 我們需要存儲(chǔ)數(shù)據(jù)的model參數(shù)
BOOL createTable = [manager createTableWithDic:@[@"cid",@"cname",@"cmap",@"cphone",@"ctime",@"conemonery",@"callmonery",@"cnumber",@"cway",@"cwaymonery",@"cother",@"cover"]];
if (createTable) {
NSLog(@"創(chuàng)建表成功");
}
//打印結(jié)果
create table if not exists t_user(id integer primary key,cid varchar,cname varchar,cmap varchar,cphone varchar,ctime varchar,conemonery varchar,callmonery varchar,cnumber varchar,cway varchar,cwaymonery varchar,cother varchar,cover varchar)
到這里 基本上創(chuàng)建表就結(jié)束了 當(dāng)你創(chuàng)建成功的時(shí)候 就會(huì)打印出 創(chuàng)建表成功 的字樣
下面開始講解 增刪改查的應(yīng)用
1.增加
//插入一個(gè)model
- (BOOL)insertWithModel:(sellModel *)model{
BOOL isBe = [self selectDataWithId:[model.cid intValue]];
if (isBe) {
[tishiXiaoShiViewController showMessage:@"ID已存在耸序，重新像一個(gè)"];
return [self updateDataWithModel:model]; //更新數(shù)據(jù)
} else {
NSString * insertSql = [NSString stringWithFormat:@"insert into t_user(cid,cname,cmap,cphone,ctime,conemonery,callmonery,cnumber,cway,cwaymonery,cother,cover) values('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@') ;", model.cid, model.cname, model.cmap,model.cphone,model.ctime,model.cOneMonery,model.cAllMonery,model.cNumber,model.cway,model.cwayMondery,model.cother,model.cover];
return [dateBase executeUpdate:insertSql];
}
}
這里 的代碼就比較簡(jiǎn)單了 基本上沒有什么大家不懂的
SQL語句中再次標(biāo)記一點(diǎn) t_user 這個(gè)字段 一定 不能更改
2忍些。刪除
//刪除表
- (BOOL)deleteDataBase{
NSString * delete=@"delete from t_user";
return [dateBase executeUpdate:delete];
}
//刪除一條記錄
- (BOOL)deleteDataBaseWithModel:(sellModel *)model{
NSString * delete = [NSString stringWithFormat:@"delete from t_user where cid = '%@'",model.cid];
return [dateBase executeUpdate:delete];
}
這里就比較簡(jiǎn)單 一條是刪除表【也就是刪除全部】
一條是單獨(dú)刪除 【這里 我是根據(jù) model中的cid 字段 查找數(shù)據(jù)并且刪除 如果你沒有id 那就跟覺別的字段是一樣的 】
不管用什么 這里需要注意的一點(diǎn)就是 SQL語句中
t_user where cid 這句話中的cid 需要 根據(jù)各自需求去更改 可以是id 可以是name 可以是age 可以是你定義的一切字段 只要 你的表中包含這個(gè)字段就行
3.查詢
//查詢所有的
- (NSArray *)selectAllModels{
NSMutableArray *results = [NSMutableArray array];
NSString * sql = @"select * from t_user";
FMResultSet *result = [dateBase executeQuery:sql];
while(result.next){
sellModel *model = [self selectDataWithResult:result];
[results addObject:model];
}
return results;
}
//查詢一條記錄
- (sellModel *)selectDataWithId:(int)id{
NSString * sql = [NSString stringWithFormat:@"select * from t_user where cid = '%d'",id];
FMResultSet *result = [dateBase executeQuery:sql];
sellModel *model;
if ([result next]) {
model = [self selectDataWithResult:result];
}
return model;
}
查詢語句中 也比較簡(jiǎn)單 復(fù)制代碼即可
這兩條分別是
查詢表中所有數(shù)據(jù)
查詢單條數(shù)據(jù)
這里需要及時(shí)一下的 依舊是SQL語句
select * from t_user where cid = '%d'",id
這句話 這個(gè)id 在傳進(jìn)來之前 已經(jīng)經(jīng)過處理 所以 不在是String
【為了model統(tǒng)一 防止出現(xiàn)類型錯(cuò)誤導(dǎo)致的一些不必要問題 所以我吧所有屬性全部設(shè)置成String】；
這里還有一種查詢方法是 條件篩選 上面那種篩選方式 是根據(jù)id查詢 如果想根據(jù)別的字段查詢 那么久需要用到條件篩選
- (NSArray *)queryData:(NSString *)querySql
{
if (querySql == nil) {
querySql = @"SELECT * FROM t_modals;";
}
NSMutableArray *arrM = [NSMutableArray array];
FMResultSet *result = [dateBase executeQuery:querySql];
while ([result next]) {
sellModel *model = [[sellModel alloc]init];
int ids = [result intForColumn:@"cid"];
NSString *name = [result stringForColumn:@"cname"];
NSString *map = [result stringForColumn:@"cmap"];
NSString *cphone = [result stringForColumn:@"cphone"];
NSString *ctime = [result stringForColumn:@"ctime"];
NSString *conemonery = [result stringForColumn:@"conemonery"];
NSString *callmonery = [result stringForColumn:@"callmonery"];
NSString *cnumber = [result stringForColumn:@"cnumber"];
NSString *cway = [result stringForColumn:@"cway"];
NSString *cwaymonery = [result stringForColumn:@"cwaymonery"];
NSString *cother = [result stringForColumn:@"cother"];
NSString *cover = [result stringForColumn:@"cover"];
model.cid = [NSString stringWithFormat:@"%d",ids];
model.cname = name;
model.cmap = map;
model.cphone = cphone;
model.ctime = ctime;
model.cOneMonery = conemonery;
model.cAllMonery = callmonery;
model.cNumber = cnumber;
model.cway = cway;
model.cwayMondery = cwaymonery;
model.cother = cother;
model.cover = cover;
[arrM addObject:model];
}
return arrM;
}
在這個(gè)語句中 querySql 就是篩選條件
使用方法
NSString * tiaojian = @"我是篩選條件";
NSString * shaixuan = @"我是篩選內(nèi)容";
NSString *fuzzyQuerySql = [NSString stringWithFormat:@"SELECT * FROM t_user WHERE %@ LIKE '%%%@%%'",tiaojian,shaixuan];
searcharray = [manager queryData:fuzzyQuerySql];
在使用的時(shí)候 需要 確認(rèn)的是
t_user WHERE %@ LIKE
至于為什么 有 %%%@%%' 這樣的符號(hào)坎怪？ 是因?yàn)?篩選條件 可能會(huì)是中文 雖然我不明白代表什么意思 但是寫上肯定沒錯(cuò)就對(duì)了坐昙！
4.修改
- (BOOL)modifyData:(sellModel *)model
{
NSString *update = [NSString stringWithFormat:@"update t_user set cid = '%@', cname = '%@',cmap = '%@',cphone = '%@',ctime = '%@',conemonery = '%@',callmonery = '%@',cnumber = '%@',cway = '%@',cwaymonery = '%@',cother = '%@',cover = '%@' WHERE cid = '%@'",model.cid,model.cname, model.cmap,model.cphone,model.ctime,model.cOneMonery,model.cAllMonery,model.cNumber,model.cway,model.cwayMondery,model.cother,model.cover, model.cid];
return [dateBase executeUpdate:update];
}
關(guān)于這個(gè)修改呢 只需要把對(duì)應(yīng)model傳進(jìn)來 就好了
這里面我依舊是根據(jù)cid 進(jìn)行判斷的
細(xì)心的朋友可能會(huì)發(fā)現(xiàn) 我在sql語句中 多了一個(gè) WHERE cid = '%@'
沒錯(cuò) 這就是 判斷信息
也就是說 本次修改 需要根據(jù) 什么條件 去查找表中對(duì)應(yīng)的信息并且修改
好了 到這里 基本上 我們已經(jīng) 簡(jiǎn)單的講解完了 FMDB 的基本使用方法 可能有些同學(xué)還是很懵逼啊
那繼續(xù)往下看！ 下面附上 代碼～
//
#import <Foundation/Foundation.h>
@class sellModel;
@interface FMDBForSell : NSObject
//創(chuàng)建數(shù)據(jù)庫(kù)
- (BOOL)createDataBaseWithName:(NSString *)name;
//創(chuàng)建表
- (BOOL)createTableWithDic:(NSArray *)arr;
//插入一個(gè)model
- (BOOL)insertWithModel:(sellModel *)model;
//刪除表
- (BOOL)deleteDataBase;
//刪除一條記錄
- (BOOL)deleteDataBaseWithModel:(sellModel *)model;
//查詢所有的
- (NSArray *)selectAllModels;
//查詢一條記錄
- (sellModel *)selectDataWithId:(int)id;
//修改數(shù)據(jù)
- (BOOL)updateDataWithModel:(sellModel *)model;
/** 修改數(shù)據(jù) */
- (BOOL)modifyData:(sellModel *)model;
//篩選查看
- (NSArray *)queryData:(NSString *)querySql;
@end
//
// FMDBForSell.m
// zhanzhan
//
// Created by 辛?xí)?on 2017/1/19.
// Copyright ? 2017年 孟小貓. All rights reserved.
//
#import "FMDBForSell.h"
#import "FMDB.h"
#import "sellModel.h"
#import "tishiXiaoShiViewController.h"
@interface FMDBForSell ()
{
FMDatabase *dateBase;
}
@end
@implementation FMDBForSell
//創(chuàng)建數(shù)據(jù)庫(kù)
- (BOOL)createDataBaseWithName:(NSString *)name{
//創(chuàng)建數(shù)據(jù)庫(kù)
NSString *path=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
dateBase = [FMDatabase databaseWithPath:[path stringByAppendingString:[NSString stringWithFormat:@"/%@",name]]];
return [dateBase open];
}
//創(chuàng)建表
- (BOOL)createTableWithDic:(NSArray *)arr{
NSMutableString *creat = [NSMutableString stringWithString:@"create table if not exists t_user(id integer primary key)"];
for (NSString *per in arr) {
[creat insertString:[NSString stringWithFormat:@",%@ varchar", per] atIndex:creat.length-1];
}
return [dateBase executeUpdate:creat];
}
//插入一個(gè)model
- (BOOL)insertWithModel:(sellModel *)model{
BOOL isBe = [self selectDataWithId:[model.cid intValue]];
if (isBe) {
[tishiXiaoShiViewController showMessage:@"ID已存在芋忿，重新像一個(gè)"];
return [self updateDataWithModel:model];
} else {
NSString * insertSql = [NSString stringWithFormat:@"insert into t_user(cid,cname,cmap,cphone,ctime,conemonery,callmonery,cnumber,cway,cwaymonery,cother,cover) values('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@') ;", model.cid, model.cname, model.cmap,model.cphone,model.ctime,model.cOneMonery,model.cAllMonery,model.cNumber,model.cway,model.cwayMondery,model.cother,model.cover];
// NSString * insertSql = [NSString stringWithFormat:@"insert into t_user(id,name,sex) values('%@','%@','%@')", model.id, model.name, model.sex];
return [dateBase executeUpdate:insertSql];
}
}
//更新數(shù)據(jù)
- (BOOL)updateDataWithModel:(sellModel *)model{
NSString *update = [NSString stringWithFormat:@"update t_user set cid = '%@',cname = '%@',cmap = '%@',cphone = '%@',ctime = '%@',conemonery = '%@',callmonery = '%@',cnumber = '%@',cway = '%@',cwaymonery = '%@',cother = '%@',cover = '%@'", model.cid, model.cname, model.cmap,model.cphone,model.ctime,model.cOneMonery,model.cAllMonery,model.cNumber,model.cway,model.cwayMondery,model.cother,model.cover];
return [dateBase executeUpdate:update];
}
- (BOOL)modifyData:(sellModel *)model
{
NSString *update = [NSString stringWithFormat:@"update t_user set cid = '%@', cname = '%@',cmap = '%@',cphone = '%@',ctime = '%@',conemonery = '%@',callmonery = '%@',cnumber = '%@',cway = '%@',cwaymonery = '%@',cother = '%@',cover = '%@' WHERE cid = '%@'",model.cid,model.cname, model.cmap,model.cphone,model.ctime,model.cOneMonery,model.cAllMonery,model.cNumber,model.cway,model.cwayMondery,model.cother,model.cover, model.cid];
return [dateBase executeUpdate:update];
}
//刪除表
- (BOOL)deleteDataBase{
NSString * delete=@"delete from t_user";
return [dateBase executeUpdate:delete];
}
//刪除一條記錄
- (BOOL)deleteDataBaseWithModel:(sellModel *)model{
NSString * delete = [NSString stringWithFormat:@"delete from t_user where cid = '%@'",model.cid];
return [dateBase executeUpdate:delete];
}
//查詢所有的
- (NSArray *)selectAllModels{
NSMutableArray *results = [NSMutableArray array];
NSString * sql = @"select * from t_user";
FMResultSet *result = [dateBase executeQuery:sql];
while(result.next){
sellModel *model = [self selectDataWithResult:result];
[results addObject:model];
}
return results;
}
//查詢一條記錄
- (sellModel *)selectDataWithId:(int)id{
NSString * sql = [NSString stringWithFormat:@"select * from t_user where cid = '%d'",id];
FMResultSet *result = [dateBase executeQuery:sql];
sellModel *model;
if ([result next]) {
model = [self selectDataWithResult:result];
}
return model;
}
- (NSArray *)queryData:(NSString *)querySql
{
if (querySql == nil) {
querySql = @"SELECT * FROM t_modals;";
}
NSMutableArray *arrM = [NSMutableArray array];
FMResultSet *result = [dateBase executeQuery:querySql];
while ([result next]) {
sellModel *model = [[sellModel alloc]init];
int ids = [result intForColumn:@"cid"];
NSString *name = [result stringForColumn:@"cname"];
NSString *map = [result stringForColumn:@"cmap"];
NSString *cphone = [result stringForColumn:@"cphone"];
NSString *ctime = [result stringForColumn:@"ctime"];
NSString *conemonery = [result stringForColumn:@"conemonery"];
NSString *callmonery = [result stringForColumn:@"callmonery"];
NSString *cnumber = [result stringForColumn:@"cnumber"];
NSString *cway = [result stringForColumn:@"cway"];
NSString *cwaymonery = [result stringForColumn:@"cwaymonery"];
NSString *cother = [result stringForColumn:@"cother"];
NSString *cover = [result stringForColumn:@"cover"];
model.cid = [NSString stringWithFormat:@"%d",ids];
model.cname = name;
model.cmap = map;
model.cphone = cphone;
model.ctime = ctime;
model.cOneMonery = conemonery;
model.cAllMonery = callmonery;
model.cNumber = cnumber;
model.cway = cway;
model.cwayMondery = cwaymonery;
model.cother = cother;
model.cover = cover;
[arrM addObject:model];
}
return arrM;
}
- (sellModel *)selectDataWithResult:(FMResultSet *)result{
sellModel *model = [[sellModel alloc] init];
int ids = [result intForColumn:@"cid"];
NSString *name = [result stringForColumn:@"cname"];
NSString *map = [result stringForColumn:@"cmap"];
NSString *cphone = [result stringForColumn:@"cphone"];
NSString *ctime = [result stringForColumn:@"ctime"];
NSString *conemonery = [result stringForColumn:@"conemonery"];
NSString *callmonery = [result stringForColumn:@"callmonery"];
NSString *cnumber = [result stringForColumn:@"cnumber"];
NSString *cway = [result stringForColumn:@"cway"];
NSString *cwaymonery = [result stringForColumn:@"cwaymonery"];
NSString *cother = [result stringForColumn:@"cother"];
NSString *cover = [result stringForColumn:@"cover"];
model.cid = [NSString stringWithFormat:@"%d",ids];
model.cname = name;
model.cmap = map;
model.cphone = cphone;
model.ctime = ctime;
model.cOneMonery = conemonery;
model.cAllMonery = callmonery;
model.cNumber = cnumber;
model.cway = cway;
model.cwayMondery = cwaymonery;
model.cother = cother;
model.cover = cover;
return model;
}
@end
使用代碼
-(void)viewDidLoad
{
manager = [[FMDBForSell alloc] init];
//創(chuàng)建數(shù)據(jù)庫(kù)
BOOL open = [manager createDataBaseWithName:@“111.sqlist”];
//打開數(shù)據(jù)庫(kù)
if (open) {
NSLog(@"數(shù)據(jù)庫(kù)打開成功");
}
// [manager deleteDataBase];
[self.updataArray removeAllObjects];
NSArray *arr = [manager selectAllModels];
for (int i = 0 ; i < arr.count; i ++) {
sellModel *model = [[sellModel alloc]init];
model = arr[i];
[self.updataArray addObject:model];
}
[self.GatoTableview reloadData];
}
下面貼上 app演示
演示視頻1.gif
演示視頻2.gif
演示視頻3.gif
演示視頻4.gif
