FMDB介绍
FMDB是iOS平台的SQLite数据库框架
FMDB以OC的方式封装了SQLite的C语言API
使用起来更加面向对象，省去了很多麻烦、冗余的C语言代码
提供了多线程安全的数据库操作方法，有效地防止数据混乱
FMDB基本使用
FMDB GitHub链接:https://github.com/ccgus/fmdb
1.导入FMDB文件
2.导入系统依赖库sqlite3.0.tbd
创建数据库并打开
```
/*1. 如果该路径下已经存在该数据库，直接获取该数据库;
2. 如果不存在就创建一个新的数据库;
3. 如果传@""，会在临时目录创建一个空的数据库，当数据库关闭时，数据库文件也被删除;
4. 如果传nil，会在内存中临时创建一个空的数据库，当数据库关闭时，数据库文件也被删除;*/
+ (FMDatabase *)databaseWithPath:(NSString*)filePath;
```
// 1.获得数据库文件的路径NSString*doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) lastObject];NSString*filename = [doc stringByAppendingPathComponent:@"my.sqlite"];// 2.得到数据库FMDatabase* db = [FMDatabase databaseWithPath:filename];// 3.打开数据库if([db open]) {NSLog(@"打开数据库成功"); }else{NSLog(@"打开数据库失败"); }
执行更新
在FMDB中，除查询以外的所有操作，都称为“更新”
create、drop、insert、update、delete等
使用executeUpdate:方法执行更新
/* 执行更新的SQL语句，字符串里面的"?"，依次用后面的参数替代，必须是对象，不能是int等基本类型 */- (BOOL)executeUpdate:(NSString*)sql,... ;/* 执行更新的SQL语句，可以使用字符串的格式化进行构建SQL语句 */- (BOOL)executeUpdateWithFormat:(NSString*)format,... ;/* 执行更新的SQL语句，字符串中有"?"，依次用arguments的元素替代 */- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray*)arguments;
/* 1. 直接使用完整的SQL更新语句 */[database executeUpdate:@"insert into mytable(num,name,sex) values(0,'liuting','m');"];NSString*sql =@"insert into mytable(num,name,sex) values(?,?,?);";/* 2. 使用不完整的SQL更新语句，里面含有待定字符串"?"，需要后面的参数进行替代 */[database executeUpdate:sql,@0,@"liuting",@"m"];/* 3. 使用不完整的SQL更新语句，里面含有待定字符串"?"，需要数组参数里面的参数进行替代 */[database executeUpdate:sql withArgumentsInArray:@[@0,@"liuting",@"m"]];/* 4. SQL语句字符串可以使用字符串格式化，这种我们应该比较熟悉 */[database executeUpdateWithFormat:@"insert into mytable(num,name,sex) values(%d,%@,%@);",0,@"liuting","m"];
执行查询
- (FMResultSet *)executeQuery:(NSString*)sql, ...- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ... - (FMResultSet *)executeQuery:(NSString*)sql withArgumentsInArray:(NSArray*)arguments
处理结果FMResultSet的方法
/* 获取下一个记录 */- (BOOL)next;/* 获取记录有多少列 */- (int)columnCount;/* 通过列名得到列序号，通过列序号得到列名 */- (int)columnIndexForName:(NSString*)columnName;- (NSString*)columnNameForIndex:(int)columnIdx;/* 获取存储的整形值 */- (int)intForColumn:(NSString*)columnName;- (int)intForColumnIndex:(int)columnIdx;/* 获取存储的长整形值 */- (long)longForColumn:(NSString*)columnName;- (long)longForColumnIndex:(int)columnIdx;/* 获取存储的布尔值 */- (BOOL)boolForColumn:(NSString*)columnName;- (BOOL)boolForColumnIndex:(int)columnIdx;/* 获取存储的浮点值 */- (double)doubleForColumn:(NSString*)columnName;- (double)doubleForColumnIndex:(int)columnIdx;/* 获取存储的字符串 */- (NSString*)stringForColumn:(NSString*)columnName;- (NSString*)stringForColumnIndex:(int)columnIdx;/* 获取存储的日期数据 */- (NSDate*)dateForColumn:(NSString*)columnName;- (NSDate*)dateForColumnIndex:(int)columnIdx;/* 获取存储的二进制数据 */- (NSData*)dataForColumn:(NSString*)columnName;- (NSData*)dataForColumnIndex:(int)columnIdx;/* 获取存储的UTF8格式的C语言字符串 */- (constunsignedcahr *)UTF8StringForColumnName:(NSString*)columnName;- (constunsignedcahr *)UTF8StringForColumnIndex:(int)columnIdx;/* 获取存储的对象，只能是NSNumber、NSString、NSData、NSNull */- (id)objectForColumnName:(NSString*)columnName;- (id)objectForColumnIndex:(int)columnIdx;
如何使用
+ (NSArray *)shops{// 得到结果集FMResultSet *set= [_db executeQuery:@"SELECT * FROM t_shop;"];// 不断往下取数据NSMutableArray *shops = [NSMutableArrayarray];while(set.next) {// 获得当前所指向的数据HMShop *shop = [[HMShop alloc] init]; shop.name = [setstringForColumn:@"name"]; shop.price = [setdoubleForColumn:@"price"]; [shops addObject:shop]; }returnshops;}
关闭数据库
[_db close];
FMDabaseQueue的使用
FMDatabase这个类是线程不安全的，如果在多个线程中同时使用一个FMDatabase实例，会造成数据混乱等问题
为了保证线程安全，FMDB提供方便快捷的FMDatabaseQueue类
创建FMDabaseQueue
NSString*path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) lastObject] stringByAppendingPathComponent:@"shops.sqlite"]; FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];
使用FMDabaseQueue
[queue inDatabase:^(FMDatabase*db) {//插入记录到表中NSString*sqlStr =@"CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real);"；BOOLresult = [db executeUpdate:sqlStr];if(!result) {NSLog(@"error when insert into database table"); [db close]; }}];
事务
事务，是指作为单个逻辑工作单元执行的一系列操作，要么完整地执行，要么完全地不执行。
比如你要更新数据库的大量数据，我们需要确保所有的数据更新成功才行，如果在更新期间出现错误，就不好办了。如果我们不使用事务，我们的更新操作直接对每个记录生效，万一遇到更新错误，已经更新的数据怎么办？难道我们要一个一个去找出来修改回来吗？事务就是解决这种情况的，只有所有的数据更新正确才成功，如果有一条更新错误，那么所有的数据都回到解放前，变为没有更新状态。
来个demo更好理解吧
FMDatabase使用事务
//事务 -(void)startTransaction {// 开启事务[self.database beginTransaction];BOOLisRollBack =NO;@try{for(inti =0; i<100; i++) {NSNumber*num = @(i+1);NSString*name = [[NSStringalloc] initWithFormat:@"student_%d",i];NSString*sql =@"insert into mytable(num,name) values(?,?);";BOOLresult = [database executeUpdate:sql,num,name,sex];if( !result ) {NSLog(@"插入失败！");return; } } }@catch(NSException*exception) { isRollBack =YES;// 事务回退[self.database rollback]; }@finally{if(!isRollBack) {//事务提交[self.database commit]; } } }
FMDatabaseQueue使用事务
//多线程事务 - (void)transactionByQueue {//开启事务[self.queue inTransaction:^(FMDatabase *db,BOOL*rollback) {for(inti =0; i<500; i++) {NSNumber*num = @(i+1);NSString*name = [[NSStringalloc] initWithFormat:@"student_%d",i];NSString*sex = (i%2==0)?@"f":@"m";NSString*sql =@"insert into mytable(num,name,sex) values(?,?,?);";BOOLresult = [db executeUpdate:sql,num,name,sex];if( !result ) {//当最后*rollback的值为YES的时候，事务回退，如果最后*rollback为NO，事务提交*rollback =YES;return; } } }]; }
demo展示
@interfaceViewController()@property(nonatomic,strong) FMDatabase *db;@end@implementationViewController@synthesizedb;- (void)viewDidLoad { [superviewDidLoad];// Do any additional setup after loading the view, typically from a nib.// 1.获得数据库文件的路径NSString*doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) lastObject];NSString*filename = [doc stringByAppendingPathComponent:@"operDB.sqlite"];// 2.得到数据库db = [FMDatabase databaseWithPath:filename];}- (IBAction)openDB:(id)sender {// 3.打开数据库if([db open]) {NSLog(@"打开operDB成功"); }else{NSLog(@"打开operDB失败"); }}- (IBAction)createShopCarTable:(id)sender {// 4.创表if([db open]) {BOOLresult = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS shopCar (dbid integer PRIMARY KEY AUTOINCREMENT, specId integer NOT NULL, shopNum integer NOT NULL default 1, shopInfo TEXT NOT NULL);"];if(result) {NSLog(@"成功创表"); }else{NSLog(@"创表失败"); } [db close]; }}- (IBAction)insertData:(id)sender {if([db open]) {for(inti =0; i <5; i++) {NSDictionary*dict = @{@"shopId":@(i+1),@"shopName":@"欧阳公仔",@"shopPrice":@(55.9+ i)};NSString*shopInfo = [dict mj_JSONString];NSString*insertSql1= [NSStringstringWithFormat:@"INSERT INTO shopCar (specId, shopNum, shopInfo) VALUES (%d, %d, '%@')", i+2, i+3, shopInfo];BOOLres = [db executeUpdate:insertSql1];if(res) {NSLog(@"数据插入表成功"); }else{NSLog(@"数据插入表失败"); } } [db close]; }}- (IBAction)selectAllData:(id)sender {if([db open]) {NSString* sql =@"SELECT * FROM shopCar"; FMResultSet * rs = [db executeQuery:sql];while([rs next]) {intdbId = [rs intForColumn:@"dbid"];intspecId = [rs intForColumn:@"specId"];intshopNum = [rs intForColumn:@"shopNum"];NSString* shopInfo = [rs stringForColumn:@"shopInfo"];NSLog(@"dbId = %d, specId = %d, shopNum = %d shopInfo = %@", dbId, specId, shopNum, shopInfo); } [db close]; }}- (IBAction)selectDbidData:(id)sender {if([db open]) {NSString* sql =@"SELECT * FROM shopCar WHERE dbid = '3'"; FMResultSet * rs = [db executeQuery:sql];while([rs next]) {intdbId = [rs intForColumn:@"dbid"];intspecId = [rs intForColumn:@"specId"];intshopNum = [rs intForColumn:@"shopNum"];NSString* shopInfo = [rs stringForColumn:@"shopInfo"];NSLog(@"dbId = %d, specId = %d, shopNum = %d shopInfo = %@", dbId, specId, shopNum, shopInfo); } [db close]; }}- (IBAction)updataDbidData:(id)sender {if([db open]) {NSString*updateSql = [NSStringstringWithFormat:@"update shopCar set shopNum = '88' where dbid ='3'"];BOOLres = [db executeUpdate:updateSql];if(res) {NSLog(@"修改数据成功"); }else{NSLog(@"修改数据失败"); } [db close]; }}- (IBAction)deleteDbidData:(id)sender {if([db open]) {NSString*updateSql = [NSStringstringWithFormat:@"DELETE FROM shopCar WHERE dbid = '3'"];BOOLres = [db executeUpdate:updateSql];if(res) {NSLog(@"删除指定数据成功"); }else{NSLog(@"删除指定数据失败"); } [db close]; }}- (IBAction)deleteAllData:(id)sender {if([db open]) {//清空数据表并将自增字段清零NSString*deleteSql =@"DELETE FROM shopCar"; [db executeUpdate:@"UPDATE sqlite_sequence set seq = 0 where name = 'shopCar'"];BOOLres = [db executeUpdate:deleteSql];if(res) {NSLog(@"清空shopCar表数据成功"); }else{NSLog(@"清空shopCar表数据失败"); } [db close]; }}- (IBAction)deleteShopCarTable:(id)sender {if([db open]) {NSString*deleteSql =@"DROP TABLE shopCar";BOOLres = [db executeUpdate:deleteSql];if(res) {NSLog(@"删除shopCar表成功"); }else{NSLog(@"删除shopCar表失败"); } [db close]; }}- (void)didReceiveMemoryWarning { [superdidReceiveMemoryWarning];// Dispose of any resources that can be recreated.}@end
作者：charlotte2018
链接：https://www.jianshu.com/p/8a86aa6da805
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。
