Search code examples
androidsqljoinsqlitecursor

How do perform joins using Cursors?

In my Android app, I use Cursors/ContentValues to do things like fetch results from the Sqlite3 database and then I iterate over the cursor / populate my objects / etc. It's easy enough to perform basic create, retrieve, update, and delete operations.

But what if I want to perform a more complicated query that relies on a join? Do I basically have to pre-write the join query like I do for the create table queries, and then use a Cursor on that somehow? What's the accepted practice here?

Sample join query:

SELECT WIDGETS.*
FROM WIDGETS INNER JOIN WIDGET_CATEGORIES 
ON WIDGETS.CATEGORY_ID = WIDGET_CATEGORIES.ID
WHERE WIDGET_CATEGORIES.ACCOUNT_ID=5;
Solution

  • Given your sample query & with WIDGET_CATEGORIES.ACCOUNT_ID being the only variable that will change in your query then you can use rawQuery(SQL statement, selectionArguments):

    db.rawQuery("SELECT " + WIDGETS.* + 
" FROM " + TABLE_WIDGETS + " INNER JOIN " + WIDGET_CATEGORIES +
" ON " + WIDGETS.CATEGORY_ID + " = " + WIDGET_CATEGORIES.ID +
" WHERE " + WIDGET_CATEGORIES.ACCOUNT_ID + " = ?;", new String[]{"5"});

    This will return a cursor with the expected join information that you're looking for.