最近使用FMDB心得
廢話不說直接上代碼
DBConst.h
/** 數(shù)據(jù)庫名稱 */
static NSString * const cart_db_sqlite = @"chat_userinfo.sqlite";
/** 購物車 數(shù)據(jù)表名稱 */
static NSString * const cart_name = @"hpbd_cart_table";
/** 商品縮略圖 參數(shù)名 */
static NSString * const cart_table_mall_thumb = @"mall_thumb";
/** 商品名稱 參數(shù)名 */
static NSString * const cart_table_mall_title = @"mall_title";
/** 商品單價(jià) 參數(shù)名 */
static NSString * const cart_table_mall_price = @"mall_price";
/** 商品數(shù)量 參數(shù)名 */
static NSString * const cart_table_mall_count = @"mall_count";
/** 商品ID 參數(shù)名 */
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";
/** 創(chuàng)建時(shí)間 */
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;
/** 商品數(shù)量 */
@property (nonatomic, copy) NSString *mallcount;
/** 商品價(jià)格 */
@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;
/** 開啟本地?cái)?shù)據(jù)庫，創(chuàng)建購物車數(shù)據(jù)表 */
+ (void)startDBService;
/** 新增/修改 商品的 商品信息／數(shù)量 */
+ (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;
/** 更新商品數(shù)量 */
+ (void)updateMallCount:(NSString*)count
withMallId:(NSString*)mallid
type:(BOOL)isAdd;
/** 刪除商品 */
+ (void)deleteMallWithID:(NSString*)mallid;
/** 提交訂單成功后清空購物車 */
+ (void)clearCartList;
/** 查詢購物車列表 */
+ (NSArray*)queryCartDatas;
/** 獲取購物車內(nèi)商品數(shù)量 */
+ (NSString*)getCartCount;
/** 查詢某個(gè)商品的數(shù)量 */
+ (NSString*)getCountWithMallId:(NSString*)mallid;
/** 查詢商品當(dāng)前庫存量 */
+ (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 - 創(chuàng)建數(shù)據(jù)庫
+ (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];
/**
* 表中數(shù)據(jù)：商品ID、商品縮略圖、商品名稱抚吠、商品數(shù)量凑阶、商品單價(jià)林束、創(chuàng)建數(shù)據(jù)時(shí)間
*
*/
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(@"創(chuàng)建購物車數(shù)據(jù)表成功");
} else {
Dlog(@"創(chuàng)建購物車數(shù)據(jù)表失敗");
}
[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) { //已存儲(chǔ)惹恃，更新
// 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 { //未存儲(chǔ)庇茫，新增
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(@"[更新數(shù)據(jù)成功]");
} else {
Dlog(@"[新增數(shù)據(jù)成功]");
}
} else {
if (hasSaved) {
Dlog(@"[更新數(shù)據(jù)失敗]");
} else {
Dlog(@"[新增數(shù)據(jù)失敗]");
}
}
[self closeDB];
}
#pragma mark - 新增/修改 商品的 商品信息／數(shù)量
+ (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) { //已存儲(chǔ)恃锉，更新 ps：需要先查出數(shù)量搀菩，累加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 { //未存儲(chǔ)，新增
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(@"[更新數(shù)據(jù)成功]");
} else {
Dlog(@"[新增數(shù)據(jù)成功]");
}
} else {
if (hasSaved) {
Dlog(@"[更新數(shù)據(jù)失敗]");
} else {
Dlog(@"[新增數(shù)據(jù)失敗]");
}
}
[self closeDB];
}
#pragma mark - 更新商品數(shù)量
+ (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(@"更新商品數(shù)量成功");
} else {
Dlog(@"更新商品數(shù)量失敗");
}
[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:@"刪除失敗破托，請(qǐng)稍后重試"];
}
[self closeDB];
}
#pragma mark - 驗(yàn)證商品是否已存儲(chǔ)
- (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 - 打開數(shù)據(jù)庫
- (BOOL)openDB
{
return [self.cardDB open];
}
#pragma mark - 關(guān)閉數(shù)據(jù)庫
- (BOOL)closeDB
{
return [self.cardDB close];
}
app啟動(dòng)的時(shí)候調(diào)用
//購物車
[CardDBManage startDBService];
