FMDatabase操作sqlite数据库

1.FMDB有三个主要的类@H_403_2@

FMDatabase – 表示一个单独的sqlite数据库。用来执行sqlite的命令。@H_403_2@

FMResultSet – 表示FMDatabase执行查询后结果集。@H_403_2@

FMDatabaseQueue – 如果你想在多线程中执行多个查询或更新，你应该使用该类。这是线程安全的。@H_403_2@

@H_403_2@

通过指定sqlite数据库文件路径来创建FMDatabase对象

FMDatabase *db = [FMDatabase databaseWithPath:path];

if (![db open]) {

NSLog(@"数据库打开失败！");

}

文件路径有三种情况

（1）具体文件路径

　　如果不存在会自动创建

（2）空字符串@""

　　会在临时目录创建一个空的数据库

　　当FMDatabase连接关闭时，数据库文件也被删除

（3）nil

　　会创建一个内存中临时数据库，当FMDatabase连接关闭时，数据库会被销毁

2.FMResultSet提供了很多方法来获得所需的格式的值：@H_403_2@

intForColumn:@H_403_2@
longForColumn:@H_403_2@
longLongIntForColumn:@H_403_2@
boolForColumn:@H_403_2@
doubleForColumn:@H_403_2@
stringForColumn:@H_403_2@
dataForColumn:@H_403_2@
datanoCopyForColumn:@H_403_2@
UTF8StringForColumnIndex:@H_403_2@
objectForColumn:@H_403_2@
@H_403_2@

3.FMDB语句执行@H_403_2@@H_403_2@@H_403_2@

一切不是SELECT命令的命令都视为更新。这包括CREATE,UPDATE,INSERT,ALTER,@H_403_2@COMMIT,BEGIN,DETACH,DELETE,DROP,END,EXPLAIN,VACUUM,and REPLACE（等）。@H_403_2@简单来说，只要不是以SELECT开头的命令都是UPDATE命令。执行更新返回一个BOOL值。YES表示执行成功，否则表示有那些错误。@H_403_2@@H_403_2@
@H_403_2@@H_403_2@

@H_403_2@@H_403_2@@H_403_2@@H_403_2@

在FMDB中，除查询以外的所有操作，都称为“更新”

create、drop、insert、update、delete等

使用executeUpdate:方法执行更新

- (BOOL)executeUpdate:(NSString*)sql,...

-(BOOL)executeUpdateWithFormat:(NSString*)format,...

- (BOOL)executeUpdate:(NSString*)sqlwithArgumentsInArray:(NSArray *)arguments

示例

[db executeUpdate:@"UPDATE t_studentSET age = ? WHERE name = ?;",@20,@"Jack"]

@H_403_2@@H_403_2@@H_403_2@

SELECT命令就是查询，执行查询的方法是以 -excuteQuery开头的。@H_403_2@执行查询时，如果成功返回FMResultSet对象，错误返回nil. 与执行更新相当，@H_403_2@支持使用 NSError**参数。@H_403_2@
@H_403_2@@H_403_2@@H_403_2@

@H_403_2@@H_403_2@@H_403_2@@H_403_2@

查询方法

- (FMResultSet*)executeQuery:(NSString*)sql,...

- (FMResultSet*)executeQueryWithFormat:(NSString*)format,...

- (FMResultSet *)executeQuery:(NSString*)sql withArgumentsInArray:(NSArray *)arguments

示例

// 查询数据

FMResultSet *rs = [dbexecuteQuery:@"SELECT * FROM t_student"];

// 遍历结果集

while ([rs next]) {

NSString *name = [RSStringForColumn:@"name"];

int age = [rsintForColumn:@"age"];

double score = [rs doubleForColumn:@"score"];

}

4.FMDB中sqlite数据绑定语法@H_403_2@@H_403_2@@H_403_2@

sqlite会识别 “?” 为一个输入的点位符，这样的执行会接受一个可变参数（或者表示为其他参数，如NSArray,NSDictionary,或va_list等），会正确为您转义。@H_403_2@
@H_403_2@@H_403_2@@H_403_2@@H_403_2@

一定要注意（提供给 -executeUpdate: 方法的参数都必须是对象。就像以下的代码就无法工作，且会产生崩溃。@H_403_2@）参数必须是 NSObject 的子类，所以象 int,double,bool 这种基本类型，需要封装成对应的包装类才行@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

