sqlite 实例教程 IOS下用sqlite打造词典－IOS开发

sqlite 是个好东西，对于移动平台来说。一直想写有关sqlite的教程，但是不知道从何写起，考虑了很久，还是从一个小Demo 谈起吧。我写了一个精简版的词典，实现了增删查改的基本功能。

工程结构如下。最后效果图如下。

效果图中可以看到，我查询 "cc",所有相关条目都查询出来了。

好了，现在开始讲解我的项目。首先可以看我的工程目录，QueryResultList 是界面控制类，DB 是数据库操作类。

整个项目的流程：我们在search框中输入要查询的内容点击搜索，底层模糊查询返回结果显示在tableView中。

我们先从底层的操作讲起，目前我就实现了插入与查询操作，删除与修改以后再补上：

1.创建数据库

?

[java]?view plaincopyprint?

1. -?(const?char*)getFilePath{//获取数据库路径??
2. ????return?[[NSString?stringWithFormat:@"%@/Documents/l",NSHomeDirectory()?]?UTF8String];??
3. }??

[java]?view plaincopyprint?

1. //??DB.h??
2. //iukey??
3. #import?<Foundation/Foundation.h>??
4. #import?"/usr/include/sqlite3.h"??
5. @interface?DB?:?NSObject{??
6. ????sqlite3*?pdb;//数据库句柄??
7. }??
8. @property(nonatomic,assign)sqlite3*?pdb;??
9. -?(BOOL)insertRecordWithEN:(NSString*)en?CN:(NSString*)cn?Comment:(NSString*)comment;//插入一条纪录??
10. -?(NSMutableArray*)quary:(NSString*)str;//查询??
11. ??
12. -?(const?char*)getFilePath;//获取数据库路径??
13. -?(BOOL)createDB;//创建数据库??
14. -?(BOOL)createTable;//创建表??
15. @end??

2.创建表

?

?

[java]?view plaincopyprint?

1. -?(BOOL)createTable{??
2. ????char*?err;??
3. ????char*?sql?=?"create?table?dictionary(ID?integer?primary?key?autoincrement,en?nvarchar(64),cn?nvarchar(128),comment?nvarchar(256))";//创建表语句??
4. ????if?(sql==NULL)?{??
5. ????????return?NO;??
6. ????}??
7. ????if?(SQLITE_OK?!=?sqlite3_open([self?getFilePath?],?&pdb)){??
8. ????????return?NO;??
9. ????}??
10. ??????
11. ????if?(SQLITE_OK?==?sqlite3_exec(pdb,?sql,?NULL,?NULL,?&err))?{//执行创建表语句成功??
12. ????????sqlite3_close(pdb);??
13. ????????return?YES;??
14. ????}else{//创建表失败??
15. ????????return?NO;??
16. ????}??
17. }??

3.插入一条纪录

[java]?view plaincopyprint?

1. -?(BOOL)insertRecordWithEN:(NSString*)en?CN:(NSString*)cn?Comment:(NSString*)comment{??
2. ????int?ret?=?0;??
3. ????if?(SQLITE_OK?!=?sqlite3_open([self?getFilePath?],?&pdb)){//打开数据库??
4. ????????return?NO;??
5. ????}??
6. ????char*?sql?=?"insert?into?dictionary(en,cn,comment)?values(?,?,?);";//插入语句，3个参数??
7. ????sqlite3_stmt*?stmt;//??
8. ????if?(sqlite3_prepare_v2(pdb,?sql,?-1,?&stmt,?nil)==SQLITE_OK)?{//准备语句??
9. ????????sqlite3_bind_text(stmt,?1,?[en?UTF8String],?-1,?NULL);//绑定参数??
10. ????????sqlite3_bind_text(stmt,?2,?[cn?UTF8String],?-1,?NULL);??
11. ????????sqlite3_bind_text(stmt,?3,?[comment?UTF8String],?-1,?NULL);??
12. ????}else{??
13. ????????return?NO;??
14. ????}??
15. ????if?(SQLITE_DONE?==?(ret?=?sqlite3_step(stmt)))?{//执行查询??
16. ????????sqlite3_finalize(stmt);??
17. ????????sqlite3_close(pdb);??
18. ????????return?YES;??
19. ????}else{??
20. ????????return?NO;??
21. ????}??
22. }??

4.查询

[java]?view plaincopyprint?

