sqlite 實例教程 IOS下用sqlite打造詞典－IOS開發 · sqlite基礎教程

<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 談起吧。我寫了一個精簡版的詞典，實現了增刪查改的基本功能。工程結構如下 ![](https://box.kancloud.cn/2016-05-17_573ab61125eab.gif) 最后效果圖如下 ![](https://box.kancloud.cn/2016-05-17_573ab620cf1bc.gif) 。效果圖中可以看到，我查詢 "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)