NSNumber * intNumber=[NSNumber numberWithInt:1];
    [self.database executeUpdate:@"insert into storeProduct(installDate,startTimes) values (？,？)",[NSDate date],intNumber];

@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

或者@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

-execute*WithFormat:的方法的内部实现会帮你封装数据，以下这些修饰符都可以使用： %@,%c,%s,%d,%D,@H_403_2@%i,%u,%U,%hi,%hu,%qi,%qu,%f,%g,%ld,%lu,%lld,and %llu.除此之外的修饰符可能导致无法预知的结果。@H_403_2@一些情况下，你需要在sql语句中使用 % 字符，你应该使用 %%。@H_403_2@
@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

NSNumber * intNumber=[NSNumber numberWithInt:1];
    [self.database executeUpdateWithFormat:@"insert into storeProduct(installDate,startTimes,request) values (%@,%@,%d)",intNumber,YES];

5.FMDatabaseQueue@H_403_2@多线程安全@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

在多个线程中同时使用一个FMDatabase实例是不明智的。现在你可以为每个线程创建一个FMDatabase对象。@H_403_2@
不要让多个线程分享同一个实例，它无法在多个线程中同时使用。若此，坏事会经常发生，程序会时不时崩溃，@H_403_2@
或者报告异常，或者陨石会从天空中掉下来砸到你Mac Pro.总之很崩溃。@H_403_2@
所以，不要初始化FMDatabase对象，然后在多个线程中使用。@H_403_2@
请使用 FMDatabaseQueue，它是你的朋友而且会帮助你。以下是使用方法：@H_403_2@
首先创建队列。@H_403_2@
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];@H_403_2@
这样使用。@H_403_2@

[queue inDatabase:^(FMDatabase *db) {@H_403_2@
[db executeUpdate:@"INSERT INTO myTable VALUES (?)",[NSNumber numberWithInt:1]];@H_403_2@
[db executeUpdate:@"INSERT INTO myTable VALUES (?)",[NSNumber numberWithInt:2]];@H_403_2@
[db executeUpdate:@"INSERT INTO myTable VALUES (?)",[NSNumber numberWithInt:3]];@H_403_2@
FMResultSet *rs = [db executeQuery:@"select * from foo"];@H_403_2@
while([rs next]) {@H_403_2@
…@H_403_2@
}@H_403_2@
}];@H_403_2@

像这样，轻松地把简单任务包装到事务里：@H_403_2@

[queue inTransaction:^(FMDatabase *db,BOOL *rollback) {@H_403_2@
[db executeUpdate:@"INSERT INTO myTable VALUES (?)",[NSNumber numberWithInt:3]];@H_403_2@
if (whoopsSomethingWrongHappened) {@H_403_2@
*rollback = YES; return;@H_403_2@
}@H_403_2@
// etc…@H_403_2@
[db executeUpdate:@"INSERT INTO myTable VALUES (?)",[NSNumber numberWithInt:4]];@H_403_2@
}];@H_403_2@

FMDatabaseQueue后台会建立系列化的G@H_403_2@-@H_403_2@C-D队列，并执行你传给G-C-D队列的块。这意味着你从多线程同时调用调用方法，@H_403_2@
GDC也会按它接收的块的顺序来执行。谁也不会吵到谁的脚，每个人都幸福。@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

6.总结写代码：@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@

#import <Foundation/Foundation.h>
#import "FMDB.h"
@interface sqlLiteSKStoreProduct : NSObject
-(BOOL)isTanchu;
-(void)setNotRequest;
-(void)resetStartTimes;
-(void)addRestartTimes;
@end

#import "sqlLiteSKStoreProduct.h"
@interface sqlLiteSKStoreProduct()
@property FMDatabase *database;
@end
@implementation sqlLiteSKStoreProduct
-(id)init{
	self=[super init];
	[self openSKStoreProductDatabase];
	[self createLoginInfoTable];
	return self;
}

-(void)dealloc{
	[_database commit];
	[_database close];
}

-(BOOL) openSKStoreProductDatabase{
	//获取Document文件夹下的数据库文件，没有则创建
	NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) objectAtIndex:0];
	NSString *dbPath = [docPath stringByAppendingPathComponent:@"SKStoreProduct.db"];
	//获取数据库并打开
	self.database  = [FMDatabase databaseWithPath:dbPath];
	if (![self.database open]) {
		NSLog(@"Open database Failed");
		return NO;
		self.database=nil;
	}
	else{
		return YES;
	}
}

