Retrieve all rows inserted in sqlite database and

问题:

I am newbie to objective-c and sqlite.I have inserted data in to table using sqlite successfully.My project contains 2 pages

Add Reminder page:Where user enters data and clicks save which is the right navigation bar

View Reminder page:This is where the user views the saved reminder in cells of table view,

i.e. 1st reminder on 1st cell,2nd reminder on 2nd cell and so on...

I have made my task much easier to specify the number of sections using the following code,means the number of saved reminders is equal to number of sections in view reminder table

-(NSInteger)numberOfSectionsInTableView:(UITableView *)view
{    
    numTableRecords = -1;
    sqlite3_stmt *statement;
    if (sqlite3_open([self.databasePath UTF8String], &remindersDB) == SQLITE_OK) 
    {
        NSString *sqlStatement = [NSString stringWithFormat: @"select count(*) from reminders"];
        const char *sql = [sqlStatement cStringUsingEncoding:NSUTF8StringEncoding];
        if(sqlite3_prepare_v2(remindersDB, sql, -1, &statement, NULL) == SQLITE_OK) 
        {          
            while(sqlite3_step(statement) == SQLITE_ROW) 
            {
                numTableRecords = sqlite3_column_int(statement, 0);
            }
        }
        else 
        {
            printf("could not prepare statement: %s\n", sqlite3_errmsg(remindersDB));
        }
    }

    else 
    {
        NSLog(@"Error in Opening Database File");
    }
    sqlite3_close(remindersDB);
    NSLog(@"Number of records = %d",numTableRecords);
    return numTableRecords; 

}

Based on suggestion given in the following link,pretty much similar to my requirement:

Populate the table view sections with rows of table in sqlite database in an order

I took an array and inserted the retrieved data as objects to the array as shown in the following:

-(void)viewWillAppear:(BOOL)animated
{

    //Retrieve the values of database
    const char *dbpath = [self.databasePath UTF8String];
    sqlite3_stmt *statement;
    if (sqlite3_open(dbpath, &remindersDB) == SQLITE_OK)
    {
        NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM reminders"];
        NSLog(@"Data = %@",querySQL);

        const char *query_stmt = [querySQL UTF8String];

        if (sqlite3_prepare_v2(self.remindersDB ,query_stmt , -1, &statement, NULL) == SQLITE_OK)
        {
            if (sqlite3_step(statement) == SQLITE_ROW)
            {
                ID = [[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)]autorelease];

                nameField = [[[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)]autorelease];                    

                eventField = [[[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)]autorelease];

                dateField = [[[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)]autorelease];
            } 

            sqlite3_finalize(statement);
        }
        sqlite3_close(self.remindersDB);
    }
    [array addObject:ID];
    [array addObject:nameField];
    [array addObject:eventField];
    [array addObject:dateField];
    [self.theTable reloadData];
    [super viewWillAppear:YES];
}

Now in (UITableViewCell)-cell for row at index path I took 4 labels and added them as subviews and assigned the [array objectAtIndex:indexPath.row] to label.text,i.e.:

 - (UITableViewCell *)tableView:(UITableView *)view cellForRowAtIndexPath:(NSIndexPath *)indexPath
{

    //NSString *CellId = [NSString stringWithFormat:@"S%1dR%1d",indexPath.section,indexPath.row];

    UITableViewCell *cell = (UITableViewCell *)[view dequeueReusableCellWithIdentifier:nil];

    if (cell == nil)
    {
        cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleValue1 reuseIdentifier:nil] autorelease];
        view.backgroundColor = [UIColor clearColor];
        cell.backgroundColor = [[UIColor alloc]initWithPatternImage:[UIImage imageNamed:@"reminderbutton.png"]];

        label1 = [[[UILabel alloc]initWithFrame:CGRectMake(26, 3, 30, 40)]autorelease];
        label1.backgroundColor = [UIColor clearColor];
        label1.textColor = [UIColor whiteColor];

        label2 = [[[UILabel alloc]initWithFrame:CGRectMake(45, 3, 100, 40)]autorelease];
        label2.backgroundColor = [UIColor clearColor];
        label2.textColor = [UIColor whiteColor];

        label3 = [[[UILabel alloc]initWithFrame:CGRectMake(119, 3, 100, 40)]autorelease];
        label3.backgroundColor = [UIColor clearColor];
        label3.textColor = [UIColor whiteColor];

        label4 = [[[UILabel alloc]initWithFrame:CGRectMake(198, 3, 120, 40)]autorelease];
        label4.backgroundColor = [UIColor clearColor];
        label4.textColor = [UIColor whiteColor];

    }
 label1.text = [array objectAtIndex:indexPath.row];
    label2.text = [array objectAtIndex:indexPath.row];
    label3.text = [array objectAtIndex:indexPath.row];
    label4.text = [array objectAtIndex:indexPath.row];

    [cell addSubview:label1];
    [cell addSubview:label2];
    [cell addSubview:label3];
    [cell addSubview:label4];

    return cell;
}

