ios FMDB使用

作者: 贝勒老爷 | 来源:发表于2017-07-20 14:21 被阅读134次

最近使用FMDB心得
废话不说直接上代码

DBConst.h

/** 数据库名称 */
static NSString * const cart_db_sqlite = @"chat_userinfo.sqlite";
/** 购物车 数据表名称 */
static NSString * const cart_name = @"hpbd_cart_table";


/** 商品缩略图 参数名 */
static NSString * const cart_table_mall_thumb = @"mall_thumb";
/** 商品名称 参数名 */
static NSString * const cart_table_mall_title = @"mall_title";
/** 商品单价 参数名 */
static NSString * const cart_table_mall_price = @"mall_price";
/** 商品数量 参数名 */
static NSString * const cart_table_mall_count = @"mall_count";
/** 商品ID 参数名 */
static NSString * const cart_table_mall_id = @"mall_id";
/** 商铺名称 */
static NSString * const cart_table_shop_name = @"shop_name";
/** 商铺id */
static NSString * const cart_table_shop_id = @"shop_id";
/** 商品库存 */
static NSString * const cart_table_mall_stock = @"mall_stock";
/** 创建时间 */
static NSString * const cart_table_create_time = @"create_time";

//  CartDBData.h
#import <Foundation/Foundation.h>

@interface CartDBData : NSObject

/** 商品名称 */
@property (nonatomic, copy) NSString *mallname;
/** 商品缩略图 */
@property (nonatomic, copy) NSString *mallthumb;
/** 商品id */
@property (nonatomic, copy) NSString *mallid;
/** 商品数量 */
@property (nonatomic, copy) NSString *mallcount;
/** 商品价格 */
@property (nonatomic, copy) NSString *mallprice;
/** 商铺名称 */
@property (nonatomic, copy) NSString *shopname;
/** 商铺id */
@property (nonatomic, copy) NSString *shopid;
/** 商品库存 */
@property (nonatomic, copy) NSString *mallstock;

@end

//  CardDBManage.h

#import <Foundation/Foundation.h>
#import "FMDatabase.h"
#import "DBConst.h"
#import "CartDBData.h"

@interface CardDBManage : NSObject

@property (nonatomic, strong) FMDatabase *cardDB;

/** 开启本地数据库，创建购物车数据表 */
+ (void)startDBService;

/** 新增/修改 商品的 商品信息／数量 */
+ (void)updateMallID:(NSString*)mallid
               thumb:(NSString*)thumb
                name:(NSString*)name
               price:(NSString*)price
               count:(NSString*)count
            shopname:(NSString*)shaopname
              shopid:(NSString*)shopid
               stock:(NSString*)stock;

/** 更新商品数量 */
+ (void)updateMallCount:(NSString*)count
             withMallId:(NSString*)mallid
                   type:(BOOL)isAdd;

/** 删除商品 */
+ (void)deleteMallWithID:(NSString*)mallid;

/** 提交订单成功后清空购物车 */
+ (void)clearCartList;

/** 查询购物车列表 */
+ (NSArray*)queryCartDatas;

/** 获取购物车内商品数量 */
+ (NSString*)getCartCount;

/** 查询某个商品的数量 */
+ (NSString*)getCountWithMallId:(NSString*)mallid;

/** 查询商品当前库存量 */
+ (NSString*)getMallStock:(NSString*)mallid;

/** 新增/修改 商品的 商品信息 */
+ (void)updateMallID:(NSString*)mallid
               thumb:(NSString*)thumb
                name:(NSString*)name
               price:(NSString*)price
            shopname:(NSString*)shaopname
              shopid:(NSString*)shopid
               stock:(NSString*)stock;

//  CardDBManage.m

#import "CardDBManage.h"

@implementation CardDBManage
+ (CardDBManage*)shareInstance
{
    static id shareInstance = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        shareInstance = [[self alloc] init];
    });
    return shareInstance;
}

#pragma mark - 创建数据库
+ (void)startDBService
{
    [[CardDBManage shareInstance] startService];
}

