iOS 上的輕量資料儲存首選是 SQLite。它不需要伺服器、單一檔案即可攜帶,對結構化資料的查詢效率遠優於純檔案讀寫。本文以「學生資料表」為例,完整走過開啟資料庫、建立 statement、執行 CRUD 與模型封裝。
id,通常設為 AUTOINCREMENT 由系統自動遞增。-- 建立表 CREATE TABLE 表名 (欄位名 型別 [是否主鍵], 欄位名 型別, ...); -- 查詢(* 代表所有欄位,WHERE 為條件) SELECT 欄位名 FROM 表名 WHERE 欄位名 = 值; -- 新增 INSERT INTO 表名 (欄位1, 欄位2) VALUES (值1, 值2); -- 修改 UPDATE 表名 SET 欄位 = 值 WHERE 欄位 = 值; -- 刪除 DELETE FROM 表名 WHERE 欄位 = 值;
#define FILE_NAME @"Database.sqlite"
static sqlite3 *db = nil;
@implementation Database
// 開啟資料庫
+ (sqlite3 *)openDB
{
if (!db) {
// 1. 取得 Documents 目錄路徑
// 參數1: 目錄名 參數2: 搜尋範圍 參數3: 是否絕對路徑
NSString *docPath =
[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES) firstObject];
NSString *dbPath = [docPath stringByAppendingPathComponent:FILE_NAME];
NSFileManager *fm = [NSFileManager defaultManager];
// 2. 若 Documents 中沒有,就從 App bundle 複製一份預建的空庫
if (![fm fileExistsAtPath:dbPath]) {
NSString *bundlePath =
[[NSBundle mainBundle] pathForResource:@"Database" ofType:@"sqlite"];
NSError *error = nil;
BOOL result = [fm copyItemAtPath:bundlePath toPath:dbPath error:&error];
if (!result) NSLog(@"%@", error);
}
// 3. 開啟(UTF8String 可將 NSString 轉為 C 的 char *)
sqlite3_open([dbPath UTF8String], &db);
}
return db;
}
// 關閉資料庫
+ (void)closeDB
{
sqlite3_close(db);
db = nil;
}
@end
為什麼要從 bundle 複製?因為 App bundle 是唯讀的,而預建的表結構(schema)通常隨 App 一起打包,第一次啟動時複製到可寫目錄即可。
@interface Student : NSObject @property (nonatomic, assign) NSInteger ID; @property (nonatomic, copy) NSString *name; @property (nonatomic, copy) NSString *sex; @property (nonatomic, assign) NSInteger age; @property (nonatomic, strong) UIImage *photo; @end
+ (NSMutableArray *)getAllStudents
{
sqlite3 *db = [Database openDB];
// statement 是資料庫操作指標
sqlite3_stmt *stmt = nil;
// 驗證 SQL 語句並取得 statement
// 參數1: 資料庫指標
// 參數2: SQL 語句
// 參數3: SQL 長度,-1 代表自動計算
// 參數4: 回傳的 statement
// 參數5: 保留參數,傳 NULL
int result = sqlite3_prepare_v2(db, "select * from Student", -1, &stmt, NULL);
NSMutableArray *studentArr = [NSMutableArray array];
if (result == SQLITE_OK) {
// sqlite3_step 每次推進一列,回傳 SQLITE_ROW 代表還有資料
while (sqlite3_step(stmt) == SQLITE_ROW) {
// 欄位索引從 0 開始
int ID = sqlite3_column_int(stmt, 0);
const unsigned char *name = sqlite3_column_text(stmt, 1);
const unsigned char *sex = sqlite3_column_text(stmt, 2);
int age = sqlite3_column_int(stmt, 3);
// BLOB 型別的讀取需要四步
int length = sqlite3_column_bytes(stmt, 4); // 1. 取得長度
const void *photo = sqlite3_column_blob(stmt, 4); // 2. 取得指標
NSData *photoData = [NSData dataWithBytes:photo length:length]; // 3. 轉 NSData
UIImage *image = [UIImage imageWithData:photoData]; // 4. 轉 UIImage
Student *student = [[Student alloc] init];
student.ID = ID;
student.name = [NSString stringWithUTF8String:(const char *)name];
student.sex = [NSString stringWithUTF8String:(const char *)sex];
student.age = age;
student.photo = image;
[studentArr addObject:student];
}
}
sqlite3_finalize(stmt); // 釋放 statement
[Database closeDB];
return studentArr;
}
+ (Student *)getStudentWithID:(NSInteger)aID
{
sqlite3 *db = [Database openDB];
sqlite3_stmt *stmt = nil;
NSString *sqlStr = [NSString stringWithFormat:
@"select * from Student where id = %ld", (long)aID];
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
Student *student = nil;
if (result == SQLITE_OK && sqlite3_step(stmt) == SQLITE_ROW) {
int ID = sqlite3_column_int(stmt, 0);
const unsigned char *name = sqlite3_column_text(stmt, 1);
const unsigned char *sex = sqlite3_column_text(stmt, 2);
int age = sqlite3_column_int(stmt, 3);
int length = sqlite3_column_bytes(stmt, 4);
const void *photo = sqlite3_column_blob(stmt, 4);
NSData *photoData = [NSData dataWithBytes:photo length:length];
student = [[Student alloc] init];
student.ID = ID;
student.name = [NSString stringWithUTF8String:(const char *)name];
student.sex = [NSString stringWithUTF8String:(const char *)sex];
student.age = age;
student.photo = [UIImage imageWithData:photoData];
}
sqlite3_finalize(stmt);
[Database closeDB];
return student;
}
// 新增
+ (BOOL)insertStudent:(Student *)aStudent
{
sqlite3 *db = [Database openDB];
sqlite3_stmt *stmt = nil;
NSString *sqlStr = [NSString stringWithFormat:
@"insert into Student (name, sex, age) values ('%@', '%@', '%ld')",
aStudent.name, aStudent.sex, (long)aStudent.age];
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK && sqlite3_step(stmt) == SQLITE_DONE) {
sqlite3_finalize(stmt);
[Database closeDB];
return YES;
}
sqlite3_finalize(stmt);
[Database closeDB];
return NO;
}
// 修改姓名
+ (BOOL)updateStudentName:(NSString *)aName byID:(NSInteger)aID
{
sqlite3 *db = [Database openDB];
sqlite3_stmt *stmt = nil;
NSString *sqlStr = [NSString stringWithFormat:
@"update Student set name = '%@' where id = %ld", aName, (long)aID];
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK && sqlite3_step(stmt) == SQLITE_DONE) {
sqlite3_finalize(stmt);
[Database closeDB];
return YES;
}
sqlite3_finalize(stmt);
[Database closeDB];
return NO;
}
// 刪除
+ (BOOL)deleteStudentWithID:(NSInteger)aID
{
sqlite3 *db = [Database openDB];
sqlite3_stmt *stmt = nil;
NSString *sqlStr = [NSString stringWithFormat:
@"delete from Student where id = %ld", (long)aID];
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK && sqlite3_step(stmt) == SQLITE_DONE) {
sqlite3_finalize(stmt);
[Database closeDB];
return YES;
}
sqlite3_finalize(stmt);
[Database closeDB];
return NO;
}
@end
原始範例是教學用途,直接搬進正式專案前請注意以下幾點:
stringWithFormat 拼接 SQL 是危險寫法。若字串中含有單引號(例如名字是 O'Brien),語句就會出錯甚至被攻擊。正確做法是使用 sqlite3_bind_* 綁定參數:
sqlite3_prepare_v2(db, "insert into Student (name, age) values (?, ?)", -1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_int(stmt, 2, age);
sqlite3_step(stmt);
SQLITE_DONE,不是 SQLITE_ROW。原範例中「先 step 一次判斷有沒有資料、再 step 一次執行」的寫法邏輯有誤,會導致永遠回傳 NO。INTEGER、TEXT、BLOB、REAL、NUMERIC 即可,但建議明確標示以便閱讀。理解原生 C API 的 prepare → bind → step → finalize 四步流程很重要——所有上層封裝(FMDB、Core Data)背後跑的都是這一套。搞懂了,換任何工具都能快速上手。