建立SQLite数据管理
//sqlite数据管理
@interface SQLiteNoteDao : NSObject<BaseNoteDao>
{
sqlite3* _db;
}
#define DBName @"Notes.sqlite3"
@end
为Note添加一个方法
-(id)initWithSqliteStmt:(sqlite3_stmt *)statement
{
self = [super init];
if (self) {
//第二个参数为字段编号从0开始
char* cID = (char*)sqlite3_column_text(statement, 0);
self.ID = [[[NSString alloc]initWithUTF8String:cID] integerValue];
char* cContent = (char*)sqlite3_column_text(statement, 1);
self.content = [[NSString alloc]initWithUTF8String:cContent];
}
return self;
}
具体实现
@implementation SQLiteNoteDao
+(id)sharedManager
{
static dispatch_once_t onceToken;
__block SQLiteNoteDao* instance = nil;
dispatch_once(&onceToken, ^{
instance = [[SQLiteNoteDao alloc]init];
[instance createObjectCacheFile];
});
return instance;
}
-(void)createObjectCacheFile
{
if ([self openDB]) {
char* err;
NSString* createTableSQL = @"CREATE TABLE IF NOT EXISTS Note(ID INTEGER PRIMARY KEY,content TEXT)";
if (sqlite3_exec(_db, [createTableSQL UTF8String], NULL, NULL, &err)!= SQLITE_OK) {
sqlite3_close(_db);
NSAssert1(NO, @"建表失败,%s",err);
}
sqlite3_close(_db);
}
}
-(NSString *)ObjectCacheFilePath
{
NSString* documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
return [documentDirectory stringByAppendingPathComponent:DBName];
}
-(BOOL)openDB
{
NSString* dbPath = [self ObjectCacheFilePath];
if (sqlite3_open([dbPath UTF8String], &_db)!= SQLITE_OK) {
sqlite3_close(_db);
NSAssert(NO, @"数据库打开失败");
return NO;
}
return YES;
}
-(void)addNote:(Note *)note
{
if ([self openDB]) {
NSString* exeSql = @"INSERT INTO Note(ID,content) VALUES(?,?)";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(_db, [exeSql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%d",note.ID]UTF8String], -1, nil);
sqlite3_bind_text(statement, 2, [note.content UTF8String], -1, nil);
if (sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @"数据库插入失败");
}
}
sqlite3_finalize(statement);
sqlite3_close(_db);
}
}
-(void)removeNote:(Note *)note
{
if ([self openDB]) {
NSString* exeSql = @"DELETE FRON Note where ID=?";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(_db, [exeSql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%d",note.ID]UTF8String], -1, nil);
if (sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @"数据库插入失败");
}
}
sqlite3_finalize(statement);
sqlite3_close(_db);
}
}
-(void)modify:(Note *)note
{
if ([self openDB]) {
NSString* exeSql = @"UPDATE Note set content=? and ID=?";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(_db, [exeSql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%d",note.ID]UTF8String], -1, nil);
sqlite3_bind_text(statement, 1, [note.content UTF8String], -1, nil);
if (sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @"数据库插入失败");
}
}
sqlite3_finalize(statement);
sqlite3_close(_db);
}
}
-(NSMutableArray*)queryAllNote
{
NSMutableArray* datas = [NSMutableArray array];
if ([self openDB]) {
NSString* exeSql = @"SELECT * FROM Note";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(_db, [exeSql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
Note* note = [[Note alloc]initWithSqliteStmt:statement];
[datas addObject:note];
}
}
sqlite3_finalize(statement);
sqlite3_close(_db);
}
return datas;
}
-(Note*)queryNoteByID:(NSInteger)ID
{
if ([self openDB]) {
NSString* exeSql = [NSString stringWithFormat:@"SELECT * FROM Note where ID=%d",ID];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(_db, [exeSql UTF8String], -1, &statement, NULL)) {
//第二个参数为字段编号从1开始
sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%d",ID]UTF8String], -1, NULL);
if (sqlite3_step(statement) == SQLITE_ROW) {
Note* note = [[Note alloc]initWithSqliteStmt:statement];
sqlite3_finalize(statement);
sqlite3_close(_db);
return note;
}
}
sqlite3_finalize(statement);
sqlite3_close(_db);
}
return nil;
}
@end