sqlite的简单示例

保存

 

- (void)save {
	if ([txt1.text isEqualToString:@""]) {
		return;
	}
	User *new_user = [[User alloc] init];
	new_user.name = txt1.text;
	new_user.age = txt2.text;
	new_user.sex = txt3.text;
	if (sqlite3_open([[self databasePath] UTF8String],&database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"open database faild!");
	}
	
	char *erroMsg;
	NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(ROW INTEGER PRIMARY KEY,NAME TEXT,AGE TEXT,SEX TEXT)", TableName];
	if (sqlite3_exec(database, [createSQL UTF8String], NULL, NULL, &erroMsg) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert1(0, @"create table %@ faild", TableName);
		NSAssert1(0, @"the error is %s", erroMsg);
	}
	
	NSString *insertUser = [NSString stringWithFormat:@"INSERT INTO %@(NAME,AGE,SEX) VALUES('%@','%@','%@')", TableName, new_user.name, new_user.age, new_user.sex];
	NSLog(@"%@",insertUser);
	if (sqlite3_exec(database, [insertUser UTF8String], NULL, NULL, &erroMsg) != SQLITE_OK) {
		NSAssert1(0, @"Error updating tables: %s", erroMsg);
	}
    
	txt1.text = @"";
	txt2.text = @"";
	txt3.text = @"";
}

 

查询

 

- (User *)getUser:(int)id {
	if (sqlite3_open([[self databasePath] UTF8String],&database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0, @"open database faild!");
		return nil;
	}
	User *user = [[User alloc] init];
	NSString *countSQL = [NSString stringWithFormat:@"SELECT ROW,NAME,AGE,SEX FROM UserTable WHERE ROW = %i", id];
	sqlite3_stmt *statement;
	if (sqlite3_prepare_v2(database, [countSQL UTF8String], -1, &statement, nil) == SQLITE_OK) {
		while (sqlite3_step(statement) == SQLITE_ROW) {
			user.id = [NSString stringWithFormat:@"%d", sqlite3_column_int(statement, 0)];
			user.name = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 1)];
			user.age = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 2)];
			user.sex = [NSString stringWithFormat:@"%s", sqlite3_column_text(statement, 3)];
		}
		sqlite3_finalize(statement);
		return user;
	}
	return nil;
}

 

具体的使用示例请参考附件。