iOS SQLite example with Create Insert Select Update and Delete operations

The underlying database for iOS applications to store large amounts of data is SQLite. It is considered a de facto industry standard for lightweight embedded SQL database programming. You can use the core data framework provided in the iOS SDK to interact with the database in an abstract manner without actually going into the details how the low-level data management is implemented. The other solution is to interact with the SQLite database directly using SQL CRUD operations most of which programmers are familiar with and queries are almost similar to other databases such as MySQL, DB2, Oracle, etc. In this tutorial we are going to do the later.

In this example we are going to create a simple employee maintenance application that let's a user add, edit and delete an employee from the database. We also fetch all existing employees to display them in a UITableView for easy selection. Please note after you create your new project just go into the Build Phases and add libsqlite3.dylib library to the list of libraries and frameworks. For help see the images below …

Interface file for the App Delegate - SqliteDataAppDelegate.h

#import <UIKit/UIKit.h>
#import "DisplayViewController.h"

@interface SqliteDataAppDelegate : UIResponder <UIApplicationDelegate>

@property (strong, nonatomic) UIWindow *window;
@property (strong, nonatomic) UINavigationController *navigationController;
@property (strong, nonatomic) DisplayViewController *displayViewController;

@end

Implementation file for the App Delegate - SqliteDataAppDelegate.m

#import "SqliteDataAppDelegate.h"

@implementation SqliteDataAppDelegate

@synthesize navigationController;
@synthesize displayViewController;

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
    self.window = [[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]];
    
    //create the navigation controller and add the controllers view to the window
    navigationController = [[UINavigationController alloc] init];
    [self.window addSubview:[self.navigationController view]];
    
    //check if the display viewcontroller eixsts, otherwise create it
    if(self.displayViewController == nil)
    {
        DisplayViewController *inputView = [[DisplayViewController alloc] init];
        self.displayViewController = inputView;
    }
    
    //push the display viewcontroller into the navigation view controller stack
    [self.navigationController pushViewController:self.displayViewController animated:YES];
    
    self.window.backgroundColor = [UIColor whiteColor];
    [self.window makeKeyAndVisible];
    return YES;

}


@end

Interface file for the Employee Object - Employee.h

#import <Foundation/Foundation.h>

@interface Employee : NSObject

@property (nonatomic) NSInteger employeeID;
@property (nonatomic, strong) NSString *name;
@property (nonatomic, strong) NSString *department;
@property (nonatomic) NSInteger age;

@end

Implementation file for the Employee Object - Employee.m

#import "Employee.h"

@implementation Employee

@synthesize employeeID, name, department, age;

@end

Interface file for the SQLite Database Utility - EmployeeDbUtil.h

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "Employee.h"

@interface EmployeeDbUtil : NSObject {
    sqlite3 *mySqliteDB;
}

@property (nonatomic, strong) NSString *databasePath;

- (void) initDatabase;
- (BOOL) saveEmployee:(Employee *)employee;
- (BOOL) deleteEmployee:(Employee *)employee;
- (NSMutableArray *) getEmployees;
- (Employee *) getEmployee:(NSInteger) employeeID;

@end

Implementation file for the SQLite Database Utility - EmployeeDbUtil.m

#import "EmployeeDbUtil.h"

@implementation EmployeeDbUtil

@synthesize databasePath;

- (void) initDatabase {
    
    NSString *docsDir;
    NSArray *dirPaths;
    
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];
    
    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString:
                    [docsDir stringByAppendingPathComponent:@"employees.db"]];
    
    NSFileManager *filemgr = [NSFileManager defaultManager];
    
    //the file will not be there when we load the application for the first time
    //so this will create the database table
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &mySqliteDB) == SQLITE_OK)
        {
            char *errMsg;
            NSString *sql_stmt = @"CREATE TABLE IF NOT EXISTS EMPLOYEES (";
            sql_stmt = [sql_stmt stringByAppendingString:@"id INTEGER PRIMARY KEY AUTOINCREMENT, "];
            sql_stmt = [sql_stmt stringByAppendingString:@"name TEXT, "];
            sql_stmt = [sql_stmt stringByAppendingString:@"department TEXT, "];
            sql_stmt = [sql_stmt stringByAppendingString:@"age TEXT)"];
            
            if (sqlite3_exec(mySqliteDB, [sql_stmt UTF8String], NULL, NULL, &errMsg) != SQLITE_OK)
            {
                NSLog(@"Failed to create table");
            }
            else
            {
                NSLog(@"Employees table created successfully");
            }
            
            sqlite3_close(mySqliteDB);
            
        } else {
            NSLog(@"Failed to open/create database");
        }
    }
    
}

