iPhone SDK Tutorial: Reading data from a SQLite Database

lyd2004888

　　http://dblog.com.au/iphone-development-tutorials/iphone-sdk-tutorial-reading-data-from-a-sqlite-database/
　　I see many people asking for SQLite tutorials around, and since I am
using SQLite for the next part in the Advanced RSS Reader Tutorial
, I thought I would
write up a quick tutorial on using SQLite with the iPhone SDK.

1. Project Requirements
　　I suggest that you have at least a basic understanding of SQLite,
writing SQL statements, the XCode interface and using the terminal in
OSX. If you don’t know anything about any of these topics then this
tutorial probably isn’t for you.

2. Creating our SQLite database for our tutorial
　　We first need to create a database for use with our application. For
the purposes of this tutorial we will be building a database of animals
along with a little information on them and a picture.
　　Fire up a new Terminal window and make a new folder to store the
database in, here are the commands I ran

cd /Users/lookaflyingdonkey/Documents

mkdir SQLiteTutorial

cd SQLiteTutorial

sqlite3 AnimalDatabase.sql
　　You should now be at a “sqlite” command prompt, this is where we will
be building our database structure and entering some test data.
　　For our example we need the name of the animal, a short description
and a link to an image. Follow the commands below to create the table
and to enter some sample data.

CREATE TABLE animals ( id INTEGER PRIMARY KEY, name VARCHAR(50), description TEXT, image VARCHAR(255) );


INSERT INTO animals (name, description, image) VALUES ('Elephant', 'The elephant is a very large animal that lives in Africa and Asia', 'http://dblog.com.au/wp-content/elephant.jpg');

INSERT INTO animals (name, description, image) VALUES ('Monkey', 'Monkies can be VERY naughty and often steal clothing from unsuspecting tourists', 'http://dblog.com.au/wp-content/monkey.jpg');

INSERT INTO animals (name, description, image) VALUES ('Galah', 'Galahs are a wonderful bird and they make a great pet (I should know, I have one)', 'http://dblog.com.au/wp-content/galah.jpg');

INSERT INTO animals (name, description, image) VALUES ('Kangaroo', 'Well I had to add the Kangaroo as they are the essence of the Australian image', 'http://dblog.com.au/wp-content/kangaroo.jpg');
　　The first command will create the table with the required structure
and the next four will insert some test data for us to work with. To
ensure that you have entered the data correctly you can execute “SELECT *
FROM animals;” and see if it returns the items above. Once you are
confident that everything had been created successfully you can leave
the sqlite command line by typing “.quit”.

3. Creating our Project
　　Now that our database is all ready to go we need to setup our X-Code
project.
　　Start off by creating a new “Navigation-Based Application”.


　　Give your Project a name, I called mine “SQLiteTutorial”.
　　Now set your screen layout to how you prefer it, I suggest making the
window as large as possible, and making the code view as tall as
possible by dragging the horizontal slider to the top. This will allow
you the most room to move when building your application.
　　Now its time to create the required classes and views for our
application, we will start off by making our views.
　　Right Click on the “Resources” folder in the left hand pane and click
“Add File”, we want to create a new “View XIB” under the “User
Interfaces” group.


　　We now need to give it a name, to stick the Apple’s naming
conventions we are going to call it “AnimalViewController.xib”
,
Now Click “Finish”.
　　Now we need to create two classes, the first one will represent an
animal, right click on the “Classes” folder in the left hand pane, click
“Add > New File…”, choose the “NSObject subclass” template under the
“Cocoa Touch Classes” group and name it “Animal”.
　　The second class will be for our AnimalsViewController, right click
on the “Classes” folder in the left hand pane, click “Add > New
File…”, choose the “UIViewController subclass” under the “Cocoa Touch
Classes” group and name it “AnimalViewController”.

