关于FMDB-本地数据库【简单基础教学】

作者: iOS_Gato_老猫 | 来源:发表于2017-01-20 13:54 被阅读304次

最近给我媳妇写一个微商用的app。主要功能就是
1。销售记录添加查询删除修改
2。进货记录添加查询删除修改
3。常用广告语一键复制增加修改查询删除
4。财务记录各种进出账目统计

嗯就是一个本地数据库app 不需要网络请求【主要是因为我不会后台。。。】

好吧废话不多说下面开始讲解

在这里必须要感谢一下好基友【大刘】嗯帮我学习了FMDB 并且给我写了一个demo 哈哈哈

1.加载FMDB

首先当然是在程序中加载FMDB了不然怎么用
传送门--点击我跳转下载链接

2.FMDB简单介绍

嗯对于从来没用过这个功能的朋友来说只是简单知道这是一个方便处理数据存储本地的第三方

其实他只是给我们更好的封装了 SQL语句系统自带的存储方法

SQL语句是我写iOS以来遇到过第二恶心的东西第一个是WebService

在 FMDB 中有三个重要的类：

FMDatabase：是一个提供 SQLite 数据库的类，用于执行 SQL 语句。
FMResultSet：用在 FMDatabase 中执行查询的结果的类。
FMDatabaseQueue：在多线程下查询和更新数据库用到的类。

3. 下面根据demo讲解 FMDB

//创建数据库
@interface FMDBForSell ()
{
     FMDatabase *dateBase;
}
@end

- (BOOL)createDataBaseWithName:(NSString *)name{
    //创建数据库
    NSString *path=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    dateBase = [FMDatabase databaseWithPath:[path stringByAppendingString:[NSString stringWithFormat:@"/%@",name]]];
    return [dateBase open];
}
//创建表
- (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];
}

首先第一步肯定是创建数据库并且在数据库中创建表格

使用方法:

-(void)addFMDB
{
    manager = [[FMDBForSell alloc] init];
    
    //创建数据库
    BOOL open = [manager createDataBaseWithName:@"abc.splist"];
    //打开数据库
    if (open) {
        NSLog(@"数据库打开成功");
    }
}

好下面开始讲解一下这个创建数据库跟创建表的内容

我们可以看到创建表中有一段
@"create table if not exists t_user(id integer primary key)"
这样的内容那么这句话是什么意思呢？

首先要拆分一下

create table if not exists  t_  这句话 是前缀 不用更改，我只知道需要这么写 至于为什么 不好意思 我也不懂
t_user是表名，表示user表，table的缩写
(id integer primary key)  id是integer类型，primary key表示是主键

这里我把创建表代码跟创建表的打印信息标记出来

 //创建表
//这里传的参数 就是 我们需要存储数据的model参数
    BOOL createTable = [manager createTableWithDic:@[@"cid",@"cname",@"cmap",@"cphone",@"ctime",@"conemonery",@"callmonery",@"cnumber",@"cway",@"cwaymonery",@"cother",@"cover"]];
    if (createTable) {
        NSLog(@"创建表成功");
    }
//打印结果
 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)

到这里基本上创建表就结束了当你创建成功的时候就会打印出创建表成功的字样

下面开始讲解增删改查的应用

1.增加

//插入一个model
- (BOOL)insertWithModel:(sellModel *)model{
    
    BOOL isBe = [self selectDataWithId:[model.cid intValue]];
    if (isBe) {
        [tishiXiaoShiViewController showMessage:@"ID已存在，重新像一个"];
        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];
        return [dateBase executeUpdate:insertSql];
    }
}

这里的代码就比较简单了基本上没有什么大家不懂的

SQL语句中再次标记一点 t_user 这个字段一定不能更改

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];
}

这里就比较简单一条是删除表【也就是删除全部】
一条是单独删除【这里我是根据 model中的cid 字段查找数据并且删除如果你没有id 那就跟觉别的字段是一样的】
不管用什么这里需要注意的一点就是 SQL语句中

