SQLite Tutorial - Selecting Data

Like any other applications you may have the need to store data in some kind of a database. With iPhone applications we can use SQLite for FREE. In the first part of this tutorial, I will show you how to do some basic operations using SQLite database.

• SQLite Tutorial - Displaying data in a UITableView.
• SQLite Tutorial - Deleting data from UITableView.
• SQLite Tutorial - Inserting data into SQLite database.
• SQLite Tutorial - Loading data as required.
• SQLite Tutorial - Updating data in SQLite database.
• SQLite Tutorial - Saving images in the database

Introduction
Like any other applications you may have the need to store data in some kind of a database. For iPhone applications, we can use SQLite for free. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Note: iPhone applications cannot work with remote databases.

In this tutorial, I will discuss how to create a new SQLite database and use it in a iPhone application.

This is how the application will look like
SQLite Manager
To use SQLite in your application, you do not have to install any new software on your mac. You can either use the command line to create a new SQL database or use SQLite Manager for Firefox add-on, which is what I use.

The screenshots below shows you the database schema used, which is very simple.

iPhone App
Create a new project by selecting Navigation-based application. The name of the project used for this tutorial is "SQL". We first need to add a library which understands how to communicate with the SQLite database. In Xcode select "Frameworks" folder and click on the Action dropdown and select Add -> Existing Frameworks and browse to /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.1.sdk/usr/lib and select libsqlite3.0.dylib file and it will be added in the "Frameworks" folder.

Now add the SQLite database file to the "Resources" folder in Xcode.

Next we need to create a data structure to hold the data from the database. Create a new class and name it "Coffee". This is how the class looks like.

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

@interface Coffee : NSObject {

NSInteger coffeeID;
NSString *coffeeName;
NSDecimalNumber *price;

//Intrnal variables to keep track of the state of the object.
BOOL isDirty;
BOOL isDetailViewHydrated;
}

@property (nonatomic, readonly) NSInteger coffeeID;
@property (nonatomic, copy) NSString *coffeeName;
@property (nonatomic, copy) NSDecimalNumber *price;

@property (nonatomic, readwrite) BOOL isDirty;
@property (nonatomic, readwrite) BOOL isDetailViewHydrated;

@end

Note that in the "Coffee" class the price variable is declared as "NSDecimalNumber" because in the database its corresponding column is of type "REAL". Note: Although SQLite does not enforce data type constraints, it is good practice to enforce data type constraints at the application level. The two boolean variables keep track of the state of the object. The boolean "isDirty" tells us if the object was changed in memory or not and "isDetailViewHydrated" tell us, if the data which shows up on the detail view is fetched from the database or not.

NOTE: It is good practice to fetch only the data required to show on the screen, which makes the application load faster. So in our case, we will only get the primary key and the name of the coffee from the database as the price is only shown in the detail view.

Synthesize the properties and release price and coffeeName in the dealloc method. This is how the implementation file of class "Coffee" looks like

#import "Coffee.h"

@implementation Coffee

@synthesize coffeeID, coffeeName, price, isDirty, isDetailViewHydrated;

- (void) dealloc {

[price release];
[coffeeName release];
[super dealloc];
}

@end

Copying the database
The first thing we need to do when the application loads is to check whether the user's phone has the database or not, if not then we copy it to the user's phone. We will create two methods which will help us in copying the database to the user's phone. This is how SQLAppDelegate header file will look like

#import <UIKit/UIKit.h>

@class Coffee;

@interface SQLAppDelegate : NSObject <UIApplicationDelegate> {

UIWindow *window;
UINavigationController *navigationController;

//To hold a list of Coffee objects
NSMutableArray *coffeeArray;
}

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

@property (nonatomic, retain) NSMutableArray *coffeeArray;

- (void) copyDatabaseIfNeeded;
- (NSString *) getDBPath;

@end

coffeeArray is the array used to hold all the "Coffee" objects. It is declared in the application delegate file and not anywhere else is because the object lives in memory as long as the application is running and it also gets a notification when the application is being terminated. The method copyDatabaseIfNeeded is used to copy the database on the user's phone when the application is finished launching. Another method used with "copyDatabaseIfNeeded" is "getDBPath" which gets the database location on the user's phone.