#pragma mark -
- (void)startService
{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) lastObject];
    NSString *fileName = [docPath stringByAppendingPathComponent:HPString(@"%@",cart_db_sqlite)];
    Dlog(@"===> [购物车DB路径] %@ <===",fileName);
    self.cardDB =[FMDatabase databaseWithPath:fileName];
    [self openDB];
    /**
     *  表中数据：商品ID、商品缩略图、商品名称、商品数量、商品单价、创建数据时间
     *
     */
    NSString *createCartTable = HPString(
                                             @"create table if not exists %@(%@ integer primary key autoincrement, %@ text, %@ text, %@ text, %@ text, %@ text, %@ text, %@ text, %@ text);",
                                              cart_name,
                                              cart_table_mall_id,
                                              cart_table_mall_thumb,
                                              cart_table_mall_title,
                                              cart_table_mall_price,
                                              cart_table_mall_count,
                                              cart_table_shop_name,
                                              cart_table_shop_id,
                                              cart_table_mall_stock,
                                              cart_table_create_time);
    BOOL createCartTableResult = [self.cardDB executeUpdate:createCartTable];
    if (createCartTableResult) {
        Dlog(@"创建购物车数据表成功");
    } else {
        Dlog(@"创建购物车数据表失败");
    }
    [self closeDB];
}

/** 新增/修改 商品的 商品信息 */
+ (void)updateMallID:(NSString*)mallid thumb:(NSString*)thumb name:(NSString*)name price:(NSString*)price shopname:(NSString*)shaopname shopid:(NSString*)shopid stock:(NSString*)stock
{
    [[CardDBManage shareInstance] updateMallID:mallid thumb:thumb name:name price:price shopname:shaopname shopid:shopid stock:stock];
}

- (void)updateMallID:(NSString*)mallid thumb:(NSString*)thumb name:(NSString*)name price:(NSString*)price shopname:(NSString*)shaopname shopid:(NSString*)shopid stock:(NSString*)stock
{
    BOOL hasSaved = [self hasSaved:mallid];
    [self openDB];
    NSString *sql = nil;
    if (hasSaved) { //已存储，更新
//        sql = HPString(@"UPDATE %@ SET '%@' = '%@','%@' = '%@','%@' = '%@', '%@' = '%@', '%@' = '%@', '%@' = '%@', '%@' = '%@' WHERE %@ = %@",
//                       cart_name,
//                       cart_table_mall_thumb,
//                       thumb,
//                       cart_table_mall_title,
//                       name,
//                       cart_table_mall_price,
//                       price,
//                       cart_table_mall_count,
//                       @"0",
//                       cart_table_shop_name,
//                       shaopname,
//                       cart_table_shop_id,
//                       shopid,
//                       cart_table_mall_stock,
//                       stock,
//                       cart_table_mall_id,
//                       mallid);
    } else {    //未存储，新增
        sql = HPString(@"INSERT INTO %@ ('%@','%@','%@','%@','%@','%@','%@','%@','%@') VALUES ('%@','%@','%@','%@','%@','%@','%@','%@','%@')",
                       cart_name,
                       cart_table_mall_id,
                       cart_table_mall_thumb,
                       cart_table_mall_title,
                       cart_table_mall_price,
                       cart_table_mall_count,
                       cart_table_create_time,
                       cart_table_shop_name,
                       cart_table_shop_id,
                       cart_table_mall_stock,
                       mallid,
                       thumb,
                       name,
                       price,
                       @"0",
                       [NSString getCurrentDate],
                       shaopname,
                       shopid,
                       stock);
    }
    BOOL result = [self.cardDB executeUpdate:sql];
    if (result) {
        if (hasSaved) {
            Dlog(@"[更新数据成功]");
        } else {
            Dlog(@"[新增数据成功]");
        }
    } else {
        if (hasSaved) {
            Dlog(@"[更新数据失败]");
        } else {
            Dlog(@"[新增数据失败]");
        }
    }
    [self closeDB];
}

