SQLite Tutorial for iOS: Making Our App

Creating a Table View

Now that we have the code to read in our data set, it’s pretty easy to create a table view to display the data.

Right click on Classes and click “Add\New File…” and pick “UIViewController subclass”, making sure “UITableVIewController subclass” is checked and “With XIB for user interface” is NOT checked. Name the class FailedBanksListViewController.

Open up FailedBanksListViewController.h and add a member variable/property for the failedBankInfos which we’ll retrieve from the database. When you’re done it should look like the following:

#import <UIKit/UIKit.h>

@interface FailedBanksListViewController : UITableViewController {
    NSArray *_failedBankInfos;
}

@property (nonatomic, retain) NSArray *failedBankInfos;

@end

Switch over to FailedBanksListViewController.m and add some imports, your synthesize statement, and your cleanup code:

// At very top, in import section
#import "FailedBankDatabase.h"
#import "FailedBankInfo.h"

// At top, under @implementation
@synthesize failedBankInfos = _failedBankInfos;

// In dealloc
self.failedBankInfos = nil;

Then uncomment viewDidLoad and modify it to look like the following:

- (void)viewDidLoad {
    [super viewDidLoad];
    self.failedBankInfos = [FailedBankDatabase database].failedBankInfos;
    self.title = @"Failed Banks";
}

Make a slight tweak to numberOfRowsInSection to return the number of items in the array:

- (NSInteger)tableView:(UITableView *)tableView 
    numberOfRowsInSection:(NSInteger)section {
    return [_failedBankInfos count];
}

Finally modify cellForRowAtIndexPath to look like the following:

- (UITableViewCell *)tableView:(UITableView *)tableView 
    cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    
    static NSString *CellIdentifier = @"Cell";
    
    UITableViewCell *cell = 
        [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
    if (cell == nil) {
        cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle 
            reuseIdentifier:CellIdentifier] autorelease];
    }
    
    // Set up the cell...
    FailedBankInfo *info = [_failedBankInfos objectAtIndex:indexPath.row];
    cell.textLabel.text = info.name;
    cell.detailTextLabel.text = [NSString stringWithFormat:@"%@, %@", 
        info.city, info.state];
	
    return cell;
}

All we did here was set the style of the table view cell to be the subtitle style, get the object in the array corresponding to the current row, and set the title and subtitle on the cell appropriately.

Now, we just need to hook our table view into the app. What we want to do to add a navigation controller to our app, and have the root view controller be our FailedBanksListViewController. So first, let’s add an outlet into FailedBanksAppDelegate.h for the UINavigationController we’re about to add:

#import <UIKit/UIKit.h>

@interface FailedBanksAppDelegate : NSObject <UIApplicationDelegate> {
    UIWindow *window;
    UINavigationController *_navController;
}

@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain) IBOutlet UINavigationController *navController;

@end

Open up Resources and double click on MainWindow.xib. Drag a Navigation Controller from the library into the MainWindow.xib. Click on the down arrow on the Navigation Controller that you just added, click on the View Controller, over in the attribute panel switch to the fourth tab, and switch the “Class” to “FailedBanksListViewController.”

Finally, control-drag from “Failed Banks App Delegate” in MainWindow.xib to “Navigation Controller”, and connect it to the “navController” outlet. Save the xib and close.

Now all we need to do is add a few lines to our FailedBanksAppDelegate.m:

// Under @implementation
@synthesize navController = _navController;
// In applicationDisFinishLaunching, before makeKeyAndVisible:
[window addSubview:_navController.view];
// In dealloc
self.navController = nil;

If all goes well, you should be able to compile your app and see the following:

Adding a Detail View

Now, let’s extend the app so that when you tap a particular row, it loads up the details for that row in a second view controller.

First, we’re going to need another model class to store ALL of the information for a failed bank row, rather than our info class which just held a subset. Create a new subclass of NSObject named FailedBankDetails. Replace FailedBankDetails.h with the following:

#import <Foundation/Foundation.h>

@interface FailedBankDetails : NSObject {
    int _uniqueId;
    NSString *_name;
    NSString *_city;
    NSString *_state;
    int _zip;
    NSDate *_closeDate;
    NSDate *_updatedDate;
}

