sqlite运用总结
1、打开数据库。
//打开数据库-(BOOL)openDatabase{NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);NSString *paths = [[path objectAtIndex:0] stringByAppendingPathComponent:@"NineCellDiaryDB.sqlite3"];if(sqlite3_open([paths UTF8String], &database) != SQLITE_OK){[self closeDatabase];return NO;}return YES;}//关闭数据库-(BOOL)closeDatabase{return (sqlite3_close(database) == SQLITE_OK)?YES:NO;}//打开数据库if(![self openDatabase]){[self closeDatabase];return NO;}sqlite3_stmt *insert_statement;//准备sql语句const char* sql = "insert into userinfo(uname,upassword,uexp,uportrait) values(?,?,?,?)";//绑定语句值if (sqlite3_prepare_v2(database,sql, -1, &insert_statement, NULL) != SQLITE_OK)return NO;sqlite3_bind_text(insert_statement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);sqlite3_bind_text(insert_statement, 2, [passwd UTF8String], -1, SQLITE_TRANSIENT);sqlite3_bind_int(insert_statement, 3, [e intValue]);//提供png格式和jpg格式支持int returnValue = -1;NSData *imageData = nil; UIImage *por = [UIImage imageNamed:@"kill.png"];imageData = UIImagePNGRepresentation(por);//png格式if (nil == por) {returnValue = sqlite3_bind_blob(insert_statement, 4, nil, -1, NULL);}else {returnValue = sqlite3_bind_blob(insert_statement, 4,[imageData bytes],[imageData length], NULL);}//执行插入if (sqlite3_step(insert_statement) != SQLITE_DONE) {sqlite3_finalize(insert_statement);[self closeDatabase];return NO;}sqlite3_finalize(insert_statement);[self closeDatabase];//打开数据库if(![self openDatabase]){[self closeDatabase];return NO;}//准备陈述命令sqlite3_stmt *update_statement;//准备sql语句const char* sql = "update userinfo set uname=?,upassword=?,uexp=?,uportrait=? where id=1";//绑定语句值if (sqlite3_prepare_v2(database,sql, -1, &update_statement, NULL) != SQLITE_OK)return NO;sqlite3_bind_text(update_statement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);sqlite3_bind_text(update_statement, 2, [passwd UTF8String], -1, SQLITE_TRANSIENT);sqlite3_bind_int(update_statement, 3, [e intValue]);//提供png格式和jpg格式支持int returnValue = -1;NSData *imageData = nil;UIImage *por = [UIImage imageNamed:@"killer.png"];if (nil == por) {returnValue = sqlite3_bind_blob(update_statement, 4, nil, -1, NULL); }else {imageData = UIImageJPEGRepresentation(por,1.0f);//jpg格式returnValue = sqlite3_bind_blob(update_statement, 4,[imageData bytes],[imageData length], NULL);}}//执行if (sqlite3_step(update_statement) != SQLITE_OK){sqlite3_finalize(update_statement);[self closeDatabase];return NO;}sqlite3_finalize(update_statement);[self closeDatabase];//打开数据库if (![self openDatabase]) {return self;}//查询所有数据sqlite3_stmt *statement;const char* zTail;sqlite3_prepare_v2(database,"SELECT * from userinfo;",-1,&statement,&zTail); const unsigned char *name; const unsigned char *password;int exp=0;NSString *newName = nil;NSString *newPassword = nil;NSData *imgData = nil;[color=green]//sqlite3_reset(statement);[/color]if (SQLITE_ROW == sqlite3_step(statement)) {name = sqlite3_column_text(statement,1);password = sqlite3_column_text(statement,2);exp = sqlite3_column_int(statement,3);if (NULL != sqlite3_column_blob(statement,4)) {imgData = [[NSData alloc] initWithBytes:sqlite3_column_blob(statement,4) length:sqlite3_column_bytes(statement, 1)];}newName = [NSString stringWithCString:name encoding:CFStringConvertEncodingToNSStringEncoding(kCFStringEncodingUTF8)];newPassword = [NSString stringWithCString:password encoding:CFStringConvertEncodingToNSStringEncoding(kCFStringEncodingUTF8)];}