#pragma mark - 新增/修改 商品的 商品信息／数量
+ (void)updateMallID:(NSString *)mallid thumb:(NSString *)thumb name:(NSString *)name price:(NSString *)price count:(NSString *)count shopname:(NSString *)shaopname shopid:(NSString*)shopid stock:(NSString *)stock
{
    [[CardDBManage shareInstance] updateMallID:mallid thumb:thumb name:name price:price count:count shopname:shaopname shopid:shopid stock:stock];
}

#pragma mark -
- (void)updateMallID:(NSString *)mallid thumb:(NSString *)thumb name:(NSString *)name price:(NSString *)price count:(NSString *)count shopname:(NSString *)shaopname shopid:(NSString*)shopid stock:(NSString *)stock
{
    BOOL hasSaved = [self hasSaved:mallid];
    [self openDB];
    NSString *sql = nil;
    if (hasSaved) { //已存储，更新 ps：需要先查出数量，累加1
        NSString *countSql = HPString(@"select * from %@ where %@ = %@",cart_name,cart_table_mall_id,mallid);
        FMResultSet *countResult =  [self.cardDB executeQuery:countSql];
        NSString *addCount = @"1";
        NSString *subCount = stock;
        while ([countResult next]) {
            CartDBData *data = [[CartDBData alloc] init];
            data.mallcount = [countResult stringForColumn:cart_table_mall_count];
            data.mallstock = [countResult stringForColumn:cart_table_mall_stock];
            addCount = HPString(@"%d",data.mallcount.intValue+count.intValue);
            subCount = HPString(@"%d",data.mallstock.intValue - count.intValue);
        }

        sql = HPString(@"UPDATE %@ SET '%@' = '%@','%@' = '%@','%@' = '%@', '%@' = '%@', '%@' = '%@', '%@' = '%@', '%@' = '%@' WHERE %@ = %@",
                       cart_name,
                       cart_table_mall_thumb,
                       thumb,
                       cart_table_mall_title,
                       name,
                       cart_table_mall_price,
                       price,
                       cart_table_mall_count,
                       addCount,
                       cart_table_shop_name,
                       shaopname,
                       cart_table_shop_id,
                       shopid,
                       cart_table_mall_stock,
                       subCount,
                       cart_table_mall_id,
                       mallid);
    } else {    //未存储，新增
        NSString *currentStock = HPString(@"%d",stock.intValue-1);
        sql = HPString(@"INSERT INTO %@ ('%@','%@','%@','%@','%@','%@','%@','%@','%@') VALUES ('%@','%@','%@','%@','%@','%@','%@','%@','%@')",
                       cart_name,
                       cart_table_mall_id,
                       cart_table_mall_thumb,
                       cart_table_mall_title,
                       cart_table_mall_price,
                       cart_table_mall_count,
                       cart_table_create_time,
                       cart_table_shop_name,
                       cart_table_shop_id,
                       cart_table_mall_stock,
                       mallid,
                       thumb,
                       name,
                       price,
                       count,
                       [NSString getCurrentDate],
                       shaopname,
                       shopid,
                       currentStock);
    }
    BOOL result = [self.cardDB executeUpdate:sql];
    if (result) {
        if (hasSaved) {
            Dlog(@"[更新数据成功]");
        } else {
            Dlog(@"[新增数据成功]");
        }
    } else {
        if (hasSaved) {
            Dlog(@"[更新数据失败]");
        } else {
            Dlog(@"[新增数据失败]");
        }
    }
    [self closeDB];
}

#pragma mark - 更新商品数量
+ (void)updateMallCount:(NSString *)count withMallId:(NSString *)mallid type:(BOOL)isAdd
{
    [[CardDBManage shareInstance] updateMallCount:count withMallId:mallid type:isAdd];
}

