Helper class for using SQLite database in iOS

SQLite database has native support in iOS. The post provides a simple helper class that you can use to retrieve data and perform update operations on the data.

The helper class uses the libsqlite3.dylib library. Link the XCode project with the library. Create a helper class in Objective-C: DbHelper.

Header file

Define the DbHelper class with a static method (getInstance) to create a singleton instance. Add methods to execute a query (executeQuery) or command (executeCommand).

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

@interface DbHelper : NSObject
{
    sqlite3 *database;
}
-(bool)executeCommand:(NSString *)sql;
-(bool)executeQuery:(NSString *)sql statement:(sqlite3_stmt **)statement;
+(DbHelper *)getInstance;
@end

Moving the database to Caches Directory

The database is part of the application bundle. For querying the database, use it directly from the application bundle. For performing update operations, move the database to Library directory of the Application Sandbox.  Don’t move it to the Documents directory of the application sandbox. If the database is not created for the user.

Move it to the Caches sub-directory within the Library directory. The Caches directory is not backed up by iTunes. When there is a space constraint in the device, the system deletes the cache. But only when the application is not in use.

- (void)copyDbIfNeeded
{
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSString *cacheDir = [NSSearchPathForDirectoriesInDomains
    (NSCachesDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *path = [cacheDir stringByAppendingPathComponent:
        @"accounting.sqlite"];
    BOOL isDir;
    if(![fileManager fileExistsAtPath:path isDirectory:&isDir]) {
        NSString *bundlePath = [[NSBundle mainBundle] 
                                pathForResource:@"accounting"
                                ofType:@"sqlite" inDirectory:@""];
        // will subdirectories be created. sometimes, the cache dir gets deleted.
        [fileManager copyItemAtPath:bundlePath  toPath:path error:nil];
    }
}

Call the method when the application loads.

Opening the database

Open the database in the init method of the helper class.

-(id)init
{
    if ((self = [super init])) {
        NSString *cacheDir = [NSSearchPathForDirectoriesInDomains
        (NSCachesDirectory, NSUserDomainMask, YES) objectAtIndex:0];
        NSString *dbPath = [cacheDir 
        stringByAppendingPathComponent:@"accounting.sqlite"];
        
        if (sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {
            NSLog(@"Failed to open database!");
        }
    }
    return self;
}

Implementing as Singleton

The helper object is implemented as a singleton.

+(DbHelper *)getInstance
{
    static DbHelper *instance;
    @synchronized(self) {
        if(instance==nil)
            instance = [[DbHelper alloc] init];
    }
    return instance;
}

Querying the Database

To execute queries on the database, use the executeQuery method. Querying the database will yield a statement object. The statement object has a cursor. Fill entity objects using the cursor.

-(bool)executeQuery:(NSString *)sql statement:(sqlite3_stmt **)statement
{
    const char *sql_stmt = [sql UTF8String];
    return sqlite3_prepare_v2(database, sql_stmt,
                              -1, statement, NULL) == SQLITE_OK;
}

Executing commands on the Database

To execute commands on the database, use the executeCommand method.

-(bool)executeCommand:(NSString *)sql
{
    sqlite3_stmt *statement;
    const char *sql_stmt = [sql UTF8String];
    if(sqlite3_prepare_v2(database, sql_stmt,
                          -1, &statement, NULL) == SQLITE_OK) {
        bool done = sqlite3_step(statement) == SQLITE_DONE;
        sqlite3_finalize(statement);
        return done;
    }
    return false;
}

Create a statement object from a SQL query. Executes the statement object. And don’t forget to dispose the statement object.

We have completed our simple helper class which executes a query or command on the database.

Related Posts

Leave a Reply

Your email address will not be published.