How to use sqlite with UITableView in iPhone SDK

Hi All,
I have been creating an App using TableView with SQLite Manager….As a Beginner Like many others I too visited many sites in search of understanding about tableviews and how to Integrate it with Database….Here I am giving you all a detailed explanation of about TableViews and Database….let us look at it….
AIM OF THE Project: Here our Aim is Afterwhen launching into the Application you get list of Animals names Displayed and when you click on those animal names you get details of those animals in the next view.
So Initially for developing any project you must have:
Database —- At first we must have our database ready in our Hand in order to make our lifes easy for using that in our Application.
Now let us look at how to create Database using SQLite Manager….
Foreign key (Missing):
SQlite Manager: Is easy to use and create database but the only issue with it is THERE IS NO FOREIGN KEY OPTION….Foreign keys Constraint is Missing in SQLite Manager.
SQLite3: In the NEW VERSION of SQLite 3.6.19 and latest versions they have added the Foreign key concepts in them. But remember SQLITE3 is Different from SQLite Manager.
Download SQLite Manager Add-on from FireFox and then Open Firefox > Tools(Menu) > SQLite Manager

Then Menu (In SQLite Manager)> (click) NewDatabase > SampleDB( Name of Database )
Then select the database from the Directory (option on the top, Go to your Folder) > SampleDB(select your Database)
After that From the Menu on the Top in SQLite Manager you will find Different options for Creating Table, Editing Table etc….so from that Click Create Table and then create tables after that Insert Data into those using “Insert Into” Statement into those tables….
Inserting data into those tables We Must go to EXECUTE SQL (Option in SQLite Manager ) > Write Queary for Inserting data into the table > RunSQL
In this App for SampleDB, I wrote two Tables called:
1) Animals 2) Descsription —- here is how the database look like….
So We have completed Creating the DataBase for our Project,now we will be jumping into X-Code for Developing the App….
TableViews:
In Table Views the most important thing we need to keep track is switching between the Views….In my Project I have two views
1) For the First View I am inserting the data Manually
2) For the Second View I am Inserting data into tableview from the DataBase.
so Initially Go to X-Code > iPhoneOS > Navigational-based Applications > SampleTableViewApp (Name of The App) > Choose
Now you see the Couple of Files Generated for us….
Here you see 1) RootViewController.h 2) RootViewController.m 3) SampleTableViewAppDeligate.h 4) SampleTableViewAppDeligate.m 5) RootViewController.xib
These are the Files generated by X-Code and you can see those files in Classes Folder and Resources Folder.
Adding SQLite3 lib file: Before starting the code we have to Add the SQLite3 Library into the Framework in the Application.
(right Click) Frameworks > Add File > Add Existing Frameworks > LibSqlite3.0.dylib > Add
FMDB Wrapper: I am using FMDB Wrapper as an alternative for SQLite3 as it has easy way of steps for accessing the DataBase. You can download this wrapper from Google. After downloading JUST DRAG THE FMDB FOLDER INTO YOUR APPLICATION.
Adding Database: Next Step is Add Database file to your Application
Go to your DataBase folder where you stored your SampleDB > Drag it in to your App ( You can store it in anywhere )
Coding: Let US Start coding the App…..so
The Next Step is We have Got the Files above and now Go to:
(right click on) SampleTableViewApp > Add > NewFile > UIViewController SubClass > Next > ElephantViewController.m (Name of File) > Click Checkbox to add .h Files as shown in Figure.
In The Same way Add the LionViewController file….

Don’t get confused with the RootViewController,Deligate file, ElephantViewControllerfiles, LionViewController…. I will Explain you all those….
RootViewController: It is the First View of our App
ElephantViewController, LionViewController: These are the Detailed View of the items which are displayed in the first view
Deligete Files: This is the File which have all the details of deligate methods of TableView.
So now we have our RootView files and the DetailedView file of the Root View….Now we will code our RootViewController File:
Steps:
1) RootViewController.h : Import “AnimalViewController” , then Create An Mutable Array for storing the Elements. Here is the code below:
#import <UIKit/UILit.h>
#import "ElephantViewController.h"
#import "LionViewController.h"