#pragma mark -
- (void)updateMallCount:(NSString *)count withMallId:(NSString *)mallid type:(BOOL)isAdd
{
    [self openDB];
    
    NSString *countSql = HPString(@"select * from %@ where %@ = %@",cart_name,cart_table_mall_id,mallid);
    FMResultSet *countResult =  [self.cardDB executeQuery:countSql];
    NSString *stockCount = @"0";
    while ([countResult next]) {
        CartDBData *data = [[CartDBData alloc] init];
        data.mallstock = [countResult stringForColumn:cart_table_mall_stock];
        stockCount = HPString(@"%@",data.mallstock);
    }
    
    if (isAdd) {
        stockCount = HPString(@"%d",stockCount.intValue-1);
    } else {
        stockCount = HPString(@"%d",stockCount.intValue+1);
    }

    NSString *sql = HPString(@"UPDATE %@ SET '%@' = '%@', '%@'='%@' WHERE %@ = %@",
                             cart_name,
                             cart_table_mall_count,
                             count,
                             cart_table_mall_stock,
                             stockCount,
                             cart_table_mall_id,
                             mallid);
    
    BOOL result = [self.cardDB executeUpdate:sql];
    if (result) {
        Dlog(@"更新商品数量成功");
    } else {
        Dlog(@"更新商品数量失败");
    }
    [self closeDB];
}


#pragma mark - 删除商品
+ (void)deleteMallWithID:(NSString *)mallid
{
    [[CardDBManage shareInstance] deleteMallWithID:mallid];
}

#pragma mark -
- (void)deleteMallWithID:(NSString *)mallid
{
    NSString *sql = HPString(@"DELETE FROM %@ WHERE %@ = %@",cart_name,cart_table_mall_id,mallid);
    [self openDB];
    BOOL result = [self.cardDB executeUpdate:sql];
    if (result) {
        [HubLoading showText:@"删除成功"];
    } else {
        [HubLoading showText:@"删除失败，请稍后重试"];
    }
    [self closeDB];
}

#pragma mark - 验证商品是否已存储
- (BOOL)hasSaved:(NSString *)mallid
{
    if (HPVerifyStrLegal(mallid)) {
        [self openDB];
        NSString *verifySql = [NSString stringWithFormat:@"select * from %@ where %@ = %@",cart_name,cart_table_mall_id,mallid];
        FMResultSet *resultSet = [self.cardDB executeQuery:verifySql];
        if ([resultSet next]) {
            [self closeDB];
            return YES;
        }
        [self closeDB];
        return NO;
    }
    return NO;
}

#pragma mark - 提交订单成功后清空购物车
+ (void)clearCartList
{
    [[CardDBManage shareInstance] clearCartList];
}

#pragma mark -
- (void)clearCartList
{
    NSString *sql = HPString(@"DELETE FROM %@",cart_name);
    [self openDB];
    BOOL clearResult = [self.cardDB executeUpdate:sql];
    if (clearResult) {
        Dlog(@"已清空购物车");
    } else {
        Dlog(@"清空购物车失败");
    }
    [self closeDB];
}

#pragma mark - 查询购物车列表
+ (NSArray*)queryCartDatas
{
    return [[CardDBManage shareInstance] queryCartDatas];
}

#pragma mark -
- (NSArray*)queryCartDatas
{
    NSMutableArray *cartDatas = [NSMutableArray arrayWithCapacity:0];
    NSString *sql = HPString(@"select * from %@",cart_name);
    [self openDB];
    FMResultSet *queryResult =  [self.cardDB executeQuery:sql];
    while ([queryResult next]) {
        if ([[queryResult stringForColumn:cart_table_mall_count] isEqualToString:@"0"])
        {
            break;
        }
        CartDBData *data = [[CartDBData alloc] init];
        data.mallid = [queryResult stringForColumn:cart_table_mall_id];
        data.mallthumb = [queryResult stringForColumn:cart_table_mall_thumb];
        data.mallname = [queryResult stringForColumn:cart_table_mall_title];
        data.mallcount = [queryResult stringForColumn:cart_table_mall_count];
        data.mallprice = [queryResult stringForColumn:cart_table_mall_price];
        data.shopname = [queryResult stringForColumn:cart_table_shop_name];
        data.shopid = [queryResult stringForColumn:cart_table_shop_id];
        [cartDatas addObject:data];
    }
    [self closeDB];
    return cartDatas;
}

