最近使用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];
