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