1. -?(NSMutableArray*)quary:(NSString?*)str{??
2. ????NSMutableArray*?arr?=[[NSMutableArray?alloc]init];//存放查询结果??
3. ????if?(SQLITE_OK?!=?sqlite3_open([self?getFilePath?],?&pdb)){??
4. ????????????return?NO;??
5. ????}??
6. ????char*?sql?=?"select?*?from?dictionary?where?en?like???or?cn?like???or?comment?like??;";//查询语句??
7. ????sqlite3_stmt*?stmt;??
8. ????if?(sqlite3_prepare_v2(pdb,?sql,?-1,?&stmt,?nil)==SQLITE_OK)?{//准备??
9. ????????sqlite3_bind_text(stmt,?1,[[NSString?stringWithFormat:@"%%%@%%",str]UTF8String],?-1,?NULL);??
10. ????????sqlite3_bind_text(stmt,?2,?[[NSString?stringWithFormat:@"%%%@%%",str]UTF8String],?-1,?NULL);??
11. ????????sqlite3_bind_text(stmt,?3,?[[NSString?stringWithFormat:@"%%%@%%",str]UTF8String],?-1,?NULL);??
12. ????}else{??
13. ????????return?nil;??
14. ????}??
15. ????while(?SQLITE_ROW?==?sqlite3_step(stmt)?){//执行??
16. ????????char*?_en?=?(char*)sqlite3_column_text(stmt,?1);??
17. ????????char*?_cn?=?(char*)sqlite3_column_text(stmt,?2);??
18. ????????char*?_comment?=?(char*)sqlite3_column_text(stmt,?3);??
19. ??
20. ????????NSMutableDictionary*?dict?=?[[NSMutableDictionary?alloc]init];//单条纪录??
21. ????????[dict?setObject:[NSString?stringWithCString:_en?encoding:NSUTF8StringEncoding]?forKey:@"kEN"];??
22. ????????[dict?setObject:[NSString?stringWithCString:_cn?encoding:NSUTF8StringEncoding]?forKey:@"kCN"];??
23. ????????[dict?setObject:[NSString?stringWithCString:_comment?encoding:NSUTF8StringEncoding]?forKey:@"kCOMMENT"];??
24. ????????[arr?addObject:dict];//插入到结果数组??
25. ???????????}??
26. ????sqlite3_finalize(stmt);??
27. ????sqlite3_close(pdb);??
28. ????return?[arr?autorelease];//返回查询结果数组??
29. }??

5.DB 初始化

?

我先定义了一个宏，用来标识是否是第一次运行程序，如果是第一次运行就要运行创建数据库与表的函数，否则就不运行，具体看代码：

?

[java]?view plaincopyprint?

1. #define?FIRSTINIT?1//第一次运行则设为1,否则就是0??
2. -?(id)init{??
3. ????self?=?[super?init];??
4. ????if?(self!=nil)?{??
5. #if?FIRSTINIT??
6. ????????[self?createDB];??
7. ????????[self?createTable];??
8. ????????[self?insertRecordWithEN:@"cctv1"?CN:@"央视1套"?Comment:@"SB电视台1"];//为了方便测试我插入了一些纪录??
9. ????????[self?insertRecordWithEN:@"cctv2"?CN:@"央视2套"?Comment:@"SB电视台2"];??
10. ????????[self?insertRecordWithEN:@"cctv3"?CN:@"央视3套"?Comment:@"SB电视台3"];??
11. ????????[self?insertRecordWithEN:@"cctv4"?CN:@"央视4套"?Comment:@"SB电视台4"];??
12. ????????[self?insertRecordWithEN:@"cctv5"?CN:@"央视5套"?Comment:@"SB电视台5"];??
13. ????????[self?insertRecordWithEN:@"cctv6"?CN:@"央视6套"?Comment:@"SB电视台6"];??
14. ????????[self?insertRecordWithEN:@"cctv7"?CN:@"央视7套"?Comment:@"SB电视台7"];??
15. ????????[self?insertRecordWithEN:@"cctv8"?CN:@"央视8套"?Comment:@"SB电视台8"];??
16. ????????[self?insertRecordWithEN:@"cctv9"?CN:@"央视9套"?Comment:@"SB电视台9"];??
17. ????????[self?insertRecordWithEN:@"cctv10"?CN:@"央视10套"?Comment:@"SB电视台10"];??
18. ????????[self?insertRecordWithEN:@"cctv11"?CN:@"央视11套"?Comment:@"SB电视台11"];??
19. ????????[self?insertRecordWithEN:@"cctv12"?CN:@"央视12套"?Comment:@"SB电视台12"];??
20. #endif??
21. ????}??
22. ????return?self;??
23. }??

底层的数据库暂时就这些，接着讲上层的界面部分

?

?

[java]?view plaincopyprint?

1. //??QueryResultList.h??
2. //??iukey??
3. ??
4. #import?<UIKit/UIKit.h>??
5. #import?"DB.h"??
6. ??
7. @interface?QueryResultList?:?UITableViewController<UISearchBarDelegate>{??
8. ????NSMutableArray*?mArr;//tableView数据源??
9. ????DB*?db?;//数据库对象??
10. ????UISearchBar*?searchBar?;//搜索框??
11. }??
12. @property(nonatomic,retain)NSMutableArray*?mArr;??
13. @property(nonatomic,retain)DB*?db;??
14. @property(nonatomic,retain)UISearchBar*?searchBar?;??
15. @end??

