您现在的位置: IOS教程網 >> IOS系統 >  >> iOS 程式開發 >> iOS 開發綜合

SQLite 模糊查詢實戰:從 LIKE 語法到常用 C 語言 API

編輯:iOS 開發綜合

一、模糊查詢完整範例

本文用一個「通訊錄」小專案,把 SQLite 模糊查詢從建模、封裝到使用者介面串起來,最後再整理開發時最常打交道的幾個 C 語言 API。

1. 建立資料模型

先新增一個繼承自 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

2. 封裝工具類

再建立一個工具類 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

3. 搭出列表介面

在 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

執行後可以看到,輸入關鍵字就能即時過濾列表,並依年齡遞增排序。

4. 關鍵語句怎麼讀

SELECT id, name, age FROM t_person WHERE name like '%%%@%%' ORDER BY age ASC;

三個容易踩坑的細節:

  • name like '西門' 等同於 name = '西門',是完全比對。
  • name like '%西%' 才是模糊比對:只要字串中間含有「西」,左右可以是任意內容。
  • stringWithFormat: 中的 % 是跳脫字元,要產生一個 % 必須寫成兩個 %%,所以才會出現 '%%%@%%' 這種看似複雜的寫法。

二、SQLite 常用函數

1. 核心 API 速查

開啟(或建立)資料庫:

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 的編號規則不同,很容易搞混。

2. 開啟與關閉

sqlite3 *db = NULL;
int result = sqlite3_open([path UTF8String], &db);   // 路徑必須是 C 字串
if (result == SQLITE_OK) { /* 開啟成功 */ }
sqlite3_close(db);

3. 執行不回傳資料的語句

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; 等交易指令;但查詢不建議用它,因為它不會把結果交回來。

4. 用 placeholder 插入資料

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。

5. 查詢資料

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 之類的封裝,就能明白它在背後替你做了哪些事。

  1. 上一篇文章:
  2. 下一篇文章:
蘋果刷機越獄教程| IOS教程問題解答| IOS技巧綜合| IOS7技巧| IOS8教程
Copyright © IOS教程網 All Rights Reserved