This is how "copyDatabaseIfNeeded" method looks like

- (void) copyDatabaseIfNeeded {

//Using NSFileManager we can perform many file system operations.
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSString *dbPath = [self getDBPath];
BOOL success = [fileManager fileExistsAtPath:dbPath];

if(!success) {

NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"SQL.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];

if (!success)
NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}

and this is how the getDBPath method looks like

- (NSString *) getDBPath {

//Search for standard documents using NSSearchPathForDirectoriesInDomains
//First Param = Searching the documents directory
//Second Param = Searching the Users directory and not the System
//Expand any tildes and identify home directories.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:@"SQL.sqlite"];
}

In "copyDatabaseIfNeeded" method we get the NSFileManager object with which we can perform some basic file management tasks. The method "getDBPath" gives us the database location on the user's phone. Method "NSSearchPathForDirectoriesInDomains" is used to find documents in the user's documents directory expanding the tildes so we get the whole path. Using the "fileManager" object we check if the database exists or not, if it doesn't exists then we copy it to the user's phone from the application bundle. Method "copyDatabaseIfNeeded" is called from "applicationDidFinishLaunching". Once the database is copied to the user's phone, we need to display a list of coffee's from the database on the UITableView.

Getting data from the database
The approach that I have taken here to get the data from the database is a little clean, as I have all my database operations in the Coffee Class. I have declared a class method in the "Coffee" class which is responsible to get the data from the database and fill the coffeeArray which is declared in the application delegate object (SQLAppDelegate).

This is how the header file of the "Coffee" class will look like after the method decelerations are added.

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

@interface Coffee : NSObject {

NSInteger coffeeID;
NSString *coffeeName;
NSDecimalNumber *price;

//Intrnal variables to keep track of the state of the object.
BOOL isDirty;
BOOL isDetailViewHydrated;
}

@property (nonatomic, readonly) NSInteger coffeeID;
@property (nonatomic, copy) NSString *coffeeName;
@property (nonatomic, copy) NSDecimalNumber *price;

@property (nonatomic, readwrite) BOOL isDetailViewHydrated;

//Static methods.
+ (void) getInitialDataToDisplay:(NSString *)dbPath;
+ (void) finalizeStatements;

//Instance methods.
- (id) initWithPrimaryKey:(NSInteger)pk;

@end

The method "getInitialDataToDisplay" gets the data from the database and creates "Coffee" objects using "initWithPrimaryKey" method and fills the objects in the coffeeArray which is declared in SQLAppDelegate.

This is how the "getInitialDataToDisplay" method looks like

+ (void) getInitialDataToDisplay:(NSString *)dbPath {

SQLAppDelegate *appDelegate = (SQLAppDelegate *)[[UIApplication sharedApplication] delegate];

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

const char *sql = "select coffeeID, coffeeName from coffee";
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

while(sqlite3_step(selectstmt) == SQLITE_ROW) {

NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);
Coffee *coffeeObj = [[Coffee alloc] initWithPrimaryKey:primaryKey];
coffeeObj.coffeeName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];

coffeeObj.isDirty = NO;

[appDelegate.coffeeArray addObject:coffeeObj];
[coffeeObj release];
}
}
}
else
sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
}

initWithPrimaryKey method is very simple and it looks like this

- (id) initWithPrimaryKey:(NSInteger) pk {

[super init];
coffeeID = pk;

isDetailViewHydrated = NO;

return self;
}

In "getInitialDataToDisplay" method we first get a reference to the application delegate because that is where the coffeeArray is declared. We open the database using sqlite3_open method which takes the database path and a database object. The database object is declared as static in the "Coffee.m" file.

#import "Coffee.h"

static sqlite3 *database = nil;

@implementation Coffee
...

After we open the connection to the database we create a select statement and if that returns "SQLITE_OK" we execute the select statement using sqlite3_step method, which will return "SQLITE_ROW" if the operation is a success and it has one or more rows to return. To get a full list of return codes click here. We then get the coffeeID, coffeename and create "Coffee" objects using "initWithPrimaryKey" method. When reading data from the database, the column index starts from zero instead of one. The coffee object is added to the array and we do this for n number of rows. If the connection to the database fails, only then we close the connection and release any resources associated with the connection object. The static database connection is left open to be used by the "Coffee" class.