@property (nonatomic, assign) int uniqueId;
@property (nonatomic, copy) NSString *name;
@property (nonatomic, copy) NSString *city;
@property (nonatomic, copy) NSString *state;
@property (nonatomic, assign) int zip;
@property (nonatomic, retain) NSDate *closeDate;
@property (nonatomic, retain) NSDate *updatedDate;

- (id)initWithUniqueId:(int)uniqueId name:(NSString *)name city:(NSString *)city 
    state:(NSString *)state zip:(int)zip closeDate:(NSDate *)closeDate 
    updatedDate:(NSDate *)updatedDate;

@end

And replace FailedBankDetails.m with the following:

#import "FailedBankDetails.h"

@implementation FailedBankDetails
@synthesize uniqueId = _uniqueId;
@synthesize name = _name;
@synthesize city = _city;
@synthesize state = _state;
@synthesize zip = _zip;
@synthesize closeDate = _closeDate;
@synthesize updatedDate = _updatedDate;

- (id)initWithUniqueId:(int)uniqueId name:(NSString *)name 
    city:(NSString *)city state:(NSString *)state zip:(int)zip closeDate:(NSDate *)closeDate 
    updatedDate:(NSDate *)updatedDate {
    if ((self = [super init])) {
        self.uniqueId = uniqueId;
        self.name = name;
        self.city = city;
        self.state = state;
        self.zip = zip;
        self.closeDate = closeDate;
        self.updatedDate = updatedDate;
    }
    return self;
}

- (void) dealloc
{
    self.name = nil;
    self.city = nil;
    self.state = nil;
    self.closeDate = nil;
    self.updatedDate = nil;
    [super dealloc];
}

@end

Again, nothing particularly interesting here – just an object to hold our information.

Next, let’s add a new function inside our FailedBankDatabase to retrieve the FailedBankDetails for a particular uniqueId. Add the following to FailedBankDatabase.h:

// Before the @interface delc
@class FailedBankDetails;
// After the failedBankInfos decl
- (FailedBankDetails *)failedBankDetails:(int)uniqueId;

Then add the following code into FailedBankDatabase.m:

// In the #import section
#import "FailedBankDetails.h"

// Anywhere inside the @implementation
- (FailedBankDetails *)failedBankDetails:(int)uniqueId {
    FailedBankDetails *retval = nil;
    NSString *query = [NSString stringWithFormat:@"SELECT id, name, city, state, 
        zip, close_date, updated_date FROM failed_banks WHERE id=%d", uniqueId];
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(_database, [query UTF8String], -1, &statement, nil) 
        == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            int uniqueId = sqlite3_column_int(statement, 0);
            char *nameChars = (char *) sqlite3_column_text(statement, 1);
            char *cityChars = (char *) sqlite3_column_text(statement, 2);
            char *stateChars = (char *) sqlite3_column_text(statement, 3);
            int zip = sqlite3_column_int(statement, 4);          
            char *closeDateChars = (char *) sqlite3_column_text(statement, 5);
            char *updatedDateChars = (char *) sqlite3_column_text(statement, 6);
            NSString *name = [[NSString alloc] initWithUTF8String:nameChars];
            NSString *city = [[NSString alloc] initWithUTF8String:cityChars];
            NSString *state = [[NSString alloc] initWithUTF8String:stateChars];
            NSString *closeDateString =
                [[NSString alloc] initWithUTF8String:closeDateChars];
            NSString *updatedDateString = 
                [[NSString alloc] initWithUTF8String:updatedDateChars];            
            NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
            [formatter setDateFormat:@"yyyy-MM-dd hh:mm:ss"];
            NSDate *closeDate = [formatter dateFromString:closeDateString];
            NSDate *updateDate = [formatter dateFromString:updatedDateString];
            
            retval = [[[FailedBankDetails alloc] initWithUniqueId:uniqueId name:name 
                city:city state:state zip:zip closeDate:closeDate 
                updatedDate:updateDate] autorelease];
            
            [name release];
            [city release];
            [state release];
            [closeDateString release];
            [updatedDateString release];
            [formatter release];            
            break;            
        }
        sqlite3_finalize(statement);
    }
    return retval;
}

This is very similar to failedBankInfos, except our SQL statement is modified to just get one particular ID, and we pull out all of the fields. Notice we stil specify the fields to pull rather than doing a SELECT *, so that if we ever update our database and add another column, it won’t break our code.

