The Open Event Android App, downloads data from the API (about events, sessions speakers etc), and saves them locally in an SQLite database, so that the app can work even without internet connection.
Since there are multiple entities like Sessions, Speakers, Events etc, and each Session has ids of speakers, and id of it’s venue etc, we often need to use JOIN queries to join data from two tables.
Android has some really nice SQLite helper classes and methods. And the ones I like the most are the SQLiteDatabase.query, SQLiteDatabase.update, SQLiteDatabase.insert ones, because they take away quite a bit of pain for typing out SQL commands by hand.
But unfortunately, if you have to use a JOIN, then usually you have to go and use the SQLiteDatabase.rawQuery method and end up having to type your commands by hand.
But but but, if the two tables you are joining do not have any common column names (actually it is good design to have them so – by having all column names prefixed by tablename_
maybe), then you can hack the usual SQLiteDatabase.query()
method to get a JOINed query.
Now ideally, to get the Session where speaker_id was 1, a nice looking SQL query should be like this –
SELECT * FROM speaker INNER JOIN session
ON speaker_id = session_speaker_id
WHERE speaker_id = 1
Which, in android, can be done like this –
String rawQuery = "SELECT * FROM " + SpeakerTable.TABLE_NAME + " INNER JOIN " + SessionTable.TABLE_NAME
+ " ON " + SessionTable.EXP_ID + " = " + SpeakerTable.ID
+ " WHERE " + SessionTable.ID + " = " + id;
Cursor c = db.rawQuery(
rawQuery,
null
);
But of course, because of SQLite’s backward compatible support of the primitive way of querying, we turn that command into
SELECT *
FROM session, speaker
WHERE speaker_id = session_speaker_id AND speaker_id = 1
Now this we can write by hacking the terminology used by the #query() method –
Cursor c = db.query(
SessionTable.TABLE_NAME + " , " + SpeakerTable.TABLE_NAME,
Utils.concat(SessionTable.PROJECTION, SpeakerTable.PROJECTION),
SessionTable.EXP_ID + " = " + SpeakerTable.ID + " AND " + SpeakerTable.ID + " = " + id,
null,
null,
null,
null
);
To explain a bit, the first argument String tableName
can take table1, table2
as well safely, The second argument takes a String array of column names, I concatenated the two projections of the two classes. and finally, put by WHERE clause into the String selection
argument.
You can see the code for all database operations in the android app here https://github.com/fossasia/open-event-android/blob/master/android/app/src/main/java/org/fossasia/openevent/dbutils/DatabaseOperations.java