In a previous article I wrote on SQLite performance techniques, I explained that using SQLite bulk transactions, especially in a WebView application on your mobile app, can be a huge performance booster. In this article, I’d like to expand a little more and offer some basic code solutions for your Android and iOS applications.
Let’s start with a simple example. Let’s say, for instance, that you are creating a mobile WebView-based application where you are pulling a large amount of data from a back-end service or API and inserting the rows in your your local SQLite database. For this example, let’s say we are pulling in a list of coffee shops and we are going to insert into our SQLite table the following columns:
CREATE TABLE IF NOT EXISTS coffee_shops (
id INTEGER PRIMARY KEY,
name TEXT,
address TEXT,
price_range TEXT
);
For each coffee shop, we’ll give it an auto-incremented ID and insert a name, address, and a “price range” (in this case, either “$”, “$$”, “$$$”, or “$$$$”). You already know that you want to use bulk transactions, but you need to create a function to do it.
Android SQLiteStatement
Android native Java code is built to work hand in hand with SQLite. The SQLiteDatabase and SQLiteStatement classes will both help you tremendously, and we will use them in our example. Let’s say, for the example’s sake, that the data we have retrieved from the back-end service/API is in JSON format using an AJAX call in our WebView.
The data might look something like:
{"coffee_shops":
[
{
"id": 0,
"name": "Downtown Coffee",,
"address": "123 Main Street, New York, NY 10012"
"price_range": "$$"
}
//...
]
};
We’ll also say that we’ve converted it to a string using JSON.stringify in our Webview, and are now passing the string to our native application to perform the SQLite interactions.
Once we pass that data to the native portion, we’re gong to be using SQLiteDatabase and SQLiteStatement. Most notably, SQLiteDatabase has the BeginTransaction(), SetTransactionSuccessful(), and EndTransaction() methods which are essential for bulk transactions, while the SQLiteStatement class allows you to use a compiled prepared statement in your inserts. If you want to look at another more in depth solution on these classes, I also recommend checking out This great article with examples.
Circling back to the coffee shop example, here’s a function we can write in Java to take our Stringify’d JSON object and insert all the objects using a bulk transaction.
// objectID = "coffee_shops"
public JSONObject bulkInsertCoffeeShops(String stringValues, String objectID) {
try {
JSONObject stringJson = new JSONObject(stringValues);
JSONArray values = stringJson.getJSONArray(objectID);
try {
String sql = "INSERT OR REPLACE INTO coffee_shops VALUES (?, ?, ?, ?);";
coffeeDB = mContext.openOrCreateDatabase("coffeeDB",
Context.MODE_PRIVATE, null);
SQLiteStatement statement = coffeeDb.compileStatement(sql);
coffeeDb.beginTransaction();
for (int i = 0; i < values.length(); i++) {
statement.clearBindings();
try {
JSONObject o = values.getJSONObject(i);
statement.bindDouble(1,o.getDouble("id"));
statement.bindString(2,o.getString("name"));
statement.bindString(3,o.getString("address"));
statement.bindString(4,o.getString("price_range"));
statement.execute();
} catch (JSONException e) {
errorMessage = e.getMessage();
Log.e(TAG, errorMessage);
}
}
coffeeDb.setTransactionSuccessful();
} catch (Exception e) {
errorMessage = e.getMessage();
Log.e(TAG, errorMessage);
} finally {
//end transaction
coffeeDb.endTransaction();
coffeeDb.close();
}
} catch (JSONException e) {
errorMessage = e.getMessage();
Log.e(TAG, errorMessage);
}
};
Using Swift for iOS
If you’ve also got an iOS app running on the same or similar WebView code, you’ll probably also need an equivalent in your iOS app to insert your coffee shop data. For swift, it will be the same process, but we’ll use SQLite.swift as our library as well as SwiftyJSON to help process our JSON data.
func bulkInsertCoffeeShops(values:String, objectID:String) -> Bool{
let fileUrl = //your file path to your DB
//open our database
if sqlite3_open(fileUrl.path, &db) != SQLITE_OK {
}
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
// convert our JSON string into an object
let fieldStringData = fieldString.data(using: .utf8, allowLossyConversion: false)
let objectID = String(objectID)
let data = values.data(using: .utf8, allowLossyConversion: false)
if let json = try? JSON(data: data!)
{
var compiledStatement: OpaquePointer?
//Start our transaction
sqlite3_exec(db, "BEGIN IMMEDIATE TRANSACTION", nil, nil, nil)
var query = "INSERT OR REPLACE INTO coffee_shops VALUES (?, ?, ?, ?);";
let rowObjects = json[objectID]
if(sqlite3_prepare_v2(db, query, -1, &compiledStatement, nil) == SQLITE_OK)
{//Bind or variables and execute each statement
for (index, obj) in rowObjects
{
sqlite3_bind_int(compiledStatement, Int32(1),
Int32(obj["id"].stringValue)!);
sqlite3_bind_text(compiledStatement, Int32(2),
obj["name"].stringValue, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(compiledStatement, Int32(3),
obj["address"].stringValue, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(compiledStatement, Int32(4),
obj["price_range"].stringValue, -1, SQLITE_TRANSIENT);
if (sqlite3_step(compiledStatement) != SQLITE_DONE)
{
NSLog("%s",sqlite3_errmsg(db));
}
if (sqlite3_reset(compiledStatement) != SQLITE_OK)
{
NSLog("%s",sqlite3_errmsg(db));
}
}
}
if (sqlite3_finalize(compiledStatement) != SQLITE_OK){
NSLog("%s",sqlite3_errmsg(db));
}//Finally, let's commit our transaction
if (sqlite3_exec(db, "COMMIT TRANSACTION", nil, nil, nil) !=
SQLITE_OK) {
NSLog("%s",sqlite3_errmsg(db));
}
}
//Close our DB
if sqlite3_close_v2(db) != SQLITE_OK {
print("error closing the database")
}
return true
}
//Close our DB
if sqlite3_close_v2(db) != SQLITE_OK {
print("error closing the database")
}
return false
}
And there you have it. We can now successfully insert our large sets of data into our SQLite databases using both Swift and Java. If you like this article or found it useful, feel free to leave comments in the comment section.