iOS.数据持久化.PersistenceLayer.SQLite
#import <Foundation/Foundation.h> #import "Persistence03Note.h" #import "sqlite3.h" #define DBFILE_NAME @"Persistence03NotesList.sqlite3" @interface Persistence03NoteDAO : NSObject { sqlite3 *db; } + (Persistence03NoteDAO*)sharedManager; - (NSString *)applicationDocumentsDirectoryFile; - (void)createEditableCopyOfDatabaseIfNeeded; //插入Persistence03Note方法 -(int) create:(Persistence03Note*)model; //删除Persistence03Note方法 -(int) remove:(Persistence03Note*)model; //修改Persistence03Note方法 -(int) modify:(Persistence03Note*)model; //查询所有数据方法 -(NSMutableArray*) findAll; //按照主键查询数据方法 -(Persistence03Note*) findById:(Persistence03Note*)model; @end
#import "Persistence03NoteDAO.h" @implementation Persistence03NoteDAO static Persistence03NoteDAO *sharedManager = nil; + (Persistence03NoteDAO*)sharedManager { static dispatch_once_t once; dispatch_once(&once, ^{ sharedManager = [[self alloc] init]; [sharedManager createEditableCopyOfDatabaseIfNeeded]; }); return sharedManager; } - (void)createEditableCopyOfDatabaseIfNeeded { NSString *writableDBPath = [self applicationDocumentsDirectoryFile]; if (sqlite3_open([writableDBPath UTF8String], &db) != SQLITE_OK) { sqlite3_close(db); NSAssert(NO,@"数据库打开失败。"); } else { char *err; NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS Persistence03Note (cdate TEXT PRIMARY KEY, content TEXT);"]; if (sqlite3_exec(db,[createSQL UTF8String],NULL,NULL,&err) != SQLITE_OK) { sqlite3_close(db); NSAssert1(NO, @"建表失败, %s", err); } sqlite3_close(db); } } - (NSString *)applicationDocumentsDirectoryFile { NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]; NSString *path = [documentDirectory stringByAppendingPathComponent:DBFILE_NAME]; return path; } //插入Persistence03Note方法 -(int) create:(Persistence03Note*)model { NSString *path = [self applicationDocumentsDirectoryFile]; if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) { sqlite3_close(db); NSAssert(NO,@"数据库打开失败。"); } else { NSString *sqlStr = @"INSERT OR REPLACE INTO Persistence03Note (cdate, content) VALUES (?,?)"; sqlite3_stmt *statement; //预处理过程 if (sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK) { NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; NSString *nsdate = [dateFormatter stringFromDate:model.date]; //绑定参数开始 sqlite3_bind_text(statement, 1, [nsdate UTF8String], -1, NULL); sqlite3_bind_text(statement, 2, [model.content UTF8String], -1, NULL); //执行插入 if (sqlite3_step(statement) != SQLITE_DONE) { NSAssert(NO, @"插入数据失败。"); } } sqlite3_finalize(statement); sqlite3_close(db); } return 0; } //删除Persistence03Note方法 -(int) remove:(Persistence03Note*)model { NSString *path = [self applicationDocumentsDirectoryFile]; if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) { sqlite3_close(db); NSAssert(NO,@"数据库打开失败。"); } else { NSString *sqlStr = @"DELETE from Persistence03Note where cdate =?"; sqlite3_stmt *statement; //预处理过程 if (sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK) { NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; NSString *nsdate = [dateFormatter stringFromDate:model.date]; //绑定参数开始 sqlite3_bind_text(statement, 1, [nsdate UTF8String], -1, NULL); //执行 if (sqlite3_step(statement) != SQLITE_DONE) { NSAssert(NO, @"删除数据失败。"); } } sqlite3_finalize(statement); sqlite3_close(db); } return 0; } //修改Persistence03Note方法 -(int) modify:(Persistence03Note*)model { NSString *path = [self applicationDocumentsDirectoryFile]; if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) { sqlite3_close(db); NSAssert(NO,@"数据库打开失败。"); } else { NSString *sqlStr = @"UPDATE Persistence03Note set content=? where cdate =?"; sqlite3_stmt *statement; //预处理过程 if (sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK) { NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; NSString *nsdate = [dateFormatter stringFromDate:model.date]; //绑定参数开始 sqlite3_bind_text(statement, 1, [model.content UTF8String], -1, NULL); sqlite3_bind_text(statement, 2, [nsdate UTF8String], -1, NULL); //执行 if (sqlite3_step(statement) != SQLITE_DONE) { NSAssert(NO, @"修改数据失败。"); } } sqlite3_finalize(statement); sqlite3_close(db); } return 0; } //查询所有数据方法 -(NSMutableArray*) findAll { NSString *path = [self applicationDocumentsDirectoryFile]; NSMutableArray *listData = [[NSMutableArray alloc] init]; if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) { sqlite3_close(db); NSAssert(NO,@"数据库打开失败。"); } else { NSString *qsql = @"SELECT cdate,content FROM Persistence03Note"; sqlite3_stmt *statement; //预处理过程 if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) == SQLITE_OK) { NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; //执行 while (sqlite3_step(statement) == SQLITE_ROW) { char *cdate = (char *) sqlite3_column_text(statement, 0); NSString *nscdate = [[NSString alloc] initWithUTF8String: cdate]; char *content = (char *) sqlite3_column_text(statement, 1); NSString * nscontent = [[NSString alloc] initWithUTF8String: content]; Persistence03Note* persistence03Note = [[Persistence03Note alloc] init]; persistence03Note.date = [dateFormatter dateFromString:nscdate]; persistence03Note.content = nscontent; [listData addObject:persistence03Note]; } } sqlite3_finalize(statement); sqlite3_close(db); } return listData; } //按照主键查询数据方法 -(Persistence03Note*) findById:(Persistence03Note*)model { NSString *path = [self applicationDocumentsDirectoryFile]; if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) { sqlite3_close(db); NSAssert(NO,@"数据库打开失败。"); } else { NSString *qsql = @"SELECT cdate,content FROM Persistence03Note where cdate =?"; sqlite3_stmt *statement; //预处理过程 if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) == SQLITE_OK) { //准备参数 NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; NSString *nsdate = [dateFormatter stringFromDate:model.date]; //绑定参数开始 sqlite3_bind_text(statement, 1, [nsdate UTF8String], -1, NULL); //执行 if (sqlite3_step(statement) == SQLITE_ROW) { char *cdate = (char *) sqlite3_column_text(statement, 0); NSString *nscdate = [[NSString alloc] initWithUTF8String: cdate]; char *content = (char *) sqlite3_column_text(statement, 1); NSString * nscontent = [[NSString alloc] initWithUTF8String: content]; Persistence03Note* persistence03Note = [[Persistence03Note alloc] init]; persistence03Note.date = [dateFormatter dateFromString:nscdate]; persistence03Note.content = nscontent; sqlite3_finalize(statement); sqlite3_close(db); return persistence03Note; } } sqlite3_finalize(statement); sqlite3_close(db); } return nil; } @end