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