SQLite 是 iOS 上最直接的關聯式資料庫選擇,不需要額外套件、也不必啟動服務,整個資料庫就是一個檔案。
sqlite3.h 並連結 libsqlite3 函式庫。把資料庫操作集中在一個單例裡,是實務上最常見的做法。
#import <Foundation/Foundation.h> @class Student; @interface DataBaseHandle : NSObject + (DataBaseHandle *)shareDB; // 單例 - (NSString *)documentsPath; // 取得 Documents 路徑 - (void)openDB; // 開啟資料庫 - (void)closeDB; // 關閉資料庫 - (void)createTable; // 建表 - (void)insertStudent:(Student *)stu; // 新增 - (void)updateMessage; // 修改 - (void)deleteMessage; // 刪除 - (void)selectAllStudent; // 查詢全部 - (void)selectWithSex:(NSString *)sex; // 條件查詢 @end
#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Student.h"
static DataBaseHandle *shareModle = nil;
static sqlite3 *db = nil;
@implementation DataBaseHandle
#pragma mark - 單例
+ (DataBaseHandle *)shareDB {
if (nil == shareModle) {
shareModle = [[DataBaseHandle alloc] init];
}
return shareModle;
}
#pragma mark - 路徑
- (NSString *)documentsPath {
return NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
}
#pragma mark - 開啟與關閉
- (void)openDB {
if (nil == db) {
NSString *dbPath = [[self documentsPath] stringByAppendingString:@"/Student.sqlite"];
// 路徑下若無資料庫檔,會自動建立一個
int result = sqlite3_open(dbPath.UTF8String, &db);
NSLog(result == SQLITE_OK ? @"資料庫開啟成功" : @"資料庫開啟失敗");
}
}
- (void)closeDB {
if (sqlite3_close(db) == SQLITE_OK) {
db = nil; // 關閉成功後記得清空指標
NSLog(@"資料庫關閉成功");
} else {
NSLog(@"資料庫關閉失敗");
}
}
#pragma mark - 建表
- (void)createTable {
NSString *createString = @"CREATE TABLE IF NOT EXISTS student (sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, sname TEXT, ssex TEXT, sage TEXT);";
int result = sqlite3_exec(db, createString.UTF8String, NULL, NULL, NULL);
NSLog(result == SQLITE_OK ? @"建表成功" : @"建表失敗");
}
#pragma mark - 新增
- (void)insertStudent:(Student *)stu {
NSString *insertString = [NSString stringWithFormat:
@"INSERT INTO student (sname, ssex, sage) VALUES ('%@', '%@', '%@');",
stu.name, stu.sex, stu.age];
int result = sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);
NSLog(result == SQLITE_OK ? @"新增成功" : @"新增失敗");
}
#pragma mark - 修改
- (void)updateMessage {
NSString *updateString = @"UPDATE student SET ssex = '女', sname = '小夢' WHERE sid = 10;";
int result = sqlite3_exec(db, updateString.UTF8String, NULL, NULL, NULL);
NSLog(result == SQLITE_OK ? @"修改成功" : @"修改失敗");
}
#pragma mark - 刪除
- (void)deleteMessage {
NSString *deleteString = @"DELETE FROM student WHERE sid = 1;";
int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);
NSLog(result == SQLITE_OK ? @"刪除成功" : @"刪除失敗");
}
#pragma mark - 查詢全部
- (void)selectAllStudent {
NSString *selectAll = @"SELECT * FROM student;";
sqlite3_stmt *stmt = nil;
if (sqlite3_prepare(db, selectAll.UTF8String, -1, &stmt, NULL) == SQLITE_OK) {
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSInteger sid = sqlite3_column_int(stmt, 0);
NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
NSLog(@"sid:%ld sname:%@ ssex:%@ sage:%@", (long)sid, sname, ssex, sage);
}
} else {
NSLog(@"查詢失敗");
}
sqlite3_finalize(stmt); // 不論成功或失敗都要收尾
}
#pragma mark - 條件查詢(預編譯 + 綁定)
- (void)selectWithSex:(NSString *)sex {
NSString *selectString = @"SELECT * FROM student WHERE ssex = ?;";
sqlite3_stmt *stmt = nil;
if (sqlite3_prepare(db, selectString.UTF8String, -1, &stmt, NULL) == SQLITE_OK) {
// 1 代表綁定第一個問號
sqlite3_bind_text(stmt, 1, sex.UTF8String, -1, NULL);
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSInteger sid = sqlite3_column_int(stmt, 0);
NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
NSLog(@"sid: %ld sname: %@ ssex: %@ sage: %@", (long)sid, sname, ssex, sage);
}
sqlite3_finalize(stmt);
} else {
sqlite3_finalize(stmt);
NSLog(@"查詢失敗");
}
}
@end
// Student.h #import <Foundation/Foundation.h> @interface Student : NSObject @property (nonatomic, strong) NSString *name; @property (nonatomic, strong) NSString *age; @property (nonatomic, strong) NSString *sex; @end
// ViewController.m
- (void)viewDidLoad {
[super viewDidLoad];
NSLog(@"%@", [[DataBaseHandle shareDB] documentsPath]);
[[DataBaseHandle shareDB] openDB];
[[DataBaseHandle shareDB] createTable];
// 新增
// Student *stu = [[Student alloc] init];
// stu.name = @"小露";
// stu.age = @"29";
// stu.sex = @"女";
// [[DataBaseHandle shareDB] insertStudent:stu];
// 修改、刪除
// [[DataBaseHandle shareDB] updateMessage];
// [[DataBaseHandle shareDB] deleteMessage];
// 查詢
// [[DataBaseHandle shareDB] selectAllStudent];
[[DataBaseHandle shareDB] selectWithSex:@"女"];
}
範例中的 insertStudent: 用 stringWithFormat 拼 SQL,這在正式專案中是 SQL Injection 的入口:只要使用者輸入的內容裡有一個單引號,語法就會被打斷,甚至被寫入非預期的指令。正確作法是全程使用 sqlite3_prepare + sqlite3_bind_xxx,讓 SQLite 自己處理跳脫。
sqlite3_stmt 用完就必須 sqlite3_finalize,而且要在所有分支(包含錯誤分支)都收尾,否則長時間執行會耗盡資源。
批次寫入若逐筆 commit,效能會差上幾十倍。用 BEGIN TRANSACTION … COMMIT 把一批操作包起來,失敗時再 ROLLBACK。