SQLite Tutorial for iOS: Making Our App

This is the second part of a two-part series where we show how to make an app that displays a list of failed US banks from a SQLite database.

In the first part of the series, we talked about what SQLite is and why we’d want to use it, how to use the sqlite3 command-line utility, and how to import data programatically via Python.

This time, we will cover how to make an iPhone app that reads data from the SQLite database we created, and display data in a table view and a drill down detail view.

Reading the SQLite Data

Start by creating a new project in XCode using the Window-based Application template, and name the project FailedBanks.

Then let’s link in the sqlite3 framework. Right click on Frameworks, click “Add\Existing Frameworks…”, and select “libsqlite3.dylib” from the dropdown.

Next, add in the database file that we created last time. Right click on Resources, click “Add\Existing Files…”, browse to where your banklist.sqlite3 file is saved, make sure “Copy items to destination group’s folder (if needed)” is checked, and click Add.

We’re going to have two screens for our app. The first will show a list of banks in a table view. Tapping on a bank in the table view should bring up another view with details on that bank.

Rather than retrieving all of the data from the database, we’re just going to pull out the specific subset we need to display the first table view to save memory. So let’s create a class to store this:

Click on Classes and click “File\New File…”, choose “Cocoa Touch Class” on the side, pick “Objective-C class”, select “Subclass of NSObject”, and click “Next”. Name the class “FailedBankInfo.”

Replace FailedBankInfo.h with the following:

#import <Foundation/Foundation.h>

@interface FailedBankInfo : NSObject {
    int _uniqueId;
    NSString *_name;
    NSString *_city;
    NSString *_state;
}

@property (nonatomic, assign) int uniqueId;
@property (nonatomic, copy) NSString *name;
@property (nonatomic, copy) NSString *city;
@property (nonatomic, copy) NSString *state;

- (id)initWithUniqueId:(int)uniqueId name:(NSString *)name city:(NSString *)city 
    state:(NSString *)state;

@end

And replace FailedBankInfo.m with the following:

#import "FailedBankInfo.h"

@implementation FailedBankInfo

@synthesize uniqueId = _uniqueId;
@synthesize name = _name;
@synthesize city = _city;
@synthesize state = _state;

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

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

@end

This is pretty standard Objective-C – there should be no surprises here. We’re just creating a class to store the few pieces of data we’ll be displaying in our table view, and make a convenience constructor.

Next we’re going to create a helper class to handle all of the interaction with our sqlite3 database. This is good practice because by keeping everything abstracted, it makes it easier to switch to another storage method if we wanted to in the future.

So make another subclass of NSObject like you did above, but name it FailedBankDatabase.h. Replace FailedBankDatabase.h with the following:

#import <Foundation/Foundation.h>
#import <sqlite3.h>

@interface FailedBankDatabase : NSObject {
    sqlite3 *_database;
}

+ (FailedBankDatabase*)database;
- (NSArray *)failedBankInfos;

@end

Here we include the header file for sqlite3 at the top, and keep a member variable to store the pointer to our SQLite database. We also declare a static function to return the singleton instance of our FailedBankDatabase object, and declare a method to return an array of all of the FailedBankInfos from our database.

Erase everything in FailedBankDatabase.m and add the following to the top:

#import "FailedBankDatabase.h"
#import "FailedBankInfo.h"

@implementation FailedBankDatabase

static FailedBankDatabase *_database;

+ (FailedBankDatabase*)database {
    if (_database == nil) {
        _database = [[FailedBankDatabase alloc] init];
    }
    return _database;
}

First we import our header files, then we add the standard code to create a singleton instance of FailedBankDatabase for ease of access.

Add the following next:

- (id)init {
    if ((self = [super init])) {
        NSString *sqLiteDb = [[NSBundle mainBundle] pathForResource:@"banklist" 
            ofType:@"sqlite3"];
        
        if (sqlite3_open([sqLiteDb UTF8String], &_database) != SQLITE_OK) {
            NSLog(@"Failed to open database!");
        }
    }
    return self;
}

- (void)dealloc {
    sqlite3_close(_database);
    [super dealloc];
}

When we initialize our object, we construct a path to our database file. We’re storing the database in our application’s bundle, so we use the pathForResource method to obtain the path.

Note that since the database is stored in our main bundle, that means we couldn’t write to the database. This is fine for this app, but if you have an app that you need to both read and write to the database, check out my GDataXML tutorial for an example of how to save your bundled data to the documents directory for editing.

Once we have the path to the database, we open it up with the sqlite3_open API call. It will return an error if anything goes wrong – otherwise we’re good to go! Note that when we’re done we should close the database handle with sqlite3_close – I put that in the dealloc method.

Now for the fun part – retrieving the data from the database!

- (NSArray *)failedBankInfos {
    
    NSMutableArray *retval = [[[NSMutableArray alloc] init] autorelease];
    NSString *query = @"SELECT id, name, city, state FROM failed_banks 
        ORDER BY close_date DESC";
    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);
            NSString *name = [[NSString alloc] initWithUTF8String:nameChars];
            NSString *city = [[NSString alloc] initWithUTF8String:cityChars];
            NSString *state = [[NSString alloc] initWithUTF8String:stateChars];
            FailedBankInfo *info = [[FailedBankInfo alloc] 
                initWithUniqueId:uniqueId name:name city:city state:state];                        
            [retval addObject:info];
            [name release];
            [city release];
            [state release];
            [info release];
        }
        sqlite3_finalize(statement);
    }
    return retval;
    
}
@end

Here we construct our SQL string, and execute it with the sqlite3_prepare_v2 API call. We then step through each row, and pull out the return values one by one. We have to do a little conversion here to get the data from UTF8 strings into NSStrings, then we construct FailedBankInfo objects based on the data and add it to our array.

We have to call sqlite3_finalize to clean up the memory used for the statement, then we return the data.

So let’s see if this works. Open up FailedBanksAppDelegate.m and add the following imports to the top of the file:

#import "FailedBankDatabase.h"
#import "FailedBankInfo.h"

Then add the following inside applicationDidFinishLaunching:

NSArray *failedBankInfos = [FailedBankDatabase database].failedBankInfos;
for (FailedBankInfo *info in failedBankInfos) {
    NSLog(@"%d: %@, %@, %@", info.uniqueId, info.name, info.city, info.state);
}

If all goes well, you should see lines like the following in your deubug log:

1: Desert Hills Bank, Phoenix, AZ
2: Unity National Bank, Cartersville, GA
3: Key West Bank, Key West, FL

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:

Gimme the Code!

Here’s a a project file with all of the code we’ve developed in this SQLite tutorial so far.

What’s Next?

With this as a foundation, you should feel quite comfortable using SQLite in your projects. I may write some articles about Core Data and its advantages over SQLite in the future.

in the meantime, I’d like to hear from you! How have you used SQLite in your projects, if at all? Which do you prefer between SQLite and Core Data, and why?