iOS SQLite 數據庫封裝：查詢單欄、全表、條件查詢與錯誤處理

1. 查詢單一欄位所有值

- (NSArray *)selectWithColumName:(NSString *)columName tableName:(NSString *)tableName {
    if ([self openDatabase]) {
        NSString *sql = [NSString stringWithFormat:@"SELECT %@ FROM %@", columName, tableName];
        sqlite3_stmt *stmt;
        if (sqlite3_prepare_v2(_db, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK) {
            NSMutableArray *arr = [NSMutableArray array];
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                [arr addObject:[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)]];
            }
            sqlite3_finalize(stmt);
            return arr;
        }
    }
    return nil;
}

2. 查詢全表映射到模型對象

- (NSArray *)selectAllMembersWithTableName:(NSString *)tableName objectModel:(id)object {
    if ([self openDatabase]) {
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@", tableName];
        sqlite3_stmt *stmt;
        if (sqlite3_prepare_v2(_db, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK) {
            NSMutableArray *arr = [NSMutableArray array];
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                id model = [[object class] alloc] init];
                for (int i = 0; i < sqlite3_column_count(stmt); i++) {
                    [model setValue:[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, i)]
                          forKey:[NSString stringWithUTF8String:(const char *)sqlite3_column_name(stmt, i)]];
                }
                [arr addObject:model];
            }
            sqlite3_finalize(stmt);
            return arr;
        }
    }
    return nil;
}

3. 條件查詢（字典構建 WHERE 子句）

- (NSArray *)selectWithSqlQueryDictionary:(NSDictionary *)dict tableName:(NSString *)tableName model:(id)object {
    if ([self openDatabase]) {
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE ", tableName];
        for (NSString *key in dict.allKeys) {
            sql = [sql stringByAppendingFormat:@"%@ = '%@' AND ", key, dict[key]];
        }
        sql = [sql substringToIndex:sql.length - 4]; // 移除尾部 " AND "
        // ... 準備語句、逐行映射模型，同上 ...
    }
    return nil;
}

4. 統一錯誤處理

- (NSError *)errorWithMessage:(NSString *)msg {
    return [NSError errorWithDomain:@"XXDB" code:sqlite3_errcode(_db)
                         userInfo:@{NSLocalizedDescriptionKey: msg}];
}

常見問題

• Qt 配合 SQLite 查詢：原理相同，調用 Qt SQL 模組即可。
• 按年月查詢：SELECT * FROM db WHERE substr(date(field),1,7) = '2012-07'。