Also note that we use NSDateFormatter to convert from our date string values to NSDates. See the date format patterns reference for more info here.

Once that is done, right click on Classes and click “Add\New File…” and pick “UIViewController subclass”, making sure “UITableVIewController subclass” is NOT checked and “With XIB for user interface” IS checked. Name the class FailedBanksDetailViewController.

Then modify FailedBanksDetailViewController.h to look like the following:

#import <UIKit/UIKit.h>

@interface FailedBanksDetailViewController : UIViewController {
    UILabel *_nameLabel;
    UILabel *_cityLabel;
    UILabel *_stateLabel;
    UILabel *_zipLabel;
    UILabel *_closedLabel;
    UILabel *_updatedLabel;
    int _uniqueId;
}

@property (nonatomic, retain) IBOutlet UILabel *nameLabel;
@property (nonatomic, retain) IBOutlet UILabel *cityLabel;
@property (nonatomic, retain) IBOutlet UILabel *stateLabel;
@property (nonatomic, retain) IBOutlet UILabel *zipLabel;
@property (nonatomic, retain) IBOutlet UILabel *closedLabel;
@property (nonatomic, retain) IBOutlet UILabel *updatedLabel;
@property (nonatomic, assign) int uniqueId;

@end

Then open up FailedBanksDetailViewController and drag a bunch of labels out so they look like the following. By the way, I find it helpful to turn on “TopBar\Navigation Bar” in the Attributes Inspector for the view to see better the actual screen space available.

Then control-drag from File’s Owner to each of the labels on the right, connecting the nameLabel, cityLabel, etc. to the appropriate positions. When you’re done, save and close the XIB.

Then open up FailedBanksDetailViewController.m and finish up your properties:

// In the #import section
#import "FailedBankDatabase.h"
#import "FailedBankDetails.h"
// In the @implementation section
@synthesize nameLabel = _nameLabel;
@synthesize cityLabel = _cityLabel;
@synthesize stateLabel = _stateLabel;
@synthesize zipLabel = _zipLabel;
@synthesize closedLabel = _closedLabel;
@synthesize updatedLabel = _updatedLabel;
@synthesize uniqueId = _uniqueId;
// In the dealloc section AND the viewDidUnload section
self.nameLabel = nil;
self.cityLabel = nil;
self.stateLabel = nil;
self.zipLabel = nil;
self.closedLabel = nil;
self.updatedLabel = nil;

Next, add a viewWillAppear method to look up the entry in the database and set the labels appropriately:

- (void)viewWillAppear:(BOOL)animated {
    FailedBankDetails *details = [[FailedBankDatabase database] 
        failedBankDetails:_uniqueId];
    if (details != nil) {
        [_nameLabel setText:details.name];
        [_cityLabel setText:details.city];
        [_stateLabel setText:details.state];
        [_zipLabel setText:[NSString stringWithFormat:@"%d", details.name]];
        NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
        [formatter setDateFormat:@"MMMM dd, yyyy"];
        [_closedLabel setText:[formatter stringFromDate:details.closeDate]];
        [_updatedLabel setText:[formatter stringFromDate:details.updatedDate]];        
    }
}

The only thing left is to add in the code inside the table view controller to push this view controller onto the stack when a row gets selected.

Modify FailedBanksListViewController.h like the following:

// Before the @interface
@class FailedBanksDetailViewController;

// Inside the @interface
FailedBanksDetailViewController *_details;

// After the @interface
@property (nonatomic, retain) FailedBanksDetailViewController *details;

Then modify FailedBanksListViewController.m like the following:

// In the import section


// After the @implementation
@synthesize details = _details;

// In viewDidUnload AND dealloc
self.details = nil;

And finally, modify the didSelectRowAtIndexPath like the following:

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
    if (self.details == nil) {
        self.details = [[[FailedBanksDetailViewController alloc] initWithNibName:@"FailedBanksDetailViewController" bundle:nil] autorelease];        
    }
    FailedBankInfo *info = [_failedBankInfos objectAtIndex:indexPath.row];
    _details.uniqueId = info.uniqueId;
    [self.navigationController pushViewController:_details animated:YES];
}

If all goes well, you should see the following: