數據持久化之sqlite

首先附上SQL語句學習地址,供大家學習參考!

第一步獲取沙箱目錄

//定義數據庫名字
#define DBFILE_NAME @"NotesList.sqlite3"

NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, TRUE) lastObject];
     self.plistFilePath = [documentDirectory stringByAppendingPathComponent:DBFILE_NAME];

第二步:初始化數據庫

const char *cpath = [self.plistFilePath UTF8String];

    if (sqlite3_open(cpath, &db) != SQLITE_OK) {
        NSLog(@"数据库打开失败。");
    } else {
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS Note (cdate TEXT PRIMARY KEY, content TEXT);"];
        const char *cSql = [sql UTF8String];

        if (sqlite3_exec(db, cSql, NULL, NULL, NULL) != SQLITE_OK) {
            NSLog(@"建表失败。");
        }
    }
    sqlite3_close(db);

接下來就可以查詢,插入,刪除等動作了

以查詢為例具體步驟如下:

1.使用sqlite3_open函數打開數據庫
2.使用sqlite3_prepare_v2函數預處理SQL語句//將SQL變成二進制,提高執行速度
3.使用sqlite3_bind_text函數綁定參數
4.使用sqlite3_step函數執行sql語句,遍歷結果集
5.使用sqlite3_column_text等函數提取字段數據
6.使用sqlite3_finalize和sqlite3_close函數釋放資源
代碼如下:

//按照主键查询数据方法
- (Note *)findById:(Note *)model {
    const char *cpath = [self.plistFilePath UTF8String];

    if (sqlite3_open(cpath, &db) != SQLITE_OK) {
        NSLog(@"数据库打开失败。");
    } else {
        NSString *sql = @"SELECT cdate,content FROM Note where cdate =?";
        const char *cSql = [sql UTF8String];

         //语句对象
        sqlite3_stmt *statement;
        //预处理过程
        if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
            //准备参数
            NSString *strDate = [self.dateFormatter stringFromDate:model.date];
            const char *cDate = [strDate UTF8String];

            //绑定参数开始
            sqlite3_bind_text(statement, 1, cDate, -1, NULL);

            //执行查询
            if (sqlite3_step(statement) == SQLITE_ROW) {

                char *bufDate = (char *) sqlite3_column_text(statement, 0);
                NSString *strDate = [[NSString alloc] initWithUTF8String:bufDate];
                NSDate *date = [self.dateFormatter dateFromString:strDate];

                char *bufContent = (char *) sqlite3_column_text(statement, 1);
                NSString *strContent = [[NSString alloc] initWithUTF8String:bufContent];

                Note *note = [[Note alloc] initWithDate:date content:strContent];

                sqlite3_finalize(statement);
                sqlite3_close(db);

                return note;
            }
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);
    return nil;
}

• 插入sql
  NSString *sql = @"INSERT OR REPLACE INTO note (cdate, content) VALUES (?,?)";
• 修改sql
  NSString *sql = @"UPDATE note set content=? where cdate =?";
• 查詢所有sql
  NSString *sql = @"SELECT cdate,content FROM Note";