Nothing is getting displayed on cells,please help me to fix this issue.Struggling very bad for this,as I am new to objective-c,I would be very happy if any one comes up with detailed explanation and solution.Sorry for the question if it sounds stupid

Thanks all in advance :)

回答1:

I understand what your trying to do now.

I would recommend to create a class to hold the data for each reminder, then you can have an array of the reminder data.

So I would add a new Class to your project, call it Reminder, then add a property for each of your fields.

Your reminder class would be as follows:

Reminder.h

#import <Foundation/Foundation.h>

@interface Reminder : NSObject

@property (nonatomic, copy) NSString *reminderId; 
@property (nonatomic, copy) NSString *name;
@property (nonatomic, copy) NSString *event;
@property (nonatomic, copy) NSString *date;

@end

and your reminder.m file would be:

#import "Reminder.h"

@implementation Reminder
@synthesize reminderId, name, event, date;

-(void)dealloc
{

    self.reminderId = nil;
    self.name = nil;
    self.event = nil;
    self.date = nil;
}

@end

So this class will simply hold the data for each of your reminders.

Then in your view controller which shows the list of the reminders you need to add an array property to hold the reminder objects.

So add the following property to your ViewController which shows the list of reminders.

@property (nonatomic, retain) NSMutableArray *reminders;

make sure you also synthesize this in your view controllers .m file, to do this add this line of code under the @implementation line in your view controller .m file:

@synthesize reminders;

Also remember to set the reminders property to nil in your dealloc method to ensure proper memory cleanup:

self.reminders = nil;

We will be using the Reminder class we created in the view controller so you need to also import your class, so in the view controller .m file add an import statement near the top of the file as follows:

#import "Reminder.h"

Now your view controller is setup and ready to use the reminder class we created.

You then need to add a method into your view controller that will be used to load your data from the database and populate the reminder objects and the reminders array.

Add a new method to your view controller, call it something like loadReminders

The implementation for this method will be:

-(void)loadReminders
{

    // setup the reminders array
    self.reminders = nil;
    self.reminders = [[[NSMutableArray alloc] init] autorelease];

    //Retrieve the values of database
    const char *dbpath = [self.databasePath UTF8String];
    sqlite3_stmt *statement;
    if (sqlite3_open(dbpath, &remindersDB) == SQLITE_OK)
    {
        NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM reminders"];
        NSLog(@"Data = %@",querySQL);

        const char *query_stmt = [querySQL UTF8String];

        if (sqlite3_prepare_v2(self.remindersDB ,query_stmt , -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                Reminder *loadedReminder = [[Reminder alloc] init];

                loadedReminder.reminderId = [[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)]autorelease];

                loadedReminder.name = [[[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)]autorelease];                    

                loadedReminder.event = [[[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)]autorelease];

                loadedReminder.date = [[[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)]autorelease];

                [self.reminders addObject:loadedReminder];
                [loadedReminder release];
            } 

            sqlite3_finalize(statement);
        }
        sqlite3_close(self.remindersDB);
    }


}

