iOS 開發之資料庫(SQLite)
SQLite嵌入式資料庫優點:
1.嵌入式資料庫
2.支援事件,不需要配置,不需要安裝,不需要管理員
3.支援大部分SQL92標準
4.完整的資料庫儲存在磁碟上面一個檔案,同一個資料庫檔案可以在不同機器上面使用,最大支援資料庫到2T
5.整個系統少於3萬行,少於250KB的記憶體佔用
開始使用SQLite:
1.引入標頭檔
2.開啟資料庫
3.執行SQL命令
4.關閉資料庫
以下代碼展示整個資料庫的 開啟、關閉、增加、刪除、尋找、修改。
建立一個單例:
#import @class Student;@interface DataBaseHandle : NSObject//建立一個單例+(DataBaseHandle *)shareDB; //擷取Documents路徑- (NSString *)documentsPath; //開啟資料庫- (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 #import Student.h//全域單利對象static DataBaseHandle *shareModle = nil;@implementation DataBaseHandle#pragma mark 實現單利+ (DataBaseHandle *)shareDB{ if (nil == shareModle) { shareModle = [[DataBaseHandle alloc] init]; } return shareModle;} #pragma mark 擷取Documents路徑- (NSString *)documentsPath{ NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0]; return documentPath;} //在操作資料之前引入架構(sql...3.0)//聲明一個資料庫的對象static sqlite3 *db = nil;#pragma mark 開啟資料庫- (void)openDB{ //判斷資料庫是不是為空白 if (nil == db) { //與documents路徑拼接資料庫存放路徑(獲得存放路徑) NSString *dbPath = [[self documentsPath] stringByAppendingString:@/Student.sqlite]; //根據路徑開啟資料庫,如該路徑下沒有資料庫,就自動建立一個資料庫 //開啟資料庫(c語言中的文法) int result = sqlite3_open(dbPath.UTF8String, &db); //判斷是否開啟資料庫成功 if (result == SQLITE_OK) { NSLog(@資料庫開啟成功); } else { NSLog(@資料庫開啟失敗); } }} #pragma mark 關閉資料庫- (void)closeDB{ int result = sqlite3_close(db); if (result == SQLITE_OK) { //如果關閉成功,置為nill db = nil; NSLog(@資料庫關閉成功); } else { NSLog(@資料庫關閉失敗); }} #pragma mark 建立表- (void)createTable{ //要建立表,就要使用SQL語句. //SQL語句在這是以字串的形式存在的 NSString *createString = @CREATE TABLE IF NOT EXISTS student (sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , sname TEXT, ssex TEXT, sage TEXT); //執行SQL語句 int result = sqlite3_exec(db, createString.UTF8String, NULL, NULL, NULL); if (result == SQLITE_OK) { NSLog(@建立表成功); } else { NSLog(@建立失敗); } } #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); if (result == SQLITE_OK) { NSLog(@插入成功); } else { NSLog(@插入失敗); }} #pragma mark 修改資料- (void)updateMessage{ //準備修改語句 NSString *undataString = @UPDATE student SET ssex = '女' ,sname = '小夢' WHERE sid = 10; int result = sqlite3_exec(db, undataString.UTF8String, NULL, NULL, NULL); if (result == SQLITE_OK) { NSLog(@修改成功); } else { NSLog(@修改失敗); }} #pragma mark 刪除資料- (void)deleteMessage{ //準備刪除語句 NSString *deleteString = @DELETE FROM student WHERE sid = 1; int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL); if (result == SQLITE_OK) { NSLog(@delete OK); } else { NSLog(@delete _NO); } } #pragma mark 尋找全部- (void)selectAllStudent{ //準備尋找語句 NSString *selectAll = @SELECT *FROM student; //建立伴隨指標 sqlite3_stmt *stmt = nil; //預備執行 int result = sqlite3_prepare(db, selectAll.UTF8String, -1, &stmt, NULL); if (result == SQLITE_OK) { //在沒有尋找完成之前一直迴圈執行 while (sqlite3_step(stmt) == SQLITE_ROW) { //取出sid(第0列) NSInteger sid = sqlite3_column_int(stmt, 0); //取出sname(第1列) NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)]; //取出ssex(第2列) NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)]; //取出sage(第3列) NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)]; NSLog(@sid:%d sname:%@ ssex:%@ sage:%@,sid,sname,ssex,sage); } sqlite3_finalize(stmt); } else { //如果尋找失敗,結束伴隨指標 sqlite3_finalize(stmt); NSLog(@尋找失敗); } }#pragma mark 條件尋找- (void)selectWithSex:(NSString *)sex{ //準備尋找語句 NSString *selecstString = @SELECT * FROM student WHERE ssex = ?; //建立伴隨指標 sqlite3_stmt *stmt = nil; //預執行 int result = sqlite3_prepare(db, selecstString.UTF8String, -1, &stmt, NULL); if (result == 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: %d sname: %@ ssex: %@ sage: %@,sid,sname,ssex,sage); } } else { //關閉指標 sqlite3_finalize(stmt); NSLog(@查詢失敗); }}@end
建立一個Student類
#import @interface Student : NSObject@property(nonatomic,strong)NSString *name;@property(nonatomic,strong)NSString *age;@property(nonatomic,strong)NSString *sex;@end
在 ViewController.m中 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:@女];