一、FMDB简介
什么是FMDB
FMDB是iOS平台的SQLite数据库框架
FMDB以OC的方式封装了SQLite的C语言API
FMDB的优点
使用起来更加面向对象，省去了很多麻烦、冗余的C语言代码
对比苹果自带的Core Data框架，更加轻量级和灵活
提供了多线程安全的数据库操作方法，有效地防止数据混乱
FMDB的github地址
二、核心类
FMDB有三个主要的类
FMDatabase
一个FMDatabase对象就代表一个单独的SQLite数据库
用来执行SQL语句
FMResultSet
使用FMDatabase执行查询后的结果集
FMDatabaseQueue
用于在多线程中执行多个查询或更新，它是线程安全的
三、打开数据库
通过指定SQLite数据库文件路径来创建FMDatabase对象
FMDatabase *db = [FMDatabase databaseWithPath:path];
if (![db open]) {
NSLog(@"数据库打开失败！");
}
文件路径有三种情况
具体文件路径
如果不存在会自动创建
空字符串@""
会在临时目录创建一个空的数据库
当FMDatabase连接关闭时，数据库文件也被删除
nil
会创建一个内存中临时数据库，当FMDatabase连接关闭时，数据库会被销毁
四、执行更新
在FMDB中，除查询以外的所有操作，都称为“更新”
create、drop、insert、update、delete等
使用executeUpdate:方法执行更新
- (BOOL)executeUpdate:(NSString*)sql, ...
- (BOOL)executeUpdateWithFormat:(NSString*)format, ...
- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments
示例
[db executeUpdate:@"UPDATE t_student SET age = ? WHERE name = ?;", @20, @"Jack"]
五、执行查询
查询方法
- (FMResultSet *)executeQuery:(NSString*)sql, ...
- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ...
- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments
示例
// 查询数据
FMResultSet *rs = [db executeQuery:@"SELECT * FROM t_student"];
// 遍历结果集
while ([rs next]) {
NSString *name = [rs stringForColumn:@"name"];
int age = [rs intForColumn:@"age"];
double score = [rs doubleForColumn:@"score"];
}
六、FMDatabaseQueue
FMDatabase这个类是线程不安全的，如果在多个线程中同时使用一个FMDatabase实例，会造成数据混乱等问题
为了保证线程安全，FMDB提供方便快捷的FMDatabaseQueue类
FMDatabaseQueue的创建
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];
简单使用
[queue inDatabase:^(FMDatabase *db) {
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];
FMResultSet *rs = [db executeQuery:@"select * from t_student"];
while ([rs next]) {
// …
}
}];
使用事务
[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];
FMResultSet *rs = [db executeQuery:@"select * from t_student"];
while ([rs next]) {
// …
}
}];
事务回滚
*rollback = YES;
我的代码
1 //
2 // ViewController.m
3 // IOS_0331_SQLite3
4 //
5 // Created by ma c on 16/3/31.
6 // Copyright © 2016年 博文科技. All rights reserved.
7 //
8
9 #import "ViewController.h"
10 #import "FMDB.h"
11
12 @interface ViewController ()
13
14 @property (nonatomic, strong) FMDatabase *db;
15
16
17 - (IBAction)insert;
18 - (IBAction)update;
19 - (IBAction)query;
20 - (IBAction)delete;
21
22 @end
23
24 @implementation ViewController
25
26 - (void)viewDidLoad {
27 [super viewDidLoad];
28
29 [self createDatabase];
30 }
31
32 - (void)createDatabase
33 {
34 // 0.获得沙盒中的数据库文件名
35 NSString *fileName = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
36 NSLog(@"%@",fileName);
37
38 // 1.创建数据库实例对象
39 self.db = [FMDatabase databaseWithPath:fileName];
40
41 // 2.打开数据库
42 if ([self.db open]) {
43 NSLog(@"数据库打开成功");
44
45 //创表
46 BOOL flag = [self.db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement, name text, age integer) ;"];
47 if (flag) {
48 NSLog(@"建表成功");
49 } else {
50 NSLog(@"建表失败");
51 }
52 } else {
53 NSLog(@"数据库打开失败");
54 }
55 }
56
57 - (IBAction)insert {
58
59 [self.db executeUpdate:@"insert into t_student (name, age) values (?, ?);",@"rose",@10];
60
61 }
62
63 - (IBAction)update {
64
65 [self.db executeUpdate:@"update t_student set age = ? where name = ?;",@20, @"rose"];
66 }
67
68 - (IBAction)query {
69 //1.查询数据
70 FMResultSet *rs = [self.db executeQuery:@"select * from t_student;"];
71 //2.遍历结果集
72 while (rs.next) {
73 int ID = [rs intForColumn:@"id"];
74 NSString *name = [rs stringForColumn:@"name"];
75 int age = [rs intForColumn:@"age"];
76
77 NSLog(@"%d %@ %d",ID, name, age);
78 }
79
80 }
81
82 - (IBAction)delete {
83
84 [self.db executeUpdate:@"delete from t_student where name = ?;",@"rose"];
85
86 }
87 @end
线程安全和事物
1 //
2 // IWViewController.m
3 // 01-SQLite的基本使用
4 //
5 // Created by apple on 14-5-22.
6 // Copyright (c) 2014年 itcast. All rights reserved.
7 //
8
9 #import "IWViewController.h"
10 #import "FMDB.h"
11
12 @interface IWViewController ()
13 @property (nonatomic, strong) FMDatabaseQueue *queue;
14 - (IBAction)insert;
15 - (IBAction)update;
16 - (IBAction)delete;
17 - (IBAction)query;
18 @end
19
20 @implementation IWViewController
21
22 - (void)viewDidLoad
23 {
24 [super viewDidLoad];
25
26 // 0.获得沙盒中的数据库文件名
27 NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
28
29 // 1.创建数据库队列
30 self.queue = [FMDatabaseQueue databaseQueueWithPath:filename];
31
32 // 2.创表
33 [self.queue inDatabase:^(FMDatabase *db) {
34 BOOL result = [db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement, name text, age integer);"];
35
36 if (result) {
37 NSLog(@"创表成功");
38 } else {
39 NSLog(@"创表失败");
40 }
41 }];
42 }
43
44 - (IBAction)insert
45 {
46 [self.queue inDatabase:^(FMDatabase *db) {
47 for (int i = 0; i<40; i++) {
48 NSString *name = [NSString stringWithFormat:@"rose-%d", arc4random() % 1000];
49 NSNumber *age = @(arc4random() % 100 + 1);
50 [db executeUpdate:@"insert into t_student (name, age) values (?, ?);", name, age];
51 }
52 }];
53 }
54
55 - (IBAction)update
56 {
57 [self.queue inDatabase:^(FMDatabase *db) {
58 // 开启事务
59 // [db executeUpdate:@"begin transaction;"];
60 // [db beginTransaction];
61
62 [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];
63 [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];
64
65
66 // if (发现情况不对){
67 // // 回滚事务
68 // [db rollback];
69 //// [db executeUpdate:@"rollback transaction;"];
70 // }
71
72
73 [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];
74
75 // 提交事务
76 // [db commit];
77 // [db executeUpdate:@"commit transaction;"];
78 }];
79 }
80
81 - (IBAction)delete
82 {
83 [self.queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
84 [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];
85 [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];
86
87 // if (发现情况不对){
88 // // 回滚事务
89 // *rollback = YES;
90 // }
91 }];
92 }
93
94 - (IBAction)query
95 {
96 [self.queue inDatabase:^(FMDatabase *db) {
97 // 1.查询数据
98 FMResultSet *rs = [db executeQuery:@"select * from t_student where age > ?;", @50];
99
100 // 2.遍历结果集
101 while (rs.next) {
102 int ID = [rs intForColumn:@"id"];
103 NSString *name = [rs stringForColumn:@"name"];
104 int age = [rs intForColumn:@"age"];
105
106 NSLog(@"%d %@ %d", ID, name, age);
107 }
108 }];
109 }
110 @end