So what this method is doing is loading the rows from the database, and for each row in the database it is creating a new Reminder object, setting the properties using the data from the row, then adding this reminder object to the reminders array.

notice the change I have made from your original code:

while (sqlite3_step(statement) == SQLITE_ROW)

the while statement will loop round and fetch each row from the database.

You would call this new method (loadReminders) whenever you want to refresh the list of data, so anywhere in your code where you are currently reloading the table, call this method before you reload the table, this will ensure your array of reminders is setup ready for the table to use when reloading. So in your example above, I would replace your viewWillAppear method to be:

-(void)viewWillAppear:(BOOL)animated
{
    [self loadReminders];
    [self.theTable reloadData];
}

Now all the data is setup and ready to use. And using the reminder object should make the code in your tableView much easier to read.

So to get your data into the table, replace your current tableview methods with the following.

-(NSInteger)numberOfSectionsInTableView:(UITableView *)view
{
     return [self.reminders count];
}

This will tell the table to create a section for each reminder in the reminders array.

Now from your description (and link) what you are doing is creating 1 cell in each section. So add the following method to tell the tableView we want 1 row for each section

-(NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    return 1;
}

Right now for the cellForRowAtIndexPath, replace your current method with the following:

- (UITableViewCell *)tableView:(UITableView *)view cellForRowAtIndexPath:(NSIndexPath *)indexPath
{

    Reminder *reminderToDisplay;
    reminderToDisplay = [self.reminders objectAtIndex:indexPath.section];

    // Now create the cell to display the reminder data:

    cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleValue1 reuseIdentifier:nil] autorelease];
    view.backgroundColor = [UIColor clearColor];
    cell.backgroundColor = [[UIColor alloc]initWithPatternImage:[UIImage imageNamed:@"reminderbutton.png"]];

    label1 = [[[UILabel alloc]initWithFrame:CGRectMake(26, 3, 30, 40)]autorelease];
    label1.backgroundColor = [UIColor clearColor];
    label1.textColor = [UIColor whiteColor];

    label2 = [[[UILabel alloc]initWithFrame:CGRectMake(45, 3, 100, 40)]autorelease];
    label2.backgroundColor = [UIColor clearColor];
    label2.textColor = [UIColor whiteColor];

    label3 = [[[UILabel alloc]initWithFrame:CGRectMake(119, 3, 100, 40)]autorelease];
    label3.backgroundColor = [UIColor clearColor];
    label3.textColor = [UIColor whiteColor];

    label4 = [[[UILabel alloc]initWithFrame:CGRectMake(198, 3, 120, 40)]autorelease];
    label4.backgroundColor = [UIColor clearColor];
    label4.textColor = [UIColor whiteColor];


    // Now set the labels using our reminder object
    label1.text = reminderToDisplay.reminderId;
    label2.text = reminderToDisplay.name;
    label3.text = reminderToDisplay.event;
    label4.text = reminderToDisplay.date;


    // now add the labels to the cell
    cell.contentView = [[UIView alloc] init] autorelease]
    [cell.contentView addSubview:label1];
    [cell.contentView addSubview:label2];
    [cell.contentView addSubview:label3];
    [cell.contentView addSubview:label4];

    return cell;

}

So whats happening in this method, is first we are getting the reminder for the current section out of the reminders array:

Reminder *reminderToDisplay;
reminderToDisplay = [self.reminders objectAtIndex:indexPath.section];

The next few lines should be familiar, we are creating the cell, and the labels. Then we are using the reminder object to set the labels text.

The reason your existing solution was not working was because you was using indexPath.Row, and because you are using a section for each reminder the indexPath.Row will always be 0, as you only ever have 1 row in each section.

I know its a long post, but hopefully should help you understand what is happening. Any questions then let me know

回答2:

You should really create a Custom UITableViewCell.

But for a quick fix answer you could replace the bottom [cell addSubview] lines with the following

cell.contentView = [[UIView alloc] init] autorelease]
[cell.contentView addSubview:label1];
[cell.contentView addSubview:label2];
[cell.contentView addSubview:label3];
[cell.contentView addSubview:label4];