Skip to content

Instantly share code, notes, and snippets.

@sooop
Last active Dec 25, 2015
Embed
What would you like to do?
SQLite3 - iOS Sample
#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
#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);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment