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代码：

View Row Code

1	#import <UIKit/UIKit.h>
2	#import "sqlite3.h"
3	@interface HomeViewController : UIViewController{
4	sqlite3 *db; //声明一个sqlite3数据库
5	}
6	- (NSString *)filePath;//数据库文件的路径。一般在沙箱的Documents里边操作
7	@end

2. HomeViewController.m代码：

View Row Code

1	#import "HomeViewController.h"
2
3	@interface HomeViewController ()
4	@end
5	@implementation HomeViewController
6	//该方法用于返回数据库在Documents文件夹中的全路径信息
7
8	- (NSString *)filePath{
9	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
10	NSString *documentsDir = [paths objectAtIndex:0];
11	return [documentsDir stringByAppendingPathComponent:@"Contacts.sqlite"];
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)insertRecordIntoTableName:(NSString *)tableName
23	withField1:(NSString *)field1 field1Value:(NSString *)field1Value
24	andField2:(NSString *)field2 field2Value:(NSString *)field2Value
25	andField3:(NSString *)field3 field3Value:(NSString *)field3Value{
26	/*方法1：经典方法
27	NSString *sql = [NSString stringWithFormat:@"INSERT INTO '%@' ('%@', '%@', '%@') VALUES('%@', '%@', '%@')", tableName, field1, field2, field3, field1Value, field2Value, field3Value];
28	char *err;
29	if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
30	sqlite3_close(db);
31	NSAssert(0, @"插入数据错误！");
32	}
33	*/
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 insertRecordIntoTableName:@"members" withField1:@"name" field1Value:@"李1" andField2:@"email" field2Value:@"df@qq.com" andField3:@"birthday" field3Value:@"12-45-78"];
88
89	[self insertRecordIntoTableName:@"members" withField1:@"name" field1Value:@"李2" andField2:@"email" field2Value:@"df@qq.com" andField3:@"birthday" field3Value:@"12-45-78"];
90
91	[self insertRecordIntoTableName:@"members" withField1:@"name" field1Value:@"李3" andField2:@"email" field2Value:@"df@qq.com" andField3:@"birthday" field3Value:@"12-45-78"];
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()函数。

查询分三个阶段：

1. 准备阶段：sqlite3_stat、sqlite3_prepare_v2()
2. 执行阶段：sqlte3_step()
3. 终止阶段： sqlite3_finalize()

2.附表

sqlite3数据库打开时的返回值及其所代表的含义：