How to handle SQLite database on iPhone development:
I have seen somewhere that people was struggling to add and handle sqlite database on iPhone development. I would like to help them on giving simple explanation with a project sample here.
If you might have seen some more details easily than this blog in somewhere else, then please don't hesitate to follow up there wherever you are convenient.
Here you go friends !!!
I. SQLite:
i) Creating a sqlite database using Terminal sql command in "Documents" folder.
In Terminal,
go to Documents directory and create a folder first.
my-macbook:~ myname$ cd Documents
my-macbook:Documents myname$ mkdir MPSQLiteMainFolder
Create a sqlite database there. I named it as "MPsqlitedb"
my-macbook:Documents myname$ cd MPSQLiteMainFolder
my-macbook:MPSQLiteMainFolder myname$ sqlite3 MPsqlitedb // for creation
After applying the above creation command, you should now be in the sqlite command: (like below)
SQLite version 3.4.0
Enter ".help" for instructions
sqlite>
Since database have been created, we need to create a table and insert data into it. This will be based on your requirement. I am going to create a table with 3
parameters required for me. And then insert country name, some description and flag image there. Please see example below.
// Table creation. Table name "mysqlitetable"
sqlite> CREATE TABLE mysqlitetable (id INTEGER PRIMARY KEY, countryname VARCHAR(50), nationalanthem TEXT, cntryflagimg VARCHAR(255) );
// inserting data into it.
sqlite> INSERT INTO mysqlitetable(countryname,nationalanthem,cntryflagimg) VALUES ('INDIA', 'India is my country. I found one of the fast growing country in the world','http://flagpedia.net/data/flags/normal/in.png');
sqlite> INSERT INTO mysqlitetable(countryname,nationalanthem,cntryflagimg) VALUES ('UNITED STATUS', 'United Status is one of the well grown country in the world','http://flagpedia.net/data/flags/normal/us.png');
sqlite>
After done this, if you have doubt whether you have created database successfully or not, then follow the command to make sure it is done perfectly.
SELECT * FROM mysqlitetable;
Now you have created a sqlite database and also inserted data into it. Next, we will see how can we access this database in iPhone code and handle it with this simple example.
II. Project
1. Create a navigation based project in Xcode.
After project has created,
2. Add sqlite library into our project as we are going to handle sqlite database in our code.
To do that,
1. Right click your project "Framework", and choose "Add existing framework"
2. Take the path of, "Developer/Platforms/iPhoneOS.Platform/Developer/SDKs/iPhoneOS3.0SDK/usr/lib/libsqlite3.dylib"
3. We have to also add our database(which we created above as "MPsqlitedb".
To do that,
1. Right click your project "Resources", and choose "Add existing files"
2. Choose the sqlite database file which you created as "MPsqlitedb" from Documents folder.
Now, the time to add code:
1. What we will do is, we will create a structure(here it is Class, we will name it as " CountryDetails ") where-in maintains three variables for countryname, nationalanthem and cntryflagimg..
2. In AppDelegate, we read the database which we created. Read values will be added in CountryDetails class and CountryDetails class object will be added its combined data into a mutable array called "DataCollector", which will act as storing multiple CountryDetails class object.
3. Now the data is ready, we will show "name" of the country first in TabelView rootviewcontroller by accessing this data.
4. After the name is shown, we will have to add another view controller called "DetailsViewController", where-in our data country national anthem description and flag image will be shown there by accessing them from the CountryDetails objects.
Code:
Here is the code:
// AppDelegate.h file ########################################################
#import#import
@interface MySQLLiteSampleAppDelegate : NSObject{ UIWindow *window;UINavigationController *navigationController;NSString *databaseName;NSString *databasePath;NSMutableArray *elements;}
@property (nonatomic, retain) IBOutlet UIWindow *window;@property (nonatomic, retain) IBOutlet UINavigationController *navigationController;
@property (nonatomic, retain) NSMutableArray *elements;
-(void) CreateDatabaseIfRequired;-(void) ReadAndExtractDatabase;
// AppDelegate.m file ########################################################
#import "MySQLLiteSampleAppDelegate.h"#import "RootViewController.h"
#import "CountryDetails.h"
@implementation MySQLLiteSampleAppDelegate
@synthesize window;@synthesize navigationController;@synthesize elements;
#pragma mark -#pragma mark Application lifecycle
- (void)applicationDidFinishLaunching:(UIApplication *)application {// Our database namedatabaseName = @"MPsqlitedb.sql";// Get the document directory path and append databaes name to the path..NSArray *docsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);NSString *docDir = [docsPath objectAtIndex:0];databasePath = [docDir stringByAppendingPathComponent:databaseName];// Add the database into the path if it is already not exists.[self CreateDatabaseIfRequired];// Read the database and extract the contents..[self ReadAndExtractDatabase];// Override point for customization after app launch[window addSubview:[navigationController view]];[window makeKeyAndVisible];}
-(void) CreateDatabaseIfRequired {NSFileManager *fileManager = [NSFileManager defaultManager];BOOL bAvailable = [fileManager fileExistsAtPath:databasePath];// database already exists so just exit from here..if ( bAvailable ) return;// if not exists copy the db into the path..NSString *databaseAtResourcePath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];[fileManager copyItemAtPath:databaseAtResourcePath toPath:databasePath error:nil];[fileManager release];}
-(void) ReadAndExtractDatabase {
sqlite3 *database;elements = [[NSMutableArray alloc] init];// Open databaseif ( sqlite3_open([databasePath UTF8String], &database) ==SQLITE_OK ){const char *sqlstatement = "select * from mysqlitetable";sqlite3_stmt *combiledstatement;printf( "could not prepare statemnt: %s\n", sqlite3_errmsg(database) );if ( sqlite3_prepare_v2(database, sqlstatement, -1, &combiledstatement, NULL)==SQLITE_OK ){while ( sqlite3_step(combiledstatement)==SQLITE_ROW ){NSString *name = [NSString stringWithUTF8String:(char*) sqlite3_column_text(combiledstatement, 1)];NSString *nationalAnthemDesc = [NSString stringWithUTF8String:(char*) sqlite3_column_text(combiledstatement, 2)];NSString *image = [NSString stringWithUTF8String:(char*) sqlite3_column_text(combiledstatement,3)];CountryDetails *detObj = [ [CountryDetails alloc]initDetails:name :nationalAnthemDesc :image ];[elements addObject:detObj];[detObj release];}}// release the combiled statementsqlite3_finalize(combiledstatement);}sqlite3_close(database);}
- (void)applicationWillTerminate:(UIApplication *)application {// Save data if appropriate}
#pragma mark -#pragma mark Memory management
- (void)dealloc {[navigationController release];[window release];[super dealloc];}
// RootViewController.h file ########################################################
#import "DetailViewController.h"
@interface RootViewController : UITableViewController {DetailViewController *detViewController;}
@property (nonatomic, retain) DetailViewController *detViewController;
@end
// RootViewController.m file ########################################################
#import "RootViewController.h"#import "MySQLLiteSampleAppDelegate.h"#import "CountryDetails.h"
@implementation RootViewController@synthesize detViewController;
- (void)viewDidLoad {[super viewDidLoad];
// Uncomment the following line to display an Edit button in the navigation bar for this view controller.// self.navigationItem.rightBarButtonItem = self.editButtonItem;self.title = @"Country details";}
- (void)didReceiveMemoryWarning {// Releases the view if it doesn't have a superview.[super didReceiveMemoryWarning];// Release any cached data, images, etc that aren't in use.}
- (void)viewDidUnload {// Release anything that can be recreated in viewDidLoad or on demand.// e.g. self.myOutlet = nil;}
#pragma mark Table view methods
- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {return 1;}
// Customize the number of rows in the table view.- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {MySQLLiteSampleAppDelegate *appDelegate = (MySQLLiteSampleAppDelegate*) [ [UIApplication sharedApplication] delegate];return appDelegate.elements.count;}
// Customize the appearance of table view cells.- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {static NSString *CellIdentifier = @"Cell";UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];if (cell == nil) {cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier] autorelease];}// Configure the cell.MySQLLiteSampleAppDelegate *appDelegate = (MySQLLiteSampleAppDelegate*) [ [UIApplication sharedApplication] delegate ];CountryDetails *contryDet = (CountryDetails*) [appDelegate.elements objectAtIndex:indexPath.row];[cell setText:contryDet.countryname];return cell;}
// Override to support row selection in the table view.- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {MySQLLiteSampleAppDelegate *myAppDelegate = (MySQLLiteSampleAppDelegate *) [ [UIApplication sharedApplication] delegate ];CountryDetails *contryDet = (CountryDetails *) [myAppDelegate.elements objectAtIndex:indexPath.row];// Navigation logic may go here -- for example, create and push another view controller.detViewController = [[DetailViewController alloc] initWithNibName:@"DetailViewController" bundle:nil];[self.navigationController pushViewController:detViewController animated:YES];detViewController.title = [contryDet countryname];[detViewController.natAnthemDescView setText:[contryDet nationalanthem] ];// download image from urlNSData *imgdata = [NSData dataWithContentsOfURL:[NSURL URLWithString:[contryDet cntryflagimg] ] ];UIImage *flagImage = [ [UIImage alloc] initWithData:imgdata];detViewController.flagImageView.contentMode = UIViewContentModeScaleAspectFit;//detViewController.flagImageView.frame = CGRectMake(329, 460, 329, 186);
[detViewController.flagImageView setImage:flagImage ];[detViewController release];}
- (void)dealloc {[super dealloc];}
// CountryDetails.h file ########################################################
#import
@interface CountryDetails : NSObject {NSString *countryname;NSString *nationalanthem;NSString *cntryflagimg;}@property (nonatomic, retain) NSString* countryname;@property (nonatomic, retain) NSString* nationalanthem;@property (nonatomic, retain) NSString* cntryflagimg;
-(id) initDetails : (NSString*) cntryname :(NSString*) natAnthDetail :(NSString*) flagImage;
// CountryDetails.m file ########################################################
#import "CountryDetails.h"
@implementation CountryDetails@synthesize countryname;@synthesize nationalanthem;@synthesize cntryflagimg;
-(id) initDetails : (NSString*) cntryname :(NSString*) natAnthDetail :(NSString*) flagImage{self.countryname = cntryname;self.nationalanthem = natAnthDetail;self.cntryflagimg = flagImage;return self;}
@end
// DetailViewController.h file ########################################################
#import
@interface DetailViewController : UIViewController {IBOutlet UITextView *natAnthemDescView;IBOutlet UIImageView *flagImageView;}@property (nonatomic, retain) IBOutlet UITextView *natAnthemDescView;@property (nonatomic, retain) IBOutlet UIImageView *flagImageView;
@end
// DetailViewController.m file ########################################################
#import "DetailViewController.h"
@implementation DetailViewController@synthesize natAnthemDescView;@synthesize flagImageView;
// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.- (void)viewDidLoad {[super viewDidLoad];}
- (void)didReceiveMemoryWarning {// Releases the view if it doesn't have a superview.[super didReceiveMemoryWarning];// Release any cached data, images, etc that aren't in use.}
- (void)viewDidUnload {// Release any retained subviews of the main view.// e.g. self.myOutlet = nil;}
- (void)dealloc {[super dealloc];}
##### Add a detail view controller.xib file (which might get added when you added the view controller class itself). I named it as DetailViewController.xib
** Add a TextView and UIImageView there and link them all.
Please drop your comments if it really helped you in someway....
Thank you.
Cheers!
M.P.Prabakar
Senior Systems Analyst.
Have fun and be addictive by playing "TossRing" marvelous iPhone game. Link is below..