We close the database connection in the "finalizeStatements" method which is called from "applicationWillTerminate" which is in SQLAppDelegate.m file.

+ (void) finalizeStatements {

if(database) sqlite3_close(database);
}

Now all we have to do is call the right methods from "applicationDidFinishLaunching" method in SQLAppDelegate.m file. This is how the code looks like

- (void)applicationDidFinishLaunching:(UIApplication *)application {

//Copy database to the user's phone if needed.
[self copyDatabaseIfNeeded];

//Initialize the coffee array.
NSMutableArray *tempArray = [[NSMutableArray alloc] init];
self.coffeeArray = tempArray;
[tempArray release];

//Once the db is copied, get the initial data to display on the screen.
[Coffee getInitialDataToDisplay:[self getDBPath]];

// Configure and show the window
[window addSubview:[navigationController view]];
[window makeKeyAndVisible];
}

To recap, we first copy the database to the user's phone if it does not exists, then we initialize the coffee array and get the initial data to display on the UITableView.

Display data in the UITableView
Since the Coffee Array is in the application delegate file (SQLAppDelegate), for simplicity I' am going to add the "SQLAppDelegate.h" header file in "SQL_Prefix.pch" file so that the file is added to all the files in the project. This is how the file should look like

#ifdef __OBJC__
#import <Foundation/Foundation.h>
#import <UIKit/UIKit.h>
#import "SQLAppDelegate.h"
#endif

Open "RootViewController.h" file and create a variable of type SQLAppDelegate like this

#import <UIKit/UIKit.h>

@class Coffee;

@interface RootViewController : UITableViewController {

SQLAppDelegate *appDelegate;
}

@end

We are able to do the above because "SQLAppDelegate.h" file is added as a header file to all the files in the project.

Open "RootViewController.m" and import "Coffee.h" file, change the "viewDidLoad" method like this

- (void)viewDidLoad {
[super viewDidLoad];

self.navigationItem.rightBarButtonItem = self.editButtonItem;

appDelegate = (SQLAppDelegate *)[[UIApplication sharedApplication] delegate];

self.title = @"Coffee List";
}

We get a reference to the application delegate in the second line and everything is self explanatory. Using the appDelegate we can access the array.

Now all we have to do is tell the table view how many rows to expect and set the text property of the UITableViewCell which is returned in "cellForRowAtIndexPath" method.

Method "numberOfRowsInSection" returns the number of rows UITableView should expect in a section. By default "numberOfSectionsInTableView" returns 1, so the UITableView looks like a regular table.

Below is a code snippet of "numberOfRowsInSection"

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

and cellForRowAtIndexPath

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

static NSString *CellIdentifier = @"Cell";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero reuseIdentifier:CellIdentifier] autorelease];
}

//Get the object from the array.
Coffee *coffeeObj = [appDelegate.coffeeArray objectAtIndex:indexPath.row];

//Set the coffename.
cell.text = coffeeObj.coffeeName;

// Set up the cell
return cell;
}

cellForRowAtIndexPat is called n number of times, where n is the total number of objects in the coffeeArray which is returned in "numberOfRowsInSection" method. We get the Coffee object from the array using "objectAtIndex" method and set the coffeeName as the text of the UITableViewCell.

Conclusion
This concludes part one of the SQLite Tuturial, where we learn how easy it is to manage databases using SQLite Manager and using it in your iPhone applications. Overall SQLite is FREE for all to use. You also learn how to copy database to the user's phone and perform select operations on the database.

The next tutorial will show you how to delete data.

Happy Programming,
iPhone SDK Articles

---

Attachments

• Download the source code used in this article

Suggested Readings

• SQLite Tutorial - Deleting data
• SQLite Tutorial - Inserting data into SQLite database
• SQLite Tutorial - Saving images in the SQLite database

• SQLite Tutorial - Displaying data in a UITableView.
• SQLite Tutorial - Deleting data from UITableView.
• SQLite Tutorial - Inserting data into SQLite database.
• SQLite Tutorial - Loading data as required.
• SQLite Tutorial - Updating data in SQLite database.
• SQLite Tutorial - Saving images in the database