三.数据持久化之sqlite数据库操作（增删查改）

1.前提条件引入如下库


Paste_Image.png

2.操作数据库前（一般是操作数据库里面对应的表），我们应该创建对应的表
在单列中创建表

//  NoteDAO.h
@interface NoteDAO : NSObject
//单例
+ (NoteDAO*)sharedInstance;
@end

//  NoteDAO.m
#import "NoteDAO.h"
#import "sqlite3.h"
#define DBFILE_NAME @"NotesList.sqlite3"

@interface NSString (filePath)
-(NSString *)documentsFilePath;
@end

@implementation NSString (filePath)
-(NSString *)documentsFilePath
{
    NSString * documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
    NSString * filePath = [documentsPath stringByAppendingPathComponent:self];
    return filePath;
}
@end
//声明NoteDAO扩展
@interface NoteDAO () {
    sqlite3 *db;
}
@property(nonatomic, strong) NSString *plistFilePath; // 沙箱目录
@property(nonatomic, strong) NSDateFormatter *dateFormatter; //时间格式化用

@end
@implementation NoteDAO

static NoteDAO *sharedSingleton = nil;
+ (NoteDAO *)sharedInstance {
    static dispatch_once_t once;
    dispatch_once(&once, ^{

        sharedSingleton = [[self alloc] init];

        //1.数据库文件沙盒路径
        sharedSingleton.plistFilePath = [DBFILE_NAME documentsFilePath];
        //2.创建数据库表
        [sharedSingleton createSqliteDatabaseIfNeeded];
         //初始化DateFormatter
        NSDateFormatter *aDataFormatter = [[NSDateFormatter alloc] init];
        [aDataFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
        sharedSingleton.dateFormatter = aDataFormatter;

    });
    return sharedSingleton;
}

//第一步先初始化，创建要操作的表
- (void)createSqliteDatabaseIfNeeded {

    const char *cpath = [self.plistFilePath UTF8String];
    //1.打开
    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];
        //2.建表
        if (sqlite3_exec(db, cSql, NULL, NULL, NULL) != SQLITE_OK) {
            NSLog(@"建表失败。");
        }
    }
    sqlite3_close(db);
}

3.操作数据表 增删查改 （一般分查询和增删改稍微有点点区别）
第一种查询

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

    //1.打开数据库
    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;//语句对象
        //2.函数预处理SQL语句
        if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
            //准备参数
            NSString *strDate = [self.dateFormatter stringFromDate:model.date];
            const char *cDate = [strDate UTF8String];

            //3.绑定预处理问号参数,从1开始
            sqlite3_bind_text(statement, 1, cDate, -1, NULL);

            //4.执行SQL，且遍历结果集  （因为只查询一个model，所以不用while）
            if (sqlite3_step(statement) == SQLITE_ROW) {
                //5.提取具体字段，从0开始（sqlite3_column_text读取字符串类型字段，sqlite3_column_double读取浮点类型）
                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];
                result = note;
            }
        }
        //6.1释放satatement
        sqlite3_finalize(statement);
    }
    //6.2释放db
    sqlite3_close(db);
    return result;
}

//查询所有数据方法
- (NSMutableArray *)findAll
{
    NSMutableArray *result = nil;
    const char *cpath = [self.plistFilePath UTF8String];
    //1.打开
    if (sqlite3_open(cpath, &db) != SQLITE_OK) {
        NSLog(@"数据库打开失败。");
    } else {

        NSString *sql = @"SELECT cdate,content FROM Note";
        const char *cSql = [sql UTF8String];
        sqlite3_stmt *statement;
        //2.预处理
        if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
            NSMutableArray *listData = [[NSMutableArray alloc] init];
            //<3.没有问号（参数）不用绑定>
            //4.执行SQL，且遍历结果集
            while (sqlite3_step(statement) == SQLITE_ROW) {
                //5.取字段
                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];
                [listData addObject:note];
            }
            result = listData;
        }
        //6.1
        sqlite3_finalize(statement);
    }
    //6.2
    sqlite3_close(db);
    return result;
}

第二种 增删改（除了sql语句，其他都类似）

//增加
- (int)create:(Note *)model {

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

    //1.打开
    if (sqlite3_open(cpath, &db) != SQLITE_OK) {
        NSLog(@"数据库打开失败。");
    } else {
        NSString *sql = @"INSERT OR REPLACE INTO note (cdate, content) VALUES (?,?)";
        const char *cSql = [sql UTF8String];

        //语句对象
        sqlite3_stmt *statement;
        //2.预处理SQL
        if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {

            NSString *strDate = [self.dateFormatter stringFromDate:model.date];
            const char *cDate = [strDate UTF8String];
            const char *cContent = [model.content UTF8String];

            //3.绑定参数
            sqlite3_bind_text(statement, 1, cDate, -1, NULL);
            sqlite3_bind_text(statement, 2, cContent, -1, NULL);

            //4.执行SQL,
            if (sqlite3_step(statement) != SQLITE_DONE) {
                NSLog(@"增加数据失败。");
            }
            //(5不需要提起字段，增删改）
        }
        //6.1
        sqlite3_finalize(statement);
    }
    //6.2
    sqlite3_close(db);
    return 0;
}
//删除
- (int)remove:(Note *)model {

    const char *cpath = [self.plistFilePath UTF8String];
    //1.打开
    if (sqlite3_open(cpath, &db) != SQLITE_OK) {
        NSLog(@"数据库打开失败。");
    } else {
        NSString *sql = @"DELETE  from note where cdate =?";
        const char *cSql = [sql UTF8String];
        sqlite3_stmt *statement;
        //2.预处理
        if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {

            NSString *strDate = [self.dateFormatter stringFromDate:model.date];
            const char *cDate = [strDate UTF8String];

            //3.绑定参数
            sqlite3_bind_text(statement, 1, cDate, -1, NULL);
            //4.执行SQL
            if (sqlite3_step(statement) != SQLITE_DONE) {
                NSLog(@"删除数据失败。");
            }
            //(5不需要提起字段，增删改）
        }
        //6.1
        sqlite3_finalize(statement);
    }
    //6.2
    sqlite3_close(db);
    return 0;
}
//修改Note方法
- (int)modify:(Note *)model {

    const char *cpath = [self.plistFilePath UTF8String];
    //1.打开
    if (sqlite3_open(cpath, &db) != SQLITE_OK) {
        NSLog(@"数据库打开失败。");
    } else {
        NSString *sql = @"UPDATE note set content=? where cdate =?";
        const char *cSql = [sql UTF8String];
        sqlite3_stmt *statement;
        //2.预处理SQL
        if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {

            NSString *strDate = [self.dateFormatter stringFromDate:model.date];
            const char *cDate = [strDate UTF8String];
            const char *cContent = [model.content UTF8String];

            //3.绑定参数
            sqlite3_bind_text(statement, 1, cContent, -1, NULL);
            sqlite3_bind_text(statement, 2, cDate, -1, NULL);
            //4.执行SQL
            if (sqlite3_step(statement) != SQLITE_DONE) {
                NSLog(@"修改数据失败。");
            }
            //(5不需要提起字段，增删改
        }
        //6.1
        sqlite3_finalize(statement);
    }
    //6.2
    sqlite3_close(db);
    return 0;
}

如果你发现本文对你有所帮助，如果你认为其他人也可能受益，请把它分享出去。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容