Zachary Powell

Software Programmer

Zachary Powell

Download my CV Send me a Message

SQLite

Using SQlite on iOS

Zachary Powell - 21/12/2015

We have in the past looked at using SQlite on Android, today we shall look at doing the same under iOS.

Before we start, I will assume that you either have a pre-existing app you want to use SQLite with, or you have just created a new project within Xcode. Either way will work well.

SQLite on iOS

Before we start with the actual implementation, we need to import the SQLite 3 library into our project. This will allow us to then use it within our database handler class.

First in the project navigator, click on your projects name. Make sure the general tab is selected under “Linked Frameworks and Libraries” section, then click on the small plus icon.

Screen Shot 2015-12-17 at 18.09.28

This will bring up a window that allows you to select frameworks and libraries to be added. Here, we need to search for sqlite and then select the item called “libsqlite3.tdb”. Finally click on ‘add’ to actually add the library to your project.

Screen Shot 2015-12-17 at 18.14.30

Objective C Object Class

Just as with the Android example, lets first make a simple Object class that we can use to save and get the data from the database.

BuildingLocation.h

#import <Foundation/Foundation.h>

@interface BuildingLocation : NSObject {
    NSString *_name;
    NSString *_description;
    float  _Long;
    float  _Lat;
}

@property (nonatomic, copy) NSString *name;
@property (nonatomic, copy) NSString *description;
@property (nonatomic) float Long;
@property (nonatomic) float Lat;

- (id)init:(NSString *)name description:(NSString *)description Long:(float)Long Lat:(float)Lat;

@end

BuildingLocation.m

#import "BuildingLocation.h"

@implementation BuildingLocation

@synthesize name = _name;
@synthesize description = _description;
@synthesize Long = _Long;
@synthesize Lat =_Lat;

-(id)init:(NSString *)name description:(NSString *)description Long:(float)Long Lat:(float)Lat {
    if ((self = [super init])) {
        self.name = name;
        self.description = description;
        self.Long = Long;
        self.Lat = Lat;
    }
    return self;
}

@end

The above header and main file are much the same as our Android version. We set up a couple of variables to store a location, and then set up an init function. Unlike the Java Android version, we did not set up getter and setter methods as this time around we will access the variables directly.

Database Handler

Now we shall set up the Header and main file for the actual database handler.

DBHandler.h

#import <Foundation/Foundation.h>
#import "BuildingLocation.h"
#import <sqlite3.h>
#import <CoreLocation/CoreLocation.h>


@interface DBHelper : NSObject
{
    NSString *databasePath;
}

+(DBHelper*)getSharedInstance;

-(BOOL)createDB;

-(BOOL) clearLocations;

-(BOOL) insertLocation:(BuildingLocation*)location;

-(NSArray*) getAllLocations;

@end

Here we just define the methods that we will be creating within the Main file. Also make sure to include the header file for your object class and the SQlite library.

DBHandler.m

#import "DBHelper.h"
static DBHelper *sharedInstance = nil;
static sqlite3 *database = nil;
static sqlite3_stmt *statement = nil;

@implementation DBHelper

+(DBHelper*)getSharedInstance{
    if (!sharedInstance) {
        sharedInstance = [[super allocWithZone:NULL]init];
        [sharedInstance createDB];
    }
    return sharedInstance;
}


-(BOOL)createDB{
    NSString *docsDir;
    NSArray *dirPaths;
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains
    (NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString:
                    [docsDir stringByAppendingPathComponent: @"town.db"]];
    BOOL isSuccess = YES;
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &database) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE if not exists LocationInfo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Description TEXT, Longitude TEXT, Lat TEXT)";
            
            if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create table");
            }
            sqlite3_close(database);
            return  isSuccess;
        }
        else {
            isSuccess = NO;
            NSLog(@"Failed to open/create database");
        }
    }
    return isSuccess;
}

-(BOOL) clearLocations{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *insertSQL = @"DELETE FROM LocationInfo";
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            return YES;
        }
        else {
            return NO;
        }
    }
    return NO;
}

-(BOOL) insertLocation:(BuildingLocation*)location{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
     
        NSNumber *longNumber = [NSNumber numberWithFloat:location.Long];
        NSNumber *latNumber = [NSNumber numberWithFloat:location.Lat];

        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO LocationInfo (Name, Description, Longitude, Lat) values (\"%@\",\"%@\", \"%@\", \"%@\")",location.name, location.description, [longNumber stringValue], [latNumber stringValue]];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            return YES;
        }
        else {
            NSLog(@"Error while updating. '%s'", sqlite3_errmsg(database));
            return NO;
        }
    }
    return NO;
}

