雖然 Core Data、Realm 等 ORM 框架流行,但直接使用 SQLite C API 仍是理解底層存儲、輕量級項目、性能敏感場景的核心技能。本文提供一套完整的 DB 管理類與 Person 模型層 CRUD 封裝。
@interface DB : NSObject
+ (sqlite3 *)openDatabase; // 打開/創建數據庫,返回連接句柄
- (void)closeDatabase; // 關閉連接
@end
@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
@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
+ (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 @[];
}
+ (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;
}
sqlite3_bind_xxx。prepare_v2 必配對 finalize,否則洩漏語句句柄。sqlite3_errmsg(db) 獲取詳細錯誤信息。BEGIN TRANSACTION / COMMIT,性能提升 100 倍+。生產環境強烈建議使用成熟封裝庫(如 FMDB),提供:
理解底層 C API 讓你在排查 ORM 故障、極致優化時游刃有餘。