iOS 開發之數據庫(SQLite) – iPhone手機開發 iPhone軟體開發教學課程

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:@女];