数据库是数据持久化的一种方法之一,今天简单的来了解一下它的实用方法以及注意事项
我们所使用到的数据库因为经常要对数据库里的数据进行操作,如果频繁的打开,关闭数据库,可能会引发数据库卡死,所以我们只要打开数据库,不用关闭,程序结束,会自动关闭数据库
第一步,添加 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