数据库操作(使用FMDB)

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