iOS sqlite 工具类

理论分析:

首先封装一个获取文件路径方法

//获取数据库路径- (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