萬盛學電腦網

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

iOS SQLite 數據庫操作完整封裝:打開、建表、增刪改查代碼範例

核心類設計

@interface DBManager : NSObject
@property (nonatomic) sqlite3 *database;
- (BOOL)openDatabase;
- (BOOL)createTables;
- (BOOL)insertReport:(NSString *)time title:(NSString *)title cal:(NSString *)cal runtime:(NSString *)runtime;
- (NSArray *)queryAllReports;
@end

1. 打開/創建數據庫文件

- (BOOL)openDatabase {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docDir = paths.firstObject;
    NSString *path = [docDir stringByAppendingPathComponent:@"mydb.sqlite"];
    
    NSFileManager *fm = [NSFileManager defaultManager];
    BOOL exists = [fm fileExistsAtPath:path];
    
    if (sqlite3_open([path UTF8String], &_database) != SQLITE_OK) {
        sqlite3_close(_database);
        NSLog(@"❌ 打開數據庫失敗:%s", sqlite3_errmsg(_database));
        return NO;
    }
    
    if (!exists) {
        return [self createTables];
    }
    return YES;
}

2. 創建表結構

- (BOOL)createTables {
    const char *sql = "CREATE TABLE IF NOT EXISTS reports ("
                      "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                      "stime TEXT, stitle TEXT, scal TEXT, sruntime TEXT)";
    char *errMsg = nil;
    if (sqlite3_exec(_database, sql, NULL, NULL, &errMsg) != SQLITE_OK) {
        NSLog(@"❌ 建表失敗:%s", errMsg);
        sqlite3_free(errMsg);
        return NO;
    }
    NSLog(@"✅ 表創建成功");
    return YES;
}

3. 插入數據(參數化查詢,防注入)

- (BOOL)insertReport:(NSString *)time title:(NSString *)title cal:(NSString *)cal runtime:(NSString *)runtime {
    const char *sql = "INSERT INTO reports (stime, stitle, scal, sruntime) VALUES (?, ?, ?, ?)";
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(_database, sql, -1, &stmt, NULL) != SQLITE_OK) {
        NSLog(@"❌ 準備語句失敗:%s", sqlite3_errmsg(_database));
        return NO;
    }
    
    sqlite3_bind_text(stmt, 1, [time UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [title UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, [cal UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, [runtime UTF8String], -1, SQLITE_TRANSIENT);
    
    BOOL success = (sqlite3_step(stmt) == SQLITE_DONE);
    sqlite3_finalize(stmt);
    
    if (!success) NSLog(@"❌ 插入失敗:%s", sqlite3_errmsg(_database));
    return success;
}

4. 查詢數據

- (NSArray *)queryAllReports {
    const char *sql = "SELECT id, stime, stitle, scal, sruntime FROM reports ORDER BY id DESC";
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(_database, sql, -1, &stmt, NULL) != SQLITE_OK) return nil;
    
    NSMutableArray *results = [NSMutableArray array];
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        NSDictionary *row = @{
            @"id": @(sqlite3_column_int(stmt, 0)),
            @"time": @(sqlite3_column_text(stmt, 1) ? [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)] : @""),
            @"title": @(sqlite3_column_text(stmt, 2) ? [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)] : @""),
            @"cal": @(sqlite3_column_text(stmt, 3) ? [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)] : @""),
            @"runtime": @(sqlite3_column_text(stmt, 4) ? [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 4)] : @"")
        };
        [results addObject:row];
    }
    sqlite3_finalize(stmt);
    return results;
}

5. 關閉數據庫

- (void)closeDatabase {
    if (_database) {
        sqlite3_close(_database);
        _database = NULL;
    }
}
- (void)dealloc { [self closeDatabase]; }

現代化建議:使用 FMDB / GRDB / SQLite.swift

// FMDB 示例(OC)
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];
[queue inDatabase:^(FMDatabase *db) {
    [db executeUpdate:@"INSERT INTO reports (stime, stitle) VALUES (?, ?)", time, title];
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM reports"];
    while ([rs next]) { NSLog(@"%@", rs.resultDictionary); }
}];

// GRDB (Swift) - 類型安全、Codable 支援、異步安全
let dbQueue = try DatabaseQueue(path: path)
try dbQueue.write { db in
    try db.execute(sql: "INSERT INTO reports (stime, stitle) VALUES (?, ?)", arguments: [time, title])
}
let reports = try dbQueue.read { db in
    try Report.fetchAll(db, sql: "SELECT * FROM reports")
}

原生 SQLite API 適用於極簡依賴場景;生產項目強烈建議使用成熟封裝庫。

copyright © 萬盛學電腦網 all rights reserved