+ (NSString*)getCartCount
{
    return [[CardDBManage shareInstance] getCartCount];
}

- (NSString*)getCartCount
{
    NSInteger count = 0;
    NSString *sql = HPString(@"select * from %@",cart_name);
    [self openDB];
    FMResultSet *queryResult =  [self.cardDB executeQuery:sql];
    while ([queryResult next]) {
        CartDBData *data = [[CartDBData alloc] init];
        data.mallcount = [queryResult stringForColumn:cart_table_mall_count];
        count = count+data.mallcount.integerValue;
    }
    [self closeDB];
    return HPString(@"%ld",(long)count);
}

+ (NSString*)getCountWithMallId:(NSString *)mallid
{
    return [[CardDBManage shareInstance] getCountWithMallId:mallid];
}

- (NSString*)getCountWithMallId:(NSString *)mallid
{
    NSInteger count = 0;
    NSString *sql = HPString(@"select * from %@ where %@ = %@",cart_name,cart_table_mall_id,mallid);
    [self openDB];
    FMResultSet *queryResult =  [self.cardDB executeQuery:sql];
    while ([queryResult next]) {
        CartDBData *data = [[CartDBData alloc] init];
        data.mallcount = [queryResult stringForColumn:cart_table_mall_count];
        count = count+data.mallcount.integerValue;
    }
    [self closeDB];
    return HPString(@"%ld",(long)count);
}

+ (NSString*)getMallStock:(NSString *)mallid
{
    return [[CardDBManage shareInstance] getMallStock:mallid];
}

- (NSString*)getMallStock:(NSString *)mallid
{
    NSInteger count = 0;
    NSString *sql = HPString(@"select * from %@ where %@ = %@",cart_name,cart_table_mall_id,mallid);
    [self openDB];
    FMResultSet *queryResult =  [self.cardDB executeQuery:sql];
    while ([queryResult next]) {
        CartDBData *data = [[CartDBData alloc] init];
        data.mallstock = [queryResult stringForColumn:cart_table_mall_stock];
        count = data.mallstock.integerValue;
    }
    [self closeDB];
    return HPString(@"%ld",(long)count);
}

#pragma mark - 打开数据库
- (BOOL)openDB
{
    return [self.cardDB open];
}

#pragma mark - 关闭数据库
- (BOOL)closeDB
{
    return [self.cardDB close];
}

app启动的时候调用
//购物车
    [CardDBManage startDBService];

网友评论

footSInRoad:请问：HPString这是什么，我复制运行报错，还有import "DBConst.h"这个也没有，能给把他们发给我么 1379516894@qq.com 谢了
footSInRoad:@猜猜猜猜猜恩恩晓得了还有一个 HPVerifyStrLegal这个验证方法怎么写的？
贝勒老爷:@footSInRoad
/** 数据库名称 */
static NSString * const cart_db_sqlite = @"chat_userinfo.sqlite";
/** 购物车数据表名称 */
static NSString * const cart_name = @"hpbd_cart_table";

/** 商品缩略图参数名 */
static NSString * const cart_table_mall_thumb = @"mall_thumb";
/** 商品名称参数名 */
static NSString * const cart_table_mall_title = @"mall_title";
/** 商品单价参数名 */
static NSString * const cart_table_mall_price = @"mall_price";
/** 商品数量参数名 */
static NSString * const cart_table_mall_count = @"mall_count";
/** 商品ID 参数名 */
static NSString * const cart_table_mall_id = @"mall_id";
/** 商铺名称 */
static NSString * const cart_table_shop_name = @"shop_name";
/** 商铺id */
static NSString * const cart_table_shop_id = @"shop_id";
/** 商品库存 */
static NSString * const cart_table_mall_stock = @"mall_stock";
/** 创建时间 */
static NSString * const cart_table_create_time = @"create_time";
贝勒老爷:@footSInRoad 是一个字符串拼接
footSInRoad:谢谢分享！！！

ios FMDB使用

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

自己尝试等装一下

学无止境

上海恩美路演

商城

iOS技术资料

将来跳槽用

恩美第二个APP项目

牛叉的demo