SQLite Tutorial for iOS: Making Our App

App Showing Failed Bank Data from SQLite DB

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