4. Adding SQLite Framework and our Animal Database
　　Now that we have created all of our views and classes it is time to
start the real grunt work.
　　First off we need to include the SQLite libraries so our application
can utilise them. To do this you will need to right click on the
“Frameworks” folder in the left hand pane, then click on “Add >
Existing Frameworks…”, then navigate to
“/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.0.sdk/usr/lib/”
and double click the “libsqlite3.0.dylib” file. A popup will appear,
just click “Add” and the library will be added to your project.
　　We also need to add our database we created earlier to the Resources
folder, to do this simply right click on the “Resources” folder, click
“Add > Existing Files…”, navigate to the location you created the
database in then double click on the AnimalDatabase.sql file. Another
popup will appear, just click add.
　　All done with the importing, time to code!

5. The Coding begins!
　　We are going to start the coding by building our “Animal” object,
every animal will have 3 properties, a name, a description and an image
URL.
　　Open up the “Animal.h” file from the “Classes” folder and edit its
contents to look like below,

#import <UIKit/UIKit.h>


@interface Animal : NSObject {

NSString *name;

NSString *description;

NSString *imageURL;

}


@property (nonatomic, retain) NSString *name;

@property (nonatomic, retain) NSString *description;

@property (nonatomic, retain) NSString *imageURL;


-(id)initWithName:(NSString *)n description:(NSString *)d url:(NSString *)u;


@end

　　Most of the above code should be pretty familiar to you, the only
thing that may not be is the initWithName
line, this
line will allow us to create a new object with the required data, we
could have used the default init
function, but it will
be easier for us to define our own.
　　Now we will actually have to implement the Animal Object, open up the
“Animal.m” file and edit its contents to look like below:

#import "Animal.h"


@implementation Animal

@synthesize name, description, imageURL;


-(id)initWithName:(NSString *)n description:(NSString *)d url:(NSString *)u {

self.name = n;

self.description = d;

self.imageURL = u;

return self;

}

@end

　　The above code should be pretty easy to read as well, it basically
stores the supplied data from the initWithName function and return the
object (self).
　　Now its time to setup the Application delegate to access the
database.
　　Open up the “SQLiteTutorialAppDelegate.h” and edit its contents to
look like below:

#import <UIKit/UIKit.h>

#import <sqlite3.h> // Import the SQLite database framework


@interface SQLiteTutorialAppDelegate : NSObject  {


UIWindow *window;

UINavigationController *navigationController;


// Database variables

NSString *databaseName;

NSString *databasePath;


// Array to store the animal objects

NSMutableArray *animals;

}


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

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

@property (nonatomic, retain) NSMutableArray *animals;


@end

　　What we are doing here is importing the SQLite database framework and
creating some variables for storing the database details and an array
of animal objects.
　　Now open up the “SQLiteTutorialAppDelegate.m” file and edit its
contents to look like below:

#import "SQLiteTutorialAppDelegate.h"

#import "RootViewController.h"

#import "Animal.h" // Import the animal object header


@implementation SQLiteTutorialAppDelegate


@synthesize window;

@synthesize navigationController;

@synthesize animals; // Synthesize the aminals array


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

// Setup some globals

databaseName = @"AnimalDatabase.sql";


// Get the path to the documents directory and append the databaseName

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *documentsDir = [documentPaths objectAtIndex:0];

databasePath = [documentsDir stringByAppendingPathComponent:databaseName];


// Execute the "checkAndCreateDatabase" function

[self checkAndCreateDatabase];


// Query the database for all animal records and construct the "animals" array

[self readAnimalsFromDatabase];


// Configure and show the window

[window addSubview:[navigationController view]];

[window makeKeyAndVisible];

}


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

// Save data if appropriate

}


- (void)dealloc {

[animals release];

[navigationController release];

[window release];

[super dealloc];

}


-(void) checkAndCreateDatabase{

// Check if the SQL database has already been saved to the users phone, if not then copy it over

BOOL success;


// Create a FileManager object, we will use this to check the status

// of the database and to copy it over if required

NSFileManager *fileManager = [NSFileManager defaultManager];


// Check if the database has already been created in the users filesystem

success = [fileManager fileExistsAtPath:databasePath];


// If the database already exists then return without doing anything

if(success) return;


// If not then proceed to copy the database from the application to the users filesystem


// Get the path to the database in the application package

NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];


