iphone數據庫(sqlite3)的用法操作 iPhone軟體開發教學課程

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

 

 

摘自 任海麗(3G/移動開發)

You May Also Like