iOS and SQLite make a powerful combination for building data persistent iPad, iPhone or iPod Touch mobile applications. The iOS SDK provides native support for SQLite through the use of the C programming language. This tutorial will walk you through how to setup a SQLite database application and to read text and images from the database into a scene.
Create the database
To start with, you will need FireFox from Mozilla and the SQLite Database Manager plugin. If you don’t have them, they can be downloaded and installed from the FireFox web site. Once FireFox is installed, install the SQLite Manager from the Add-on Manager.
The SQLite Manager can be launched from the Firefox menu or Tools menu depending on the version you are using (see figure 1).
Figure 1: SQLite Manager in Firefox
Figure 1: SQLite Manager in Firefox
Click on the new Database button (figure 2) to create a new database. You can give any meaningful name you want. Note, the SQLite extension will be automatically appended. You will be prompted to save the file to the file system, (naturally). Take note where you are saving it because you are going to copy the file later into your project.
Next, click on the new table button (figure 3) to create a new table, again I will leave it up to you to name it something useful. For this tutorial, my named the table wineTbl and I have created four columns: id [primary, autoinc, integer], winename [varchar], winerating [varchar] and wineimage [blob].
How To Develop iOS Database Apps using SQLite
This book will teach you how to develop iOS apps using SQLite. The book features award winning articles previously published online which have garnered approximately 1 million pageviews and new original content
Figure 2: Create a table
Figure 2: Create a table
Figure 3: Create the necessary columns
Figure 3: Create the necessary columns
For the sake of this tutorial, I will pre-populate the database with some wine entries and images from the web. You can add data by selecting the table and selecting the browse and data tab. To upload an image, click on the paper clip icon next to the blob field. (Figure 4 and figure 5).
Now you can close the database from the Firefox menu and Firefox as well since we won’t need anymore for the tutorial.
Figure 4: Adding a new record in the database
Figure 4: Adding a new record in the database
Figure 5: Record listing in the database
Figure 5: Record listing in the database
Create IOS 5 Project
Launch XCode 4.2 and create a Single-View IOS 5 application. Give it a meaningful name and select Storyboard and ARC. Setup your Git, or not, source control and complete the creation of your project. (figure 6).
Figure 6: The Wine List App
Figure 6: The Wine List App
Configure SQLite
Expand the Frameworks folder, right click on one of the frameworks and select Show in Finder to open Finder at the Framework location. You will need to add the libsqlite_3.0.dylib file to your project (figure 6), so move up two or three levels (see Go to Enclosing folder in the Finder menu) until you get to the usr folder. Open it and open the lib folder. Scroll down until you find the sqlite_3.0.lib. Drag the file to your Frameworks taking care to NOT copy file into the frameworks, but ONLY create a reference (Figure 7).
Next select the project root, right click and select Show in Finder. Locate your sql database you created in the first part of this tutorial and copy it into the project group where you project header and implementations files are (Figure 8).
Figure 7: Copy Reference of sqlite3.0.dylib to the Framework folder
Figure 7: Copy Reference of sqlite3.0.dylib to the Framework folder
Figure 8: Copy database file to the project folder
Figure 8: Copy database file to the project folder
Setup DAO Operations
Create a new Group (File | New Group) or from the (Context Menu | New Group). Name it “Model”. Next create two Objective-C implementation files and corresponding header files. Select the Model group and from the File menu or Context menu | select New File. Select the Objective-C node and then the Objective-C class template.
Give your file a name: WineList (if you are following this tutorial), select NSObject as the Subclass and create the file. Repeat the process for the next set of files: MyWineList, (or you can choose a name like WinesDAO). Again select the NSObject as the Subclass and create the file (Figure 9).
For the WineList class create four properties in the WineList.h (header) file, one for each column in the wineTbl (Figure 10):
wineId
wine
rating
photo
Next open the WineList.m (implementation) file to set up the getter and setter methods. So your WineList should contain four @synthesize statements, one four each property (Figure 11).
@synthesize wineId;
@synthesize wine;
@synthesize rating;
@synthesize photo;
Figure 9: Create the WineList class
Figure 9: Create the WineList class
Figure 10: Create the WineLists class
Figure 10: Create the WineLists class
Figure 11: The WineList header
Figure 11: The WineList header
Create CRUD Operations
Well CRUD is a bit of a stretch. For this tutorial it is really just a R (read) operation. Ok now the application is going to need DAO classes for the CRUD (Read) operations, so if you haven’t already done so, create a new Objective-C class: MyWineLists or whatever you want so long as the declaration and implementation works. For the MyWineLists header file, a sqlite3 object is declared and an NSMutableArray method (figure 11):
db
getMyWines
To implement these objects, open the MyWineLists.m file. In this file, the gut if the operations will take place.
To start create the NSMutableArray method getMyWines and add an array pointer variable:
wineArray
Next declare a NSFileManager object, a NSString object and a Bool object:
fileMgr
dbPath
success
…
NSMutableArray *wineArray = [[NSMutableArray alloc] init];
@try {
NSFileManager *fileMgr = [NSFileManager defaultManager];
NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]stringByAppendingPathComponent:@"IOSDB.sqlite"];
BOOL success = [fileMgr fileExistsAtPath:dbPath];
...
The dbPath will contain to the filename and path of the SQLite database which will be passed to the fileMgr. If the file is located, success will be true. Next test to see if the file was located and if not log an error. The following operation will attempt to open the database, sqlite3_open before setting up the Select statement and sql3_stmt:
sql
sqlStatement
…
if(!success)
{
NSLog(@"Cannot locate database file '%@'.", dbPath);
}
if(!(sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK))
{
NSLog(@"An error has occured.");
}
const char *sql = "SELECT id, Wine, Rating, Photo FROM WineTbl";
sqlite3_stmt *sqlStatement;
if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
{
NSLog(@"Problem with prepare statement");
}
...
If the database is successfully opened, the sqlite3_prepare will attempt to execute the sqlStatement. If the statement is successfully executed resulting in a result set being returned, then execute a while loop to traverse the result set assigning the values to the NSMutableArray fields.
...
while (sqlite3_step(sqlStatement)==SQLITE_ROW) {
WineList *MyWine = [[WineList alloc]init];
MyWine.wineId = sqlite3_column_int(sqlStatement, 0);
MyWine.wine = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement,1)];
MyWine.rating = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement, 2)];
const char *raw = sqlite3_column_blob(sqlStatement, 3);
int rawLen = sqlite3_column_bytes(sqlStatement, 3);
NSData *data = [NSData dataWithBytes:raw length:rawLen];
MyWine.photo = [[UIImage alloc] initWithData:data];
[wineArray addObject:MyWine];
}
}
@catch (NSException *exception) {
NSLog(@"An exception occured: %@", [exception reason]);
}
@finally {
return wineArray;
}
...
This pretty much takes care of the of the cRud operations. The next step will involve setting up the UI, creating IBActions and IBOutlets connections. (See figure 12, 13).
Figure 12: The implementation of WineLists
Figure 12: The implementation of WineLists
Figure 13: The CRUD operations
Figure 13: The CRUD operations
Create UI Operations
Start by locating and opening the storyboard file. You should have a single blank scene (View Controller). For this part, four labels (UILabel) are required: one for Wine Name and the value from the database and likewise for the two others: one for Wine Rating and the corresponding value from the database that will be stored in the NSMutableArray. For the images, drag an UIImageView onto the scene. As a final step for the UI, drag a UIToolbar and place it at the bottom of the screen and rename the included button: Next Bottle (Figure 14).
Figure 14: Connecting the dots
Figure 14: Connecting the dots
Figure 15: The project structure
Figure 15: The project structure
To finish off the app, some code needs to be added to the ViewController header and implementation files. So to setup the IBAction and IBOutlet, open the header file alongside the storyboard by clicking on the Assistant Editor, the face icon in the Toolbar (Figure 14). Start by selecting the first label and dragging a connection line (Ctrl+left mouse button) to the header file between the last curly brace and the @end directive. In the Popup, select IBOutlet and enter a name like: winename. Continue with second label that will contain the rating information. This will also be an IBOutlet and the name will be: winerating. For the image, repeat the same operation as the two preceding ones. This connection will also be an IBOutlet and the name will be : wineViewer. Finally drag a connection line from the button in the Toolbar. This will be an IBAction and the name of the method: GetWineListing. Also add a NSMutableArray object:
wines
You should have little filled in dot in the margin indicating that connections have been made.
Next open the implementation file. Setup the getter and setters:
…
@synthesize wineViewer;
@synthesize winename;
@synthesize winerating;
@synthesize wines;
…
In the viewDidLoad, which is called when the app is finished initializing itself, add pointers to hold the initial data in the array so the app will display some information and image that is located at index 0.
…
- (void)viewDidLoad
{
MyWineLists * mywines =[[MyWineLists alloc] init];
self.wines = [mywines getMyWines];
[self.wineViewer setImage:((WineList *) [self.wines objectAtIndex:0]).photo];
[self.winename setText:((WineList *) [self.wines objectAtIndex:0]).wine];
[self.winerating setText:((WineList *) [self.wines objectAtIndex:0]).rating];
[super viewDidLoad];
}
...
in the viewDidUnload set your properties to nil to release them from memory
…
- (void)viewDidUnload
{
[self setWineViewer:nil];
[self setWinename:nil];
[self setWinerating:nil];
[super viewDidUnload];
}
...
Finally implement the GetWineListing method, so when the user clicks on the button, the index gets incremented and retrieves the data at the selected index number.
…
- (IBAction)GetWineListing:(id)sender {
static NSInteger currentIndex = 0;
if (++currentIndex == [self.wines count]) {
currentIndex=0;
}else{
WineList *aWine = (WineList *) [self.wines objectAtIndex: currentIndex];
[self.winename setText:aWine.wine];
[self.winerating setText:aWine.rating];
[self.wineViewer setImage:aWine.photo];
}
}
…
Test Your App
Ok, we are done. Click on the Run button to launch your app. After the app is finished initializing you should have from data and image on screen. Click the Next Bottle to get the next listing.
Figure 15: The running app
Figure 15: The running app
Source Code
Here is the complete source code of the various files that were created.
WineList.m
//
// WineList.m
// MyWineList
//
// Created by Kevin Languedoc on 11/25/11.
// Copyright (c) 2011 kCodebook. All rights reserved.
//
#import "WineList.h"
@implementation WineList
@synthesize wineId;
@synthesize wine;
@synthesize rating;
@synthesize photo;
//With ARC, if you selected id, you don't need to dealloc
@end
MyWineLists
//
// MyWineLists.h
// MyWineList
//
// Created by Kevin Languedoc on 11/25/11.
// Copyright (c) 2011 kCodebook. All rights reserved.
//
#import
#import
@interface MyWineLists : NSObject{
sqlite3 *db;
}
- (NSMutableArray *) getMyWines;
@end
WineList.h
//
// WineList.h
// MyWineList
//
// Created by Kevin Languedoc on 11/25/11.
// Copyright (c) 2011 kCodebook. All rights reserved.
//
#import
@interface WineList : NSObject{
NSInteger wineId;
NSString *wine;
NSString *rating;
UIImage *photo;
}
@property (nonatomic,retain)NSString *wine;
@property (nonatomic, assign) NSInteger wineId;
@property (nonatomic, retain)NSString *rating;
@property (nonatomic, retain) UIImage *photo;
@end
MyWineLists.m
//
// MyWineLists.m
// MyWineList
//
// Created by Kevin Languedoc on 11/25/11.
// Copyright (c) 2011 kCodebook. All rights reserved.
//
#import "MyWineLists.h"
#import "WineList.h"
@implementation MyWineLists
- (NSMutableArray *) getMyWines{
NSMutableArray *wineArray = [[NSMutableArray alloc] init];
@try {
NSFileManager *fileMgr = [NSFileManager defaultManager];
NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]stringByAppendingPathComponent:@"IOSDB.sqlite"];
BOOL success = [fileMgr fileExistsAtPath:dbPath];
if(!success)
{
NSLog(@"Cannot locate database file '%@'.", dbPath);
}
if(!(sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK))
{
NSLog(@"An error has occurred.");
}
const char *sql = "SELECT id, Wine, Rating, Photo FROM WineTbl";
sqlite3_stmt *sqlStatement;
if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
{
NSLog(@"Problem with prepare statement");
}
//
while (sqlite3_step(sqlStatement)==SQLITE_ROW) {
WineList *MyWine = [[WineList alloc]init];
MyWine.wineId = sqlite3_column_int(sqlStatement, 0);
MyWine.wine = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement,1)];
MyWine.rating = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement, 2)];
const char *raw = sqlite3_column_blob(sqlStatement, 3);
int rawLen = sqlite3_column_bytes(sqlStatement, 3);
NSData *data = [NSData dataWithBytes:raw length:rawLen];
MyWine.photo = [[UIImage alloc] initWithData:data];
[wineArray addObject:MyWine];
}
}
@catch (NSException *exception) {
NSLog(@"An exception occured: %@", [exception reason]);
}
@finally {
return wineArray;
}
}
@end
kcbViewController
//
// kcbViewController.h
// MyWineList
//
// Created by Kevin Languedoc on 11/25/11.
// Copyright (c) 2011 kCodebook. All rights reserved.
//
#import
@interface kcbViewController : UIViewController{
NSMutableArray *wines;
}
@property(nonatomic,retain) NSMutableArray *wines;
@property (weak, nonatomic) IBOutlet UIImageView *wineViewer;
@property (weak, nonatomic) IBOutlet UILabel *winename;
@property (weak, nonatomic) IBOutlet UILabel *winerating;
- (IBAction)GetWineListing:(id)sender;
@end
kcbViewController.m
//
// kcbViewController.m
// MyWineList
//
// Created by Kevin Languedoc on 11/25/11.
// Copyright (c) 2011 kCodebook. All rights reserved.
//
#import "kcbViewController.h"
#import "WineList.h"
#import "MyWineLists.h"
@implementation kcbViewController
@synthesize wineViewer;
@synthesize winename;
@synthesize winerating;
@synthesize wines;
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
// Release any cached data, images, etc that aren't in use.
}
#pragma mark - View lifecycle
- (void)viewDidLoad
{
MyWineLists * mywines =[[MyWineLists alloc] init];
self.wines = [mywines getMyWines];
[self.wineViewer setImage:((WineList *) [self.wines objectAtIndex:0]).photo];
[self.winename setText:((WineList *) [self.wines objectAtIndex:0]).wine];
[self.winerating setText:((WineList *) [self.wines objectAtIndex:0]).rating];
[super viewDidLoad];
}
- (void)viewDidUnload
{
[self setWineViewer:nil];
[self setWinename:nil];
[self setWinerating:nil];
[super viewDidUnload];
}
- (IBAction)GetWineListing:(id)sender {
static NSInteger currentIndex = 0;
if (++currentIndex == [self.wines count]) {
currentIndex=0;
}else{
WineList *aWine = (WineList *) [self.wines objectAtIndex: currentIndex];
[self.winename setText:aWine.wine];
[self.winerating setText:aWine.rating];
[self.wineViewer setImage:aWine.photo];
}
}
- (void)viewWillAppear:(BOOL)animated
{
[super viewWillAppear:animated];
}
- (void)viewDidAppear:(BOOL)animated
{
[super viewDidAppear:animated];
}
- (void)viewWillDisappear:(BOOL)animated
{
[super viewWillDisappear:animated];
}
- (void)viewDidDisappear:(BOOL)animated
{
[super viewDidDisappear:animated];
}
- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation
{
// Return YES for supported orientations
return (interfaceOrientation != UIInterfaceOrientationPortraitUpsideDown);
}
@end
In summary
That pretty wraps it up. In the hindsight, implementing SQLite was very easy. The only thing missing is the operations to create records, updating and deleting them. That as they say is another story, or article in this case. If you need to know how to perform inserts, updates and deletes, check out my article:
emm bunyi macam sedap kat mana tempat 2..leh le ajak abg sekali(emm x malu masuk bakul angkat sndiri hehehe)
ReplyDelete