// Copy the database from the package to the users filesystem

[fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];


[fileManager release];

}


-(void) readAnimalsFromDatabase {

// Setup the database object

sqlite3 *database;


// Init the animals Array

animals = [[NSMutableArray alloc] init];


// Open the database from the users filessytem

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

// Setup the SQL Statement and compile it for faster access

const char *sqlStatement = "select * from animals";

sqlite3_stmt *compiledStatement;

if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

// Loop through the results and add them to the feeds array

while(sqlite3_step(compiledStatement) == SQLITE_ROW) {

// Read the data from the result row

NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];

NSString *aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];

NSString *aImageUrl = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];


// Create a new animal object with the data from the database

Animal *animal = [[Animal alloc] initWithName:aName description:aDescription url:aImageUrl];


// Add the animal object to the animals Array

[animals addObject:animal];


[animal release];

}

}

// Release the compiled statement from memory

sqlite3_finalize(compiledStatement);


}

sqlite3_close(database);


}


@end

　　Now I know that may look like a fair bit of code and it probably also
looks quite scary! But really it is quite simple and I have tried to
comment nearly every line to describe to you what the line does and why
it is there.
　　The checkAndCreateDatabase
function checks to see if
we have already copied our database from the application bundle to the
users filesystem (in their documents folder), if the database hasn’t
already been created or it has been removed for some reason it will be
recreated from the default database.
　　Next the readAnimalsFromDatabase
function will make a
connection to the database that is stored in the users documents
folder, and then executes the SQL statement “SELECT * FROM animals”. It
will then go through each row that is returned and it will extract the
name, description and imageURL from the result and build an Animal
object for each. You  will see the “sqlite3_column_text”
function used here, there are many more of these for returning other
field types such as “sqlite3_column_int”
for integers, “sqlite3_column_blob”
for blobs or “sqlite3_column_value”
to get an unknown
value.
　　Now that we have the data in our array and we have it in our known
format we are ready to start displaying it.
　　Open up the “RootViewController.m”
file and edit the
numberOfRowsInSection
to look like the following:

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

return appDelegate.animals.count;

　　What this does is it creates a link to the application delegate, and
then the second line returns the size f the animals array in out
Application delegate, this array was filled previously from the SQLite
database.
　　Now in the cellForRowAtIndexPath function you will need at change it
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] initWithFrame:CGRectZero reuseIdentifier:CellIdentifier] autorelease];

}


// Set up the cell

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

Animal *animal = (Animal *)[appDelegate.animals objectAtIndex:indexPath.row];


[cell setText:animal.name];

return cell;

}

　　We pretty much just added 3 lines under the “// Set up the
cell”
line, the first one is the same as we added previously to
access the application delegate. The second line creates a new Animal
object based on the array from the application delegate, it will be
used to create a row for each individual record in the database. On the
final line we are just setting the text of the cell to the name field
from the Animal
object.
　　You can now run the program and you should see a table view with the 4
animals we added to the database, if you added more than my default
animals you should see them in here as well.
　　We will now setup the AnimalViewController
, open up
the “AnimalViewController.h” file and edit its contents to below:

#import <UIKit/UIKit.h>


@interface AnimalViewController : UIViewController {

IBOutlet UITextView *animalDesciption;

IBOutlet UIImageView *animalImage;

}


@property (nonatomic, retain) IBOutlet UITextView *animalDesciption;

@property (nonatomic, retain) IBOutlet UIImageView *animalImage;


@end

　　What we are doing above is adding an outlet for the description and
image for the Animal
, we will use these later on when
we link the view up.
　　Now open up the “AnimalViewController.m” file and add a synthesize
call for for the description and image, this will go under the “@implementation
AnimalViewController”
line, like so:

