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;
}
如果你发现本文对你有所帮助，如果你认为其他人也可能受益，请把它分享出去。
