iphone数据库（sqlite3）的用法操作oc,xcode

首先你在用之前要在项目中加入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