This is the simple iPhone project which is based on SQLite database.This is very simple way for using SQLite in out iPhone APP.
We have to add a SQLite framework in our project for using database. Do a control-click (right click) on the Frameworks folder. Click Add -> Existing Frameworks. So in the search bar type in libsqlite3. Now add libsqlite3.0.dylib.
Here i have done all the basic database operation
- Add/insert
- Delete
- Update
- Retrieve
Demp app is developed in Xcode 4.3.2 and IOS SDK 5
AppDelegate.h
===============
#import <UIKit/UIKit.h>
#import "sqlite3.h"
@class ViewController;
@interface AppDelegate : UIResponder <UIApplicationDelegate>{
sqlite3 *contactDB;
NSString *databasePath;
NSString *relinkUserId;
}
@property (strong, nonatomic) UIWindow *window;
@property (strong, nonatomic) ViewController *viewController;
@end
Create the database structure in AppDelegate once
AppDelegate.m
===============
#import "AppDelegate.h"
#import "ViewController.h"
@implementation AppDelegate
@synthesize window = _window;
@synthesize viewController = _viewController;
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
[self createDb];
self.window = [[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]];
// Override point for customization after application launch.
self.viewController = [[ViewController alloc] initWithNibName:@"ViewController" bundle:nil];
UINavigationController *navController = [[UINavigationController alloc] initWithRootViewController:self.viewController];
self.window.rootViewController = navController;
[self.window makeKeyAndVisible];
return YES;
}
-(void)createDb{
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: @"Test.db"]];
NSFileManager *filemgr = [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath: databasePath ] == NO)
{
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
char *errMsg;
const char *sql_stmt = "CREATE TABLE IF NOT EXISTS STUDENT (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT,EMAIL TEXT)";
if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
{
NSLog( @"Failed to create STUDENT table");
}
sqlite3_close(contactDB);
} else {
NSLog( @"Failed to open/create database");
}
}
}
@end
ViewController.h
================
#import <UIKit/UIKit.h>
#import "sqlite3.h"
@interface ViewController : UIViewController{
IBOutlet UITextField *nameTf,*phoneTf,*emailTf,*addressTf;
NSString *name,*phone,*email,*address,*databasePath;
sqlite3 *contactDB;
}
@property(nonatomic,retain)NSString *name,*phone,*email,*address;
-(IBAction)save:(id)sender;
@end
ViewController.m
===================
How to save/insert data.
-(IBAction)save:(id)sender{
name=nameTf.text;
phone=phoneTf.text;
email=emailTf.text;
address=addressTf.text;
databasePath=[self dataBasePath];
// NSFileManager *filemgr = [NSFileManager defaultManager];
sqlite3_stmt *statement;
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO STUDENT (name, address, phone,email) VALUES (\"%@\", \"%@\", \"%@\",\"%@\")", name, address, phone,email];
const char *insert_stmt = [insertSQL UTF8String];
sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
if (sqlite3_step(statement) == SQLITE_DONE)
{
NSInteger lastRowId = sqlite3_last_insert_rowid(contactDB);
NSLog(@"lastRowId==%d",lastRowId);
}else {
NSLog(@"Failed to add contact");
NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(contactDB));
}
sqlite3_finalize(statement);
sqlite3_close(contactDB);
}
}
-(NSString *)dataBasePath{
NSString *docsDir;
NSArray *dirPaths;
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];
// Build the path to the database file
return [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"Test.db"]];
}
How to retrieve data from table
==========
-(void)readStudentlist{
self.studentList=[[NSMutableArray alloc] init];
// NSFileManager *filemgr = [NSFileManager defaultManager];
const char *dbpath = [[self dataBasePath] UTF8String];
if(sqlite3_open(dbpath , &contactDB) == SQLITE_OK) {
// Setup the SQL Statement and compile it for faster access
const char *sqlStatement = "select * from STUDENT order by NAME asc";
sqlite3_stmt *compiledStatement;
if(sqlite3_prepare_v2(contactDB, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
// Loop through the results and add them to the feeds array
while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
// Read the data from the result row
int rowID = sqlite3_column_int(compiledStatement, 0);
//NSLog(@"primaryKey==%d",primaryKey);
NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
NSString *aAdd = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
NSString *aPh = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
NSString *aEmail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];
NSLog(@"%@",aName);
CustomerVO *vo = [[CustomerVO alloc] init];
vo.sName=aName;
vo.sAddress=aAdd;
vo.sPhone=aPh;
vo.sEmail=aEmail;
vo.sId=rowID;
[self.studentList addObject:vo];
}
}
// Release the compiled statement from memory
sqlite3_finalize(compiledStatement);
}
sqlite3_close(contactDB);
}
Delete a specific data
======================
-(void)deleteStudent:(int)rowId{
const char *dbpath = [[self dataBasePath] UTF8String];
sqlite3_stmt *statement;
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
NSString *deleteSQL = [NSString stringWithFormat: @"delete from STUDENT where id=%d",rowId];
const char *del_stmt = [deleteSQL UTF8String];
sqlite3_prepare_v2(contactDB, del_stmt, -1, &statement, NULL);
if (sqlite3_step(statement) == SQLITE_DONE)
{
//[self dismissModalViewControllerAnimated:YES];
//NSInteger lastRowId = sqlite3_last_insert_rowid(contactDB);
NSLog(@"deleted folder");
} else {
NSLog(@"Failed to add contact");
NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(contactDB));
}
sqlite3_finalize(statement);
sqlite3_close(contactDB);
}
}
CustomerVO.h
=============
#import <Foundation/Foundation.h>
@interface CustomerVO : NSObject{
NSString *sName;
NSString *sAddress;
NSString *sPhone;
NSString *sEmail;
NSInteger sId;
}
@property (assign, nonatomic) NSInteger sId;
@property (nonatomic, retain) NSString *sName,*sAddress,*sPhone,*sEmail;
@end
CustomerVO.m
=============
#import "CustomerVO.h"
@implementation CustomerVO
@synthesize sName,sAddress,sEmail,sPhone,sId;
@end
