sqlite施用总结

//打开数据库-(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)];		}