iOS 資料庫詳解 sqlite實現增刪改查操作

iOS 資料庫詳解 sqlite實現增刪改查操作

////  CLViewController.m//  LessonDatabase////  Created by lanouhn on 14-9-19.//  Copyright (c) 2014年 vaercly@163.com 陳聰雷. All rights reserved.//#import "CLViewController.h"#import "DatabaseHelper.h"#import "Student.h"@interface CLViewController ()@end@implementation CLViewController- (void)viewDidLoad{    [super viewDidLoad];// Do any additional setup after loading the view, typically from a nib.    //資料庫(Database): 存放資料的倉庫, 存放的是一張的表, 特別像Excel, Numbers, 都以表格的形式存放資料, 可以建立多張表    //常見的資料庫: sqlite, MySQL, SQLServer, Oracle, Access    //為什麼要用資料庫 1 檔案讀寫和歸檔讀取資料需要一次把資料全部讀出來, 佔用記憶體開銷大 2 資料庫資料效率高, 體現在增刪改查    //SQL Structured Query Language 用於對資料庫的動作陳述式 (增刪改查)    //SQL 陳述式不區分大小寫, 字串需要加""或''    //主鍵: 是一條資料的唯一標示符, 一張表只能有一個主鍵, 主鍵不能夠重複, 一般把主鍵名設為"id", 不需要賦值, 會自增    //*代表所有的欄位    //where是條件    //建立表: creat table 表名 (欄位名 欄位資料類型 是否為主鍵, 欄位名 欄位資料類型, 欄位名 欄位資料類型...)    //查: select 欄位名 (或者*) from 表名 where 欄位名 = 值    //增: insert into 表名 (欄位1, 欄位2...) values (值1, 值2...)    //改: update 表名 set 欄位 = 值 where 欄位 = 值    //刪: delete from 表名 where 欄位 = 值    }- (void)didReceiveMemoryWarning{    [super didReceiveMemoryWarning];    // Dispose of any resources that can be recreated.}- (IBAction)selectAll:(id)sender {    NSMutableArray *array = [DatabaseHelper getAllStudents];    for (Student *stu in array) {        NSLog(@"%@", stu);    }}- (IBAction)selectOne:(id)sender {    Student *stu = [DatabaseHelper getStudentWithID:2];    NSLog(@"%@", stu);}- (IBAction)insetOne:(id)sender {    Student *stu = [[Student alloc] init];    stu.name = @"vaercly";    stu.sex = @"man";    stu.age = 22;    BOOL result = [DatabaseHelper insertStudent:stu];    NSLog(@"%d", result);}- (IBAction)updateName:(id)sender {    [DatabaseHelper updateStudentName:@"陳聰雷" byID:5];}- (IBAction)deleteOne:(id)sender {    [DatabaseHelper deleteStudentWithID:5];}@end////  Datebase.m//  LessonDatabase////  Created by lanouhn on 14-9-19.//  Copyright (c) 2014年 vaercly@163.com 陳聰雷. All rights reserved.//#define FILE_NAME       @"Database.sqlite"#import "Database.h"static sqlite3 *db = nil;@implementation Database//開啟資料庫+ (sqlite3 *)openDB{    if (!db) {        //1 擷取document檔案夾的路徑        //參數1: 檔案夾的名字 參數2: 尋找域 參數3: 是否使用絕對路徑        NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];        //擷取資料庫檔案的路徑        NSString *dbPath = [docPath stringByAppendingPathComponent:FILE_NAME];        //iOS 中管理檔案的類, 負責複製檔案, 刪除檔案, 移動檔案        NSFileManager *fm = [NSFileManager defaultManager];        //判斷document中是否有sqlite檔案        if (![fm fileExistsAtPath:dbPath]) {            //擷取在*.app中sqlite檔案的路徑            NSString *boundlePath = [[NSBundle mainBundle] pathForResource:@"Database" ofType:@"sqlite"];            NSError *error = nil;            //將*.app中sqlite檔案複製一份到dbPath            BOOL result = [fm copyItemAtPath:boundlePath toPath:dbPath error:&error];            //若複製檔案失敗, 列印錯誤資訊            if (!result) {                NSLog(@"%@", error);            }        }        //開啟資料庫 參數1: 檔案路徑(UTF8String可以將OC的NSString轉為C中的char) 參數2: 接受資料庫的指標        sqlite3_open([dbPath UTF8String], &db);    }    return db;}//關閉資料庫+ (void)closeDB{    sqlite3_close(db);    db = nil;}@end////  DatabaseHelper.m//  LessonDatabase////  Created by lanouhn on 14-9-19.//  Copyright (c) 2014年 vaercly@163.com 陳聰雷. All rights reserved.//#import "DatabaseHelper.h"#import "Student.h"#import "Database.h"@implementation DatabaseHelper//查詢所有學生+ (NSMutableArray *)getAllStudents{    //開啟資料庫    sqlite3 *db = [Database openDB];    //資料庫操作指標 stmt:statement    sqlite3_stmt *stmt = nil;    //驗證SQL的正確性 參數1: 資料庫指標, 參數2: SQL語句, 參數3: SQL語句的長度 -1代表無限長(會自動匹配長度), 參數4: 返回資料庫操作指標, 參數5: 為未來做準備的, 預留參數, 一般寫成NULL    int result = sqlite3_prepare_v2(db, "select * from Student", -1, &stmt, NULL);    NSMutableArray *studentArr = [NSMutableArray array];    //判斷SQL執行的結果    if (result == SQLITE_OK) {        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類型的擷取            //1 擷取長度            int length = sqlite3_column_bytes(stmt, 4);            //2 擷取資料            const void *photo = sqlite3_column_blob(stmt, 4);            //3 轉成NSData            NSData *photoData = [NSData dataWithBytes:photo length:length];            //4 轉成UIImage            UIImage *image = [UIImage imageWithData:photoData];            //封裝Student模型            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];        }    }    //釋放stmt指標    sqlite3_finalize(stmt);    //關閉資料庫    [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 = %d", aID];    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);    Student *student = nil;    if (result == SQLITE_OK) {        if (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 unsigned char *photo = sqlite3_column_blob(stmt, 4);            NSData *photoData = [NSData dataWithBytes:photo length:length];            UIImage *image = [UIImage imageWithData:photoData];            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;        }    }    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 ('%@', '%@', '%d')", aStudent.name, aStudent.sex, aStudent.age];    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);    if (result == SQLITE_OK) {        //判斷語句執行完成沒有        if (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 = %d", aName, aID];    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);    if (result == SQLITE_OK) {        if (sqlite3_step(stmt) == SQLITE_ROW) {//覺的應加一個判斷, 若有這一行則修改            if (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 = %d", aID];    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);    if (result == SQLITE_OK) {        if (sqlite3_step(stmt) == SQLITE_ROW) {//覺的應加一個判斷, 若有這一行則刪除            if (sqlite3_step(stmt) == SQLITE_DONE) {                sqlite3_finalize(stmt);                [Database closeDB];                return YES;            }        }    }    sqlite3_finalize(stmt);    [Database closeDB];    return NO;}@end////  Student.h//  LessonDatabase////  Created by lanouhn on 14-9-19.//  Copyright (c) 2014年 vaercly@163.com 陳聰雷. All rights reserved.//#import @interface Student : NSObject@property (nonatomic, assign) NSInteger ID;@property (nonatomic, retain) NSString *name;@property (nonatomic, retain) NSString *sex;@property (nonatomic, assign) NSInteger age;@property (nonatomic, retain) UIImage *photo;@end