[iOS]数据持久化-数据库的简单使用

数据库是数据持久化的一种方法之一，今天简单的来了解一下它的实用方法以及注意事项

我们所使用到的数据库因为经常要对数据库里的数据进行操作，如果频繁的打开，关闭数据库，可能会引发数据库卡死，所以我们只要打开数据库，不用关闭，程序结束，会自动关闭数据库

第一步，添加 libsqlite3.dylib 类库

第二部，获得数据库指针

以下为具体代码

DataBase.h

#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface DataBase : NSObject

+ (sqlite3 *)openDataBase;
@end

DataBase.m

/* 第一步，添加 libsqlite3.dylib 类库 */
/* 第二部，获得数据库指针 */
#import "DataBase.h"
// 1.头文件引入
#import <sqlite3.h>
// 2.静态内存区域,定义数据库指针
static sqlite3 *dbPoint = nil;

@implementation DataBase

//打开数据库
+ (sqlite3 *)openDataBase
{
    // 判断,如果dbPoint存在了，直接返回它
    if (dbPoint) {
        return dbPoint;
    }
    
    // 获取沙盒路径
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
    path = [NSString stringWithFormat:@"%@/db",path];
   // NSLog(@"path == %@",path);
    
    // 判断文件是否存在 文件存在拷贝，不存在不拷贝
    if (![[NSFileManager defaultManager] fileExistsAtPath:path]) {
        // 获得将要拷贝的文件路径，bundle路径
        NSString *sourcePath = [[NSBundle mainBundle] pathForResource:@"DataBase" ofType:@"sqlite"];
     //   NSLog(@"bundle 路径 %@",sourcePath);
        
        NSError *error = nil;
        // 拷贝文件
        [[NSFileManager defaultManager] copyItemAtPath:sourcePath toPath:path error:&error];
        
        if (error) {
            NSLog(@"error == %@",error);
        }
    }
    
    // 创建数据库的指针对象
    // 打开数据库
    sqlite3_open([path UTF8String], &dbPoint);
    
    return dbPoint;
}

@end

这两个文件封装好，以后简单的修改以下，就可以拿过来用了
下面来看一下增、删、改、查语句的代码

NovelDataBase.h

#import <Foundation/Foundation.h>
@class NovelModel;
@interface NovelDataBase : NSObject

+ (NSArray *)selectAllNovel;
+ (NovelModel *)selectWithId:(NSInteger)num;
+ (void)insertWithModel:(NovelModel *)model;
+ (void)updateNovelWithId:(NSInteger)num name:(NSString *)newName;
+ (void)deleteNovelWithId:(NSInteger)num;
@end

NovelDataBase.m

#import "NovelDataBase.h"
#import "DataBase.h"
#import "NovelModel.h"

@implementation NovelDataBase

#pragma mark - 查询
+ (NSArray *)selectAllNovel
{
    NSMutableArray *sqlResult = [NSMutableArray array];
    // 获得数据库指针
    sqlite3 *db = [DataBase openDataBase];
    
    // 创建数据库替身
    sqlite3_stmt *stmt = nil;
    
    // 数据库查询语句
    NSString *sqlStr = [NSString stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score,nv_type from Novel"];
    
    // 通过SQL语句进行查询，并且将查询结果赋值给替身(第三个参数，sql语句长度限制)
    // 检验sql语句是否正确，（正确给替身赋值，不正确不赋值）
    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        // 取出替身内数据(链表)
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 第二个参数代表的时，取的sql语句中，对应列名的顺序
            int nid = sqlite3_column_int(stmt, 0);
            const unsigned char *nauthor = sqlite3_column_text(stmt, 1);
            const unsigned char *nbookName = sqlite3_column_text(stmt, 2);
            float nscore = sqlite3_column_double(stmt, 3);
            const unsigned char *ntype = sqlite3_column_text(stmt, 4);
            
            // NovelModel 赋值
            NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];
            NSString *bookName = [NSString stringWithUTF8String:(const char *)nbookName];
            NSString *type = [NSString stringWithUTF8String:(const char *)ntype];
            NovelModel *novel = [NovelModel novelWithNovelId:nid author:author bookName:bookName score:nscore type:type];
            [sqlResult addObject:novel];
        }
    }
    // 删除替身
    sqlite3_finalize(stmt);
    
    return sqlResult;
}
+ (NovelModel *)selectWithId:(NSInteger)num
{
    sqlite3 *db = [DataBase openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlStr = [NSString stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score,nv_type from Novel where nv_id=%d",num];
    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            int nid = sqlite3_column_int(stmt, 0);
            const unsigned char *nauthor = sqlite3_column_text(stmt, 1);
            const unsigned char *nbookName = sqlite3_column_text(stmt, 2);
            float nscore = sqlite3_column_double(stmt, 3);
            const unsigned char *ntype = sqlite3_column_text(stmt, 4);
            
            NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];
            NSString *bookName = [NSString stringWithUTF8String:(const char *)nbookName];
            NSString *type = [NSString stringWithUTF8String:(const char *)ntype];
            NovelModel *novel = [NovelModel novelWithNovelId:nid author:author bookName:bookName score:nscore type:type];
            return novel;
        }
    }
    sqlite3_finalize(stmt);
    return nil;
}
#pragma mark - 插入
+ (void)insertWithModel:(NovelModel *)model
{
    sqlite3 *db = [DataBase openDataBase];
    // 简化版本SQL语句
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Novel (nv_id,nv_author,nv_bookname,nv_type,nv_score) values(%d,'%@','%@','%@',%f)",model.novelId,model.author,model.bookName,model.type,model.score];
    
    // 插入语句
    int result = sqlite3_exec(db, [sqlStr UTF8String], nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    }
}
#pragma mark - 修改
+ (void)updateNovelWithId:(NSInteger)num name:(NSString *)newName
{
    sqlite3 *db = [DataBase openDataBase];
    // 简化版本SQL语句
    NSString *sqlStr = [NSString stringWithFormat:@"update Novel set nv_bookname='%@' where nv_id=%d",newName,num];
    
    // 插入语句
    int result = sqlite3_exec(db, [sqlStr UTF8String], nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    }
}

#pragma mark - 删除
+ (void)deleteNovelWithId:(NSInteger)num
{
    sqlite3 *db = [DataBase openDataBase];
    // 简化版本SQL语句
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Novel where nv_id=%d",num];
    
    // 插入语句
    int result = sqlite3_exec(db, [sqlStr UTF8String], nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
}
@end