数据库工具类 (通过外面传入一个数值和表中字段值进行比较)

#import <Foundation/Foundation.h>

@interface QJFMDBTool : NSObject

/**

 *  根据外面传入一个数值和表中字段值进行比较

 *

 *  @param params 比较的参数

 */

+ (NSArray *)compareWithParams:(NSString *)str;

/**

 *  存储数据到沙盒中

 *

 *  @param goods 需要存储的数据

 */

+  (void)saveGoods:(NSArray *)goods;

+ (NSArray *)delGoods:(NSString *)str;

@end

#import "QJFMDBTool.h"

#import "FMDB.h"

@implementation QJFMDBTool

static FMDatabase *_db;

+ (void)initialize

{

    // 1.打开数据库

    NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"goods.sqlite"];

    _db = [FMDatabase databaseWithPath:path];

    [_db open];

    

    // 2.创表

    [_db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_goods (goodIdtext PRIMARY KEY, good blob NOT NULL);"];

}

+ (NSArray *)compareWithParams:(NSString *)str

{

    // SELECT 列名称 FROM 表名称 WHERE列 运算符 值

    NSString *sql = [NSStringstringWithFormat:@"SELECT * FROM t_goods WHERE goodId = %@ ;", str];

    // 执行SQL

    FMResultSet *set = [_db executeQuery:sql];

    NSMutableArray *datas = [NSMutableArray array];

    while (set.next) {

        NSData *goodsData = [set objectForColumnName:@"good"];

        NSDictionary *goodD = [NSKeyedUnarchiver unarchiveObjectWithData:goodsData];

        [datas addObject:goodD];

    }

    return datas;

}

+ (void)saveGoods:(NSArray *)goods

{

   // 要将一个对象存进数据库的blob字段,最好先转为NSData

    // 一个对象要遵守NSCoding协议,实现协议中相应的方法,才能转成NSData

    for (NSDictionary *good in goods) {

        // NSDictionary --> NSData

        NSData *goodsData = [NSKeyedArchiver archivedDataWithRootObject:good];

        [_db executeUpdateWithFormat:@"INSERT INTO t_goods(goodId, good) VALUES (%@, %@);", good[@"goodId"],goodsData];

    }

}

+ (NSArray *)delGoods:(NSString *)str

{

    // DELETE FROM 表名称 WHERE 列名称 = 值

    NSString *sql = [NSString stringWithFormat:@"DELETE FROM t_goods WHERE goodId = %@ ;", str];

    // 执行SQL

    FMResultSet *set = [_db executeQuery:sql];

    NSMutableArray *datas = [NSMutableArray array];

    while (set.next) {

        NSData *goodsData = [set objectForColumnName:@"good"];

        NSDictionary *goodD = [NSKeyedUnarchiver unarchiveObjectWithData:goodsData];

        [datas addObject:goodD];

    }

    return datas;

}