@interface RootViewController : UITableViewController
{

NSMutableArray *mAnimal;

}

@end
2) Go to RootViewController.m: then add the below code in View Did Load Function:
Here we are adding objects in the Array and Setting Title to the First View of The App….Every thing is explained in the Code:
- (void)viewDidLoad
{
[super viewDidLoad];

/* Here we have allocated some memory to the Array object so we have to release it in Dealloc Function */

mAnimal = [[NSMutableArray alloc] init];

/* Next Step is Adding Objects to the Array*/

[mAnimal addObject:@"Elephant"];
[mAnimal addObject:@"Lion"];

/* Setting Title to the First View */

[self setTitle:@"Quote Category"];

}
Modifying TableView Deligate Methods:
3) In this step we have to Modify the Deligate Methods of the Tableview according to our App Requirements:
These can be found in the Bottom after the ” #pragma mark Table view methods” Line. so Add the Below Code in those Methods:
Here the First Method we have to change is “NumberOfRowsInSection” by default it is “0″ but We want our Rows to be Displayed as the number of elements in the Array so Update your Code as shown below:
- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {
return 1;
}
// Customize the number of rows in the table view.
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
return [mAnimal count];
}
4) In this step we can Configure the cells in the TableView by changing this Method “cellForRowAtIndexPath”
we can set the Accessory type like” > ” as shown in the iPhone or iPod:
// Customize the appearance of table view cells.
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

/* These are the Default Methods we don't need to configure them */

static NSString *CellIdentifier = @"Cell";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier] autorelease];
}
/* Configure the cell.
We have to start configuring cells from here
*/

[cell setText:[[qCategoriesList objectAtIndex:indexPath.row] retain]];

/* set the Accessory Type to our Cells like ">"*/

[cell setAccessoryType:UITableViewCellAccessoryDetailDisclosureButton];

return cell;
}
5) Next Most Important step is “Pushing the First View to Second View” for this uncomment the “DidSelectRow” and do the following code:
// Override to support row selection in the table view.

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

/* all the Methods are already given in this Function, We just need to change them according to our Requirements
If we Select Elephant then the View has to Push into another View giving details abuot the Elephant */

// Elephant View

if([[mAnimal objectAtIndex:indexPath.row] isEqual:@"Elephat"])
{
ElephantViewController *mEle = [[ElephantViewController alloc] initWithNibName:@"ElephantViewController" bundle:nil];
//Setting title to the TableView
[mEle setTitle:@"Elephant Details"];
//Pushing the View from RootView to ElephantView
[self.navigationController pushViewController:mEle animated:YES];
//releasing the Allocated Object
[mEle release];
}

// Lion View

if([[mAnimal objectAtIndex:indexPath.row] isEqual:@"Lion"])
{
LionViewController *mLion = [[LionViewController alloc] initWithNibName:@"LionViewController" bundle:nil];

//Setting title to the TableView
[mLion setTitle:@"Lion Details"];

//Pushing the View from RootView to ElephantView
[self.navigationController pushViewController:mLion animated:YES];

//releasing the Allocated Object
[mLion release];
}
/* There ends the Pushing of our view now we have to code in our detailed views of Elephant and Lion in order to display data about them----we are displaying this data from the Database */

// Navigation logic may go here -- for example, create and push another view controller.
// AnotherViewController *anotherViewController = [[AnotherViewController alloc] initWithNibName:@"AnotherView" bundle:nil];
// [self.navigationController pushViewController:anotherViewController animated:YES];
// [anotherViewController release];
}
Now we have to Code Our Detailed Views and Display data in them…We first Code our ElephantViewController
6) Now As we have created our “ElephatViewController” as a SubClass of UIViewController we need to Change that to UITableViewController Class
i:e “ElephantTableViewController” as we want to display data in a TableView in ElephantViewController. Here is the Code of that:
/* We have to import the FMDB Headers in our file as we have to use its queary methods for accessing the DB*/
#import
#import "FMDatabase.h"
#import "FMResultSet.h"