-(NSArray*) getAllLocations{
    const char *dbpath = [databasePath UTF8String];
    NSString *querySQL;
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *querySQL = @"";
        
        querySQL = @"SELECT * FROM LocationInfo";
       
        const char *query_stmt = [querySQL UTF8String];
        NSMutableArray *resultArray = [[NSMutableArray alloc]init];
        if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                BuildingLocation *location = [[BuildingLocation alloc] init];
                
                location.name = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
                location.description = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
                NSNumberFormatter * nFormatter = [[NSNumberFormatter alloc] init];
                [nFormatter setNumberStyle:NSNumberFormatterDecimalStyle];
                NSNumber *numlong = [nFormatter numberFromString:[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)]];
                NSNumber *numlat = [nFormatter numberFromString:[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)]];

                location.Long = [numlong floatValue];
                location.Lat = [numlat floatValue];

                [resultArray addObject:location];
            }
            return resultArray;
        }
    }
    return nil;

}

@end

This is the complete file with the same methods we implemented with Android.

+(DBHelper*)getSharedInstance{
    if (!sharedInstance) {
        sharedInstance = [[super allocWithZone:NULL]init];
        [sharedInstance createDB];
    }
    return sharedInstance;
}

The first method we create will get the instance of a created database. This allows us to use the same database for the life of the app. If the database is already initialised we will use it, if not we will run the createDB method to create a new instance and then pass that.

Next, we create the method that will actually create the database

-(BOOL)createDB{
    NSString *docsDir;
    NSArray *dirPaths;
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains
    (NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString:
                    [docsDir stringByAppendingPathComponent: @"town.db"]];
    BOOL isSuccess = YES;
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &database) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE if not exists LocationInfo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Description TEXT, Longitude TEXT, Lat TEXT)";
            
            if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create table");
            }
            sqlite3_close(database);
            return  isSuccess;
        }
        else {
            isSuccess = NO;
            NSLog(@"Failed to open/create database");
        }
    }
    return isSuccess;
}

This method is pretty straight forward. First we create a database path in the apps document directory, then using the FileManager we can check if the database file exists. If it already does, there is nothing more we need to do. However if the file has not yet been created, we will go ahead and create the new file.

Using the method sqlite3_open to open a database at the file location is the quickest way to do this. The result is a database object that uses a newly created .db file at the specified location. Using this database object we can then execute our SQL statement to populate the database with our location table.

-(BOOL) clearLocations{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *insertSQL = @"DELETE FROM LocationInfo";
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            return YES;
        }
        else {
            return NO;
        }
    }
    return NO;
}

The next method we implement is the simple clearLocations. First we open the database using the set path. If the database is opened successfully (or already open), we can then execute the delete SQL. Using the sqlite3_prepare_v2 method, we step over the statement to check that the SQLite has been executed.

-(BOOL) insertLocation:(BuildingLocation*)location{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
     
        NSNumber *longNumber = [NSNumber numberWithFloat:location.Long];
        NSNumber *latNumber = [NSNumber numberWithFloat:location.Lat];

        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO LocationInfo (Name, Description, Longitude, Lat) values (\"%@\",\"%@\", \"%@\", \"%@\")",location.name, location.description, [longNumber stringValue], [latNumber stringValue]];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            return YES;
        }
        else {
            NSLog(@"Error while updating. '%s'", sqlite3_errmsg(database));
            return NO;
        }
    }
    return NO;
}

Our next method looks at inserting a location into the database. We pass a BuildingLocation object to the method, then open the database. In the BuildingLocation, we are storing the long and lat as a float. To convert these to strings for the database, we must first create NSNumber objects from the float values.

NSNumber *longNumber = [NSNumber numberWithFloat:location.Long];
NSNumber *latNumber = [NSNumber numberWithFloat:location.Lat];

Then we create the SQL string that will be what we execute in the database.

NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO LocationInfo (Name, Description, Longitude, Lat) values (\"%@\",\"%@\", \"%@\", \"%@\")",location.name, location.description, [longNumber stringValue], [latNumber stringValue]];

Finally, we run this SQL string much the same we as the clearLocation method, checking that it was completed and returning true if that is the case.

