理论分析:
首先封装一个获取文件路径方法
//获取数据库路径
- (NSString *) applicationDocumentsDirecrotyFile {
NSArray * documentDirectory = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString * myDocPath = [documentDirectory objectAtIndex:0];
NSString * writableFile = [myDocPath stringByAppendingPathComponent:@"note.db"];
return writableFile;
}
1、使用sqlite3_open函数打开数据库;
2、使用sqlite3_exec函数执行Create Table语句,创建数据库表;
3、使用sqlite3_close函数释放资源;
//创建数据库
- (void) createDatabase {
NSString * writableDBPath = [self applicationDocumentsDirecrotyFile];
const char* cpath = [writableDBPath UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
char *err;
NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS note(_id integer primary key autoincrement, title text, content text, createDate text, updateDate text)"];
const char* cSql = [sql UTF8String];
if (sqlite3_exec(db, cSql, NULL, NULL, &err) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"建表失败");
}
sqlite3_close(db);
}
}
查询数据,以下为查询步骤:
1、使用sqlite3_open函数打开数据库;
2、使用sqlite3_prepare_v2函数预处理SQL语句;
3、使用sqlite3_bind_text函数绑定参数;
4、使用sqlite3_step函数执行SQL语句,遍历结果集;
5、使用sqlite3_column_text等函数提取字段数据;
6、使用sqlite3_finalize和sqlite3_close函数释放资源;
补充:
绑定参数类似函数:
sqlite3_bind_int等
提取字段数据类似函数:
sqlite3_column_int等
具体的可以查看API
//按id查找数据
- (Note *) findById:(int) _id {
NSString *path = [self applicationDocumentsDirecrotyFile];
const char* cpath = [path UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
NSString *sql = @"select _id, title, content, createDate, updateDate from note where _id = ?";
const char* cSql = [sql UTF8String];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
sqlite3_bind_int(statement, 1, _id);
//执行
if(sqlite3_step(statement) == SQLITE_ROW) {
//id
int cId = sqlite3_column_int(statement, 0);
//标题
char* cTitle = (char*)sqlite3_column_text(statement, 1);
NSString *title = [[NSString alloc] initWithUTF8String:cTitle];
//内容
char* cContent = (char*)sqlite3_column_text(statement, 2);
NSString *content = [[NSString alloc] initWithUTF8String:cContent];
//创建时间
char* cCreateDate = (char*)sqlite3_column_text(statement, 3);
NSString *createDate = [[NSString alloc] initWithUTF8String:cCreateDate];
//更新时间
char* cUpdateDate = (char*)sqlite3_column_text(statement, 4);
NSString *updateDate = [[NSString alloc] initWithUTF8String:cUpdateDate];
Note *note = [[Note alloc] initWithID:cId title:title content:content createDate:createDate updateDate:updateDate];
sqlite3_finalize(statement);
sqlite3_close(db);
return note;
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return nil;
}
修改数据:(添加、更新和删除数据)一下为修改数据步骤:
1、使用sqlite3_open函数打开数据库;
2、使用sqlite3_prepare_v2函数预处理SQL语句;
3、使用sqlite3_bind_text函数绑定参数;
4、使用sqlite3_step函数执行SQL语句;
5、使用sqlite3_finalize和sqlite3_close函数释放资源;
//添加数据
- (void) create:(Note *)note {
NSString *path = [self applicationDocumentsDirecrotyFile];
const char* cpath = [path UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
NSString *sql = @"insert into note(title, content, createDate, updateDate)values(?,?,?,?)";
const char* cSql = [sql UTF8String];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
const char* cTitle = [note.title UTF8String];
const char* cContent = [note.content UTF8String];
const char* cCreateDate = [note.createDate UTF8String];
const char* cUpdateDate = [note.updateDate UTF8String];
//绑定参数
sqlite3_bind_text(statement, 1, cTitle, -1, NULL);
sqlite3_bind_text(statement, 2, cContent, -1, NULL);
sqlite3_bind_text(statement, 3, cCreateDate, -1, NULL);
sqlite3_bind_text(statement, 4, cUpdateDate, -1, NULL);
//执行插入
if (sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @"插入数据失败");
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
}
Note实体类
Node.h文件
#import <Foundation/Foundation.h>
@interface Note : NSObject
//id
@property (nonatomic, assign) int _id;
//标题
@property (nonatomic, strong) NSString *title;
//内容
@property (nonatomic, strong) NSString *content;
//创建时间
@property (nonatomic, strong) NSString *createDate;
//更新时间
@property (nonatomic, strong) NSString *updateDate;
- (id) initWithID:(int) _id title:(NSString *) title content:(NSString *)content createDate:(NSString *) createDate updateDate:(NSString *) updateDate;
- (id) initWithTitle:(NSString *) title content:(NSString *)content createDate:(NSString *) createDate updateDate:(NSString *) updateDate;
- (id) initWithID:(int) _id title:(NSString *) title content:(NSString *)content updateDate:(NSString *) updateDate;
@end
note.m文件
#import "Note.h"
@implementation Note
- (id) initWithID:(int) _id title:(NSString *) title content:(NSString *)content createDate:(NSString *) createDate updateDate:(NSString *) updateDate {
if (self = [super init]) {
self._id = _id;
self.title = title;
self.content = content;
self.createDate = createDate;
self.updateDate = updateDate;
}
return self;
}
- (id) initWithTitle:(NSString *) title content:(NSString *)content createDate:(NSString *) createDate updateDate:(NSString *) updateDate {
if (self = [super init]) {
self.title = title;
self.content = content;
self.createDate = createDate;
self.updateDate = updateDate;
}
return self;
}
- (id) initWithID:(int) _id title:(NSString *) title content:(NSString *)content updateDate:(NSString *) updateDate {
if (self = [super init]) {
self._id = _id;
self.title = title;
self.content = content;
self.updateDate = updateDate;
}
return self;
}
@end
工具类实现
DatabaseAdapter.h文件
#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "Note.h"
#define DB_FILE @"note.db"
@interface DatabaseAdapter : NSObject {
sqlite3 *db;
}
//获取写入数据库路径
- (NSString *) applicationDocumentsDirecrotyFile;
//创建数据库
- (void) createDatabase;
//创建数据
- (void) create:(Note *) note;
//删除数据
- (void) remove:(int) id;
//更新数据
- (void) update:(Note *) note;
//按id查找数据
- (Note *) findById:(int) id;
//查找所有数据
- (NSMutableArray *) findAll;
//分页查找
//limit:查找条数
//ship:跳过条数
- (NSMutableArray *) findLimit:(int) limit withSkip:(int) skip;
+ (DatabaseAdapter *)shareManager;
- (id) init;
@end
DatabaseAdapter.m文件
#import "DatabaseAdapter.h"
@implementation DatabaseAdapter
- (id) init {
if (self = [super init]) {
[self createDatabase];
}
return self;
}
static DatabaseAdapter *sharedManager = nil;
+ (DatabaseAdapter *)shareManager {
static dispatch_once_t once;
dispatch_once(&once, ^{
sharedManager = [[self alloc]init];
[sharedManager createDatabase];
});
return sharedManager;
}
//创建数据库
- (void) createDatabase {
NSString * writableDBPath = [self applicationDocumentsDirecrotyFile];
const char* cpath = [writableDBPath UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
char *err;
NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS note(_id integer primary key autoincrement, title text, content text, createDate text, updateDate text)"];
const char* cSql = [sql UTF8String];
if (sqlite3_exec(db, cSql, NULL, NULL, &err) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"建表失败");
}
sqlite3_close(db);
}
}
//添加数据
- (void) create:(Note *)note {
NSString *path = [self applicationDocumentsDirecrotyFile];
const char* cpath = [path UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
NSString *sql = @"insert into note(title, content, createDate, updateDate)values(?,?,?,?)";
const char* cSql = [sql UTF8String];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
const char* cTitle = [note.title UTF8String];
const char* cContent = [note.content UTF8String];
const char* cCreateDate = [note.createDate UTF8String];
const char* cUpdateDate = [note.updateDate UTF8String];
//绑定参数
sqlite3_bind_text(statement, 1, cTitle, -1, NULL);
sqlite3_bind_text(statement, 2, cContent, -1, NULL);
sqlite3_bind_text(statement, 3, cCreateDate, -1, NULL);
sqlite3_bind_text(statement, 4, cUpdateDate, -1, NULL);
//执行插入
if (sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @"插入数据失败");
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
}
//删除数据
- (void) remove:(int) _id {
NSString *path = [self applicationDocumentsDirecrotyFile];
const char* cpath = [path UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
NSString *sql = @"delete from note where _id = ?";
const char* cSql = [sql UTF8String];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
sqlite3_bind_int(statement, 1, _id);
if (sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @"插入数据失败");
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
}
//更新数据
- (void) update:(Note *) note {
NSString *path = [self applicationDocumentsDirecrotyFile];
const char* cpath = [path UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
NSString *sql = @"update note set title = ?, content = ?, updateDate = ? where _id = ?";
const char* cSql = [sql UTF8String];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
const char* cTitle = [note.title UTF8String];
const char* cContent = [note.content UTF8String];
const char* cUpdateDate = [note.updateDate UTF8String];
sqlite3_bind_text(statement, 1, cTitle, -1, NULL);
sqlite3_bind_text(statement, 2, cContent, -1, NULL);
sqlite3_bind_text(statement, 3, cUpdateDate, -1, NULL);
sqlite3_bind_int(statement, 4, note._id);
if (sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @"插入数据失败");
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
}
//按id查找数据
- (Note *) findById:(int) _id {
NSString *path = [self applicationDocumentsDirecrotyFile];
const char* cpath = [path UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
NSString *sql = @"select _id, title, content, createDate, updateDate from note where _id = ?";
const char* cSql = [sql UTF8String];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
sqlite3_bind_int(statement, 1, _id);
//执行
if(sqlite3_step(statement) == SQLITE_ROW) {
//id
int cId = sqlite3_column_int(statement, 0);
//标题
char* cTitle = (char*)sqlite3_column_text(statement, 1);
NSString *title = [[NSString alloc] initWithUTF8String:cTitle];
//内容
char* cContent = (char*)sqlite3_column_text(statement, 2);
NSString *content = [[NSString alloc] initWithUTF8String:cContent];
//创建时间
char* cCreateDate = (char*)sqlite3_column_text(statement, 3);
NSString *createDate = [[NSString alloc] initWithUTF8String:cCreateDate];
//更新时间
char* cUpdateDate = (char*)sqlite3_column_text(statement, 4);
NSString *updateDate = [[NSString alloc] initWithUTF8String:cUpdateDate];
Note *note = [[Note alloc] initWithID:cId title:title content:content createDate:createDate updateDate:updateDate];
sqlite3_finalize(statement);
sqlite3_close(db);
return note;
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return nil;
}
//查找所有数据
- (NSMutableArray *) findAll {
NSMutableArray *listData = [[NSMutableArray alloc] init];
NSString *path = [self applicationDocumentsDirecrotyFile];
const char* cpath = [path UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
NSString *sql = @"select _id, title, content, createDate, updateDate from note";
const char* cSql = [sql UTF8String];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
//执行
while(sqlite3_step(statement) == SQLITE_ROW) {
//id
int cId = sqlite3_column_int(statement, 0);
//标题
char* cTitle = (char*)sqlite3_column_text(statement, 1);
NSString *title = [[NSString alloc] initWithUTF8String:cTitle];
//内容
char* cContent = (char*)sqlite3_column_text(statement, 2);
NSString *content = [[NSString alloc] initWithUTF8String:cContent];
//创建时间
char* cCreateDate = (char*)sqlite3_column_text(statement, 3);
NSString *createDate = [[NSString alloc] initWithUTF8String:cCreateDate];
//更新时间
char* cUpdateDate = (char*)sqlite3_column_text(statement, 4);
NSString *updateDate = [[NSString alloc] initWithUTF8String:cUpdateDate];
Note *note = [[Note alloc] initWithID:cId title:title content:content createDate:createDate updateDate:updateDate];
[listData addObject:note];
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return listData;
}
//分页查找
//limit:查找条数
//ship:跳过条数
- (NSMutableArray *) findLimit:(int) limit withSkip:(int) skip {
NSMutableArray *listData = [[NSMutableArray alloc] init];
NSString *path = [self applicationDocumentsDirecrotyFile];
const char* cpath = [path UTF8String];
if (sqlite3_open(cpath, &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO, @"数据库打开失败");
} else {
NSString *sql = @"select _id, title, content, createDate, updateDate from note limit ? offset ?";
const char* cSql = [sql UTF8String];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, cSql, -1, &statement, NULL) == SQLITE_OK) {
sqlite3_bind_int(statement, 1, limit);
sqlite3_bind_int(statement, 2, skip);
//执行
while(sqlite3_step(statement) == SQLITE_ROW) {
//id
int cId = sqlite3_column_int(statement, 0);
//标题
char* cTitle = (char*)sqlite3_column_text(statement, 1);
NSString *title = [[NSString alloc] initWithUTF8String:cTitle];
//内容
char* cContent = (char*)sqlite3_column_text(statement, 2);
NSString *content = [[NSString alloc] initWithUTF8String:cContent];
//创建时间
char* cCreateDate = (char*)sqlite3_column_text(statement, 3);
NSString *createDate = [[NSString alloc] initWithUTF8String:cCreateDate];
//更新时间
char* cUpdateDate = (char*)sqlite3_column_text(statement, 4);
NSString *updateDate = [[NSString alloc] initWithUTF8String:cUpdateDate];
Note *note = [[Note alloc] initWithID:cId title:title content:content createDate:createDate updateDate:updateDate];
[listData addObject:note];
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return listData;
}
//获取数据库路径
- (NSString *) applicationDocumentsDirecrotyFile {
NSArray * documentDirectory = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString * myDocPath = [documentDirectory objectAtIndex:0];
NSString * writableFile = [myDocPath stringByAppendingPathComponent:@"note.db"];
return writableFile;
}
@end