ORMLite Android v2.2+
Hack 42 Creating custom functions in SQLite Android v1.6+
Android uses SQLite for its databases. Although it offers a good API, you’ll sometimes feel a bit limited. What would you do if you want to sort results using a comparator? Did you ever try to implement a query that returns the distance between two GPS coordi- nates? One of SQLite’s biggest limitations is its lack of math functions, making some queries impossible to achieve.
In this hack, I’ll show you how to use the Android
NDK to provide custom functions to your SQLite que- ries. We’ll create an application that uses a custom SQLite function to calculate distances from different
POIs (points of interest) in a database. This function will use the GPS coordinates of the POIs and the hav- ersine formula to return the distance in kilometers.
We can see the application running in figure 42.1. In this figure, we see that different POIs from France were added. Later, the user searches using the Notre Dame de Paris’ GPS coordinates and the distance to the different POIs is shown.
To make this work, we’ll use the Android NDK. We’ll use Java to create POIs and insert them in the database using the ordinary SQLiteOpenHelper class, but when the user searches the database we’ll use an NDK call. We’ll first see how to handle the Java part, and afterward we’ll see the NDK code.
42.1
Java code
The idea to make this work correctly is to keep doing the simple database queries using the Java API and only use the NDK when we need to use a custom function. The inter- esting code in the Java part is the DatabaseHelper class. This class will be in charge of calling the NDK code when necessary.
Let’s check the DatabaseHelper’s code:
public class DatabaseHelper extends SQLiteOpenHelper { public static final String DATABASE_NAME = "pois.db"; private static final int DATABASE_VERSION = 1; private Context mContext;
static {
System.loadLibrary("hack042-native");
B
Load native library }public DatabaseHelper(Context context) {
Figure 42.1 Distance from Notre Dame to different POIs in France
149
Creating custom functions in SQLite
super(context, DATABASE_NAME, null, DATABASE_VERSION); mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE " +
"pois (" +
C
POIs table schema"_id INTEGER PRIMARY KEY AUTOINCREMENT," + "title TEXT," +
"longitude FLOAT," + "latitude FLOAT);"); }
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS pois;"); }
public List<Poi>getNear(float latitude, float longitude) {
D
getNear() Java implementationFile file = mContext.getDatabasePath(DATABASE_NAME);
return getNear(file.getAbsolutePath(), latitude, longitude); }
private native List<Poi> getNear(String dbPath, float latitude, float longitude);
E
getNear() native implementation signature}
The fist important line is loading the native library
B
. System.loadLibrary() is usu- ally called from a static block. This means that when the class is loaded, it will also load the native library called hack042-native. In the onCreate() methodC
, we can learn what the database schema looks like. Our DatabaseHelper class contains a get- Near()D
method that will be called when the user clicks on the Search button. This method is just a wrapper for its native versionE
. The Java version is the public one because the native implementation needs the database path, and only the Database- Helper class knows where it is.42.2
Native code
We’ll use the NDK to query our database when we need to use custom functions. To do so, we’ll need to be able to operate with SQLite from the NDK, and that means we’ll need to compile it. Fortunately, it’s easier than you would expect. We simply add .c and .h file extensions. Adding sqlite3.c to the LOCAL_SRC_FILES inside the Android.mk file is enough to use it.
Inside main.cpp we have all the NDK code. We’ll need to do the following: Use JNI to create Java objects.
Use the SQLite’s C/C++ API to query our database. Return a List<Poi> as a jobject.
Let’s take a look at the implementation of getNear():
jobject Java_com_manning_androidhacks_hack042_db_DatabaseHelper_getNear( JNIEnv *env, jobject thiz, jstring dbPath,
B
getNear() native method
jfloat lat, jfloat lon) { sqlite3 *db;
sqlite3_stmt *stmt;
const char *path = env->GetStringUTFChars(dbPath, 0); jclass arrayClass = env->FindClass("java/util/ArrayList");
jmethodID mid_init = env->GetMethodID(arrayClass, "<init>", "()V"); jobject objArr = env->NewObject(arrayClass, mid_init);
C
ArrayList
creation jmethodID mid_add = env->GetMethodID(arrayClass, "add", "(Ljava/lang/
Object;)Z");
jclass poiClass = env->FindClass(
"com.manning.androidhacks.hack042.model.Poi");
jmethodID poi_mid_init = env->GetMethodID(poiClass, "<init>", "(Ljava/lang/String;FFF)V");
sqlite3_open(path, &db);
D
Open database with a certain pathenv->ReleaseStringUTFChars(dbPath, path);
sqlite3_create_function(db, "distance", 4, SQLITE_UTF8,
E
Create custom function
NULL, &distanceFunc, NULL, NULL); if (sqlite3_prepare(db,
"SELECT title, latitude, longitude,
distance(latitude, longitude, ?, ?) as kms
Create
query
F
FROM pois ORDER BY kms", -1, &stmt, NULL) == SQLITE_OK) {int err;
sqlite3_bind_double(stmt, 1, lat); sqlite3_bind_double(stmt, 2, lon);
while ((err = sqlite3_step(stmt)) == SQLITE_ROW) {
G
Iterate through resultsconst char *name = (char const *) sqlite3_column_text(stmt, 0);
jfloat latitude = sqlite3_column_double(stmt, 1); jfloat longitude = sqlite3_column_double(stmt, 2); jfloat distance = sqlite3_column_double(stmt, 3); jobject poiObj = env->NewObject(poiClass,
poi_mid_init,
H
Create new Poi object env->NewStringUTF(name), latitude, longitude, distance);env->CallBooleanMethod(objArr, mid_add, poiObj); }
if (err != SQLITE_DONE) {
LOGI("Query execution failed: %s\n", sqlite3_errmsg(db)); }
sqlite3_finalize(stmt); } else {
151
Creating custom functions in SQLite
LOGI("Can't execute query: %s\n", sqlite3_errmsg(db)); }
return objArr; }
The first thing to notice is the difference between the Java and NDK signatures
B
. Since we need to return a List<Poi>, we create a new ArrayList using JNIC
. After that, we can open the database using the path providedD
and create a custom func- tion passing a function pointerE
. The distance() function is defined inside the main.cpp file. After the custom function is created, we can write our query using the distance() functionF
. The final step is iterating through the resultsG
, create a Poi object using the row dataH
, and add it to the list.Now that we have all the native code in place, whenever we call the Database- Helper’s getNear() method, it will use the custom function created in this section.
42.3
The bottom line
Using the NDK might sound like a lot of work, but doing so will give you more flexibil- ity. You might be thinking that instead of returning an array from native code, you could query the database through Java, calculate the distance and sort after doing the query. This is true, but if the database is big enough, using an array wouldn’t work. The best way to solve this is returning a Cursor from the native code. The implemen- tation to return a Cursor would be much harder to code, but someone already did it. You can check the android-database-sqlcipher source code; it’s already implemented there. When you have a Cursor, you’ll be able to use a CursorAdapter as an adapter for your ListView, making everything extremely easy.
You should also know that there’s a way to avoid creating custom functions. You can precalculate values and insert them into the row. This might be sufficient, depending on the type of queries your application does.