-(NSArray*) getAllLocations{
    const char *dbpath = [databasePath UTF8String];
    NSString *querySQL;
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *querySQL = @"";
        
        querySQL = @"SELECT * FROM LocationInfo";
       
        const char *query_stmt = [querySQL UTF8String];
        NSMutableArray *resultArray = [[NSMutableArray alloc]init];
        if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                BuildingLocation *location = [[BuildingLocation alloc] init];
                
                location.name = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
                location.description = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
                NSNumberFormatter * nFormatter = [[NSNumberFormatter alloc] init];
                [nFormatter setNumberStyle:NSNumberFormatterDecimalStyle];
                NSNumber *numlong = [nFormatter numberFromString:[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)]];
                NSNumber *numlat = [nFormatter numberFromString:[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)]];

                location.Long = [numlong floatValue];
                location.Lat = [numlat floatValue];

                [resultArray addObject:location];
            }
            return resultArray;
        }
    }
    return nil;

}

The last method will get all the locations from the database and return an array of locations. As before, we first open the database, and then create the SQL and run it. The difference is, because we are now using a SELECT statement, sqlite3_prepare_v2 will return the rows of the database. sqlite3_step allows us to iterate over these rows and access the data. We then create a new BuildingLocation object and set its variables to the matching values in that database row.

Once they have all be set, we add the created object to the array. Once we have done iterating over the rows, we will then return the array.

Example of Use

DBHelper *db;
db = [DBHelper getSharedInstance];

BuildingLocation *locationObject = [[BuildingLocation alloc] init];
locationObject.name = "test";
locationObject.description = "this is a test place";
locationObject.Long = 1.458375;
locationObject.Lat =  52.105336;
           
[db insertLocation:locationObject];

NSMutableArray *locations;
locations = [db getAllLocations];

Here is an example of how we would use the code, first creating a new instance of the database, then we create a BuildingLocation object and insert it into the database. Finally, we get back the array of saved locations from the database.

If you have any comments or questions please ask below.

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Tags: ,

Using SQLITE database to store data within Android App

Zachary Powell - 09/12/2015

In our last post, we explored using SharedPreferences to save data. This works well for simple primitive data, but if we have a more advanced data structure we will need a better way to store it. As with other programs we would look towards a database. Android supports SQLite Databases, so this is what we would use.

What is SQLite?

First and foremost, before we look at how we can use the SQLite database on Android, lets take a second to look at what SQLite actually is.

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world, with more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers and views, is contained in a single disk file. The database file format is cross-platform – you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

From – https://www.sqlite.org/about.html

Or more simply put SQLite is a very light and easy to use implementation of a SQL database, which does not require a server to run and is perfect for running on a phone.

Java Object Class

Lets first look at an example, simple object class. This will be the object we save to the database and the object we build when retrieving data back from the database. In our example, we will look at a database which holds a range of buildings. Each building will have its name, description, long and lat stored.

public class Building {
    String Name;
    String Decription;
    double Long;
    double Lat;

    Building(){
    }
    Building(String n, String d, double lo, double la){
        Name = n;
        Decription = d;
        Long = lo;
        Lat = la;
    }
    public double getLong() {
        return Long;
    }
    public double getLat() {
        return Lat;
    }
    public String getName() {
        return Name;
    }
    public String getDecription() {
        return Decription;
    }
    public void setName(String name) {
        this.Name = name;
    }
    public void setDesc(String desc) {
        this.Decription = desc;
    }
    public void setLong(double aLong) {
        this.Long = aLong;
    }
    public void setLat(double lat) {
        this.Lat = lat;
    }
    public String toString(){
        return "Name: " + Name + " Decription: " + Decription + " Long: " + Long + " Lat: " + Lat;
    }
}

This is a pretty standard object class with getters and setters set up as well as two construction methods, one that is empty and another that takes all the arguments. These will make creating and reading data much easier. Of course this is just an example of a data structure, you may wish to save to a database.

Database Handler

The Database Handler is the class that we will use to create and access the SQLite database. Below is the complete example which we will then brake down and explore.

public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "locations.db";
    SQLiteDatabase db;

    public DBHelper(Context context)
    {
        super(context, DATABASE_NAME , null, 1);
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE LocationInfo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Description TEXT, Longitude TEXT, Lat TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS LocationInfo");
        onCreate(db);
    }

    public void clearLocations(){
        db = this.getWritableDatabase();
        db.execSQL("delete from LocationInfo");
        db.close();
    }

    public boolean insertLocation  (Building place) {
        db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("Name", place.getName());
        contentValues.put("Description", place.getDecription());
        contentValues.put("Longitude", place.getLong());
        contentValues.put("Lat", place.getLat());
        db.insert("LocationInfo", null, contentValues);
        db.close();
        return true;
    }

    public ArrayList<Building> getAllLocations()
    {
        db = this.getWritableDatabase();
        ArrayList<Building> array_list = new ArrayList();
        Cursor res;
        res =  db.rawQuery( "select * from LocationInfo", null );
       
        res.moveToFirst();
        while(res.isAfterLast() == false){
            BuildingLocation place = new BuildingLocation();
            place.setName(res.getString(res.getColumnIndex("Name")));
            place.setDesc(res.getString(res.getColumnIndex("Description")));
            place.setLat(res.getDouble(res.getColumnIndex("Lat")));
            place.setLong(res.getDouble(res.getColumnIndex("Longitude")));
            array_list.add(place);
            res.moveToNext();
        }
        db.close();
        return array_list;
    }
}

