数据库操作(使用FMDB)

来源：互联网发布：优化机构设置编辑：程序博客网时间：2024/05/22 03:20

iOS中原生的SQLite API在使用上相当不友好，在使用时，非常不便。于是，就出现了一系列将SQLite API进行封装的库，例如FMDB、PlausibleDatabase、sqlitepersistentobjects等，FMDB (https://github.com/ccgus/fmdb) 是一款简洁、易用的封装库，这一篇文章简单介绍下FMDB的使用。

在FMDB下载文件后，工程中必须导入如下文件，并使用 libsqlite3.dylib 依赖包。

FMDB同时兼容ARC和非ARC工程，会自动根据工程配置来调整相关的内存管理代码。

FMDB常用类：

FMDatabase ：一个单一的SQLite数据库，用于执行SQL语句。
FMResultSet ：执行查询一个FMDatabase结果集，这个和android的Cursor类似。
FMDatabaseQueue ：在多个线程来执行查询和更新时会使用这个类。

创建一个类其方法可以被其它类共用作为存储（单列为主）

#import <Foundation/Foundation.h>#import "FMDatabase.h"#import "WYShopCarModel.h"#import "NewsModel.h"@interface WYDataBase : NSObject{   //数据库    FMDatabase *_database;}//获取数据库管理对象单例的方法+ (WYDataBase *)sharedDataBase;//返回数据的路径+ (NSString *)getDataBasePath;//关闭数据库- (void)closeDataBase;//清空数据库- (BOOL)deleteDatabase;/*********** *搜索记录模块 **************///向搜索记录表中插入新记录- (BOOL)insertSeachText:(NSString *)seachText;//查询数据库中是否包含当前搜索记录- (BOOL)isExistSeachText:(NSString *)seachText;//获取所有搜索记录- (NSMutableArray *)getAllSeachText;//删除所有搜索记录- (BOOL)deleAllSeachText;/*********** *购物车模块 **************///向购物车中添加信息- (BOOL)insertPdcToCarWithModel:(WYShopCarModel *)model;//向购物车(兴趣)中添加信息- (BOOL)insertPdcToIntrestCarWithModel:(WYShopCarModel *)model;//获取购物车(兴趣)列表- (NSArray *)getAllpdcInIntrestCar;//购物车是否存在商品- (BOOL)isExistInCarWithModel:(WYShopCarModel*)model;//获取购物车列表- (NSArray *)getAllpdcInCar;//修改购物车中产品的数量- (BOOL)updataCountInCar:(int)newCount pdc:(int)pdcSID;//修改购物车中产品的选中状态- (BOOL)updateSelectedState:(int)select pdc:(int)pdcId;//删除购物车中得产品记录- (BOOL)deletePdcInCarById:(int)pdcSId;/*********** *系统消息模块 **************///插入一条消息- (BOOL)insertNewToTBNew:(NewsModel *)model;//判断消息是否存在记录中- (BOOL)isExitInTabNew:(NSString *)newId;//改变消息是否阅读过的状态- (BOOL)changeScanfFlagWithNewId:(NSString *)newId;//获取一个model比对- (NewsModel *)getNewsModelById:(NSString *)newId;//获取所有的消息记录- (NSArray *)getAllNews;

实现

#import "WYDataBase.h"#import "WYShopCarModel.h"#import "ShoppingCarVC.h"//定义单例对象#pragma mark -- 单例对象获取//static WYDataBase *wydatase;@implementation WYDataBase- (id)init{    if (self = [super init]) {        //初始化数据库对象 并打开        _database = [FMDatabase databaseWithPath:[WYDataBase getDataBasePath]];        //如果数据库打开失败返回空值        if (![_database open]) {            return nil;        }    }    //如果数据库打开成功 创建表    //创建搜索历史记录表    NSString *sql = @"create table if not exists tb_history(tb_id integer primary key autoincrement,seachText text)";            //创建购物车表    NSString *sql2 = @"create table if not exists tb_car(carId integer primary key autoincrement,pdcId text,pdcSID text,img text,selected text,name text,price float,yprice float,allcnt text,qishu text,allneed text,leftneed text)";    //创建兴趣推荐表    NSString *sql3 = @"create table if not exists tb_intrest_car(carId integer primary key autoincrement,pdcID text,pdcSID text,img text,selected integer,name text,price float,yprice float,allcnt integer)";    //创建系统消息表    NSString *sql4 = @"create table if not exists tb_News(tb_id integer primary key autoincrement,  integer,newTitle text,newTime text,isScanfed integer)";        BOOL is = [_database executeUpdate:sql];    BOOL is2 = [_database executeUpdate:sql2];    BOOL is3 = [_database executeUpdate:sql3];    BOOL is4 = [_database executeUpdate:sql4];        if (is2 && is && is3 && is4) {        NSLog(@"创建表成功！");    }    return self;}//获取数据库管理对象单例的方法+ (WYDataBase *)sharedDataBase{    static WYDataBase *wydatase = nil;    static dispatch_once_t once;        dispatch_once(&once, ^{        wydatase = [[WYDataBase alloc]init];    });       return wydatase;}//返回数据库的路径+ (NSString *)getDataBasePath{    NSString *path = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];//    NSLog(@"home:%@",path);    return [path stringByAppendingPathComponent:@"wy.db"];}//查询数据库中是否包含当前搜索记录- (BOOL)isExistSeachText:(NSString *)seachText{    NSString *sql = @"select * from tb_history";    FMResultSet *results = [_database executeQuery:sql];        while (results.next) {        if ([seachText isEqualToString:[results stringForColumn:@"seachText"]]) {            return YES;        }    }    return NO;}//向搜索记录表中插入新记录- (BOOL)insertSeachText:(NSString *)seachText{    // 若果传入参数为空 或者数据库中存在该条记录 则插入数据失败    if (!seachText||[seachText isEqualToString:@""]||[self isExistSeachText:seachText]) {        NSLog(@"数据已存在");        return NO;    }    NSString *sql = @"insert into tb_history(seachText) values (?)";    BOOL isInsertOK = [_database executeUpdate:sql,seachText];    if (isInsertOK) {        NSLog(@"%@-->插入成功",seachText);        return YES;    }    NSLog(@"%@-->插入失败",seachText);    return NO;}//获取所有搜索记录- (NSMutableArray *)getAllSeachText{    NSString *sql = @"select * from tb_history order by tb_id desc";    //保存所有数据的数组    NSMutableArray *seachTexts = [NSMutableArray array];    FMResultSet *results = [_database executeQuery:sql];    while (results.next) {        NSString *result = [results stringForColumn:@"seachText"];        [seachTexts addObject:result];    }        return seachTexts;}//删除所有搜索记录- (BOOL)deleAllSeachText{    NSString *sql = @"delete from tb_history";    BOOL isDeleteOK = [_database executeUpdate:sql];        if (isDeleteOK) {//        NSLog(@"删除成功");        return YES;    }    return NO;}#pragma mark -- 购物车模块/*********** *购物车模块 **************///向购物车中添加信息- (BOOL)insertPdcToCarWithModel:(WYShopCarModel *)model{    //    NSString *sql2 = @"create table if not exists tb_car(carId integer primary key autoincrement,pdcId text,pdcSID text,img text,selected integer,name text,price float,yprice float,allcnt integer,qishu integer)";//    if([model.left_need intValue] == 0){return NO;}//    if ([model.left_need intValue] < 5) {//        NSString *sqll = @"insert into tb_car(pdcId,pdcSID,img,name,allcnt,qishu,allneed,leftneed) values (?,?,?,?,?,?,?,?)";//        BOOL isOK = [_database executeUpdate:sqll,model.car_id,model.car_new_id,model.imgPath,model.name,model.left_need ,model.qishu,model.all_need,model.left_need];//        if (isOK) {//            ShoppingCarVC *shopVC = [ShoppingCarVC shareShoppingCarVC];//            shopVC.firstFlag = YES;////            return YES;//        }//    }        if ([self isExistInCar:[model.car_new_id intValue]]) {//        [self deletePdcInCarById:[model.car_id intValue]];        return NO;    }            /***************如果该产品在购物车中不存在，加入购物车*******************/    NSString *sql = @"insert into tb_car(pdcId,pdcSID,img,name,allcnt,qishu,allneed,leftneed) values (?,?,?,?,?,?,?,?)";          BOOL isInsertOK = [_database executeUpdate:sql,model.car_id,model.car_new_id, model.imgPath,model.name,@"5",model.qishu ,model.all_need,model.left_need];    if (isInsertOK) {            NSLog(@"%@-->插入成功",model.car_id);            return YES;        }    return NO;}//查询购物车中是否包含此产品记录- (BOOL)isExistInCar:(int)pdcSID{    NSString *sql = @"select * from tb_car";    FMResultSet *results = [_database executeQuery:sql];        while (results.next) {        NSString *pdcSID2 = [results stringForColumn:@"pdcSID"];//        NSString *pdcID = [results stringForColumn:@"pdcId"];        if (pdcSID  == pdcSID2.intValue) {                        return YES;        }    }       return NO;}- (BOOL)isExistInCarWithModel:(WYShopCarModel *)model{    return [self isExistInCar:model.car_new_id.intValue];}//获取购物车列表- (NSArray *)getAllpdcInCar{//     NSString *sql2 = @"create table if not exists tb_car(carId integer primary key autoincrement,pdcId text,pdcSID text,img text,selected text,name text,price float,yprice float,allcnt text,qishu text)";        NSString *sql = @"select * from tb_car order by carId desc";        FMResultSet *results = [_database executeQuery:sql];    NSMutableArray *arr = [NSMutableArray array];    while (results.next) {        WYShopCarModel *model = [[WYShopCarModel alloc]init];        model.car_id = [NSString stringWithFormat:@"%d",[results intForColumn:@"pdcId"]];        model.car_new_id = [NSString stringWithFormat:@"%d",[results intForColumn:@"pdcSID"]];        model.name = [results stringForColumn:@"name"];        model.count = [results intForColumn:@"allcnt"];        model.imgPath = [results stringForColumn:@"img"];        model.qishu  = [results stringForColumn:@"qishu"];        model.all_need = [results stringForColumn:@"allneed"];        model.left_need = [results stringForColumn:@"leftneed"];                [arr addObject:model];    }        return arr;}//修改购物车中产品的数量- (BOOL)updataCountInCar:(int)newCount pdc:(int)pdcSID{    if(![self isExistInCar:pdcSID])/********如果购物车中没有该产品*********/    {        return NO;    }    NSString *sqll = @"update tb_car set allcnt = ? where pdcSID = ?";    BOOL isOK = [_database executeUpdate:sqll,[NSString stringWithFormat:@"%d",newCount],[NSString stringWithFormat:@"%d",pdcSID]];    if (isOK) {        return YES;    }    return NO;}//修改购物车中产品的选中状态- (BOOL)updateSelectedState:(int)select pdc:(int)pdcId{    if(![self isExistInCar:pdcId])/********如果购物车中没有该产品*********/    {        return NO;    }    NSString *sqll = @"update tb_car set selected = ? where pdcId = ?";    BOOL isOK = [_database executeUpdate:sqll,[NSString stringWithFormat:@"%d",select],[NSString stringWithFormat:@"%d",pdcId]];    if (isOK) {        return YES;    }    return NO;}//删除购物车中得产品记录- (BOOL)deletePdcInCarById:(int)pdcSID{    if(![self isExistInCar:pdcSID])/********如果购物车中没有该产品*********/    {        return NO;    }    NSString *sqll = @"delete from tb_car where pdcSID = ?";    BOOL isOK = [_database executeUpdate:sqll,[NSString stringWithFormat:@"%d",pdcSID]];    if (isOK) {        return YES;    }    return NO;}                                                      #pragma mark - 兴趣记录/*************** *兴趣记录部分 *****************///向购物车(兴趣)中添加信息- (BOOL)insertPdcToIntrestCarWithModel:(WYShopCarModel *)model{    if ([self isExistInIntrestCar:model.car_new_id.intValue]) {/***************如果该产品已存在于购物车,删掉*******/        [self deleIntrestPadWithId:model.car_new_id];    }    /***************加入购物车*******************/    NSString *sql = @"insert into tb_intrest_car(pdcID,pdcSID,img,name,allcnt) values (?,?,?,?,?)";    BOOL isInsertOK = [_database executeUpdate:sql,model.car_id,model.car_new_id, model.imgPath,model.name,[NSNumber numberWithInt:1]];    if (isInsertOK) {        return YES;    }     return NO;}//查询购物车（兴趣）中是否包含此产品记录- (BOOL)isExistInIntrestCar:(int)pdcSID{    NSString *sql = @"select * from tb_intrest_car where pdcSID = ?";    FMResultSet *results = [_database executeQuery:sql,[NSNumber numberWithInt:pdcSID]];        while (results.next) {        NSString * pdcSID2 = [results stringForColumn:@"pdcSID"];        if (pdcSID == pdcSID2.intValue) {                        return YES;        }    }        return NO;}//广东省深圳市南山区南头街道南海大道西海明珠大厦F座11楼B11//删除兴趣中某一条数据- (BOOL)deleIntrestPadWithId:(NSString*)pdcSID{    NSString *sqll = @"delete from tb_intrest_car where pdcSID = ?";    BOOL isOK = [_database executeUpdate:sqll,pdcSID];    if (isOK) {//        NSLog(@"删除成功");        return YES;    }    NSLog(@"删除失败");    return NO;}//获取购物车(兴趣)列表- (NSArray *)getAllpdcInIntrestCar{    NSString *sql = @"select * from tb_intrest_car order by carId desc";    FMResultSet *results = [_database executeQuery:sql];    NSMutableArray *arr = [NSMutableArray array];    while (results.next) {        WYShopCarModel *model = [[WYShopCarModel alloc]init];        model.car_id = [NSString stringWithFormat:@"%d",[results intForColumn:@"pdcId"]];        model.name = [results stringForColumn:@"name"];        model.count = [results intForColumn:@"allcnt"];        model.imgPath = [results stringForColumn:@"img"];        [arr addObject:model];    }        return arr;}/*********** *系统消息模块 **************///插入一条消息- (BOOL)insertNewToTBNew:(NewsModel *)model{    if ([WYPublic getUsefullStr:model.news_id].length == 0 || [self isExitInTabNew:model.news_id]) {/***************如果该产品已存在于购物车*******/        return NO;    }    /***************加入购物车*******************/    NSString *sql = @"insert into tb_News(newId,newTitle,newTime,isScanfed) values (?,?,?,?)";    BOOL isInsertOK = [_database executeUpdate:sql,model.news_id,model.news_title,model.news_time,[NSNumber numberWithInt:model.isScanfedFlag]];    if (isInsertOK) {        return YES;    }    return NO;}//判断消息是否存在记录中- (BOOL)isExitInTabNew:(NSString *)newId{    NSString *sql = @"select * from tb_News where newId = ?";    FMResultSet *results = [_database executeQuery:sql,[NSNumber numberWithInt:[newId intValue]]];        while (results.next) {        if ([newId isEqualToString:[results stringForColumn:@"newId"]]) {                        return YES;        }    }        return NO;}//改变消息是否阅读过的状态- (BOOL)changeScanfFlagWithNewId:(NSString *)newId{    if(![self isExitInTabNew:newId])/********如果记录中没有该消息*********/    {        return NO;    }    NSString *sqll = @"update tb_News set isScanfed = ? where newId = ?";    BOOL isOK = [_database executeUpdate:sqll,[NSNumber numberWithInt:1],newId];    if (isOK) {        return YES;    }    return NO;}//获取一个model比对- (NewsModel *)getNewsModelById:(NSString *)newId{    NSString *sql = @"select * from tb_News where newId = ?";    FMResultSet *results = [_database executeQuery:sql,newId];    while (results.next) {        NewsModel *model = [[NewsModel alloc]init];        model.news_id = [results stringForColumn:@"newId"];        model.news_title = [results stringForColumn:@"newTitle"];        model.isScanfedFlag = [results intForColumn:@"isScanfed"];        model.news_time = [results stringForColumn:@"newTime"];        return model;    }        return nil;}//获取所有的消息记录- (NSArray *)getAllNews{    NSString *sql = @"select * from tb_News order by tb_id desc";    FMResultSet *results = [_database executeQuery:sql];    NSMutableArray *arr = [NSMutableArray array];    while (results.next) {        NewsModel *model = [[NewsModel alloc]init];        model.news_id = [results stringForColumn:@"newId"];        model.news_title = [results stringForColumn:@"newTitle"];        model.isScanfedFlag = [results intForColumn:@"isScanfed"];        model.news_time = [results stringForColumn:@"newTime"];        [arr addObject:model];    }        return arr;}//清空数据库- (BOOL)deleteDatabase{    NSString *sql1 = @"delete from tb_car";    NSString *sql2 = @"delete from tb_history";    NSString *sql3 = @"delete from tb_intrest_car";    BOOL isOK1 = [_database executeUpdate:sql1];    BOOL isOK2 = [_database executeUpdate:sql2];    BOOL isOK3 = [_database executeUpdate:sql3];    if (isOK1 && isOK2 && isOK3) {        return YES;    }    return NO;}//关闭数据库- (void)closeDataBase{    if (_database) {        [_database close];    }}

0 0