数据库sqlite3 创建 增 删 查 改 操作

#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