-(BOOL) createLoginInfoTable{
	if (self.database!=nil) {
		return [self.database executeUpdate:@"create table if not exists storeProduct(id INTEGER PRIMARY KEY,installDate date,startTimes int,request bool)"];
	}
	else{
		return NO;
	}
}
-(BOOL)isTanchu{
	if (self.database!=nil) {
		FMResultSet *resultSet1 = [self.database executeQuery:@"select * from storeProduct"];
		if (resultSet1!=nil && ![resultSet1 next]) {
			NSNumber * intNumber=[NSNumber numberWithInt:1];
		   [self.database executeUpdateWithFormat:@"insert into storeProduct(installDate,YES];
		   return NO;
		}
		NSDate* installDate=nil;
		int restartTimes=0;
		int ID=-1;
		FMResultSet *resultSet = [self.database executeQuery:@"select * from storeProduct"];
		while ([resultSet next]) {
			BOOL requestBool =[resultSet boolForColumn:@"request"];
			if (!requestBool) {
				return NO;
			}
			installDate = [resultSet dateForColumn:@"installDate"];
			restartTimes= [resultSet intForColumn:@"startTimes"];
			ID=[resultSet intForColumn:@"id"];
			break;
		}
		NSCalendar *gregorian = [[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar];
		NSDate * curDate=[NSDate date];
		NSUInteger unitFlags = NSMonthCalendarUnit | NSDayCalendarUnit | NSYearCalendarUnit;
		NSDateComponents *components = [gregorian components:unitFlags fromDate:installDate toDate:curDate options:0];
		long days=[components day];
		if (days > 7 && restartTimes >15) {
			return YES;
		} else {
			return NO;
		}
	}
	else{
		return NO;
	}

}
-(void)setNotRequest{
	if (self.database!=nil) {
		FMResultSet *resultSet1 = [self.database executeQuery:@"select * from storeProduct"];
		if (resultSet1!=nil && ![resultSet1 next]) {
			NSNumber * intNumber=[NSNumber numberWithInt:1];
			[self.database executeUpdateWithFormat:@"insert into storeProduct(installDate,NO];
			return ;
		}
		int ID=0;
		FMResultSet *resultSet = [self.database executeQuery:@"select * from storeProduct"];
		while ([resultSet next]) {
			ID= [resultSet intForColumn:@"id"];
			break;
		}
		[self.database executeUpdateWithFormat:@"update storeProduct set request = %d where id = %d",NO,ID];
    }
}
-(void)resetStartTimes{
	if (self.database!=nil) {
		FMResultSet *resultSet1 = [self.database executeQuery:@"select * from storeProduct"];
		if (resultSet1!=nil && ![resultSet1 next]) {
			NSNumber * intNumber=[NSNumber numberWithInt:1];
			[self.database executeUpdateWithFormat:@"insert into storeProduct(installDate,YES];
			return ;
		}
		int ID=0;
		FMResultSet *resultSet = [self.database executeQuery:@"select * from storeProduct"];
		while ([resultSet next]) {
			ID= [resultSet intForColumn:@"id"];
			break;
		}
		NSNumber * intNumber=[NSNumber numberWithInt:1];
		[self.database executeUpdateWithFormat:@"update storeProduct set startTimes = %@ where id = %d",ID];
	}
}
-(void)addRestartTimes{
	if (self.database!=nil) {
		FMResultSet *resultSet1 = [self.database executeQuery:@"select * from storeProduct"];
		if (resultSet1!=nil && ![resultSet1 next]) {
			NSNumber * intNumber=[NSNumber numberWithInt:1];
			[self.database executeUpdateWithFormat:@"insert into storeProduct(installDate,YES];
			return ;
		}
		int ID=0;
		FMResultSet *resultSet = [self.database executeQuery:@"select * from storeProduct"];
		int restartTimes=0;
		while ([resultSet next]) {
			restartTimes= [resultSet intForColumn:@"startTimes"];
			ID= [resultSet intForColumn:@"id"];
			restartTimes++;
			break;
		}
		NSNumber * intNumber=[NSNumber numberWithInt:restartTimes];
		[self.database executeUpdateWithFormat:@"update storeProduct set startTimes = %@ where id = %d",ID];
	}
}
@end

参考：http://www.jb51.cc/article/p-kvuisyqi-nn.html@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@@H_403_2@