iPhone SQLite Tutorial Part 1

With the release of Core Data for the iOS, most of the time developers should choose to use that to access SQLite because of the higher level of abstraction and ease of development. However there will still be cases where you want to deal with SQLite directly. This tutorial will show you can read and display data from an SQLite database. In Part 2 we will look at how you can update new data.

Create an SQLite database

1. We will be creating a SQLite database of members of the Avengers team.
Call the database Superheroes.
Create a table 'heroes' with the following fields.

CREATE TABLE "heroes" ("serial" INTEGER PRIMARY KEY  AUTOINCREMENT  
NOT NULL  UNIQUE , "name" VARCHAR, "desc" TEXT)

Populate the table with 4 superheroes from the Avengers, namely Iron Man, Thor, Captain America and the Hulk. For 'desc' fill in with whatever details you wish.

Starting a New Project

2. Start a new Xcode project, choose Navigation-based application template. Name the project AvengersDatabase.

3. Create a new view controller to display description of our heroes.
Right click on Classes and choose Add > New File. You will be presented with a New File dialog.
Choose the UIViewController subclass template.
Ensure the option, with XIB for user interface is checked.
On the next page give a name to the controller. Call it HeroViewController
Ensure that the option to create a header file is ticked.
You now have a new view controller class and XIB file in your project.
Move the XIB file to the Resources folder.

4.For our application to use SQLite you need to include the SQLite framework. Right click on the “Frameworks” folder
Click on “Add > Existing Frameworks…”
Browse for “libsqlite3.0.dylib” file and add it to your project.

5.Next we add the database we created to our project. Right click on the “Resources” folder, click “Add > Existing Files…”, navigate to the location you created the database and add it.
In the next dialog make sure the Copy item into Destination folder is checked.
Click on Add.

Our Hero class

6. Next we are going to create a Hero class where each object will contain the information on each hero.
Right click on Classes and choose Add > New File. You will be presented with a New File dialog.
Select the Objective-C Class template.
Ensure that Subclass of NSObject is showing in the dropdown.
Cick Next
On the next page, name the file, Hero. Ensure the Also Create "Hero.h" is checked
Click Finish

7. Now we will declare and implement our Hero class. We are going to add 2 NSString variables, name and desc. We are going to use the @property keyword to expose for synthesize later. We are also going to declare a method initHero to initialise our objects. Open the Hero.h in Classes folder and edit it as follows:

@interface Hero : NSObject {
    NSString *name;
    NSString *desc;

}
@property (nonatomic, retain) NSString *name;
@property (nonatomic, retain) NSString *desc;

-(id)initHero:(NSString *)name desc:(NSString *)desc;


@end

8. Next open up Hero.m and sythesize the variables and implement the method initHero:

@implementation Hero

@synthesize name, desc;

-(id)initHero:(NSString *)name desc:(NSString *)desc{
    self.name = name;
    self.desc = desc;
    return self;

}

Database Access

9. Next we modify the application delegate for SQLite database access. For a real-world application I would almost certainly create a database access object for this but for brevity (and laziness) I am putting the code in the existing application delegate class.

Open DatabaseAvengersAppDelegate.h and import "sqlite3.h".
We need 2 database related variables and an array to store our Hero objects. Add the following:

NSString *databaseName;
    NSString *databasePath;

    // Array to store the Hero objects
    NSMutableArray *Heroes;

Now Add a @property for the Heroes array:

@property (nonatomic, retain) NSMutableArray *Heroes;

Next open the DatabaseAvengersAppDelegate.m file.
Import the Hero.h and AvengersDatabaseAppDelegate.h files
@synthesize the Heroes array:

@synthesize Heroes; // Synthesize the Heroes array

10. Time to access and get data from the database. We are going to put in quite a bit of code so we will take do this in small parts:
First we are going to set a database name and database path:

databaseName = @"superheroes.sqlite";
    // 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];

Next we are going to copy the database from the application bundle over to the user's documents folder. Part of the reason for doing so is that you can only modify files in the documents folder.

//Copy the database over to documents folder if not already done
     NSFileManager *fileManager = [NSFileManager defaultManager];
    if(![fileManager fileExistsAtPath:databasePath]){
        NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] 
                                         stringByAppendingPathComponent:databaseName];    
        [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];    
        [fileManager release];
    }

11. Next we will connect to the database that was copied to the documents folder. We then execute the SQL statement “SELECT * FROM heroes”. For each row that is returned and it will extract the name and desc from the result and instantiate a Hero object:

sqlite3 *database; // Setting up the database object    
    Heroes = [[NSMutableArray alloc] init]; //initialise the Heroes array
    
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK){        
        // construct SQL Statement and compile it for faster access
        const char *sqlStatement = "select * from heroes";
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {                
                NSString *rName = [NSString stringWithUTF8String:
                                   (char *)sqlite3_column_text(compiledStatement, 1)];
                NSString *rDescription = [NSString stringWithUTF8String:
                                          (char *)sqlite3_column_text(compiledStatement, 2)];
                Hero *hero = [[Hero alloc] initHero:rName desc:rDescription];
                [Heroes addObject:hero];                
                [hero release];                 
            }
             
        }
        // Release the compiled statement from memory
        sqlite3_finalize(compiledStatement);
        
    }
    sqlite3_close(database);

12. With our heroes in our Heroes array it is time to display them to the user. We are going to modify our rootviewcontroller which is a tableview controller. Open rootViewController.m
Modify the the tableview method numbrOfRowsInSection like this:

// Customize the number of rows in the table view.
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
    AvengersDatabaseAppDelegate *AppDelegate = (AvengersDatabaseAppDelegate *)
    [[UIApplication sharedApplication] delegate];
    return appDelegate.Heroes.count;
}

Next add the following to under the configure the cell comment at cellForRowAtIndexPath:

AvengersDatabaseAppDelegate *appDelegate = (AvengersDatabaseAppDelegate *)
    [[UIApplication sharedApplication] delegate];
    Hero *hero = (Hero *)[appDelegate.Heroes objectAtIndex:indexPath.row];
    cell.textLabel.text=hero.name;
    return cell;

If you run your application now you should see a tableview display of superheroes in the Avengers. The names are drawn from the SQLite database. In part 2 we will look at displaying data from the 'desc' field.