//save our data
- (BOOL) saveEmployee:(Employee *)employee
{
    BOOL success = false;
    sqlite3_stmt *statement = NULL;
    const char *dbpath = [databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &mySqliteDB) == SQLITE_OK)
    {
        if (employee.employeeID > 0) {
            NSLog(@"Exitsing data, Update Please");
            NSString *updateSQL = [NSString stringWithFormat:@"UPDATE EMPLOYEES set name = '%@', department = '%@', age = '%@' WHERE id = ?",
                                    employee.name,
                                    employee.department,
                                    [NSString stringWithFormat:@"%d", employee.age]];
            
            const char *update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(mySqliteDB, update_stmt, -1, &statement, NULL );
            sqlite3_bind_int(statement, 1, employee.employeeID);
            if (sqlite3_step(statement) == SQLITE_DONE)
            {
                success = true;
            }
            
        }
        else{
            NSLog(@"New data, Insert Please");
            NSString *insertSQL = [NSString stringWithFormat:
                                   @"INSERT INTO EMPLOYEES (name, department, age) VALUES (\"%@\", \"%@\", \"%@\")",
                                   employee.name,
                                   employee.department,
                                   [NSString stringWithFormat:@"%d", employee.age]];
            
            const char *insert_stmt = [insertSQL UTF8String];
            sqlite3_prepare_v2(mySqliteDB, insert_stmt, -1, &statement, NULL);
            if (sqlite3_step(statement) == SQLITE_DONE)
            {
                success = true;
            }
        }
        
        sqlite3_finalize(statement);
        sqlite3_close(mySqliteDB);
        
    }
    
    return success;
}


