GoodsBase-调用方法

本方法保存在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