i have implement this type of code to sqlite databade
ViewController.m
//
// ViewController.m
// SQLiteTutorial
//
// Created by Admin on 17/07/14.
// Copyright (c) 2014 __MyCompanyName__. All rights reserved.
//
#import "ViewController.h"
#import "DataController.h"
#import "Person.h"
@implementation ViewController
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
// Release any cached data, images, etc that aren't in use.
}
#pragma mark - View lifecycle
- (void)viewDidLoad
{
[super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
// Create datacontroller and initialize database
DataController *dataController = [[DataController alloc]init];
[dataController initDatabase];
//[dataController release];
// Create address and person objects
Address *address = [[Address alloc]initWithStreetName:@"Infinite Loop" andStreetNumber:[NSNumber numberWithInt:1]];
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@"yyyy-MM-dd"];
NSDate *birthday = [dateFormatter dateFromString: @"1955-02-24"];
Person *person = [[Person alloc]initWithFirstName:@"Steve" andLastName:@"Jobs" andBirthday:birthday andAddress:address];
// Insert the person
[dataController insertPerson:person];
// // Get the persons
// NSArray* persons = [dataController getPersons];
// NSLog(@"Persons count: %d", [persons count]);
// // Update the address
// Address *updateAddress = [[Address alloc]initWithStreetName:@"Microsoft Way" andStreetNumber:[NSNumber numberWithInt:666]];
// [dataController updateAddress:updateAddress];
// [updateAddress release];
// Cleanup
[dateFormatter release];
[address release];
[person release];
[DataController release];
}
- (void)viewDidUnload
{
[super viewDidUnload];
// Release any retained subviews of the main view.
// e.g. self.myOutlet = nil;
}
- (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
this my main view controller file then i have created datamanager
DataController.h
//
// DataController.h
// SQLiteTutorial
//
// Created by Admin on 17/07/14.
// Copyright (c) 2014 __MyCompanyName__. All rights reserved.
//
#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "Person.h"
#import "Address.h"
@interface DataController : NSObject
{
sqlite3 *databaseHandle;
}
-(void)initDatabase;
-(void)insertPerson:(Person*)person;
-(NSArray*)getPersons;
-(void)updateAddress:(Address*)address;
-(Address*)getAddressByPersonID:(int)personID;
@end
DataController.m
#import "DataController.h"
#import "Person.h"
@implementation DataController
// Method to open a database, the database will be created if it doesn't exist
-(void)initDatabase
{
// Create a string containing the full path to the sqlite.db inside the documents folder
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = @"/Users/Admin/Movies";//[paths objectAtIndex:0];
NSString *databasePath = [documentsDirectory stringByAppendingPathComponent:@"SQLiteTutorial3.db"];
// Check to see if the database file already exists
bool databaseAlreadyExists = [[NSFileManager defaultManager] fileExistsAtPath:databasePath];
// Open the database and store the handle as a data member
if (sqlite3_open([databasePath UTF8String], &databaseHandle) == SQLITE_OK)
{
// Create the database if it doesn't yet exists in the file system
if (!databaseAlreadyExists)
{
// Create the PERSON table
const char *sqlStatement = "CREATE TABLE IF NOT EXISTS PERSON (ID INTEGER PRIMARY KEY AUTOINCREMENT, FIRSTNAME TEXT, LASTNAME TEXT, BIRTHDAY DATE)";
char *error;
if (sqlite3_exec(databaseHandle, sqlStatement, NULL, NULL, &error) == SQLITE_OK)
{
// Create the ADDRESS table with foreign key to the PERSON table
sqlStatement = "CREATE TABLE IF NOT EXISTS ADDRESS (ID INTEGER PRIMARY KEY AUTOINCREMENT, STREETNAME TEXT, STREETNUMBER INT, PERSONID INT, FOREIGN KEY(PERSONID) REFERENCES PERSON(ID))";
if (sqlite3_exec(databaseHandle, sqlStatement, NULL, NULL, &error) == SQLITE_OK)
{
NSLog(@"Database and tables created.");
}
else
{
NSLog(@"Error: %s", error);
}
}
else
{
NSLog(@"Error: %s", error);
}
}
}
}
// Method to store a person and his associated address
-(void)insertPerson:(Person*)person
{
// Create insert statement for the person
NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO PERSON (FIRSTNAME, LASTNAME, BIRTHDAY) VALUES (\"%@\", \"%@\", \"%@\")", person.firstName, person.lastName, person.birthday];
char *error;
if ( sqlite3_exec(databaseHandle, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)
{
int personID = sqlite3_last_insert_rowid(databaseHandle);
// Create insert statement for the address
insertStatement = [NSString stringWithFormat:@"INSERT INTO ADDRESS (STREETNAME, STREETNUMBER, PERSONID) VALUES (\"%@\", \"%@\", \"%d\")", person.address.streetName, person.address.streetNumber, personID];
if ( sqlite3_exec(databaseHandle, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)
{
NSLog(@"Person inserted.");
}
else
{
NSLog(@"Error: %s", error);
}
}
else
{
NSLog(@"Error: %s", error);
}
}
// Get an array of all persons stored inside the database
-(NSArray*)getPersons
{
// Allocate a persons array
NSMutableArray *persons = [[NSMutableArray alloc]init];
// Create the query statement to get all persons
NSString *queryStatement = [NSString stringWithFormat:@"SELECT ID, FIRSTNAME, LASTNAME, BIRTHDAY FROM PERSON"];
// Prepare the query for execution
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(databaseHandle, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
{
// Iterate over all returned rows
while (sqlite3_step(statement) == SQLITE_ROW) {
// Get associated address of the current person row
int personID = sqlite3_column_int(statement, 0);
Address *address = [self getAddressByPersonID:personID];
// Convert the birthday column to an NSDate
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc]init];
dateFormatter.dateFormat = @"yyyy-MM-dd HH:mm:ss Z";
NSString *birthdayAsString = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 3)];
NSDate *birthday = [dateFormatter dateFromString: birthdayAsString];
[dateFormatter release];
// Create a new person and add it to the array
Person *person = [[Person alloc]initWithFirstName:[NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)]
andLastName:[NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 2)]
andBirthday:birthday
andAddress:address];
[persons addObject:person];
// Release the person because the array takes ownership
[person release];
}
sqlite3_finalize(statement);
}
// Return the persons array an mark for autorelease
return [persons autorelease];
}
// Get an address by means of the associated person ID
-(Address*)getAddressByPersonID:(int)personID
{
Address *address = nil;
// Create the query statement to find the correct address based on person ID
NSString *queryStatement = [NSString stringWithFormat:@"SELECT ID, STREETNAME, STREETNUMBER, PERSONID FROM ADDRESS WHERE PERSONID = %d", personID];
// Prepare the query for execution
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(databaseHandle, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
{
// Create a new address from the found row
while (sqlite3_step(statement) == SQLITE_ROW) {
address = [[Address alloc]initWithStreetName:[NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)]
andStreetNumber:[NSNumber numberWithInt:sqlite3_column_int(statement, 2)]];
}
sqlite3_finalize(statement);
}
// Return the found address and mark for autorelease
return [address autorelease];
}
// Close the database connection when the DataController is disposed
- (void)dealloc {
sqlite3_close(databaseHandle);
[super dealloc];
}
// Update an address
-(void)updateAddress:(Address*)address
{
// This is left as an exercise to extend both the Address and Person class with an ID and use this ID for the update
int addressID = 1;
// Create the replace statement for the address
NSString *replaceStatement = [NSString stringWithFormat:@"REPLACE INTO ADDRESS (ID, STREETNAME, STREETNUMBER) VALUES (%d, \"%@\", %@);", addressID, address.streetName, address.streetNumber];
// Execute the replace
char *error;
if (sqlite3_exec(databaseHandle, [replaceStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)
{
NSLog(@"Address updated");
}
else
{
NSLog(@"Error: %s", error);
}
}
@end
Address.h
#import <Foundation/Foundation.h>
@interface Address : NSObject
{
NSString *streetName;
NSNumber *streetNumber;
}
@property (nonatomic, retain) NSString* streetName;
@property (nonatomic, retain) NSNumber* streetNumber;
-(id)initWithStreetName:(NSString*)aStreetName
andStreetNumber:(NSNumber*)streetNumber;
@end
Address.m
#import "Address.h"
@implementation Address
@synthesize streetName;
@synthesize streetNumber;
// Custom initializer
-(id)initWithStreetName:(NSString*)aStreetName
andStreetNumber:(NSNumber*)aStreetNumber
{
self = [super init];
if(self) {
self.streetName = aStreetName;
self.streetNumber = aStreetNumber;
}
return self;
}
// Cleanup all contained properties
- (void)dealloc {
[self.streetName release];
[self.streetNumber release];
[super dealloc];
}
@end
One could say that this tutorial is composed by three parts. In the first and most important one, we are going to create a new class and in there we’ll implement all the database handling. As I have already said in the introduction, after we have it finished, you can take it and use it as a reusable component to your own apps. In the second part we are going to leave Xcode for a while, as it’s necessary to work in the Terminal and in the SQLite command line environment?