iPhone 數據庫（sqlite3）的用法操作

首先你在用之前要在項目中加入libsqlite3.dylib

1、定義模型

    #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 "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