我使用以下代码在应用程序委托中创建和读取我的数据库:
- (void) checkAndCreateDatabase {
//Check if the database has been saved to the users phone, if not then copy it over
BOOL l_Success;
//Create a file manager object, we will use this to check the status
//of the databse and to copy it over if required
NSFileManager *l_FileManager = [NSFileManager defaultManager];
//Check if the database has already been created in the users filesystem
l_Success = [l_FileManager fileExistsAtPath:m_DatabasePath];
//If the database already exists then return without doing anything
if(l_Success)
return;
//If not then proceed to copy the database from the application to the usrs filesystem
//Get the path to the database in the application package
NSString *l_DatabasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:m_DatabaseName];
//Copy the database from the package to the usrrs filesystem
[l_FileManager copyItemAtPath:l_DatabasePathFromApp toPath:m_DatabasePath error:nil];
}
- (void) readProductsFromDatabase {
//Setup the database object
sqlite3 *l_Database;
//Init the products array
m_Products = [[NSMutableArray alloc] init];
NSLog(@"%@", m_DatabasePath);
//Open the database from the users filessystem
if(sqlite3_open([m_DatabasePath UTF8String], &l_Database) == SQLITE_OK) {
//Set-up the SQL statement and compile it for faster access
const char *sqlStatement = "select *from products";
sqlite3_stmt *compiledStatement;
if(sqlite3_prepare_v2(l_Database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
{
//Loop through the results and add them to the feeds array
while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
//Read the data from the results row
NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
NSLog(@"%@",[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)]);
NSString *aCategory = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
NSString *aCalories = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
NSString *aFat = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];
NSString *aSaturates = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)];
NSString *aSugar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)];
NSString *aFibre = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 7)];
NSString *aSalt = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 8)];
NSString *aImageURL = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 9)];
//Create a new animal object with the data from the database
Product *l_Product = [[Product alloc] initWithName:aName category:aCategory calories:aCalories fat:aFat saturates:aSaturates sugar:aSugar fibre:aFibre salt:aSalt imageURL:aImageURL ];
//Add the animal object to the animals array
[m_Products addObject:l_Product];
}
}
//Release the compiled statement from memory
sqlite3_finalize(compiledStatement);
}
sqlite3_close(l_Database);
}
上述代码的调用如下:
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
//Set-up some globals
m_DatabaseName = @"ProductDatabase.sql";
//Get the path to the documents directory and append the databaseName
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
m_DatabasePath = [documentsDirectory stringByAppendingPathComponent:@"ProductDatabase.sql"];
//Execute the "checkAndCreateDatabase" function
[self checkAndCreateDatabase];
//Query the databse for all animal records and construct the "animals" array
[self readProductsFromDatabase];
..unrelated code..
return YES;
}
然后,我使用以下代码填充一个表视图:
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
NSLog(@"Cell for row");
static NSString *CellIdentifier = @"Cell";
UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier];
}
if([self.productType isEqualToString:@"ALL"]){
Product *product = (Product *) [appDelegate.m_Products objectAtIndex:indexPath.row];
cell.textLabel.text = product.name;
// Configure the cell.
return cell;
}
else {
//Instead of using appDelegate.products use the new array that will be filled
//by the numberOfReowsInSection method
Product *product = (Product *)[[m_AlphabetDictionary objectAtIndex:indexPath.section] objectAtIndex:indexPath.row];
cell.textLabel.text = product.name;
// Configure the cell.
return cell;
}
}
当我尝试插入一些测试数据时,我的问题出现了,如下所示:
-(void)insertData{
sqlite3 *database;
sqlite3_stmt *statement;
//Get the path to the documents directory and append the databaseName
AppDelegate *appDelegate = (AppDelegate *) [[UIApplication sharedApplication] delegate];
NSString *databasePath = [appDelegate m_DatabasePath];
NSLog(@"%@", [appDelegate m_DatabasePath]);
if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
{
NSString *insertSQL = @"INSERT INTO products (name,category,calories, saturates,fat,fibre,sugar,salt, image) VALUES ('Snickers',' Confectionary','300','55','55','55','55','55', 'http://upload.wikimedia.org/wikipedia/en/8/88/Snickers_wrapped.jpg');";
const char *insert_stmt = [insertSQL UTF8String];
if(sqlite3_prepare_v2(database, insert_stmt, -1, &statement, NULL)== SQLITE_OK)
{
NSLog(@"Here 1");
if(sqlite3_step(statement)==SQLITE_DONE)
{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Product Added" message:@"" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
[alert show];
}
else
{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Product Not Added" message:@"An error has occured" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
[alert show];
alert=nil;
}
}
// Release the compiled statement from memory
sqlite3_finalize(statement);
sqlite3_close(database);
}
}
调用上述代码后,我希望在表视图中看到一个新的单元格来表示新记录,但这并没有发生。有人知道我做错了什么吗?我在表视图的viewDidAppear方法中重新读取了数据库。
谢谢,
Jack
编辑:
在insertData方法和readProductsFromDatabase中执行NSLog输出数据库的路径会给出:
/Users/me/Library/Application Support/iPhone Simulator/5.0/Applications/4868830F-E02A-4A81-93AD-BEAF8B85971/Library/ProductDatabase.sql
您似乎正确地插入了数据,但随后您既没有重新查询数据,也没有告诉表视图用新数据重新加载自己。
在您的insertData
之后,我也会用您的新产品调用m_Products addObject:
以获得更好的性能(因为您知道已插入,所以您只是在更新SQLite数据库中对象的内存版本),或者如果您想进行完整的SQL重新查询,则调用readProductsFromDatabase
。
我不太确定是什么填充了m_AlphabetDictionary
,但我假设您可以在使用这两种方法中的任何一种之后找到如何更新它。
然后,要告诉您的表视图用新数据重新加载自己,请调用:
[tableView reloadData];