/*
It is Like this
@interface ElephantViewController : UIViewController
Now we are going to change it like this:
*/
@interface ElephantViewController : UITableViewController
{
// We have to Create an Array in order to Hold the Data coming from the DataBase
NSMutableArray *aElephant;
//NSString object is used to hold the elements retrived from the database
NSString *eleData;
}
@property (nonatomic,retain) NSString* eleData;
@end
7) Now as we did the .H file we have to implement the database connection and get the results form the DB
here how it goes: now go to ElephantViewController.m File then do this code….
#import "ElephantViewController.h"

@implementation ElephantViewController

@synthesize eleData;

// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.

- (void)viewDidLoad
{
[super viewDidLoad];

/* Now here we have to write the code from the database to display the details */

aElephant = [[NSMutableArray alloc] init];

/*
Here we have to set the path
We are creating the path for the datbase file in order to access
*/

NSString *path = [[NSBundle mainBundle] pathForResource:@"SampleDB" ofType:@"sqlite"];

/* FMDatabase here is used to take the database path */

FMDatabase *db = [[FMDatabase alloc] initWithPath:path];

/* Here we are opening the datase in order to access it */

[db open];

/*
Then the next step here is taking the datbase result using the sqlqueary and then carrying that result into the resultset object
Resultset for Elephant: select * from Description where a_id=1; here description is the table, a_id is the id of Elephant it is
the primary key in animals table
*/

FMResultSet *fResult= [db executeQuery:@"SELECT * FROM description WHERE a_id=1"];

/* Now we have to take these results in to a loop and then repeteadly loop it in order to get all the data in a our Array Object */

while([fResult next])
{
/* taking results from database to a string "eleData" */
eleData = [fResult stringForColumn:@"desc"];
/* adding data from the string object to Array */
[aElephant addObject:eleData];
/* Checking weather data has come or not */
NSLog(@"The data is %@=",eleData);
}
/* Closing the Database */
[db close];
}
And now we got our data form the Database and stored it in the array Element
In this Step as we got our data we have to now display that data in the table view of ElephantViewController.
For that we need deligate methods of TableView….Just Go to UITableView and copy the methods from there under the” #pragma mark Table view methods”….
So now we only copy the required methods of Deligate….methods with code are shown below, we don’t need to copy the “DidSelectRow”
as we are not pushing this view to another view…we are just displaying the data here…. copy deligate methods above the dealloc():
/* UITABLEVIEW Deligate Methods */

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {
return 1;
}

// Customize the number of rows in the table view.

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
return [aElephant 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.
//We are configuring my cells in table view

NSString *eData = [aElephant objectAtIndex:indexPath.row];
cell.textLabel.text = eData;

return cell;
}
So Finally we did the Code part now we have to do the Interface builder for the ElephantViewController….
Remember in the above RootViewController we did not do any interface builder why because by default the TableView is added to it….as initially we selected
“Navigation based Application”….
For adding the TableView in the ElephantViewController (click)ElephantViewController.xib > then drop TableView on to the View Window.
Important Info:
In the Connection we have to connect the datasource and deligate to the FilesOwner in the Interface Builder.
9) We have completed the Detailed View of ElephantViewController now we have to do the LionViewController:
Repeat the same code for LionView Controller after doing that Save the files and Interface Builder….
X-Code > Build > Go
This is the Final Result of our Application:
RootViewController View
Detailed View of ElephantViewController
Detailed View of LionViewController
And So Thus our Project gets Succeeded….Hope this article helps you….
And do comment on this post if you liked it….Finally Subscribe US(Object Graph) on FaceBook and Twitter….
Project Download:
You can Download Project by clicking here