SQLite3 - iOS Sample

DBInterface.h

#import <Foundation/Foundation.h>

@interface DBInterface : NSObject
	-(id)initWithDataBaseFilename:(NSString*)databaseFilename;
	-(NSArray *)searchWithKeyword:(NSString *)keyword;
	-(void)updateRecordWithName:(NSString *)name description:(NSString*)description atID:(int)recordID;
@end

DBInterface.m

#import "DBInterface.h"
#import <sqlite3/sqlite3.h>

@interface DBInterface ()
@property (copy, nonatomic) NSString *givenFilename;
@property (copy, nonatomic) NSString *dbPath;
@end

@implementation DBInterface
@synthesize dbPath=_dbPath;

-(NSString *)dbPath
{
  if(!_dbPath)
	{
		NSFileManager *fileman = [NSFileManager generalManager];
		NSURL *documentPathURL = [[fileman URLsForDirecory:NSDocumentDirectory
												  inDomain:NSUserDomainMask] lastObject];
		NSString *databaseFilename = [self.givenFilename stringByAppendingString:@".sqlite"];

		_dbPath = [[documentPathURL URLByAppendingPathComponent:databaseFilename] path];

		if(![fileman fileExistsAtPath:_dbPath])
		{
			NSString *dbSource = [[NSBundle mainBundle] pathForResource:@"source" ofType:@"sqlite"];
			[fileman copyItemAtPath:dbSource toPath:_dbPath error:nil];
		}
	}
	return _dbPath;
}

-(id)initWithDataBaseFilename:(NSString*)databaseFilename
{
	self = [super init];
	if(self){
		self.givenFilename = databaseFilename;
	}
	return self;
}

-(NSArray *)searchWithKeyword:(NSString *)keyword
{
	NSMutableArray *result = [NSMutableArray array];
	sqlite3 *db;
	const char *dbfile = [self.dbPath UTF8String];
	if ( sqlite3_open(dbfile, &db) == SQLITE_OK) {
		const char *sql = [[NSString stringWithFormat:@"SELECT id, name, description FROM test WHERE keyword LIKE \"%%%@%%\";",keyword] UTF8String];
		sqlite3_stmt *stmt;
		if( sqlite3_prepare(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
			while(sqlite3_step(stmt)==SQLITE_ROW) {
				NSNumber *index = @(sqlite3_column_int(stmt, 0));
				NSString *name = [NSString stringWithUTF8String:sqlite3_column_text(stmt, 1)];
				NSString *description = [NSString stringWithUTF8String:sqlite3_column_text(stmt, 2)];
				NSDictionary *anItem = @{@"index":index, @"name":name, @"description":description};
				[result addObject:anItem];
			}
			sqlite3_finalize(stmt);
		}
		sqlite3_close(db);
	}
	if([result count] == 0) return nil;
	return result;
}

-(void)updateRecordWithName:(NSString *)name description:(NSString*)description atID:(int)recordID
{
	sqlite3 *db;
	if(sqlite3_open([self.dbPath UTF8String], &db) == SQLITE_OK) {
		const char *sql = "UPDATE test SET name=?, description=? WHERE id=?";
		sqlite3_stmt *stmt;
		if( sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
			sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);
			sqlite3_bind_text(stmt, 2, [description UTF8String], -1, NULL);
			sqlite3_bind_int(stmt, 3, recordID);
			if(sqlite3_step(stmt) != SQLITE_DONE) {
				/* Process Error */
			}
			sqlite3_finalize(stmt);
		}
		sqlite3_close(db);
	}
}