Lets brake this example down a little and go over the important parts.

Firstly, we extent our helper class with SQLiteOpenHelper. This is the Android class that handles the database creation.

Next we have the Constructor method for the database, this uses the SQLiteOpenHelper Constructor to create a database file with the name variable using:

super(context, DATABASE_NAME , null, 1);

The other two methods that we override from the parent class are the below:

@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE LocationInfo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Description TEXT, Longitude TEXT, Lat TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS LocationInfo");
        onCreate(db);
    }

The onCreate method is called when the database file is created. Here we run the SQL to create the table within the database file and here we create fields for all the fields with the Building object class. As with all SQL databases we also create and ID field that is auto-incremented.

The onUpgrade method is called if we need to make changes to a pre-existing database on a users device. For example, if we wanted to release an upgrade to our app that adds another field to the table, we run this method, it drops the table and then re-runs the onCreate.

The Next method clearLocations() is a simple example of method to interact with the database. Here we just run a SQL statement to clear the table of all data. The method takes no arguments and does not return anything. It is important to note that we must first open the database again, and once we have executed the SQL, we need to close the database.

The next method (below) is an example of a method used to insert data into the database

public boolean insertLocation  (Building place) {
        db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("Name", place.getName());
        contentValues.put("Description", place.getDecription());
        contentValues.put("Longitude", place.getLong());
        contentValues.put("Lat", place.getLat());
        db.insert("LocationInfo", null, contentValues);
        db.close();
        return true;
    }

Here we take a Building object as a argument. The safest and easiest way to then insert this data into the database is to create a ContentValues object. This allows you to “put” each primitive data  object from the Building object into a form that can be inserted into the database. The .put() method takes two argument. The first is the name of the table column and the second is the data you wish to put into that column.

Using the Building objects getter classes we load all the data we plan to store in the database for that Building object. Then using the .insert() method, we can pass the ContentValues object along with the name of the database table. Finally once again we close the database.

public ArrayList<Building> getAllLocations()
    {
        db = this.getWritableDatabase();
        ArrayList<Building> array_list = new ArrayList();
        Cursor res;
        res =  db.rawQuery( "select * from LocationInfo", null );
       
        res.moveToFirst();
        while(res.isAfterLast() == false){
            BuildingLocation place = new BuildingLocation();
            place.setName(res.getString(res.getColumnIndex("Name")));
            place.setDesc(res.getString(res.getColumnIndex("Description")));
            place.setLat(res.getDouble(res.getColumnIndex("Lat")));
            place.setLong(res.getDouble(res.getColumnIndex("Longitude")));
            array_list.add(place);
            res.moveToNext();
        }
        db.close();
        return array_list;
    }

Finally we have an example of a method to get data back out of the database. Here we use a Cursor object to move over the retrieved data. The SQL query we run simply returns a Cursor object with all the data in the table, but we may want to be more specific with the data we get back.

Using a whole loop, we then move over each object within the Cursor, using the method  .getColumnIndex() to return a columns index based on its name and then using the respected .get method depending on the data type. Using out Building objects setter classes we can then build up a building object to add to the array.

Once we have looped over the whole cursor object we can simply close the database and return our array of Building objects.

Example of Use

With the above, you have all you need to start working with databases within Android, but lets just take a second to see how we might use the above code within another method, perhaps an activity where the user enters a building details and saves them to the database. In  Below example we just set the values but the data could be taken from EditText view etc.

DBHelper mydb = new DBHelper(this);

String name = "Church";
String desc = "The tallest building in the village";
double longitude = 1.458375;
double Lat = 52.105336;

Building map = new Building();
map.setName(name);
map.setDesc(desc);
map.setLong(longitude);
map.setLat(Lat);
mydb.insertLocation(map);

As always if you have any questions feel free to comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *

Tags: , ,

“ Any fool can write code that a computer can understand. Good programmers write code that humans can understand. ”

-Martin Fowler