[java]?view plaincopyprint?

1. -?(id)initWithStyle:(UITableViewStyle)style{??
2. ????self?=?[super?initWithStyle:style];??
3. ????if?(self)?{??
4. ????????mArr??=?[[NSMutableArray?alloc]init];//表数据源??
5. ????????db?=[[DB?alloc]init];//数据库控制器??
6. ????????searchBar?=?[[UISearchBar?alloc]initWithFrame:CGRectMake(44.0,0,200.0,44)];//搜索控件??
7. ????????searchBar.delegate=self;//设置搜索控件的委托??
8. ????????self.navigationItem.titleView?=?searchBar;??
9. ????}??
10. ????return?self;??
11. }??

接下来我们实现表格数据源委托

?

?

[java]?view plaincopyprint?

1. #pragma?mark?-?Table?view?data?source??
2. ??
3. -?(NSInteger)numberOfSectionsInTableView:(UITableView?*)tableView{??
4. ????return?1;//分区数??
5. }??
6. ??
7. -?(NSInteger)tableView:(UITableView?*)tableView?numberOfRowsInSection:(NSInteger)section{??
8. ????return?[mArr?count];//行数??
9. }??
10. ??
11. -?(UITableViewCell?*)tableView:(UITableView?*)tableView?cellForRowAtIndexPath:(NSIndexPath?*)indexPath{??
12. ????static?NSString?*CellIdentifier?=?@"Cell";??
13. ??????
14. ????UITableViewCell?*cell?=?[tableView?dequeueReusableCellWithIdentifier:CellIdentifier];??
15. ????for?(?UIView*?view?in?cell.contentView.subviews)?{??
16. ????????[view?removeFromSuperview];??
17. ????}??
18. ??
19. ????if?(cell?==?nil)?{??
20. ????????cell?=?[[[UITableViewCell?alloc]?initWithStyle:UITableViewCellStyleDefault?reuseIdentifier:CellIdentifier]?autorelease];??
21. ????}??
22. ????UILabel*?lblEN?=?[[UILabel?alloc]initWithFrame:CGRectMake(5.0,?5.0,?300.0,?30.0)];//显示英文的文字标签控件??
23. ????UILabel*?lblCN?=?[[UILabel?alloc]initWithFrame:CGRectMake(5.0,?35.0,?300.0,?30.0)];//中文??
24. ????UILabel*?lblComment?=?[[UILabel?alloc]initWithFrame:CGRectMake(5.0,?65.0,?300.0,?30.0)];//详细??
25. ??????
26. ????//背景颜色清掉??
27. ????lblEN.backgroundColor?=?[UIColor?clearColor];??
28. ????lblCN.backgroundColor?=?[UIColor?clearColor];??
29. ????lblComment.backgroundColor?=?[UIColor?clearColor];??
30. ????//??
31. ????lblEN.text?=?[[mArr?objectAtIndex:indexPath.row]?objectForKey:@"kEN"];??
32. ????lblCN.text?=?[[mArr?objectAtIndex:indexPath.row]?objectForKey:@"kCN"];??
33. ????lblComment.text?=?[[mArr?objectAtIndex:indexPath.row]?objectForKey:@"kCOMMENT"];??
34. ??????
35. ????[cell.contentView?addSubview:lblEN];??
36. ????[cell.contentView?addSubview:lblCN];??
37. ????[cell.contentView?addSubview:lblComment];??
38. ??????
39. ????cell.selectionStyle?=?UITableViewCellSelectionStyleNone;//选中不要高亮??
40. ????[lblEN?release];??
41. ????[lblCN?release];??
42. ????[lblComment?release];??
43. ??????
44. ????return?cell;??
45. }??

然后实现搜索委托方法：

[java]?view plaincopyprint?

1. #pragma?mark?-?UISearchBar?delegate??
2. -?(void)?searchBarSearchButtonClicked:(UISearchBar*)activeSearchbar{??
3. ????[mArr?removeAllObjects];??
4. ????NSString*?query=?searchBar.text;??
5. ?????NSMutableArray*?arr?=?[db?quary:query];??
6. ????for?(?NSMutableDictionary*?dict?in?arr)?{??
7. ????????[mArr?addObject:dict];??
8. ????}??
9. ????[searchBar?resignFirstResponder];??
10. ????[self.tableView?reloadData];??
11. }??

基本就结束了，这只是一个简单的Demo，sqlite的基本使用方法我也会慢慢整理出来，最后附上完整工程文件：DictionaryDemo