FMDB使用实例
在FMDB中，除查询（executeQuery）以外的所有操作，都称为“更新”（executeUpdate）。
例如insert、update、delete、 create、drop等SQL语句命令都是更新操作。
一、使用队列（线程安全）代码如下：
static FMDatabaseQueue *_queue;
+ (void)setup
{
// 0.获得沙盒中的数据库文件名
NSString *path = [[NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"statuses.sqlite"];
// 1.创建队列
_queue = [FMDatabaseQueue databaseQueueWithPath:path];
// 2.创表
[_queue inDatabase:^(FMDatabase *db) {
[db executeUpdate:@"create table if not exists t_status (id integer primary key autoincrement, access_token text, idstr text, status blob);"];
}];
}
+ (void)addStatuses:(NSArray *)statusArray
{
for (IWStatus *status in statusArray) {
[self addStatus:status];
}
}
+ (void)addStatus:(IWStatus *)status
{
[self setup];
[_queue inDatabase:^(FMDatabase *db) {
// 1.获得需要存储的数据
NSString *accessToken = [IWAccountTool account].access_token;
NSString *idstr = status.idstr;
NSData *data = [NSKeyedArchiver archivedDataWithRootObject:status];
// 2.存储数据
[db executeUpdate:@"insert into t_status (access_token, idstr, status) values(?, ? , ?)", accessToken, idstr, data];
}];
[_queue close];
}
+ (NSArray *)statuesWithParam:(IWHomeStatusesParam *)param
{
[self setup];
// 1.定义数组
__block NSMutableArray *statusArray = nil;
// 2.使用数据库
[_queue inDatabase:^(FMDatabase *db) {
// 创建数组
statusArray = [NSMutableArray array];
// accessToken
NSString *accessToken = [IWAccountTool account].access_token;
FMResultSet *rs = nil;
if (param.since_id) { // 如果有since_id
rs = [db executeQuery:@"select * from t_status where access_token = ? and idstr > ? order by idstr desc limit 0,?;", accessToken, param.since_id, param.count];
} else if (param.max_id) { // 如果有max_id
rs = [db executeQuery:@"select * from t_status where access_token = ? and idstr <= ? order by idstr desc limit 0,?;", accessToken, param.max_id, param.count];
} else { // 如果没有since_id和max_id
rs = [db executeQuery:@"select * from t_status where access_token = ? order by idstr desc limit 0,?;", accessToken, param.count];
}
while (rs.next) {
NSData *data = [rs dataForColumn:@"status"];
IWStatus *status = [NSKeyedUnarchiver unarchiveObjectWithData:data];
[statusArray addObject:status];
}
}];
[_queue close];
// 3.返回数据
return statusArray;
}
二、使用队列事物，代码如下：
+ (void)testTransaction
{
[self setup];
[_queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
BOOL addJack = [db executeUpdate:@"INSERT INTO t_status(access_token) VALUES (?)", @"Jack"];
BOOL addRose = [db executeUpdate:@"INSERT INTO t_status(access_token) VALUES (?)", @"Rose"];
BOOL addJim = [db executeUpdate:@"INSERT INTO t_status(access_token) VALUES (?)", @"Jim"];
FMResultSet *rs = [db executeQuery:@"select * from t_status"];
while ([rs next]) {
// …
NSString * token = [rs stringForColumn:@"access_token"];
}
// 事务回滚
if (!addJack || !addRose || !addJim) {
*rollback = YES;
}
}];
[_queue close];
}
+ (void)delete:(id)sender {
[self setup];
[_queue inDatabase:^(FMDatabase *db) {
[db executeUpdate:@"DELETE FROM t_status WHERE id=?",@1];
}];
[_queue close];
}
+ (void)update:(id)sender {
[self setup];
[_queue inDatabase:^(FMDatabase *db) {
[db executeUpdate:@"UPDATE t_status SET name='Jack' WHERE id=?",@2];
}];
[_queue close];
}