#import "AnimalViewController.h"


@implementation AnimalViewController


@synthesize animalDesciption, animalImage;

　　Now it is time to make the detailed view page appear when you select a
record. Open up the “AnimalViewController.xib” file from the resources
folder and the interface builder should appear.
　　The first thing we need to do is to set the File’s Owner
Class to AnimalViewController
, this is done by
selecting the “File’s Owner”
item in the main window
and then clicking Tools > Identity Inspector
in the
top menu, and then selecting AnimalViewController
from
the class dropdown.
　　Your inspector window should now look like this:


　　We are going to be using a UITextView
for the
description (as it will allow for word wrapping and scrolling in the
case that the description is quite large) and a UIImageView
to display the image. I have laid mine out like below:


　　Now that we have everything laid out it is time to link them all up,
start by holding control
and click+drag
from the “File’s Owner”
to the “View”
objects, a little gray menu will appear and you will need to select view
.
Now hold control
and click+drag
from
the “File’s Owner”
to the UITextView
in the layout window, you should see “animalDescription”
in the popup list, select it. Repeat this process for the UIImageView
and you should see animalImage
appear, select it also.
　　Now save the interface and close the interface builder.
　　Nearly done! All we have to do now is to setup the code for when a
user presses on a record in the table view.
　　Open up the “RootViewController.h”
file and edit its
contents to below:

#import <UIKit/UIKit.h>

#import "AnimalViewController.h"


@interface RootViewController : UITableViewController {

AnimalViewController *animalView;

}


@property(nonatomic, retain) AnimalViewController *animalView;


@end

　　We are creating an instance of the AnimalViewController
to be used bu the RootViewController
when a user
presses on an item.
　　Now open up the “RootViewController.m”
file and edit
the top part of the file to look like below:

#import "RootViewController.h"

#import "SQLiteTutorialAppDelegate.h"

#import "Animal.h"


@implementation RootViewController

@synthesize animalView;

　　This will just synthesize the animalView
that we
just added.
　　First up lets set the default title of our view, to do this you need
to uncomment the viewDidLoad function, and edit it to below:

- (void)viewDidLoad {

[super viewDidLoad];

// Uncomment the following line to add the Edit button to the navigation bar.

// self.navigationItem.rightBarButtonItem = self.editButtonItem;


self.title = @"My Zoo";

}

　　We also need to edit the didSelectRowAtIndexPath

　　function in this file, edit it to look like below:

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {

// Navigation logic -- create and push a new view controller

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

Animal *animal = (Animal *)[appDelegate.animals objectAtIndex:indexPath.row];


if(self.animalView == nil) {

AnimalViewController *viewController = [[AnimalViewController alloc] initWithNibName:@"AnimalViewController" bundle:nil];

self.animalView = viewController;

[viewController release];

}


// Setup the animation

[self.navigationController pushViewController:self.animalView animated:YES];

// Set the title of the view to the animal's name

self.animalView.title = [animal name];

// Set the description field to the animals description

[self.animalView.animalDesciption setText:[animal description]];

// Load the animals image into a NSData boject and then assign it to the UIImageView

NSData *imageData = [NSData dataWithContentsOfURL:[NSURL URLWithString:[animal imageURL]]];

UIImage *animalImage = [[UIImage alloc] initWithData:imageData cache:YES];

self.animalView.animalImage.image = animalImage;


}

　　What we are doing here is checking to see if the animalView object
has already been created, if not then create it.
　　The next few lines are used to setup the animation (slide from right
to left) and to set the actual data fields to those of the selected
animal.
　　Now you should be ready to fire up the application and see it in all
its glory.
　　You should see your windows looking like below.




5. Project Files
　　Here are the source files for this project: Download
the project source files

　　If you have any questions or comments please feel free to post them
here and I will try to get back to you ASAP. Also keep your eye out for
my second part to the advanced RSS Reader tutorial, it will be using
TouchXML, SQLite, UIWebview and more!