下载地址 http://pan.baidu.com/s/1qWrt9W4
//
// GCB_ProductDetailDB.h
// TestDemo001
//
// Created by Waltz3D on 14-3-13.
// Copyright (c) 2014年 Waltz. All rights reserved.
// #import <Foundation/Foundation.h> @interface GCB_ProductDetailDB : NSObject
+(id)share; #pragma mark - ------------------创建表------ #pragma mark -创建表
-(void)CreateTable; #pragma mark - -----------------插入-------
#pragma mark -拼接插入sql语句
-(NSString *)BuildInsertTableName:(NSString *)tablename keyArray:(NSArray *)keyArrary valueArrary:(NSArray *)valueArrary; #pragma mark -插入数据库操作
-(int )insertTablewithSql:(NSString *)sql; #pragma mark - ------------------删除------
#pragma mark -删除数据库操作
-(void)DeleteTableName:(NSString *)tableName PID:(int)ID; #pragma mark - ------------------更新------
#pragma mark -拼接更新sql语句
-(NSString *)BuildUpdateName:(NSString *)tablename keyArray:(NSArray *)keyArrary valueArrary:(NSArray *)valueArrary PID:(int)ID; #pragma mark -更新数据库操作
-(void)UpdateTableWithSql:(NSString *)sql; #pragma mark - ------------------查询------ #pragma mark -通过ID来查询数据
-(NSArray *)SelectTableName:(NSString *)tableName PID:(int)ID; #pragma mark -通过页码来查询数据（分页)
-(NSArray *)SelectTableName:(NSString *)tableName startPage:(int)page; #pragma mark -关闭数据库
-(void)closeDB;
#pragma mark-打开数据库
-(void)OpenDB;
@end
.m文件
//
// GCB_ProductDetailDB.m
// TestDemo001
//
// Created by Waltz3D on 14-3-13.
// Copyright (c) 2014年 Waltz. All rights reserved.
// #import "GCB_ProductDetailDB.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"
#import "FMDatabaseAdditions.h"
#import "GCB_Product.h" #define DB_PAGESIZE 50 @interface GCB_ProductDetailDB ()
{
FMDatabase *_db;
} @end @implementation GCB_ProductDetailDB +(id)share{
static dispatch_once_t onceToken;
static GCB_ProductDetailDB *db=nil;
dispatch_once(&onceToken, ^{
db=[[GCB_ProductDetailDB alloc] init];
});
return db;
} #pragma mark -关闭数据库
-(void)closeDB{
[_db close];
} #pragma mark-打开数据库
-(void)OpenDB{
[_db close];
[_db open];
} //===================创建表=======================
#pragma mark -创建数据库
-(void)CreateTable{
NSString *docs=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
docs=[docs stringByAppendingPathComponent:@"Boee.db"];
NSLog(@"--->doc=%@",docs); _db=[FMDatabase databaseWithPath:docs];
_db=[_db retain];
[_db open]; //现货
if (![_db tableExists:@"SpotProduce"]) {
NSArray *propertys=@[
@"ParentID"//分类ID
,@"ProductName"//品名
,@"ProductNum"//款号
,@"ProductCode"//条码
,@"ProductSize"//尺寸（具体数值）
,@"ProductPrice"//价格
,@"ProductPhoto"//大图
,@"AdPhoto"//南渄美钻广告图
,@"Description"//商品描述
,@"ProductType"//产品系列 ID（下单：1，现贷：2）
,@"Weigth"//产品重量
,@"Suttle"//净金重
,@"SetModel"//镶石类型
,@"Stuff"//材质（成色）
,@"ProductColor"//产品颜色
,@"Imprint"//字印
,@"Remark"//备注
,@"JoinDate"//最后更新时间
,@"JoinIP"//最后更新IP
,@"SubIntro"//副石集合
,@"Introduce"//主石集合
]; [self BuildCreateSqlWithTable:@"SpotProduce" Propertys:propertys]; }
//下单产品
if (![_db tableExists:@"OrderProduce"]) {
NSArray *propertys=@[
@"ParentID"//分类ID
,@"ProductName"//品名
,@"ProductNum"//款号
,@"ProductPhoto"//大图
,@"Description"//商品描述
,@"ProductType"//产品系列（下单：1，现贷：2）
,@"WeigthRange"//重量区间
,@"Remark"//备注
,@"JoinDate"//最后更新时间
,@"JoinIP"//最后更新IP
,@"DiamondIntr" ];
[self BuildCreateSqlWithTable:@"OrderProduce" Propertys:propertys];
} //分类
if (![_db tableExists:@"ProduceCategory"]) {
NSArray *propertys=@[
@"ID",
@"ParentID",
@"CatName",
@"CatPhoto",
@"Language" ];
[self BuildCreateSqlWithTable:@"ProduceCategory" Propertys:propertys];
} //南非美钻
if (![_db tableExists:@"SouthDiamond"]) {
NSArray *propertys=@[
@"Attr1",
@"Attr2",
@"AttrDescript",
@"AttrName",
@"AttrPhoto",
@"Id",
@"NodePath",
@"ParentId"
];
[self BuildCreateSqlWithTable:@"SouthDiamond" Propertys:propertys];
}
} #pragma mark -创建表
-( void)BuildCreateSqlWithTable:(NSString *)tableName Propertys:(NSArray *)propertys{
NSMutableString *sql=[NSMutableString string];
[sql appendFormat:@"CREATE TABLE %@ (PID INTEGER PRIMARY KEY AUTOINCREMENT,",tableName];
for (int i=0; i<propertys.count; i++) {
if (i==propertys.count-1) {
[sql appendFormat:@"%@ text)",propertys[i]];
}
else [sql appendFormat:@"%@ text,",propertys[i]];
}
BOOL flag= [_db executeUpdate:sql ]; if (flag) NSLog(@"create table %@ sucess",tableName);
else NSLog(@"create table %@ failed",tableName); } //===================删除=======================
#pragma mark -删除sql操作
//ID 等于0 删除所有表
-(void)DeleteTableName:(NSString *)tableName PID:(int)ID{
if(ID==0){
NSString *sql=[NSString stringWithFormat:@"DELETE FROM %@",tableName];
[_db executeUpdate:sql];
}
else{
NSString *sql=[NSString stringWithFormat:@"DELETE FROM %@ WHERE PID =%zi",tableName,ID];
[_db executeUpdate:sql];
}
if ([_db hadError])
{
NSLog(@"DELETE FROM %@ ERROR %d: %@",tableName, [_db lastErrorCode], [_db lastErrorMessage]);
}
} //===================查询=======================
#pragma mark -查询操作 -(NSArray *)SelectTableName:(NSString *)tableName PID:(int)ID{ NSMutableArray *temp=[NSMutableArray array];
FMResultSet *rs=nil;
if (ID==0) { NSString *sql=[NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY PID DESC",tableName];
rs=[_db executeQuery:sql];
}
else{
NSString *sql=[NSString stringWithFormat:@"SELECT * FROM %@ WHERE PID = %zi",tableName,ID];
rs=[_db executeQuery:sql];
}
while ([rs next]){
GCB_Product *product= [GCB_Product ProductWithResultSet:rs];
[temp addObject:product]; }
return temp;
} -(NSArray *)SelectTableName:(NSString *)tableName startPage:(int)page{ NSMutableArray *temp=[NSMutableArray array];
NSString *sql=[NSString stringWithFormat:@"SELECT * FROM %@ WHERE PID>=%zi AND PID<=%zi ORDER BY PID DESC",tableName,(page-1)*DB_PAGESIZE+1,page*DB_PAGESIZE];
FMResultSet *rs=[_db executeQuery:sql]; while ([rs next]){ if ([tableName isEqualToString:@"OrderProduce"]) {
GCB_Product *product= [GCB_Product ProductWithResultSet:rs];
[temp addObject:product];
}
}
return temp;
} //===================插入=======================
#pragma mark -插入数据库操作 -(int)insertTablewithSql:(NSString *)sql{
// NSLog(@"%@",sql);
[_db executeUpdate:sql];
if ([_db hadError])
{
NSLog(@"insertWith ERROR %d: %@", [_db lastErrorCode], [_db lastErrorMessage]);
return (- 1);
}
return [_db lastInsertRowId];
}
#pragma mark -拼接插入sql语句 -(NSString *)BuildInsertTableName:(NSString *)tablename keyArray:(NSArray *)keyArrary valueArrary:(NSArray *)valueArrary {
NSMutableString *appendStr=[NSMutableString string];
NSMutableString *paramStr=[NSMutableString string];
[appendStr appendFormat:@"INSERT INTO %@(",tablename]; for (int i=0; i<keyArrary.count; i++) {
NSString *value=valueArrary[i];
if (i==keyArrary.count-1) {
[appendStr appendFormat:@"%@)VALUES (",keyArrary[i]];
[paramStr appendFormat:@"'%@')",value];
}
else{
[appendStr appendFormat:@"%@,",keyArrary[i]];
[paramStr appendFormat:@"'%@',",value];
}
}
[appendStr appendString:paramStr];
return appendStr; } //===================更新========================
#pragma mark -更新数据库操作
-(void)UpdateTableWithSql:(NSString *)sql{
NSLog(@"%@",sql);
[_db executeUpdate:sql];
if ([_db hadError])
{
NSLog(@"UpdateWith ERROR %d: %@", [_db lastErrorCode], [_db lastErrorMessage]); }
else{
NSLog(@"更新成功");
}
} #pragma mark -拼接更新sql语句
-(NSString *)BuildUpdateName:(NSString *)tablename keyArray:(NSArray *)keyArrary valueArrary:(NSArray *)valueArrary PID:(int)ID{
NSMutableString *appendStr=[NSMutableString string];
[appendStr appendFormat:@"UPDATE %@ SET ",tablename];
for (int i=0; i<keyArrary.count; i++) {
NSString *value=valueArrary[i];
if (i==keyArrary.count-1)
[appendStr appendFormat:@"%@='%@'",keyArrary[i],value];
else
[appendStr appendFormat:@"%@='%@',",keyArrary[i],value];
}
[appendStr appendFormat:@" where PID=%zi",ID];
return appendStr; } @end