本文用一個「通訊錄」小專案,把 SQLite 模糊查詢從建模、封裝到使用者介面串起來,最後再整理開發時最常打交道的幾個 C 語言 API。
先新增一個繼承自 NSObject 的類別 YYPerson,用來對應資料表中的一筆紀錄。
// YYPerson.h #import <Foundation/Foundation.h> @interface YYPerson : NSObject @property (nonatomic, assign) int ID; @property (nonatomic, copy) NSString *name; @property (nonatomic, assign) int age; @end
再建立一個工具類 YYPersonTool,把資料庫的開啟、建表、新增與查詢都收在這裡,對外只暴露必要的類方法。
// YYPersonTool.h #import <Foundation/Foundation.h> @class YYPerson; @interface YYPersonTool : NSObject + (void)save:(YYPerson *)person; // 保存一筆聯絡人 + (NSArray *)query; // 查詢全部 + (NSArray *)queryWithCondition:(NSString *)condition; // 條件查詢 @end
實作的部分有兩個重點:用 +initialize 確保資料庫只被開啟與建表一次,以及用 sqlite3_prepare_v2 搭配 sqlite3_step 逐列取出查詢結果。
// YYPersonTool.m
#import "YYPersonTool.h"
#import "YYPerson.h"
#import <sqlite3.h>
@implementation YYPersonTool
static sqlite3 *_db;
+ (void)initialize
{
// 1. 取得沙盒中的文件目錄,拼出資料庫完整路徑
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES) lastObject];
NSString *fileName = [doc stringByAppendingPathComponent:@"person.sqlite"];
// 2. 開啟資料庫(檔案不存在時會自動建立)
if (sqlite3_open(fileName.UTF8String, &_db) == SQLITE_OK) {
// 3. 建表
const char *sql = "CREATE TABLE IF NOT EXISTS t_person ("
"id integer PRIMARY KEY AUTOINCREMENT,"
"name text NOT NULL,"
"age integer NOT NULL);";
char *errmsg = NULL;
sqlite3_exec(_db, sql, NULL, NULL, &errmsg);
if (errmsg) printf("建表失敗---%s", errmsg);
}
}
+ (void)save:(YYPerson *)person
{
NSString *sql = [NSString stringWithFormat:
@"INSERT INTO t_person (name, age) VALUES ('%@', %d);",
person.name, person.age];
char *errmsg = NULL;
sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errmsg);
if (errmsg) NSLog(@"插入失敗--%s", errmsg);
}
+ (NSArray *)query
{
return [self queryWithCondition:@""];
}
// 模糊查詢
+ (NSArray *)queryWithCondition:(NSString *)condition
{
NSMutableArray *persons = nil;
NSString *NSsql = [NSString stringWithFormat:
@"SELECT id, name, age FROM t_person WHERE name like '%%%@%%' ORDER BY age ASC;", condition];
const char *sql = NSsql.UTF8String;
sqlite3_stmt *stmt = NULL;
if (sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL) == SQLITE_OK) {
persons = [NSMutableArray array];
// 每呼叫一次 sqlite3_step,stmt 就指向下一筆
while (sqlite3_step(stmt) == SQLITE_ROW) {
int ID = sqlite3_column_int(stmt, 0);
const unsigned char *name = sqlite3_column_text(stmt, 1);
int age = sqlite3_column_int(stmt, 2);
YYPerson *p = [[YYPerson alloc] init];
p.ID = ID;
p.name = [NSString stringWithUTF8String:(const char *)name];
p.age = age;
[persons addObject:p];
}
}
return persons;
}
@end
在 storyboard 中刪除預設控制器,改放一個 UINavigationController 與一個 UITableViewController 並建立關聯,程式上讓主控制器直接繼承 UITableViewController。
// YYViewController.m
#import "YYViewController.h"
#import "YYPerson.h"
#import "YYPersonTool.h"
@interface YYViewController () <UISearchBarDelegate>
@property (nonatomic, strong) NSArray *persons;
@end
@implementation YYViewController
#pragma mark - 懶載入
- (NSArray *)persons
{
if (_persons == nil) {
_persons = [YYPersonTool query];
}
return _persons;
}
- (void)viewDidLoad
{
[super viewDidLoad];
// 把搜尋框放到導覽列標題的位置
UISearchBar *search = [[UISearchBar alloc] init];
search.frame = CGRectMake(0, 0, 300, 44);
search.delegate = self;
self.navigationItem.titleView = search;
}
#pragma mark - 資料來源
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
return self.persons.count;
}
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
static NSString *ID = @"ID";
UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:ID];
if (cell == nil) {
cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle
reuseIdentifier:ID];
}
YYPerson *person = self.persons[indexPath.row];
cell.textLabel.text = person.name;
cell.detailTextLabel.text = [NSString stringWithFormat:@"年齡 %d", person.age];
return cell;
}
// 放幾筆假資料方便測試
- (IBAction)add:(UIBarButtonItem *)sender {
NSArray *names = @[@"西門抽血", @"西門抽筋", @"西門抽風", @"西門吹雪",
@"東門抽血", @"東門抽筋", @"東門抽風", @"東門吹雪",
@"北門抽血", @"北門抽筋", @"南門抽風", @"南門吹雪"];
for (int i = 0; i < 20; i++) {
YYPerson *p = [[YYPerson alloc] init];
p.name = [NSString stringWithFormat:@"%@-%d",
names[arc4random_uniform(names.count)], arc4random_uniform(100)];
p.age = arc4random_uniform(20) + 20;
[YYPersonTool save:p];
}
}
#pragma mark - 搜尋框代理
- (void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText
{
self.persons = [YYPersonTool queryWithCondition:searchText];
[self.tableView reloadData];
[searchBar resignFirstResponder];
}
@end
執行後可以看到,輸入關鍵字就能即時過濾列表,並依年齡遞增排序。
SELECT id, name, age FROM t_person WHERE name like '%%%@%%' ORDER BY age ASC;
三個容易踩坑的細節:
name like '西門' 等同於 name = '西門',是完全比對。name like '%西%' 才是模糊比對:只要字串中間含有「西」,左右可以是任意內容。stringWithFormat: 中的 % 是跳脫字元,要產生一個 % 必須寫成兩個 %%,所以才會出現 '%%%@%%' 這種看似複雜的寫法。
開啟(或建立)資料庫:
int sqlite3_open(const char *filename, // 資料庫檔案路徑
sqlite3 **ppDb); // 傳回資料庫實例
執行任意 SQL 語句:
int sqlite3_exec(sqlite3 *, // 已開啟的實例
const char *sql, // 要執行的 SQL
int (*callback)(void*,int,char**,char**), // 執行後的回呼
void *, // 回呼的第一個參數
char **errmsg); // 錯誤訊息
查詢前的準備——檢查語句合法性:
int sqlite3_prepare_v2(sqlite3 *db, // 資料庫實例
const char *zSql, // 要檢查的 SQL
int nByte, // SQL 最大位元組長度
sqlite3_stmt **ppStmt, // 用來取資料的 stmt
const char **pzTail);
逐列取值:
int sqlite3_step(sqlite3_stmt *); // 取到一列時傳回 SQLITE_ROW double sqlite3_column_double(sqlite3_stmt *, int iCol); // 浮點數 int sqlite3_column_int(sqlite3_stmt *, int iCol); // 整數 sqlite3_int64 sqlite3_column_int64(sqlite3_stmt *, int iCol); // 長整數 const void *sqlite3_column_blob(sqlite3_stmt *, int iCol); // 二進位 const unsigned char *sqlite3_column_text(sqlite3_stmt *, int iCol);// 字串
欄位索引從 0 開始,這點和 placeholder 的編號規則不同,很容易搞混。
sqlite3 *db = NULL;
int result = sqlite3_open([path UTF8String], &db); // 路徑必須是 C 字串
if (result == SQLITE_OK) { /* 開啟成功 */ }
sqlite3_close(db);
char *errorMsg = NULL;
char *sql = "create table if not exists t_person(id integer primary key "
"autoincrement, name text, age integer);";
int result = sqlite3_exec(db, sql, NULL, NULL, &errorMsg);
sqlite3_exec 很適合建表、更新、插入、刪除,也常用來下達 begin transaction;、rollback;、commit; 等交易指令;但查詢不建議用它,因為它不會把結果交回來。
char *sql = "insert into t_person(name, age) values(?, ?);";
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, "母雞", -1, NULL);
sqlite3_bind_int(stmt, 2, 27);
}
if (sqlite3_step(stmt) != SQLITE_DONE) {
NSLog(@"插入數據錯誤");
}
sqlite3_finalize(stmt);
幾個綁定函數的共通規則:第一個參數是 sqlite3_stmt *;第二個參數是 placeholder 的位置,從 1 開始而不是 0;第三個參數是要綁定的值;第四個參數是資料長度,C 字串可以直接給 -1;第五個參數是可選的回呼,通常用來善後清理記憶體。執行成功時 sqlite3_step 傳回 SQLITE_DONE,用完別忘了 sqlite3_finalize 銷毀 stmt。
char *sql = "select id, name, age from t_person;";
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
while (sqlite3_step(stmt) == SQLITE_ROW) {
int _id = sqlite3_column_int(stmt, 0);
char *_name = (char *)sqlite3_column_text(stmt, 1);
int _age = sqlite3_column_int(stmt, 2);
NSLog(@"id=%i, name=%s, age=%i", _id, _name, _age);
}
}
sqlite3_finalize(stmt);
把這幾個 API 的職責記清楚——開啟、準備、逐列取值、收尾——之後再用 FMDB 之類的封裝,就能明白它在背後替你做了哪些事。