<table border="2" width="600" height="80" cellspacing="1" cellpadding="1" align="left"><tbody><tr><td><div style="text-align:center"><strong>聲明</strong></div><span style="color:#ff0000">歡迎轉載,但是請尊重作者勞動成果,轉載請保留此框內聲明,謝謝。<br/>文章出處:<a href="http://blog.csdn.net/iukey" target="_blank">http://blog.csdn.net/iukey</a></span></td></tr></tbody></table>
sqlite 是個好東西,對于移動平臺來說。一直想寫有關sqlite的教程,但是不知道從何寫起,考慮了很久,還是從一個小Demo 談起吧。我寫了一個精簡版的詞典,實現了增刪查改的基本功能。
工程結構如下

最后效果圖如下

。
效果圖中可以看到,我查詢 "cc",所有相關條目都查詢出來了。
好了,現在開始講解我的項目。首先可以看我的工程目錄,QueryResultList 是界面控制類,DB 是數據庫操作類。
整個項目的流程:我們在search框中輸入要查詢的內容點擊搜索,底層模糊查詢返回結果顯示在tableView中。
我們先從底層的操作講起,目前我就實現了插入與查詢操作,刪除與修改以后再補上:
1.創建數據庫
~~~
- (const char*)getFilePath{//獲取數據庫路徑
return [[NSString stringWithFormat:@"%@/Documents/l",NSHomeDirectory() ] UTF8String];
}
// DB.h
//iukey
#import <Foundation/Foundation.h>
#import "/usr/include/sqlite3.h"
@interface DB : NSObject{
sqlite3* pdb;//數據庫句柄
}
@property(nonatomic,assign)sqlite3* pdb;
- (BOOL)insertRecordWithEN:(NSString*)en CN:(NSString*)cn Comment:(NSString*)comment;//插入一條紀錄
- (NSMutableArray*)quary:(NSString*)str;//查詢
- (const char*)getFilePath;//獲取數據庫路徑
- (BOOL)createDB;//創建數據庫
- (BOOL)createTable;//創建表
@end
~~~
2.創建表
~~~
- (BOOL)createTable{
char* err;
char* sql = "create table dictionary(ID integer primary key autoincrement,en nvarchar(64),cn nvarchar(128),comment nvarchar(256))";//創建表語句
if (sql==NULL) {
return NO;
}
if (SQLITE_OK != sqlite3_open([self getFilePath ], &pdb)){
return NO;
}
if (SQLITE_OK == sqlite3_exec(pdb, sql, NULL, NULL, &err)) {//執行創建表語句成功
sqlite3_close(pdb);
return YES;
}else{//創建表失敗
return NO;
}
}
~~~
3.插入一條紀錄
~~~
- (BOOL)insertRecordWithEN:(NSString*)en CN:(NSString*)cn Comment:(NSString*)comment{
int ret = 0;
if (SQLITE_OK != sqlite3_open([self getFilePath ], &pdb)){//打開數據庫
return NO;
}
char* sql = "insert into dictionary(en,cn,comment) values(?,?,?);";//插入語句,3個參數
sqlite3_stmt* stmt;//
if (sqlite3_prepare_v2(pdb, sql, -1, &stmt, nil)==SQLITE_OK) {//準備語句
sqlite3_bind_text(stmt, 1, [en UTF8String], -1, NULL);//綁定參數
sqlite3_bind_text(stmt, 2, [cn UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 3, [comment UTF8String], -1, NULL);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//執行查詢
sqlite3_finalize(stmt);
sqlite3_close(pdb);
return YES;
}else{
return NO;
}
}
~~~
4.查詢
~~~
- (NSMutableArray*)quary:(NSString *)str{
NSMutableArray* arr =[[NSMutableArray alloc]init];//存放查詢結果
if (SQLITE_OK != sqlite3_open([self getFilePath ], &pdb)){
return NO;
}
char* sql = "select * from dictionary where en like ? or cn like ? or comment like ?;";//查詢語句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(pdb, sql, -1, &stmt, nil)==SQLITE_OK) {//準備
sqlite3_bind_text(stmt, 1,[[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 3, [[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
}else{
return nil;
}
while( SQLITE_ROW == sqlite3_step(stmt) ){//執行
char* _en = (char*)sqlite3_column_text(stmt, 1);
char* _cn = (char*)sqlite3_column_text(stmt, 2);
char* _comment = (char*)sqlite3_column_text(stmt, 3);
NSMutableDictionary* dict = [[NSMutableDictionary alloc]init];//單條紀錄
[dict setObject:[NSString stringWithCString:_en encoding:NSUTF8StringEncoding] forKey:@"kEN"];
[dict setObject:[NSString stringWithCString:_cn encoding:NSUTF8StringEncoding] forKey:@"kCN"];
[dict setObject:[NSString stringWithCString:_comment encoding:NSUTF8StringEncoding] forKey:@"kCOMMENT"];
[arr addObject:dict];//插入到結果數組
}
sqlite3_finalize(stmt);
sqlite3_close(pdb);
return [arr autorelease];//返回查詢結果數組
}
~~~
5.DB 初始化
我先定義了一個宏,用來標識是否是第一次運行程序,如果是第一次運行就要運行創建數據庫與表的函數,否則就不運行,具體看代碼:
~~~
#define FIRSTINIT 1//第一次運行則設為1,否則就是0
- (id)init{
self = [super init];
if (self!=nil) {
#if FIRSTINIT
[self createDB];
[self createTable];
[self insertRecordWithEN:@"cctv1" CN:@"央視1套" Comment:@"SB電視臺1"];//為了方便測試我插入了一些紀錄
[self insertRecordWithEN:@"cctv2" CN:@"央視2套" Comment:@"SB電視臺2"];
[self insertRecordWithEN:@"cctv3" CN:@"央視3套" Comment:@"SB電視臺3"];
[self insertRecordWithEN:@"cctv4" CN:@"央視4套" Comment:@"SB電視臺4"];
[self insertRecordWithEN:@"cctv5" CN:@"央視5套" Comment:@"SB電視臺5"];
[self insertRecordWithEN:@"cctv6" CN:@"央視6套" Comment:@"SB電視臺6"];
[self insertRecordWithEN:@"cctv7" CN:@"央視7套" Comment:@"SB電視臺7"];
[self insertRecordWithEN:@"cctv8" CN:@"央視8套" Comment:@"SB電視臺8"];
[self insertRecordWithEN:@"cctv9" CN:@"央視9套" Comment:@"SB電視臺9"];
[self insertRecordWithEN:@"cctv10" CN:@"央視10套" Comment:@"SB電視臺10"];
[self insertRecordWithEN:@"cctv11" CN:@"央視11套" Comment:@"SB電視臺11"];
[self insertRecordWithEN:@"cctv12" CN:@"央視12套" Comment:@"SB電視臺12"];
#endif
}
return self;
}
~~~
底層的數據庫暫時就這些,接著講上層的界面部分
~~~
// QueryResultList.h
// iukey
#import <UIKit/UIKit.h>
#import "DB.h"
@interface QueryResultList : UITableViewController<UISearchBarDelegate>{
NSMutableArray* mArr;//tableView數據源
DB* db ;//數據庫對象
UISearchBar* searchBar ;//搜索框
}
@property(nonatomic,retain)NSMutableArray* mArr;
@property(nonatomic,retain)DB* db;
@property(nonatomic,retain)UISearchBar* searchBar ;
@end
~~~
~~~
- (id)initWithStyle:(UITableViewStyle)style{
self = [super initWithStyle:style];
if (self) {
mArr = [[NSMutableArray alloc]init];//表數據源
db =[[DB alloc]init];//數據庫控制器
searchBar = [[UISearchBar alloc]initWithFrame:CGRectMake(44.0,0,200.0,44)];//搜索控件
searchBar.delegate=self;//設置搜索控件的委托
self.navigationItem.titleView = searchBar;
}
return self;
}
~~~
接下來我們實現表格數據源委托
~~~
#pragma mark - Table view data source
- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView{
return 1;//分區數
}
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section{
return [mArr count];//行數
}
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath{
static NSString *CellIdentifier = @"Cell";
UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
for ( UIView* view in cell.contentView.subviews) {
[view removeFromSuperview];
}
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier] autorelease];
}
UILabel* lblEN = [[UILabel alloc]initWithFrame:CGRectMake(5.0, 5.0, 300.0, 30.0)];//顯示英文的文字標簽控件
UILabel* lblCN = [[UILabel alloc]initWithFrame:CGRectMake(5.0, 35.0, 300.0, 30.0)];//中文
UILabel* lblComment = [[UILabel alloc]initWithFrame:CGRectMake(5.0, 65.0, 300.0, 30.0)];//詳細
//背景顏色清掉
lblEN.backgroundColor = [UIColor clearColor];
lblCN.backgroundColor = [UIColor clearColor];
lblComment.backgroundColor = [UIColor clearColor];
//
lblEN.text = [[mArr objectAtIndex:indexPath.row] objectForKey:@"kEN"];
lblCN.text = [[mArr objectAtIndex:indexPath.row] objectForKey:@"kCN"];
lblComment.text = [[mArr objectAtIndex:indexPath.row] objectForKey:@"kCOMMENT"];
[cell.contentView addSubview:lblEN];
[cell.contentView addSubview:lblCN];
[cell.contentView addSubview:lblComment];
cell.selectionStyle = UITableViewCellSelectionStyleNone;//選中不要高亮
[lblEN release];
[lblCN release];
[lblComment release];
return cell;
}
~~~
然后實現搜索委托方法:
~~~
#pragma mark - UISearchBar delegate
- (void) searchBarSearchButtonClicked:(UISearchBar*)activeSearchbar{
[mArr removeAllObjects];
NSString* query= searchBar.text;
NSMutableArray* arr = [db quary:query];
for ( NSMutableDictionary* dict in arr) {
[mArr addObject:dict];
}
[searchBar resignFirstResponder];
[self.tableView reloadData];
}
~~~
基本就結束了,這只是一個簡單的Demo,sqlite的基本使用方法我也會慢慢整理出來,最后附上完整工程文件:[DictionaryDemo](http://download.csdn.net/detail/iukey/4112121)