t_user where cid 这句话中的cid 需要根据各自需求去更改可以是id 可以是name 可以是age 可以是你定义的一切字段只要你的表中包含这个字段就行

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;
}

查询语句中也比较简单复制代码即可

这两条分别是
查询表中所有数据
查询单条数据
这里需要及时一下的依旧是SQL语句
select * from t_user where cid = '%d'",id
这句话这个id 在传进来之前已经经过处理所以不在是String
【为了model统一防止出现类型错误导致的一些不必要问题所以我吧所有属性全部设置成String】；

这里还有一种查询方法是条件筛选上面那种筛选方式是根据id查询如果想根据别的字段查询那么久需要用到条件筛选

- (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;
}

在这个语句中 querySql 就是筛选条件

使用方法

NSString * tiaojian = @"我是筛选条件";
NSString * shaixuan = @"我是筛选内容";
NSString *fuzzyQuerySql = [NSString stringWithFormat:@"SELECT * FROM t_user WHERE %@ LIKE '%%%@%%'",tiaojian,shaixuan];
        searcharray = [manager queryData:fuzzyQuerySql];

在使用的时候需要确认的是
t_user WHERE %@ LIKE
至于为什么有 %%%@%%' 这样的符号？是因为筛选条件可能会是中文虽然我不明白代表什么意思但是写上肯定没错就对了！

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];
}

关于这个修改呢只需要把对应model传进来就好了

这里面我依旧是根据cid 进行判断的

细心的朋友可能会发现我在sql语句中多了一个 WHERE cid = '%@'
没错这就是判断信息
也就是说本次修改需要根据什么条件去查找表中对应的信息并且修改

好了到这里基本上我们已经简单的讲解完了 FMDB 的基本使用方法可能有些同学还是很懵逼啊

那继续往下看！下面附上代码～

//

#import <Foundation/Foundation.h>
@class sellModel;
@interface FMDBForSell : NSObject

//创建数据库
- (BOOL)createDataBaseWithName:(NSString *)name;
//创建表
- (BOOL)createTableWithDic:(NSArray *)arr;
//插入一个model
- (BOOL)insertWithModel:(sellModel *)model;
//删除表
- (BOOL)deleteDataBase;
//删除一条记录
- (BOOL)deleteDataBaseWithModel:(sellModel *)model;
//查询所有的
- (NSArray *)selectAllModels;
//查询一条记录
- (sellModel *)selectDataWithId:(int)id;

//修改数据
- (BOOL)updateDataWithModel:(sellModel *)model;

/** 修改数据 */
- (BOOL)modifyData:(sellModel *)model;

//筛选查看
- (NSArray *)queryData:(NSString *)querySql;

@end

//
//  FMDBForSell.m
//  zhanzhan
//
//  Created by 辛书亮 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

//创建数据库
- (BOOL)createDataBaseWithName:(NSString *)name{
    //创建数据库
    NSString *path=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    dateBase = [FMDatabase databaseWithPath:[path stringByAppendingString:[NSString stringWithFormat:@"/%@",name]]];
    return [dateBase open];
}
//创建表
- (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];
}

//插入一个model
- (BOOL)insertWithModel:(sellModel *)model{
    
    BOOL isBe = [self selectDataWithId:[model.cid intValue]];
    if (isBe) {
        [tishiXiaoShiViewController showMessage:@"ID已存在，重新像一个"];
        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];
    }
}
//更新数据
- (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];
    
    //创建数据库
    BOOL open = [manager createDataBaseWithName:@“111.sqlist”];
    //打开数据库
    if (open) {
        NSLog(@"数据库打开成功");
    }
    
//    [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

关于FMDB-本地数据库【简单基础教学】

1.加载FMDB

2.FMDB简单介绍

3. 下面根据demo讲解 FMDB

1.增加

2。删除

3.查询

4.修改

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

iOS_不断学习

iOS Developer