Android SQLite using db.query() for JOIN instead of rawquery()

Question

I have tableA, tableB, and tableC table A and tableB are joined by tableA.Id(PK) = tableB.tableAId(FK) table B and tableC are joined by tableB.Id(PK) = tableC.tableBId(FK)

I want to be able to do this:

SELECT c.ALL from tableC c
INNER JOIN tableB b on c.tableBId = b.Id
INNER JOIN tableA a on b.tableAId = a.Id
WHERE a.Id = 108

I have found a lot of posts on the web which uses db.rawquery() to implement this query. However I have also heard that rawquery() is less secure than query(). So for the sake of seeking best practice as a beginner, my question is:

Is there a way to implement this query using db.query() instead of db.rawquery()?

thanks in advance.

Answer 1

Is there a way to implement this query using db.query() instead of db.rawquery()?

So it's worth to say that rawQuery() makes a trick. But also exists another approach.

query() method is designed for performing queries over one table. But the best way how to JOIN tables in SQLite is to use SQLiteQueryBuilder and with setTables() method you are able to join.

Hence i recommend you to use mentioned SQLiteQueryBuilder. But it's little more complicated against rawQuery() method where you need to assign only raw statement.

If don't know how to start, check this example:

• How to use a join with SQLite

Note:

Is the fact that rawQuery() is less secure than query() because query() method uses precompiled statements which are safer than "raw" statements. But always you can(should) use placeholders which significantly increase safety of statement as main protection against SQL injections and statement becomes much more human-readable as well.

Answer 2

This is kind of late, but I thought others who're looking for that might benefit from that:

db.query() method natively supports LEFT OUTER JOIN AND INNER JOIN via its table argument so you don't actually need to use SQLiteQueryBuilder to accomplish that. Also it's easier and and pretty much straight forward.

This method is widely used in Google I/O 2015 Schedule app's source code.

A Quick example (String constants left out for brevity):

Cursor cursor = db.query(NoteContract.Note.TABLE_NAME 
+ " LEFT OUTER JOIN authors ON notes._id=authors.note_id", projection, selection, 
selectionArgs, null, null, "notes._id");

The key is in the first argument to db.query().

Currently, only LEFT OUTER JOIN and INNER JOIN are supported, which is quite sufficient for most apps.

I hope this answer helps others who're looking for this.