//get a list of all our employees
- (NSMutableArray *) getEmployees
{
    NSMutableArray *employeeList = [[NSMutableArray alloc] init];
    const char *dbpath = [databasePath UTF8String];
    sqlite3_stmt    *statement;
    
    if (sqlite3_open(dbpath, &mySqliteDB) == SQLITE_OK)
    {
        NSString *querySQL = @"SELECT id, name, department, age FROM EMPLOYEES";
        const char *query_stmt = [querySQL UTF8String];
        
        if (sqlite3_prepare_v2(mySqliteDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                Employee *employee = [[Employee alloc] init];
                employee.employeeID = sqlite3_column_int(statement, 0);
                employee.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
                employee.department = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
                employee.age = sqlite3_column_int(statement, 3);
                [employeeList addObject:employee];
            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(mySqliteDB);
    }
    
    return employeeList;
}


//get information about a specfic employee by it's id
- (Employee *) getEmployee:(NSInteger) employeeID
{
    Employee *employee = [[Employee alloc] init];
    const char *dbpath = [databasePath UTF8String];
    sqlite3_stmt    *statement;
    
    if (sqlite3_open(dbpath, &mySqliteDB) == SQLITE_OK)
    {
        NSString *querySQL = [NSString stringWithFormat:
                              @"SELECT id, name, department, age FROM EMPLOYEES WHERE id=%d",
                              employeeID];
        const char *query_stmt = [querySQL UTF8String];
        
        if (sqlite3_prepare_v2(mySqliteDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            if (sqlite3_step(statement) == SQLITE_ROW)
            {
                employee.employeeID = sqlite3_column_int(statement, 0);
                employee.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
                employee.department = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
                employee.age = sqlite3_column_int(statement, 3);
            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(mySqliteDB);
    }
    
    return employee;
}

//delete the employee from the database
- (BOOL) deleteEmployee:(Employee *)employee
{
    BOOL success = false;
    sqlite3_stmt *statement = NULL;
    const char *dbpath = [databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &mySqliteDB) == SQLITE_OK)
    {
        if (employee.employeeID > 0) {
            NSLog(@"Exitsing data, Delete Please");
            NSString *deleteSQL = [NSString stringWithFormat:@"DELETE from EMPLOYEES WHERE id = ?"];
            
            const char *delete_stmt = [deleteSQL UTF8String];
            sqlite3_prepare_v2(mySqliteDB, delete_stmt, -1, &statement, NULL );
            sqlite3_bind_int(statement, 1, employee.employeeID);
            if (sqlite3_step(statement) == SQLITE_DONE)
            {
                success = true;
            }
            
        }
        else{
            NSLog(@"New data, Nothing to delete");
            success = true;
        }
        
        sqlite3_finalize(statement);
        sqlite3_close(mySqliteDB);
        
    }
    
    return success;
}


@end

Interface file for the Employee Table view - DisplayViewController.h

#import <UIKit/UIKit.h>
#import "EditViewController.h"
#import "Employee.h"
#import "EmployeeDbUtil.h"

@interface DisplayViewController : UIViewController <UITableViewDelegate,UITableViewDataSource>

@property (strong, nonatomic) EditViewController *editViewController;
@property (nonatomic, strong) UITableView *myTableView;
@property (strong, nonatomic) EmployeeDbUtil *employeeDbUtil;

@property (strong, nonatomic) NSMutableArray *employeeList;

@end

Implementation file for the Employee Table view - DisplayViewController.m

#import "DisplayViewController.h"

@interface DisplayViewController ()

@end

@implementation DisplayViewController

@synthesize editViewController;
@synthesize employeeDbUtil;
@synthesize myTableView;
@synthesize employeeList;

- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil
{
    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
    if (self) {
        // Custom initialization
        self.employeeDbUtil = [[EmployeeDbUtil alloc] init];
        [employeeDbUtil initDatabase];
    }
    return self;
}

- (void)viewDidLoad
{
    [super viewDidLoad];
 
    //set the title of the navigation view
    [self.navigationItem setTitle:@"Employee List"];
    
    //create the table view with a given style
    self.myTableView = [[UITableView alloc] initWithFrame:self.view.bounds
                                                    style:UITableViewStyleGrouped];
    
    //set the table view delegate to the current so we can listen for events
    self.myTableView.delegate = self;
    //set the datasource for the table view to the current object
    self.myTableView.dataSource = self;
    
    //make sure our table view resizes correctly
    self.myTableView.autoresizingMask = UIViewAutoresizingFlexibleWidth |
    UIViewAutoresizingFlexibleHeight;
    
    //add the table view to the main view
    [self.view addSubview:self.myTableView];

    
    //create a submit button in the navigation bar
    UIBarButtonItem *myButton = [[UIBarButtonItem alloc]
                                 initWithTitle:@"Add"
                                 style:UIBarButtonItemStylePlain
                                 target:self
                                 action:@selector(addEmployee:)];
    [self.navigationItem setRightBarButtonItem:myButton];
}

- (void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:YES];
    
    //get Employee List
    self.employeeList = [employeeDbUtil getEmployees];
    
    //reload the data in the table view
    [self.myTableView reloadData];
    
}


//asks the data source to return the number of sections in the table view
//we are retuneing the number of continents
- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView{
    
    NSLog(@"%@",@"Called number of Sections in Table View");
    NSInteger numberOfSections = 1;
    return numberOfSections;
}

//asks the data source to return the number of rows in a given section of a table view
//we are returning the number of countries in a given continent
- (NSInteger)tableView:(UITableView *)tableView
 numberOfRowsInSection:(NSInteger)section{
    
    NSLog(@"%@",@"Called number of Rows in a Section");
    NSInteger numberOfRows = self.employeeList.count;
    return numberOfRows;
}


//asks the data source for a cell to insert in a particular location of the table view
- (UITableViewCell *) tableView:(UITableView *)tableView
          cellForRowAtIndexPath:(NSIndexPath *)indexPath{
    
    NSLog(@"%@",@"Render cell at a given Index Path Section and Row");
    UITableViewCell *myCellView = nil;
    
    if ([tableView isEqual:self.myTableView]){
        
        static NSString *TableViewCellIdentifier = @"MyCells";
        
        //this method dequeues an existing cell if one is available or creates a new one
        //if no cell is available for reuse, this method returns nil
        myCellView = [tableView dequeueReusableCellWithIdentifier:TableViewCellIdentifier];
        if (myCellView == nil){
            //create a new cell
            myCellView = [[UITableViewCell alloc]
                          initWithStyle:UITableViewCellStyleDefault
                          reuseIdentifier:TableViewCellIdentifier];
        }
        
        //get the employee based on the index path section
        Employee *employee = [[Employee alloc] init];
        employee = [self.employeeList objectAtIndex:indexPath.row];
        
        //display the employee name in main label of the cell
        myCellView.textLabel.text = [NSString stringWithFormat:@"%@",
                                     employee.name];
        
        //set the accessory view to be a clickable button
        myCellView.accessoryType = UITableViewCellAccessoryDetailDisclosureButton;
        
    }
    return myCellView;
}


//informs the delegate that the specified row is now selected
- (void) tableView:(UITableView *)tableView
didSelectRowAtIndexPath:(NSIndexPath *)indexPath{
    
    if ([tableView isEqual:self.myTableView]){
        NSLog(@"%@",
              [NSString stringWithFormat:@"Cell %ld in Section %ld is selected",
               (long)indexPath.row, (long)indexPath.section]);
        [self editEmployee:indexPath];
       }
}

//informs the delegate that the user tapped the accessory view associated with a given row
- (void) tableView:(UITableView *)tableView
accessoryButtonTappedForRowWithIndexPath:(NSIndexPath *)indexPath{
    
    if ([tableView isEqual:self.myTableView]){
        NSLog(@"%@",
              [NSString stringWithFormat:@"Cell %ld accessory button in Section %ld is tapped",
               (long)indexPath.row, (long)indexPath.section]);
        
        //let go ahead and allow edit of the employee information
        [self editEmployee:indexPath];
    }
}

- (void) editEmployee:(NSIndexPath *)indexPath {
    
    //if the edit view controller doesn't exists create it
    if(self.editViewController == nil){
        EditViewController *editView = [[EditViewController alloc] init];
        self.editViewController = editView;
    }
    
    //pass the employee id to the edit view controller
    Employee *employee = [[Employee alloc] init];
    employee = [employeeList objectAtIndex:indexPath.row];
    [self.editViewController setEmployeeID:employee.employeeID];
    
    //tell the navigation controller to push a new view into the stack
    [self.navigationController pushViewController:self.editViewController animated:YES];
    
}

- (void) addEmployee:(id)sender {
    
    //get reference to the button that requested the action
    UIBarButtonItem *myButton = (UIBarButtonItem *)sender;
    
    //check which button it is, if you have more than one button on the screen
    //you must check before taking necessary action
    if([myButton.title isEqualToString:@"Add"]){
        NSLog(@"Clicked on the bar button");
        
        //if the edit view controller doesn't exists create it
        if(self.editViewController == nil){
            EditViewController *editView = [[EditViewController alloc] init];
            self.editViewController = editView;
        }
        
        //since we are in edit mode we don't have an employee id
        [self.editViewController setEmployeeID:0];
        
        //tell the navigation controller to push a new view into the stack
        [self.navigationController pushViewController:self.editViewController animated:YES];
        
    }
    
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end

Interface file for the Employee Maintenance - EditViewController.h

#import <UIKit/UIKit.h>
#import "Employee.h"
#import "EmployeeDbUtil.h"

@interface EditViewController : UIViewController

@property (nonatomic, strong) UITextField *name;
@property (nonatomic, strong) UITextField *department;
@property (nonatomic, strong) UITextField *age;

@property (nonatomic) NSInteger employeeID;
@property (strong, nonatomic) EmployeeDbUtil *employeeDbUtil;

@end

Implementation file for the Employee Maintenance - EditViewController.m

#import "EditViewController.h"

@interface EditViewController ()

@end

@implementation EditViewController

@synthesize name, department, age;
@synthesize employeeDbUtil;
@synthesize employeeID;

- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil
{
    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
    if (self) {
        // Custom initialization
        self.employeeDbUtil = [[EmployeeDbUtil alloc] init];
        [employeeDbUtil initDatabase];
    }
    return self;
}

- (void)viewDidLoad
{
    [super viewDidLoad];
    NSLog(@"View Loading...");
    
 //set the title of the navigation view
    [self.navigationItem setTitle:@"Employee Info"];
    
    //create screen layout
    [self screenLayout];
    
    //create a submit button in the navigation bar
    UIBarButtonItem *myButton = [[UIBarButtonItem alloc]
                                 initWithTitle:@"Save"
                                 style:UIBarButtonItemStylePlain
                                 target:self
                                 action:@selector(saveEmployee:)];
    [self.navigationItem setRightBarButtonItem:myButton];
}

- (void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:YES];
    
    //well if we have the employee if then its an edit mode
    //load the existing values
    if(self.employeeID > 0){
        Employee *employee = [[Employee alloc] init];
        employee = [employeeDbUtil getEmployee:self.employeeID];
        name.text = employee.name;
        department.text = employee.department;
        age.text = [NSString stringWithFormat:@"%d", employee.age];
    }
    //add mode clear our fields
    else {
        name.text = @"";
        department.text = @"";
        age.text = @"";
    }
    
    
}

//save the employee information
- (void) saveEmployee:(id)sender {
    
    //get reference to the button that requested the action
    UIBarButtonItem *myButton = (UIBarButtonItem *)sender;
    NSLog(@"Clicked on the %@ button",myButton.title);
    
    //check which button it is, if you have more than one button on the screen
    //you must check before taking necessary action
    if([myButton.title isEqualToString:@"Save"]){
        
        Employee *employee = [[Employee alloc] init];
        employee.employeeID = self.employeeID;
        employee.name = self.name.text;
        employee.department = self.department.text;
        employee.age = [self.age.text intValue];
        [employeeDbUtil saveEmployee:employee];
        
        [self.navigationController popViewControllerAnimated:YES];
        
    }
    
}

//delete the employee
- (void) deleteEmployee:(id)sender {
    
    //get reference to the button that requested the action
    UIButton *myButton = (UIButton *)sender;
    NSLog(@"Clicked on the %@ button",myButton.currentTitle);
    
    if([myButton.currentTitle isEqualToString:@"Delete"]){
        
        Employee *employee = [[Employee alloc] init];
        employee.employeeID = self.employeeID;
        [employeeDbUtil deleteEmployee:employee];
        
        [self.navigationController popViewControllerAnimated:YES];
        
    }
    
}


- (void) screenLayout {
    
    CGRect myFrame = CGRectMake(10.0f, 10.0f, 100.0f, 30.0f);
    UILabel *myLabel = [[UILabel alloc] initWithFrame:myFrame];
    myLabel.font = [UIFont boldSystemFontOfSize:16.0f];
    myLabel.textAlignment =  NSTextAlignmentRight;
    myLabel.text = @"Name:";
    [self.view addSubview:myLabel];
    
    myFrame.origin.y += 35.0f;
    myLabel = [[UILabel alloc] initWithFrame:myFrame];
    myLabel.font = [UIFont boldSystemFontOfSize:16.0f];
    myLabel.textAlignment =  NSTextAlignmentRight;
    myLabel.text = @"Department:";
    [self.view addSubview:myLabel];
    
    myFrame.origin.y += 35.0f;
    myLabel = [[UILabel alloc] initWithFrame:myFrame];
    myLabel.font = [UIFont boldSystemFontOfSize:16.0f];
    myLabel.textAlignment =  NSTextAlignmentRight;
    myLabel.text = @"Age:";
    [self.view addSubview:myLabel];
    
    myFrame = CGRectMake(115.0f, 10.0f, 200.0f, 30.0f);
    name = [[UITextField alloc] init];
    [name setFrame:myFrame];
    [name setBorderStyle: UITextBorderStyleRoundedRect];
    [self.view addSubview:name];
    
    myFrame.origin.y += 35.0f;
    department = [[UITextField alloc] init];
    [department setFrame:myFrame];
    [department setBorderStyle: UITextBorderStyleRoundedRect];
    [self.view addSubview:department];
    
    myFrame.origin.y += 35.0f;
    age = [[UITextField alloc] init];
    [age setFrame:myFrame];
    [age setBorderStyle: UITextBorderStyleRoundedRect];
    [self.view addSubview:age];
    
    UIImage *delete = [UIImage imageNamed:@"delete.png"];
    UIButton *myButton = [UIButton buttonWithType:UIButtonTypeCustom];
    myButton.frame = CGRectMake(10.0f, 10.0f, 35.0f, 35.0f);
    [myButton setImage:delete forState:UIControlStateNormal];
    [myButton setTitle:@"Delete" forState:UIControlStateNormal];
    myButton.titleLabel.hidden = TRUE;
    [myButton addTarget:self
                action:@selector(deleteEmployee:)
                forControlEvents:UIControlEventTouchUpInside];
    [self.view addSubview:myButton];
    
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end

Related Articles