萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> iOS 開發

iOS SQLite 數據庫 CRUD 完整封裝:從 DB 管理到模型層實踐

雖然 Core Data、Realm 等 ORM 框架流行,但直接使用 SQLite C API 仍是理解底層存儲、輕量級項目、性能敏感場景的核心技能。本文提供一套完整的 DB 管理類與 Person 模型層 CRUD 封裝。

1. 數據庫管理類 (DB)

DB.h

@interface DB : NSObject
+ (sqlite3 *)openDatabase; // 打開/創建數據庫,返回連接句柄
- (void)closeDatabase;     // 關閉連接
@end

DB.m

@implementation DB
static sqlite3 *_database = nil;

+ (sqlite3 *)openDatabase {
    if (_database) return _database;
    
    // 1. 確定沙盒 Documents 目錄路徑
    NSString *docPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).firstObject;
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"app.db"];
    
    // 2. 首次運行:從 Bundle 複製預置數據庫(可選)
    NSFileManager *fm = [NSFileManager defaultManager];
    if (![fm fileExistsAtPath:dbPath]) {
        NSString *seedPath = [[NSBundle mainBundle] pathForResource:@"seed" ofType:@"db"];
        if (seedPath) {
            NSError *err = nil;
            [fm copyItemAtPath:seedPath toPath:dbPath error:&err];
            if (err) NSLog(@"種子數據庫複製失敗: %@", err);
        }
    }
    
    // 3. 打開數據庫
    if (sqlite3_open([dbPath UTF8String], &_database) != SQLITE_OK) {
        NSLog(@"數據庫打開失敗: %s", sqlite3_errmsg(_database));
        return nil;
    }
    
    // 4. 可選:啟用 WAL 模式提升併發性能
    sqlite3_exec(_database, "PRAGMA journal_mode=WAL;", NULL, NULL, NULL);
    
    return _database;
}

- (void)closeDatabase {
    if (_database) {
        sqlite3_close(_database);
        _database = nil;
    }
}
@end

2. 模型層

@interface Person : NSObject
@property (nonatomic, copy) NSString *name;
@property (nonatomic, copy) NSString *phone;
@property (nonatomic, assign) int age;
@property (nonatomic, assign) int64_t ID; // SQLite rowid 通常用 int64

- (instancetype)initWithName:(NSString *)name phone:(NSString *)phone age:(int)age ID:(int64_t)ID;
@end

3. CRUD 核心實現 (Person 類方法)

查詢所有

+ (NSArray *)findAll {
    sqlite3 *db = [DB openDatabase];
    sqlite3_stmt *stmt = nil;
    const char *sql = "SELECT id, name, phone, age FROM person ORDER BY id";
    
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
        NSMutableArray *results = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int64_t ID = sqlite3_column_int64(stmt, 0);
            const char *name = (const char *)sqlite3_column_text(stmt, 1);
            const char *phone = (const char *)sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            
            Person *p = [[Person alloc] initWithName:@(name ? [NSString stringWithUTF8String:name] : @"")
                                               phone:@(phone ? [NSString stringWithUTF8String:phone] : @"")
                                                age:age ID:ID];
            [results addObject:p];
        }
        sqlite3_finalize(stmt);
        return results;
    }
    sqlite3_finalize(stmt);
    return @[];
}

按 ID 查詢

+ (Person *)findByID:(int64_t)ID {
    sqlite3 *db = [DB openDatabase];
    sqlite3_stmt *stmt = nil;
    const char *sql = "SELECT id, name, phone, age FROM person WHERE id = ?";
    
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
        sqlite3_bind_int64(stmt, 1, ID);
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            int64_t id = sqlite3_column_int64(stmt, 0);
            const char *name = (const char *)sqlite3_column_text(stmt, 1);
            const char *phone = (const char *)sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            Person *p = [[Person alloc] initWithName:@(name ? [NSString stringWithUTF8String:name] : @"")
                                               phone:@(phone ? [NSString stringWithUTF8String:phone] : @"")
                                                age:age ID:id];
            sqlite3_finalize(stmt);
            return p;
        }
    }
    sqlite3_finalize(stmt);
    return nil;
}

插入

+ (BOOL)insertWithName:(NSString *)name phone:(NSString *)phone age:(int)age {
    sqlite3 *db = [DB openDatabase];
    sqlite3_stmt *stmt = nil;
    const char *sql = "INSERT INTO person (name, phone, age) VALUES (?, ?, ?)";
    
    BOOL success = NO;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 2, [phone UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 3, age);
        
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            success = YES;
        }
    }
    sqlite3_finalize(stmt);
    return success;
}

更新

+ (BOOL)updateWithID:(int64_t)ID name:(NSString *)name phone:(NSString *)phone age:(int)age {
    sqlite3 *db = [DB openDatabase];
    sqlite3_stmt *stmt = nil;
    const char *sql = "UPDATE person SET name = ?, phone = ?, age = ? WHERE id = ?";
    
    BOOL success = NO;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 2, [phone UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 3, age);
        sqlite3_bind_int64(stmt, 4, ID);
        
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            success = (sqlite3_changes(db) > 0);
        }
    }
    sqlite3_finalize(stmt);
    return success;
}

刪除

+ (BOOL)deleteByID:(int64_t)ID {
    sqlite3 *db = [DB openDatabase];
    sqlite3_stmt *stmt = nil;
    const char *sql = "DELETE FROM person WHERE id = ?";
    
    BOOL success = NO;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
        sqlite3_bind_int64(stmt, 1, ID);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            success = (sqlite3_changes(db) > 0);
        }
    }
    sqlite3_finalize(stmt);
    return success;
}

關鍵最佳實踐

  • 參數綁定:**絕對禁止**字符串拼接 SQL(防注入、處理特殊字符),統一用 sqlite3_bind_xxx。
  • SQLITE_TRANSIENT:綁定文本時使用,表示 SQLite 需複製內容,避免調用方釋放導致野指針。
  • 語句最終化:每個 prepare_v2 必配對 finalize,否則洩漏語句句柄。
  • 錯誤處理:檢查每步返回碼,sqlite3_errmsg(db) 獲取詳細錯誤信息。
  • 事務批量操作:大量寫入時 BEGIN TRANSACTION / COMMIT,性能提升 100 倍+。
  • 線程安全:SQLite 連接**不支持跨線程共用**,每線程獨立 open/close 或使用串行佇列。

現代替代:FMDB / GRDB / SQLite.swift

生產環境強烈建議使用成熟封裝庫(如 FMDB),提供:

  • OC/Swift 友好 API
  • 自動參數綁定、事務管理、佇列線程安全
  • 遷移腳本、FTS 全文搜索、JSON1 擴展支持

理解底層 C API 讓你在排查 ORM 故障、極致優化時游刃有餘。

copyright © 萬盛學電腦網 all rights reserved