这篇学习目标:
1. 使用自定义UITableViewCell 控件
2. 使用SQLite离线数据库
引言
iPhone 中内置了 SQLite 的数据库管理系统。它是一款轻型的数据库,是遵守ACID的关联式数据库管理系统,它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如C#、PHP、Java等,还有ODBC接口。SQLite 是c写的,IOS中可以直接使用c代码来操作数据库。效率杠杠的~~
最常用的方法有如下:
- sqlite3_open() -
打开指定的数据库文件。如果数据库文件不存在,将创建此数据库。 - sqlite3_close() -
关闭先前打开的数据库文件。 - sqlite3_prepare_v2() -
准备sql语句,执行select语句或者要使用parameter bind时,用这个函数(封装了sqlite3_exec)。 - sqlite3_step() -
在调用sqlite3_prepare后,使用这个函数在记录集中移动。 - sqlite3_column_<type>() -
返回的数据字段的SQL数据集操作,其中<type>被替换的数据类型的数据进行提取(TEXT,BLOB字节,整型,INT16等)的结果。 - sqlite3_finalize() -
释放资源。 - sqlite3_exec() -
执行非查询的sql语句。
其使用步骤大致分为以下几步:
- 首先获取iPhone上Sqlite的数据库文件的地址
- 打开Sqlite的数据库文件
- 定义查询或执行的SQL语句
- 邦定执行SQL所需要的参数
- 执行SQL语句,并获取结果
- 释放相关的资源
- 关闭Sqlite数据库
思路:
我们今天来实现一个联系人的功能,读取SQLite中的数据,自定义显示在UITableView上
开始具体操作如下:
1. 创建项目,如何操作,此处略过,可以参考之前的文章,下面只显示对项目名称的图:
2. 导入SQLite库文件
拖动文件到Framework中去
3. 创建一个联系人的实体 Contact,修改Contact.h如下
// Contact.h
// listContactCustom
//
// Created by tony on 12-9-4.
// Copyright (c) 2012年 chinapcc.com. All rights reserved.
//
#import <Foundation/Foundation.h>
@interface Contact : NSObject
{
NSString *_person_id;
NSString *_name;
int _sex;
NSString *_address;
NSString *_phone;
NSString *_email;
NSString *_remark;
}
// 属性:人员编号
@property (strong,nonatomic) NSString *person_id;
// 属性:姓名
@property (strong,nonatomic) NSString *name;
// 属性:性别
@property int sex;
// 属性:地址
@property (strong,nonatomic) NSString *address;
// 属性:电话号码
@property (strong,nonatomic) NSString *phone;
// 属性:电子邮件
@property (strong,nonatomic) NSString *email;
// 属性:备注
@property (strong,nonatomic) NSString *remark;
// 方法:初始化参数
-( id) initWithName:(NSString*)name Sex:( int)sex Address:(NSString*)address Phone:(NSString*)phone Email:(NSString*)email Remark:(NSString *)remark;
@end
添加 Contact.m 如下:
2 // Contact.m
3 // listContactCustom
4 //
5 // Created by tony on 12-9-4.
6 // Copyright (c) 2012年 chinapcc.com. All rights reserved.
7 //
8
9 #import " Contact.h "
10
11 @implementation Contact
12
13 @synthesize person_id=_person_id;
14 @synthesize name=_name;
15 @synthesize sex=_sex;
16 @synthesize address=_address;
17 @synthesize phone=_phone;
18 @synthesize email=_email;
19 @synthesize remark=_remark;
20
21
22 // 方法:初始化参数
23 -( id) initWithName:(NSString*)name Sex:( int)sex Address:(NSString*)address Phone:(NSString*)phone Email:(NSString*)email Remark:(NSString *)remark
24 {
25 self = [super init];
26 if (self)
27 {
28 _person_id = [[NSProcessInfo processInfo] globallyUniqueString]; // 获取到一个GUID编号
29 _name = name; // 设置姓名
30 _sex = sex; // 设置性别
31 _address = address; // 设置地址
32 _phone = phone; // 设置电话
33 _email = email; // 设置电子邮件
34 _remark = remark; // 设置备注
35
36 }
37 return self;
38 }
39
40 // 释放内存
41 -( void) dealloc
42 {
43 self.person_id = nil;
44 self.name = nil;
45 self.address = nil;
46 self.phone = nil;
47 self.email = nil;
48 self.remark = nil;
49 }
50
51
52 @end
4. 创建一个操作数据库的类, 修改ContactDao.h代码如下:
2 // ContactDao.h
3 // listContactCustom
4 //
5 // Created by tony on 12-9-4.
6 // Copyright (c) 2012年 chinapcc.com. All rights reserved.
7 //
8
9 #import <Foundation/Foundation.h>
10
11 // 此处要引用SQLite3的头文件
12 #import <sqlite3.h>
13
14 // 要引用刚刚写的实体类
15 #import " Contact.h "
16
17 // 联系人处理的数据操作类(DAO:Data Access Objects)
18 @interface ContactDao : NSObject
19 {
20 // 数据库
21 sqlite3 *database;
22
23 // 返回的是sql解析的结果集
24 sqlite3_stmt *statement;
25
26 // 返回的错误信息
27 char *errorMsg;
28
29 // 数据库名称
30 NSString *databaseName;
31
32 // 数据库路径
33 NSString *databasePath;
34 }
35
36 // 打开数据库
37 -(BOOL)open;
38
39 // 创建数据表
40 -(BOOL)create;
41
42 // 增加、删除、修改、查询, 不用我每行注解了吧,大家都懂的
43 -(BOOL)insert:(Contact *)model;
44 -(BOOL)deleteALLContact;
45 -(BOOL)deleteContact:(Contact*)model;
46 -(BOOL)update:(Contact*)model;
47
48 -(NSMutableArray*)selectAll;
49 -(Contact*)selectContact:(NSString*)person_id;
50
51 // 为了测试,写入测试数据
52 -( void)initWithPerson;
53
54 @end
5. 修改 ContactDao.m 文件,详细如下:
5.1 找到数据库位置或重新创建数据文件的地址
2 -(BOOL)open
3 {
4 BOOL success = NO;
5 NSFileManager *fileManager = [NSFileManager defaultManager];
6 success = [fileManager fileExistsAtPath:databasePath];
7 if(!success)
8 {
9 NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
10 [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
11 }
12 if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
13 {
14 success = YES;
15 NSLog( @" 打开数据库成功! ");
16 }
17 else
18 {
19 success = NO;
20 NSLog( @" 打开数据库出错! ");
21 }
22 return success;
23 }
5.2 创建表结构
2 -(BOOL)create
3 {
4 BOOL success = NO;
5 // 打开数据库文件
6 if([self open])
7 {
8 // 创建表的SQL语句
9 const char *sql_stmt = " CREATE TABLE IF NOT EXISTS CONTACT(person_id TEXT PRIMARY KEY, NAME TEXT,SEX INTEGER, ADDRESS TEXT, PHONE TEXT, EMAIL TEXT, REMARK TEXT) ";
10 // 执行创建表的语句
11 if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
12 {
13 success = YES;
14 }
15 else
16 {
17 NSLog( @" 执行出错:%s ",errorMsg);
18 sqlite3_free(errorMsg); // 释放错误信息资源
19 }
20 }
21
22 // 关闭数据库
23 sqlite3_close(database);
24 return success;
25 }
5.3 新增方法
2 -(BOOL)insert:(Contact *)model
3 {
4 BOOL success = NO;
5 if([self open])
6 {
7 NSString *sql = [[NSString alloc] initWithFormat: @" INSERT INTO CONTACT(person_id, NAME,SEX, ADDRESS, PHONE, EMAIL, REMARK) VALUES ('%@','%@',%d,'%@','%@','%@','%@') ",model.person_id,model.name, model.sex,model.address,model.phone,model.email,model.remark];
8 const char *query_stmt = [sql UTF8String];
9 if (sqlite3_exec(database, query_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
10 {
11 NSLog( @" 写入数据成功!'%@','%@',%d,'%@','%@','%@','%@' ",model.person_id,model.name,model.sex,model.address,model.phone,model.email,model.remark);
12 success = YES;
13 } else {
14 NSLog( @" 写入数据失败!%s ",errorMsg);
15 sqlite3_free(errorMsg);
16 }
17 }
18 sqlite3_close(database);
19 return success;
20 }
5.4 删除所有联系人
2 -(BOOL)deleteALLContact
3 {
4 BOOL success = NO;
5 // 打开数据库文件
6 if([self open])
7 {
8 // 删除联系人的SQL语句
9 const char *sql_stmt = " DELETE FROM CONTACT ";
10 // 执行删除联系人的SQL语句
11 if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
12 {
13 NSLog( @" 删除所有联系人资料! ");
14 success = YES;
15 }
16 else
17 {
18 NSLog( @" 执行出错:%s ",errorMsg);
19 sqlite3_free(errorMsg); // 释放错误信息资源
20 }
21 }
22 // 关闭数据库
23 sqlite3_close(database);
24 return success;
25 }
5.5 删除单个联系人
2 -(BOOL)deleteContact:(Contact *)model
3 {
4 BOOL success = NO;
5 // 打开数据库文件
6 if([self open])
7 {
8 NSString *sql = [[NSString alloc] initWithFormat: @" DELETE FROM CONTACT WHERE (person_id='%@') ",model.person_id];
9 const char *query_stmt = [sql UTF8String];
10 // 执行删除联系人的SQL语句
11 if (sqlite3_exec(database, query_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
12 {
13 NSLog( @" 删除[%@]资料成功! ",model.name);
14 success = YES;
15 }
16 else
17 {
18 NSLog( @" 执行出错:%s ",errorMsg);
19 sqlite3_free(errorMsg); // 释放错误信息资源
20 }
21 }
22 // 关闭数据库
23 sqlite3_close(database);
24 return success;
25 }
5.6 更新联系人资料
2 -(BOOL)update:(Contact *)model
3 {
4 BOOL success = NO;
5 if([self open])
6 {
7 NSString *sql = [[NSString alloc] initWithFormat: @" UPDATE CONTACT SET NAME='%@',SEX=%d, ADDRESS='%@', PHONE='%@', EMAIL='%@', REMARK='%@') WHERE (person_id='%@') ",model.name, model.sex,model.address,model.phone,model.email,model.remark,model.person_id];
8 const char *query_stmt = [sql UTF8String];
9 if (sqlite3_exec(database, query_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
10 {
11 NSLog( @" 更新数据成功!'%@','%@',%d,'%@','%@','%@','%@' ",model.person_id,model.name,model.sex,model.address,model.phone,model.email,model.remark);
12 success = YES;
13 } else {
14 NSLog( @" 更新数据失败!%s ",errorMsg);
15 sqlite3_free(errorMsg);
16 }
17 }
18 sqlite3_close(database);
19 return success;
20 }
5.7 查询所有联系人
2 -(NSMutableArray*)selectAll
3 {
4 NSMutableArray *list = [[NSMutableArray alloc] init];
5 if([self open])
6 {
7 // 设置SQL查询语名
8 const char *sqlStatement = " SELECT * FROM CONTACT ";
9
10 if(sqlite3_prepare_v2(database, sqlStatement, - 1, &statement, NULL) == SQLITE_OK) {
11 // 循环遍历结果并将它们添加到人员列表
12 while(sqlite3_step(statement) == SQLITE_ROW) {
13 // 从结果行读取数据
14 NSString *name = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 1)];
15 int sex = sqlite3_column_int(statement, 2);
16 NSString *address = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 3)];
17 NSString *phone = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 4)];
18 NSString *email = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 5)];
19 NSString *remark = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 6)];
20
21 // 创建一个新对像,并且初始化赋值
22 Contact *person = [[Contact alloc] initWithName:name
23 Sex:sex
24 Address:address
25 Phone:phone
26 Email:email
27 Remark:remark];
28
29 // 添加对像到数组中
30 [list addObject:person];
31 }
32 }
33 }
34 return list;
35 }
5.8 查询单个联系人
2 -(Contact*)selectContact:(NSString*)person_id;
3 {
4 Contact *person = [[Contact alloc] init];
5 if([self open])
6 {
7 // 设置SQL查询语名
8 const char *sqlStatement = " SELECT * FROM CONTACT ";
9
10 if(sqlite3_prepare_v2(database, sqlStatement, - 1, &statement, NULL) == SQLITE_OK) {
11 // 取出查询到的第一条记录,返回结果
12 if (sqlite3_step(statement) == SQLITE_ROW) {
13 // 从结果行读取数据
14 NSString *name = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 1)];
15 int sex = sqlite3_column_int(statement, 2);
16 NSString *address = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 3)];
17 NSString *phone = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 4)];
18 NSString *email = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 5)];
19 NSString *remark = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 6)];
20
21 // 创建一个新对像,并且初始化赋值
22 person = [[Contact alloc] initWithName:name
23 Sex:sex
24 Address:address
25 Phone:phone
26 Email:email
27 Remark:remark];
28 }
29 }
30 }
31 return person;
32 }
5.9 为了测试,初始化一些数据
2 -( void)initWithPerson
3 {
4 [self create];
5 Contact *c1 = [[Contact alloc] initWithName: @" 汪肄敏 "
6 Sex: 1
7 Address: @" 上海市嘉定区沪宜公路88888号 "
8 Phone: @" 133xxxx8987 "
9 Email: @" chinapcc@gmail.com "
10 Remark: @" 脱脱 "];
11 [self insert:c1];
12
13 Contact *c2 = [[Contact alloc] initWithName: @" 石磊 "
14 Sex: 1
15 Address: @" 上海市长宁区武夷路99号 "
16 Phone: @" 138xxxx5775 "
17 Email: @" slpcc@gmail.com "
18 Remark: @" 情圣 "];
19 [self insert:c2];
20
21 Contact *c3 = [[Contact alloc] initWithName: @" 李清美 "
22 Sex: 0
23 Address: @" 上海市杨清区岱山路18号 "
24 Phone: @" 138xxxx1314 "
25 Email: @" lqm@gmail.com "
26 Remark: @" 妹子 "];
27 [self insert:c3];
28
29
30 Contact *c4 = [[Contact alloc] initWithName: @" 邱学军 "
31 Sex: 1
32 Address: @" 上海市闵行区长清路88号 "
33 Phone: @" 139xxxx7551 "
34 Email: @" qxjcyc@gmail.com "
35 Remark: @" Q君 "];
36 [self insert:c4];
37
38
39 Contact *c5 = [[Contact alloc] initWithName: @" 李志强 "
40 Sex: 1
41 Address: @" 上海市清东新区张江路100号 "
42 Phone: @" 136xxxx9018 "
43 Email: @" qxjcyc@gmail.com "
44 Remark: @" P哥 "];
45 [self insert:c5];
46
47 Contact *c6 = [[Contact alloc] initWithName: @" 戴艳丰 "
48 Sex: 1
49 Address: @" 上海市杨浦区政权路520号 "
50 Phone: @" 152xxxx1314 "
51 Email: @" qxjcyc@gmail.com "
52 Remark: @" 疯子 "];
53 [self insert:c6];
54 }
5.10 释放资源
2 -( void) dealloc
3 {
4 database = nil;
5 statement = nil;
6 databaseName = nil;
7 databasePath = nil;
8 errorMsg = nil;
9 }
5.11 完整代码如下:
2 // ContactDao.m
3 // listContactCustom
4 //
5 // Created by tony on 12-9-4.
6 // Copyright (c) 2012年 chinapcc.com. All rights reserved.
7 //
8
9 #import " ContactDao.h "
10
11 @implementation ContactDao
12
13 -( id) init
14 {
15 self = [super init];
16 if (self)
17 {
18 databaseName = @" contact.db ";
19 NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
20 NSString *documentsDir = [documentPaths objectAtIndex: 0];
21 databasePath = [documentsDir stringByAppendingPathComponent:databaseName];
22 }
23 return self;
24 }
25
26
27 // 打开数据库文件地址,方法内代码,不需要注解吧,(如果不懂,可以找谷歌老师)
28 -(BOOL)open
29 {
30 BOOL success = NO;
31 NSFileManager *fileManager = [NSFileManager defaultManager];
32 success = [fileManager fileExistsAtPath:databasePath];
33 if(!success)
34 {
35 NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
36 [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
37 }
38 if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
39 {
40 success = YES;
41 NSLog( @" 打开数据库成功! ");
42 }
43 else
44 {
45 success = NO;
46 NSLog( @" 打开数据库出错! ");
47 }
48 return success;
49 }
50
51 // 创建表结构
52 -(BOOL)create
53 {
54 BOOL success = NO;
55 // 打开数据库文件
56 if([self open])
57 {
58 // 创建表的SQL语句
59 const char *sql_stmt = " CREATE TABLE IF NOT EXISTS CONTACT(person_id TEXT PRIMARY KEY, NAME TEXT,SEX INTEGER, ADDRESS TEXT, PHONE TEXT, EMAIL TEXT, REMARK TEXT) ";
60 // 执行创建表的语句
61 if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
62 {
63 success = YES;
64 }
65 else
66 {
67 NSLog( @" 执行出错:%s ",errorMsg);
68 sqlite3_free(errorMsg); // 释放错误信息资源
69 }
70 }
71
72 // 关闭数据库
73 sqlite3_close(database);
74 return success;
75 }
76
77 // 方法:新增联系人
78 -(BOOL)insert:(Contact *)model
79 {
80 BOOL success = NO;
81 if([self open])
82 {
83 NSString *sql = [[NSString alloc] initWithFormat: @" INSERT INTO CONTACT(person_id, NAME,SEX, ADDRESS, PHONE, EMAIL, REMARK) VALUES ('%@','%@',%d,'%@','%@','%@','%@') ",model.person_id,model.name, model.sex,model.address,model.phone,model.email,model.remark];
84 const char *query_stmt = [sql UTF8String];
85 if (sqlite3_exec(database, query_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
86 {
87 NSLog( @" 写入数据成功!'%@','%@',%d,'%@','%@','%@','%@' ",model.person_id,model.name,model.sex,model.address,model.phone,model.email,model.remark);
88 success = YES;
89 } else {
90 NSLog( @" 写入数据失败!%s ",errorMsg);
91 sqlite3_free(errorMsg);
92 }
93 }
94 sqlite3_close(database);
95 return success;
96 }
97
98
99 // 方法:删除所有联系人
100 -(BOOL)deleteALLContact
101 {
102 BOOL success = NO;
103 // 打开数据库文件
104 if([self open])
105 {
106 // 删除联系人的SQL语句
107 const char *sql_stmt = " DELETE FROM CONTACT ";
108 // 执行删除联系人的SQL语句
109 if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
110 {
111 NSLog( @" 删除所有联系人资料! ");
112 success = YES;
113 }
114 else
115 {
116 NSLog( @" 执行出错:%s ",errorMsg);
117 sqlite3_free(errorMsg); // 释放错误信息资源
118 }
119 }
120 // 关闭数据库
121 sqlite3_close(database);
122 return success;
123 }
124
125 // 方法:删除指定的联系人
126 -(BOOL)deleteContact:(Contact *)model
127 {
128 BOOL success = NO;
129 // 打开数据库文件
130 if([self open])
131 {
132 NSString *sql = [[NSString alloc] initWithFormat: @" DELETE FROM CONTACT WHERE (person_id='%@') ",model.person_id];
133 const char *query_stmt = [sql UTF8String];
134 // 执行删除联系人的SQL语句
135 if (sqlite3_exec(database, query_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
136 {
137 NSLog( @" 删除[%@]资料成功! ",model.name);
138 success = YES;
139 }
140 else
141 {
142 NSLog( @" 执行出错:%s ",errorMsg);
143 sqlite3_free(errorMsg); // 释放错误信息资源
144 }
145 }
146 // 关闭数据库
147 sqlite3_close(database);
148 return success;
149 }
150
151 // 方法:更新联系人资料
152 -(BOOL)update:(Contact *)model
153 {
154 BOOL success = NO;
155 if([self open])
156 {
157 NSString *sql = [[NSString alloc] initWithFormat: @" UPDATE CONTACT SET NAME='%@',SEX=%d, ADDRESS='%@', PHONE='%@', EMAIL='%@', REMARK='%@') WHERE (person_id='%@') ",model.name, model.sex,model.address,model.phone,model.email,model.remark,model.person_id];
158 const char *query_stmt = [sql UTF8String];
159 if (sqlite3_exec(database, query_stmt, NULL, NULL, &errorMsg) == SQLITE_OK)
160 {
161 NSLog( @" 更新数据成功!'%@','%@',%d,'%@','%@','%@','%@' ",model.person_id,model.name,model.sex,model.address,model.phone,model.email,model.remark);
162 success = YES;
163 } else {
164 NSLog( @" 更新数据失败!%s ",errorMsg);
165 sqlite3_free(errorMsg);
166 }
167 }
168 sqlite3_close(database);
169 return success;
170 }
171
172 // 方法:列出所有联系人资料
173 -(NSMutableArray*)selectAll
174 {
175 NSMutableArray *list = [[NSMutableArray alloc] init];
176 if([self open])
177 {
178 // 设置SQL查询语名
179 const char *sqlStatement = " SELECT * FROM CONTACT ";
180
181 if(sqlite3_prepare_v2(database, sqlStatement, - 1, &statement, NULL) == SQLITE_OK) {
182 // 循环遍历结果并将它们添加到人员列表
183 while(sqlite3_step(statement) == SQLITE_ROW) {
184 // 从结果行读取数据
185 NSString *name = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 1)];
186 int sex = sqlite3_column_int(statement, 2);
187 NSString *address = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 3)];
188 NSString *phone = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 4)];
189 NSString *email = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 5)];
190 NSString *remark = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 6)];
191
192 // 创建一个新对像,并且初始化赋值
193 Contact *person = [[Contact alloc] initWithName:name
194 Sex:sex
195 Address:address
196 Phone:phone
197 Email:email
198 Remark:remark];
199
200 // 添加对像到数组中
201 [list addObject:person];
202 }
203 }
204 }
205 return list;
206 }
207
208 // 方法:根据人员编号,获取联系人的实体
209 -(Contact*)selectContact:(NSString*)person_id;
210 {
211 Contact *person = [[Contact alloc] init];
212 if([self open])
213 {
214 // 设置SQL查询语名
215 const char *sqlStatement = " SELECT * FROM CONTACT ";
216
217 if(sqlite3_prepare_v2(database, sqlStatement, - 1, &statement, NULL) == SQLITE_OK) {
218 // 取出查询到的第一条记录,返回结果
219 if (sqlite3_step(statement) == SQLITE_ROW) {
220 // 从结果行读取数据
221 NSString *name = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 1)];
222 int sex = sqlite3_column_int(statement, 2);
223 NSString *address = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 3)];
224 NSString *phone = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 4)];
225 NSString *email = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 5)];
226 NSString *remark = [NSString stringWithUTF8String:( char *)sqlite3_column_text(statement, 6)];
227
228 // 创建一个新对像,并且初始化赋值
229 person = [[Contact alloc] initWithName:name
230 Sex:sex
231 Address:address
232 Phone:phone
233 Email:email
234 Remark:remark];
235 }
236 }
237 }
238 return person;
239 }
240
241 // 为了测试初始化一些代码
242 -( void)initWithPerson
243 {
244 [self create];
245 Contact *c1 = [[Contact alloc] initWithName: @" 汪肄敏 "
246 Sex: 1
247 Address: @" 上海市嘉定区沪宜公路88888号 "
248 Phone: @" 133xxxx8987 "
249 Email: @" chinapcc@gmail.com "
250 Remark: @" 脱脱 "];
251 [self insert:c1];
252
253 Contact *c2 = [[Contact alloc] initWithName: @" 石磊 "
254 Sex: 1
255 Address: @" 上海市长宁区武夷路99号 "
256 Phone: @" 138xxxx5775 "
257 Email: @" slpcc@gmail.com "
258 Remark: @" 情圣 "];
259 [self insert:c2];
260
261 Contact *c3 = [[Contact alloc] initWithName: @" 李清美 "
262 Sex: 0
263 Address: @" 上海市杨清区岱山路18号 "
264 Phone: @" 138xxxx1314 "
265 Email: @" lqm@gmail.com "
266 Remark: @" 妹子 "];
267 [self insert:c3];
268
269
270 Contact *c4 = [[Contact alloc] initWithName: @" 邱学军 "
271 Sex: 1
272 Address: @" 上海市闵行区长清路88号 "
273 Phone: @" 139xxxx7551 "
274 Email: @" qxjcyc@gmail.com "
275 Remark: @" Q君 "];
276 [self insert:c4];
277
278
279 Contact *c5 = [[Contact alloc] initWithName: @" 李志强 "
280 Sex: 1
281 Address: @" 上海市清东新区张江路100号 "
282 Phone: @" 136xxxx9018 "
283 Email: @" qxjcyc@gmail.com "
284 Remark: @" P哥 "];
285 [self insert:c5];
286
287 Contact *c6 = [[Contact alloc] initWithName: @" 戴艳丰 "
288 Sex: 1
289 Address: @" 上海市杨浦区政权路520号 "
290 Phone: @" 152xxxx1314 "
291 Email: @" qxjcyc@gmail.com "
292 Remark: @" 疯子 "];
293 [self insert:c6];
294 }
295
296 // 释放资源
297 -( void) dealloc
298 {
299 database = nil;
300 statement = nil;
301 databaseName = nil;
302 databasePath = nil;
303 errorMsg = nil;
304 }
305
306 @end
6. 自定义UITableViewCell 类
6.1 删除默认的View, 添加Table View
6.2 设置Cell的属性
6.3 设置Cell的高度
6.4 添加展示的控件
6.5 添加Cell的控制器类
6.6 View与Controller Class关联起来
6.7 修改 ContactCell.h文件如下
2 // ContactCell.h
3 // listContactCustom
4 //
5 // Created by tony on 12-9-4.
6 // Copyright (c) 2012年 chinapcc.com. All rights reserved.
7 //
8
9 #import <UIKit/UIKit.h>
10
11 @interface ContactCell : UITableViewCell
12 {
13 IBOutlet UILabel *lbName;
14 IBOutlet UILabel *lbPhone;
15 IBOutlet UILabel *lbEmail;
16 IBOutlet UILabel *lbAddress;
17 IBOutlet UIImageView * ivPhoto;
18 }
19
20 @property (copy, nonatomic) UIImage *image;
21 @property (copy, nonatomic) NSString *name;
22 @property (copy, nonatomic) NSString *phone;
23 @property (copy, nonatomic) NSString *email;
24 @property (copy, nonatomic) NSString *address;
25
26 @end
6.8 修改 ContactCell.m 文件,重写SET方法
2 @synthesize phone;
3 @synthesize image;
4 @synthesize email;
5 @synthesize address;
6
7
8 // 重写属性
9
10 - ( void)setImage:(UIImage *)img {
11 if (![img isEqual:image]) {
12 image = [img copy];
13 self.imageView.image = image;
14 }
15 }
16
17 -( void)setName:(NSString *)n {
18 if (![n isEqualToString:name]) {
19 name = [n copy];
20 lbName.text = name;
21 }
22 }
23
24 -( void)setPhone:(NSString *)p {
25 if (![p isEqualToString:phone]) {
26 phone = [p copy];
27 lbPhone.text = phone;
28 }
29 }
30
31 -( void)setEmail:(NSString *)e {
32 if (![e isEqualToString:email]) {
33 email = [e copy];
34 lbEmail.text = email;
35 }
36 }
37
38 -( void)setAddress:(NSString *)a {
39 if (![a isEqualToString:address]) {
40 address = [a copy];
41 lbAddress.text = address;
42 }
43 }
6.9 类与View关联如下:
7. 实现UITableViewController 类
7.1 删除默认的ViewController
7.2 添加myTableViewController类,继承于TableViewController,并且绑定如下:
7.3 添加几个按钮控件
7.4 修改myTableViewController.h
2 // myTableViewController.h
3 // listContactCustom
4 //
5 // Created by tony on 12-9-4.
6 // Copyright (c) 2012年 chinapcc.com. All rights reserved.
7 //
8
9 #import <UIKit/UIKit.h>
10 #import " Contact.h "
11 #import " ContactCell.h "
12 #import " ContactDao.h "
13
14 @interface myTableViewController : UITableViewController
15 {
16 NSMutableArray *listContact;
17 }
18
19 // 事件:初始化数据
20 -(IBAction)btnInitData:( id)sender;
21
22 // 事件:清除联系人数据
23 -(IBAction)btnClearData:( id)sender;
24
25 // 事件:刷新联系人数据
26 -(IBAction)btnRefreshData:( id)sender;
27 @end
7.5 绑定事件
8. 绑定数据源,修改 myTableViewController.m 文件,具体就不细说了,前二篇看过,应该没有问题
2 {
3 // 实例化DAO
4 ContactDao *dao = [[ContactDao alloc] init];
5
6 // 获取所有联系人
7 listContact = [dao selectAll];
8
9 [super viewDidLoad];
10 }
11
12 - (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView
13 {
14 return 1;
15 }
16
17 - (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
18 {
19 return [listContact count];
20 }
21
22 - (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
23 {
24 static NSString *CellIdentifier = @" Cell ";
25 ContactCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
26 // 重点就在这里
27 Contact *p = [listContact objectAtIndex:indexPath.row];
28
29 if (p.sex== 1) {
30 // 男性
31 cell.image = [UIImage imageNamed: @" male.png "];
32 } else {
33 cell.image = [UIImage imageNamed: @" female.png "];
34 }
35 cell.name = p.name; // 显示姓名
36 cell.phone = p.phone; // 显示电话
37 cell.email = p.email; // 显示电子邮件
38 cell.address = p.address; // 显示通讯地址
39 return cell;
40 }
41
42 #pragma mark - 按钮事件
43 // 初始化数据
44 -(IBAction)btnInitData:( id)sender
45 {
46 ContactDao *dao = [[ContactDao alloc] init];
47
48 // 初始化数据
49 [dao initWithPerson];
50
51 // 获取所有联系人
52 listContact = [dao selectAll];
53
54 // 重新加载数据
55 [self.tableView reloadData];
56 }
57
58 // 清除所有数据
59 -(IBAction)btnClearData:( id)sender
60 {
61 ContactDao *dao = [[ContactDao alloc] init];
62
63 // 删除所有联系人
64 [dao deleteALLContact];
65
66 // 获取所有联系人
67 listContact = [dao selectAll];
68
69 // 重新加载数据
70 [self.tableView reloadData];
71
72 }
73
74 // 刷新数据
75 -(IBAction)btnRefreshData:( id)sender
76 {
77 ContactDao *dao = [[ContactDao alloc] init];
78
79 // 获取所有联系人
80 listContact = [dao selectAll];
81
82 [self.tableView reloadData];
83 }
9. 执行,看效果
10. 按国际惯例,附上:源代码