[摘要]本文介绍iOS中SQLite3的使用,包括插入数据、查询数据等操作,并提供简单的示例代码供参考。
一、在Firefox中打开sqlite3(如果没有,选择工具->附加组件,添加即可)新建sqlite3数据库Contacts,建立一个members表,字段 id,integer,主键,自增;name,varchar;email,varchar,null;birthday,datetime,null。
向表中添加一些数据:
二、新建Empty Appliation,添加一个HomeViewController,和一个组件libsqlite3.dylib,来支持对sqlite3的连接,关闭,增删改查等操作。
1. HomeViewController.h代码:
1 | #import |
2 | #import "sqlite3.h" |
3 | @interface HomeViewController : UIViewController{ |
4 | sqlite3 *db; //声明一个sqlite3数据库 |
5 | } |
6 | - (NSString *)filePath;//数据库文件的路径。一般在沙箱的Documents里边操作 |
7 | @end |
2. HomeViewController.m代码:
1 | #import "HomeViewController.h" |
2 | |
3 | @interface HomeViewController () |
4 | @end |
5 | @implementation HomeViewController |
6 | //该方法用于返回数据库在Documents文件夹中的全路径信息 |
7 | |
8 | - (NSString *)filePath{ |
9 | NSArray *paths = NSSearchPathForDirectori |
10 | NSString *documentsDir = [paths objectAtIndex:0]; |
11 | return [documentsDir stringByAppendingPathCom |
12 | } |
13 | //打开数据库的方法 |
14 | |
15 | - (void)openDB{ |
16 | if (sqlite3_open([[self filePath] UTF8String], &db) != SQLITE_OK) { |
17 | sqlite3_close(db); |
18 | NSAssert(0, @"数据库打开失败。"); |
19 | } |
20 | } |
21 | //插入数据方法 |
22 | - (void)insertRecordIntoTableNam |
23 | withField1:(NSString *)field1 field1Value:(NSString *)field1Value |
24 | andField2:(NSString *)field2 field2Value:(NSString *)field2Value |
25 | andField3:(NSString *)field3 field3Value:(NSString *)field3Value{ |
26 | |
34 | //方法2:变量的绑定方法 |
35 | |
36 | NSString *sql = [NSString stringWithFormat:@"INSERT INTO '%@' ('%@', '%@', '%@') VALUES (?, ?, ?)",tableName, field1, field2, field3]; |
37 | |
38 | sqlite3_stmt *statement; |
39 | |
40 | if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK) { |
41 | sqlite3_bind_text(statement, 1, [field1Value UTF8String], -1,NULL); |
42 | sqlite3_bind_text(statement, 2, [field2Value UTF8String], -1,NULL); |
43 | sqlite3_bind_text(statement, 3, [field3Value UTF8String], -1,NULL); |
44 | } |
45 | if (sqlite3_step(statement) != SQLITE_DONE) { |
46 | NSAssert(0, @"插入数据失败!"); |
47 | sqlite3_finalize(statement); |
48 | } |
49 | |
50 | } |
51 | |
52 | |
53 | //查询数据 |
54 | - (void)getAllContacts{ |
55 | NSString *sql = @"SELECT * FROM members"; |
56 | sqlite3_stmt *statement; |
57 | |
58 | if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK) { |
59 | while (sqlite3_step(statement) == SQLITE_ROW) { |
60 | |
61 | char *name = (char *)sqlite3_column_text(statement, 0); |
62 | NSString *nameStr = [[NSString alloc] initWithUTF8String:name]; |
63 | |
64 | char *email = (char *)sqlite3_column_text(statement, 1); |
65 | NSString *emailStr = [[NSString alloc] initWithUTF8String:email]; |
66 | |
67 | char *birthday = (char *)sqlite3_column_text(statement, 2); |
68 | NSString *birthdayStr = [[NSString alloc] initWithUTF8String:birthday]; |
69 | |
70 | NSString *info = [[NSString alloc] initWithFormat:@"%@ - %@ - %@", |
71 | nameStr, emailStr, birthdayStr]; |
72 | |
73 | NSLog(info); |
74 | |
75 | [nameStr release]; |
76 | [emailStr release]; |
77 | [birthdayStr release]; |
78 | [info release]; |
79 | } |
80 | sqlite3_finalize(statement); |
81 | } |
82 | } |
83 | - (void)viewDidLoad |
84 | { |
85 | [self openDB]; |
86 | |
87 | [self insertRecordIntoTableNam |
88 | |
89 | [self insertRecordIntoTableNam |
90 | |
91 | [self insertRecordIntoTableNam |
92 | |
93 | |
94 | [self getAllContacts]; |
95 | |
96 | sqlite3_close(db); |
97 | |
98 | [super viewDidLoad]; |
99 | } |
100 | @end |
插入数据后的效果:
查询的效果:
三、小结
1.数据查询:sqlite3_exec()函数执行sql语句,在没有返回值的情况下(比如创建表格、插入记录、删除记录等操作中)很好用。
也会用到sqlite3_stat结构、sqlite3_prepare_v2()函数、sqlte3_step()函数和sqlite3_finalize()函数。
查询分三个阶段:
- 准备阶段:sqlite3_stat、sqlite3_prepare_v2()
- 执行阶段:sqlte3_step()
- 终止阶段: sqlite3_finalize()
2.附表
sqlite3数据库打开时的返回值及其所代表的含义: