本方法保存在GoodsBase.m文件中
#import"GoodsBase.h"
@implementationGoodsBase
//数据库创建：0：id，1：title，2：price，3：image，4：goodsid,5：number
+(NSMutableArray*)findAll
{
//打开数据库
sqlite3 * db =[DataBase openDB];
//创建一个可执行的SQL语句对象
sqlite3_stmt * stmt =nil;
//准备SQLite语句方法
int result =
sqlite3_prepare_v2(db, "select * from goodsinfo", -1, &stmt,
nil);//长度-1，不计算长度
//存放数据库中的所有商品，形式为数组包字典
NSMutableArray *goodsArray = [[NSMutableArray alloc]init];
//判断SQLite语句是否执行成功
if (result ==SQLITE_OK) {
while (SQLITE_ROW == sqlite3_step(stmt)) {//一次一个读取数据库中的数据项
int ID = sqlite3_column_int(stmt, 0);//column的计数与数据项键值在数据库的位置相同，计数从0开始
double price =sqlite3_column_double(stmt, 2);
int goodsID =sqlite3_column_int(stmt, 4);
int number =sqlite3_column_int(stmt, 5);
//标题
const unsigned char *title = sqlite3_column_text(stmt, 1);
NSString * goodsTitle= [NSString stringWithUTF8String:(char *)title];
//图片数据
int length = sqlite3_column_bytes(stmt, 3);//字节长度
const void * value = sqlite3_column_blob(stmt, 3);//二进制数读取
NSData * imageData =[NSData dataWithBytes:value length:length];
[goodsArrayaddObject:@{@"id":@(ID),@"title":goodsTitle,@"price":@(price),@"imagedata":imageData,@"goodsid":@(goodsID),@"number":@(number)}];
//NSLog(@"findAll方法执行成功！");
}
}else{
NSLog(@"findAll方法执行错误，未能成功读取数据库信息！！代码：%d(成功码：SQLITE_OK==0)",result);
}
[DataBase closeDB];
return goodsArray;
}
//数据库创建：0：id，1：title，2：price，3：image，4：goodsid,5：number
//通过商品编号查找对应商品条目
+(NSDictionary*)findByGoodsID:(int)goodsID
{
//打开数据库
sqlite3 * db =[DataBase openDB];
sqlite3_stmt * stmt =nil;
int result =sqlite3_prepare_v2(db, "select * from goodsinfo where goodsid = ?",-1, &stmt, nil);
if (result == SQLITE_OK) {//检查准备是否成功启动
sqlite3_bind_int(stmt,1, goodsID);//绑定准备条件
if (SQLITE_ROW == sqlite3_step(stmt)){
int ID =sqlite3_column_int(stmt, 0);
const unsigned char *title = sqlite3_column_text(stmt, 1);
NSString * goodsTitle= [NSString stringWithUTF8String:(char *)title];
double price =sqlite3_column_double(stmt, 2);
int length = sqlite3_column_bytes(stmt,3);
const void * value =sqlite3_column_blob(stmt, 3);
NSData * imageData =[NSData dataWithBytes:value length:length];
int goodsID =sqlite3_column_int(stmt, 4);
int number =sqlite3_column_int(stmt, 5);
NSLog(@"findByGoodsID方法执行成功！");
return@{@"id":@(ID),@"title":goodsTitle,@"price":@(price),@"imagedata":imageData,@"goodsid":@(goodsID),@"number":@(number)};
}
}
NSLog(@"findByGoodsID方法执行错误，通过编号查找项目失败！代码：%d(成功码：SQLITE_OK==0)",result);
return nil;
}
//通过商品编号删除对应商品条目
+(BOOL)deleteByGoodsID:(int)goodsID
{
sqlite3 * db =[DataBase openDB];
sqlite3_stmt * stmt =nil;
int result =sqlite3_prepare_v2(db, "delete from goodsinfo where goodsid = ?", -1,&stmt, nil);
if (result ==SQLITE_OK) {
sqlite3_bind_int(stmt,1, goodsID);
if (SQLITE_DONE ==sqlite3_step(stmt)) {
NSLog(@"deleteByGoodsID方法执行成功！");
return YES;
}
}
NSLog(@"deleteByGoodsID方法执行错误，通过编号查找项目失败！代码：%d(成功码：SQLITE_OK==0)",result);
return NO;
}
//计数
+(int)count
{
sqlite3 * db =[DataBase openDB];
sqlite3_stmt * stmt =nil;
int result =sqlite3_prepare_v2(db, "select count(*) from goodsinfo", -1,&stmt, nil);
if (result ==SQLITE_OK) {
if (SQLITE_ROW ==sqlite3_step(stmt)) {
int count =sqlite3_column_int(stmt, 0);
return count;
}
}
return NO;
}
//数据库创建：0：id，1：title，2：price，3：image，4：goodsid,5：number
+(BOOL)updataGoodsWithTitle:(NSString*)newTitle andPrice:(double)newPrice andImage:(NSData *)imageDataandNumber:(int)newNumber ByGoodsID:(int)goodsID
{
sqlite3 * db =[DataBase openDB];
sqlite3_stmt * stmt =nil;
int result = sqlite3_prepare_v2(db,"update goodsinfo set title = ?,price = ?,image = ?,number = ? wheregoodsid = ?", -1, &stmt, nil);
if (result ==SQLITE_OK) {
sqlite3_bind_int(stmt,5, goodsID);
sqlite3_bind_text(stmt,1, [newTitle UTF8String], -1, nil);
sqlite3_bind_double(stmt,2, newPrice);
sqlite3_bind_blob(stmt,3, [imageData bytes], (int)[imageData length], nil);
sqlite3_bind_int(stmt,4, newNumber);
if (SQLITE_DONE ==sqlite3_step(stmt)) {
NSLog(@"updataGoods方法执行成功，项目信息已更新！");
return YES;
}
}
NSLog(@"updataGoods方法执行错误，通过编号更新项目失败！代码：%d(成功码：SQLITE_OK==0)",result);
return NO;
}
//根据项目ID直接进行更新
+(BOOL)updataGoodsWithNumber:(int)newNumberByGoodsID:(int)goodsID
{
sqlite3 * db =[DataBase openDB];
sqlite3_stmt * stmt =nil;
int result =sqlite3_prepare_v2(db, "update goodsinfo set number = ? where goodsid =?", -1, &stmt, nil);
if (result ==SQLITE_OK) {
sqlite3_bind_int(stmt,2, goodsID);
sqlite3_bind_int(stmt,1, newNumber);
if (SQLITE_DONE ==sqlite3_step(stmt)) {
NSLog(@"updataGoodsWithNumber方法执行成功，项目的number选项已更新！");
return YES;
}
}
NSLog(@"updataGoodsWithNumber方法执行错误，通过编号更新项目失败！代码：%d(成功码：SQLITE_OK==0)",result);
return NO;
}
//数据库创建：0：id，1：title，2：price，3：image，4：goodsid,5：number
+(BOOL)addGoodsWithTitle:(NSString*)newTitle andPrice:(double)newPrice andImage:(NSData *)imageDataandGoodsID:(int)goodsID andNumber:(int)newNumber
{
sqlite3 * db =[DataBase openDB];
sqlite3_stmt * stmt =nil;
int result =sqlite3_prepare(db, "insert intogoodsinfo(title,price,image,goodsid,number) values(?,?,?,?,?)", -1,&stmt, nil);
if (result ==SQLITE_OK) {
sqlite3_bind_text(stmt,1, [newTitle UTF8String], -1, nil);
sqlite3_bind_double(stmt,2, newPrice);
sqlite3_bind_blob(stmt,3, [imageData bytes], (int)[imageData length], nil);
sqlite3_bind_int(stmt,4, goodsID);
sqlite3_bind_int(stmt,5, newNumber);
if (SQLITE_DONE ==sqlite3_step(stmt)) {
NSLog(@"addGoods方法执行成功,项目已添加！");
return YES;
}
}
NSLog(@"addGoods方法执行错误，通过编号添加项目失败！代码：%d(成功码：SQLITE_OK==0)",result);
return NO;
}
//数据库创建：0：id，1：title，2：price，3：image，4：goodsid,5：number
//计算商品总价
+(NSNumber *)sum
{
sqlite3 * db =[DataBase openDB];
sqlite3_stmt * stmt =nil;
int result =sqlite3_prepare_v2(db, "select * from goodsinfo", -1, &stmt,nil);
double sumForGoods =0;
if (result ==SQLITE_OK) {
while (SQLITE_ROW ==sqlite3_step(stmt)) {
double price =sqlite3_column_double(stmt, 2);
int number =sqlite3_column_int(stmt, 5);
sumForGoods += price *number;
}
}else{
NSLog(@"sum方法执行错误，商品总价计算失败！代码：%d(成功码：SQLITE_OK==0)",result);
return nil;
}
NSLog(@"sum方法执行成功,商品总价：%0.lf",sumForGoods);
return @(sumForGoods);
}
@end
