//1.创建表
[_db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_status (id integer PRIMARY KEY , status blob NOT NULL , idstr text NOT NULL ); "];
增
//插入数据
+ (void)saveStatuses:(NSArray *)statuses
{
// 要将一个对象存进数据库的blob字段,最好先转为NSData
// 一个对象要遵守NSCoding 协议,实现协议中相应的方法,才能转成NSData
for (NSDictionary *status in statuses) {
NSData *data = [NSKeyedArchiver archivedDataWithRootObject:status];
[_db executeUpdateWithFormat:@"INSERT INTO t_status(status, idstr) VALUES (%@, %@);" , data ,status[@"idstr"]];
}
}
+ (void)addCollectDeal:(MTDeal *)deal
{
NSData *data = [NSKeyedArchiver archivedDataWithRootObject:deal];
[_db executeUpdateWithFormat:@"INSERT INTO t_collect_deal(deal, deal_id) VALUES(%@, %@);" , data , deal.deal_id];
}
删
//删除数据
[_db executeUpdateWithFormat:@"DELETE FROM t_collect_deal WHERE deal_id = %@; ", deal.deal_id];
查
//1.limit中从pos开始选取size条记录
FMResultSet *set = [_db executeQueryWithFormat:@"SELECT * FROM t_collect_deal ORDER BY id DESC LIMIT %d,%d;" , pos, size ];
while (set .next ) {
}
//2. 查询数量
+ (BOOL)isCollected:(MTDeal *)deal
{
FMResultSet *set = [_db executeQueryWithFormat:@"SELECT count(*) AS deal_count FROM t_collect_deal WHERE deal_id = %@;" , deal.deal_id];
[set next ];
//#warning 索引从1开始
return [set intForColumn:@"deal_count" ] == 1 ;
}
+ (int)collectDealsCount
{ FMResultSet *set = [_db executeQueryWithFormat:@"SELECT count(*) AS deal_count FROM t_collect_deal;" ];
[set next ];
return [set intForColumn:@"deal_count" ];
}