首先你在用之前要在项目中加入libsqlite3.dylib
1、定义模型
[cpp] #import <Foundation/Foundation.h>
#import "sqlite3.h"
@class NotePad;
@class NoteDb;
@interface NoteSqlite : NSObject{
sqlite3 *database;
sqlite3_stmt *statement;
char *errorMsg;
}
//打开数据库
-(BOOL)open;
//创建青
-(BOOL)create;
//增加、删除、修改、查询
-(BOOL)insert:(NotePad*)aNote;
-(BOOL)deleteALLNote;
-(BOOL)deleteaNote:(NotePad*)aNote;
-(BOOL)update:(NotePad*)aNote;
-(NoteDb*)selecteAll;
-(NoteDb*)selectNotes:(NotePad*)aNote;
@end
#import <Foundation/Foundation.h>
#import "sqlite3.h"
@class NotePad;
@class NoteDb;
@interface NoteSqlite : NSObject{
sqlite3 *database;
sqlite3_stmt *statement;
char *errorMsg;
}
//打开数据库
-(BOOL)open;
//创建青
-(BOOL)create;
//增加、删除、修改、查询
-(BOOL)insert:(NotePad*)aNote;
-(BOOL)deleteALLNote;
-(BOOL)deleteaNote:(NotePad*)aNote;
-(BOOL)update:(NotePad*)aNote;
-(NoteDb*)selecteAll;
-(NoteDb*)selectNotes:(NotePad*)aNote;
@end
2、实现方法
[cpp] #import "NoteSqlite.h"
#import "NotePad.h"
#import "NoteDb.h"
@implementation NoteSqlite
-(id)init{
self=[super init];
return self;
}
//打开数据库
-(BOOL)open{
NSArray *paths= NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"noteList.db"];
NSFileManager *fileManager = [NSFileManager defaultManager];
BOOL find = [fileManager fileExistsAtPath:path];
//判断文件是否存在
if (find) {
NSLog(@"数据库文件已经存在");
//打开数据库、返回操作是否正确
if(sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
NSLog(@"打开成功数据库");
}
return YES;
}else{
if(sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
//调用createMusicList创建数据库和表
[self create];
return YES;
} else {
sqlite3_close(database);
NSLog(@"Error: open database file.");
return NO;
}
return NO;
}
}
//创建表
-(BOOL)create{
//创建表语句
const char *createSql="create table if not exists note (id integer primary key autoincrement,theme text,information text,ndate text,priority integer)";
//创建表是否成功
if (sqlite3_exec(database, createSql, NULL, NULL, &errorMsg)==SQLITE_OK) {
NSLog(@"create ok.");
return YES;
}else{
//打印出错信息
NSLog(@"error: %s",errorMsg);
sqlite3_free(errorMsg);
}
return NO;
}
//增加、删除、修改、查询
-(BOOL)insert:(NotePad*)aNote{
//向表中插入记录
//定义一个sql语句
NSString *insertStatementNS = [NSString stringWithFormat:
@"insert into \"note\"\
(theme, information, ndate,priority)\
values (\"%@\", \"%@\", \"%@\",%d)",
aNote.theme,aNote.information,[NSString stringWithFormat:@"%@",aNote.ndate],aNote.priority
];
//将定义的NSString的sql语句，转换成UTF8的c风格的字符串
const char *insertSql = [insertStatementNS UTF8String];
//执行插入语句
if (sqlite3_exec(database, insertSql, NULL, NULL, &errorMsg)==SQLITE_OK) {
NSLog(@"insert ok.");
return YES;
}else{
NSLog(@"error: %s",errorMsg);
sqlite3_free(errorMsg);
}
return NO;
}
-(BOOL)deleteALLNote{
//删除所有数据，条件为1>0永真
const char *deleteAllSql="delete from note where 1>0";
//执行删除语句
if(sqlite3_exec(database, deleteAllSql, NULL, NULL, &errorMsg)==SQLITE_OK){
NSLog(@"删除所有数据成功");
}
return YES;
}
-(BOOL)deleteaNote:(NotePad*)aNote{
//删除某条数据
NSString *deleteString=[NSString stringWithFormat:@"delete from note where id=%d",aNote.noteId];
//转成utf-8的c的风格
const char *deleteSql=[deleteString UTF8String];
//执行删除语句
if(sqlite3_exec(database, deleteSql, NULL, NULL, &errorMsg)==SQLITE_OK){
NSLog(@"删除成功");
}
return YES;
}
-(BOOL)update:(NotePad*)aNote{
//更新语句
NSString *updateString=[NSString stringWithFormat:@"update note set theme='%@', information='%@',
ndate='%@',priority=%d where id=%d",aNote.theme,aNote.information,aNote.ndate,aNote.priority,aNote.noteId];
// NSLog(@"%@",aNote);
const char *updateSql=[updateString UTF8String];
//执行更新语句
if(sqlite3_exec(database, updateSql, NULL, NULL, &errorMsg)==SQLITE_OK){
NSLog(@"更新成功");
}
return YES;
}
-(NoteDb*)selecteAll{
NoteDb *noteDb=[[[NoteDb alloc]init]autorelease];
//查询所有语句
const char *selectAllSql="select * from note";
//执行查询
if (sqlite3_prepare_v2(database, selectAllSql, -1, &statement, nil)==SQLITE_OK) {
NSLog(@"select ok.");
//如果查询有语句就执行step来添加数据
while (sqlite3_step(statement)==SQLITE_ROW) {
NotePad *note=[[NotePad alloc]init];
int noteid=sqlite3_column_int(statement, 0);
NSMutableString *theme=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];
NSMutableString *information=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding];
NSString *ndateString=[NSString stringWithCString:(char*)sqlite3_column_text(statement, 3) encoding:NSUTF8StringEncoding];
NSDateFormatter* formater = [[NSDateFormatter alloc] init];
[formater setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
NSDate *ndate=[formater dateFromString:[ndateString substringToIndex:[ndateString length]-5]];
// NSLog(@"%@",[ndateString substringToIndex:[ndateString length]-5]);
[formater release];
int proriory=sqlite3_column_int(statement, 4);
note.noteId=noteid;
note.theme=theme;
note.information=information;
note.ndate=ndate;
note.priority=proriory;
[noteDb addNote:note];
[note release];
}
return noteDb;
}
return noteDb;
}
-(NoteDb*)selectNotes:(NotePad*)aNote{
NoteDb *noteDb=[[[NoteDb alloc]init]autorelease];
NSString *selectNSSql=[NSString stringWithFormat:@"select * from note where id=%i",aNote.noteId];
//查询所有语句
const char *selectSql=[selectNSSql UTF8String];
//执行查询
if (sqlite3_prepare_v2(database, selectSql, -1, &statement, nil)==SQLITE_OK) {
NSLog(@"select ok.");
//如果查询有语句就执行step来添加数据
while (sqlite3_step(statement)==SQLITE_ROW) {
NotePad *note=[[NotePad alloc]init];
int noteid=sqlite3_column_int(statement, 0);
NSMutableString *theme=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];
NSMutableString *information=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding];
NSString *ndateString=[NSString stringWithCString:(char*)sqlite3_column_text(statement, 3) encoding:NSUTF8StringEncoding];
NSDateFormatter* formater = [[NSDateFormatter alloc] init];
[formater setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
NSDate *ndate=[formater dateFromString:[ndateString substringToIndex:[ndateString length]-5]];
// NSLog(@"%@",[ndateString substringToIndex:[ndateString length]-5]);
[formater release];
int proriory=sqlite3_column_int(statement, 4);
note.noteId=noteid;
note.theme=theme;
note.information=information;
note.ndate=ndate;
note.priority=proriory;
[noteDb addNote:note];
[note release];
}
return noteDb;
}
return noteDb;
}
@end
#import "NoteSqlite.h"
#import "NotePad.h"
#import "NoteDb.h"
@implementation NoteSqlite
-(id)init{
self=[super init];
return self;
}
//打开数据库
-(BOOL)open{
NSArray *paths= NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"noteList.db"];
NSFileManager *fileManager = [NSFileManager defaultManager];
BOOL find = [fileManager fileExistsAtPath:path];
//判断文件是否存在 www.2cto.com
if (find) {
NSLog(@"数据库文件已经存在");
//打开数据库、返回操作是否正确
if(sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
NSLog(@"打开成功数据库");
}
return YES;
}else{
if(sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
//调用createMusicList创建数据库和表
[self create];
return YES;
} else {
sqlite3_close(database);
NSLog(@"Error: open database file.");
return NO;
}
return NO;
}
}
//创建表
-(BOOL)create{
//创建表语句
const char *createSql="create table if not exists note (id integer primary key autoincrement,theme text,information text,ndate text,priority integer)";
//创建表是否成功
if (sqlite3_exec(database, createSql, NULL, NULL, &errorMsg)==SQLITE_OK) {
NSLog(@"create ok.");
return YES;
}else{
//打印出错信息
NSLog(@"error: %s",errorMsg);
sqlite3_free(errorMsg);
}
return NO;
}
//增加、删除、修改、查询
-(BOOL)insert:(NotePad*)aNote{
//向表中插入记录
//定义一个sql语句
NSString *insertStatementNS = [NSString stringWithFormat:
@"insert into \"note\"\
(theme, information, ndate,priority)\
values (\"%@\", \"%@\", \"%@\",%d)",
aNote.theme,aNote.information,[NSString stringWithFormat:@"%@",aNote.ndate],aNote.priority
];
//将定义的NSString的sql语句，转换成UTF8的c风格的字符串
const char *insertSql = [insertStatementNS UTF8String];
//执行插入语句
if (sqlite3_exec(database, insertSql, NULL, NULL, &errorMsg)==SQLITE_OK) {
NSLog(@"insert ok.");
return YES;
}else{
NSLog(@"error: %s",errorMsg);
sqlite3_free(errorMsg);
}
return NO;
}
-(BOOL)deleteALLNote{
//删除所有数据，条件为1>0永真
const char *deleteAllSql="delete from note where 1>0";
//执行删除语句
if(sqlite3_exec(database, deleteAllSql, NULL, NULL, &errorMsg)==SQLITE_OK){
NSLog(@"删除所有数据成功");
}
return YES;
}
-(BOOL)deleteaNote:(NotePad*)aNote{
//删除某条数据
NSString *deleteString=[NSString stringWithFormat:@"delete from note where id=%d",aNote.noteId];
//转成utf-8的c的风格
const char *deleteSql=[deleteString UTF8String];
//执行删除语句
if(sqlite3_exec(database, deleteSql, NULL, NULL, &errorMsg)==SQLITE_OK){
NSLog(@"删除成功");
}
return YES;
}
-(BOOL)update:(NotePad*)aNote{
//更新语句
NSString *updateString=[NSString stringWithFormat:@"update note set theme='%@', information='%@',
ndate='%@',priority=%d where id=%d",aNote.theme,aNote.information,aNote.ndate,aNote.priority,aNote.noteId];
// NSLog(@"%@",aNote);
const char *updateSql=[updateString UTF8String];
//执行更新语句
if(sqlite3_exec(database, updateSql, NULL, NULL, &errorMsg)==SQLITE_OK){
NSLog(@"更新成功");
}
return YES;
}
-(NoteDb*)selecteAll{
NoteDb *noteDb=[[[NoteDb alloc]init]autorelease];
//查询所有语句
const char *selectAllSql="select * from note";
//执行查询
if (sqlite3_prepare_v2(database, selectAllSql, -1, &statement, nil)==SQLITE_OK) {
NSLog(@"select ok.");
//如果查询有语句就执行step来添加数据
while (sqlite3_step(statement)==SQLITE_ROW) {
NotePad *note=[[NotePad alloc]init];
int noteid=sqlite3_column_int(statement, 0);
NSMutableString *theme=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];
NSMutableString *information=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding];
NSString *ndateString=[NSString stringWithCString:(char*)sqlite3_column_text(statement, 3) encoding:NSUTF8StringEncoding];
NSDateFormatter* formater = [[NSDateFormatter alloc] init];
[formater setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
NSDate *ndate=[formater dateFromString:[ndateString substringToIndex:[ndateString length]-5]];
// NSLog(@"%@",[ndateString substringToIndex:[ndateString length]-5]);
[formater release];
int proriory=sqlite3_column_int(statement, 4);
note.noteId=noteid;
note.theme=theme;
note.information=information;
note.ndate=ndate;
note.priority=proriory;
[noteDb addNote:note];
[note release];
}
return noteDb;
}
return noteDb;
}
-(NoteDb*)selectNotes:(NotePad*)aNote{
NoteDb *noteDb=[[[NoteDb alloc]init]autorelease];
NSString *selectNSSql=[NSString stringWithFormat:@"select * from note where id=%i",aNote.noteId];
//查询所有语句
const char *selectSql=[selectNSSql UTF8String];
//执行查询
if (sqlite3_prepare_v2(database, selectSql, -1, &statement, nil)==SQLITE_OK) {
NSLog(@"select ok.");
//如果查询有语句就执行step来添加数据
while (sqlite3_step(statement)==SQLITE_ROW) {
NotePad *note=[[NotePad alloc]init];
int noteid=sqlite3_column_int(statement, 0);
NSMutableString *theme=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];
NSMutableString *information=[NSMutableString stringWithCString:(char*)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding];
NSString *ndateString=[NSString stringWithCString:(char*)sqlite3_column_text(statement, 3) encoding:NSUTF8StringEncoding];
NSDateFormatter* formater = [[NSDateFormatter alloc] init];
[formater setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
NSDate *ndate=[formater dateFromString:[ndateString substringToIndex:[ndateString length]-5]];
// NSLog(@"%@",[ndateString substringToIndex:[ndateString length]-5]);
[formater release];
int proriory=sqlite3_column_int(statement, 4);
note.noteId=noteid;
note.theme=theme;
note.information=information;
note.ndate=ndate;
note.priority=proriory;
[noteDb addNote:note];
[note release];
}
return noteDb;
}
return noteDb;
}
@end