Martin's random and not so random knowledge

Android Using joins with a provider (SQLite)

In this post I will show how to use a left outer join in your provider.
Now note that you can also create a view in your database and query on the view. I might do a post on that subject in the future.

For this example I will use a SQLite database with two tables:
book
_id – Id field
title – Title of our book
category – Id references to table categories.

categories
_id – Id field
title – Caption of our category

Note that I’m using title in both tables, I’ve used these field names so I can show you how to deal with fields that have the same name.

First I have created a helper class for our table:

/**
 * Books table
 */
public static final class BookColumns implements BaseColumns {
	// This class cannot be instantiated
	private BookColumns () {}

	/**
	 * The table name of books = "books"
	 */
	public static final String TABLENAME = "books";

	/**
	 * The title of the book
	 * <P>Type: TEXT</P>
	 */
	public static final String TITLE = "title";        

	/**
	 * The category of the book (ref to table categories)
	 * <P>Type: INT</P>
	 */
	public static final String CATEGORY = "category";
}

/**
 * Categories table
 */
public static final class CategoriesColumns implements BaseColumns {
	// This class cannot be instantiated
	private CategoriesColumns () {}

	/**
	 * The table name of categories = "categories"
	 */
	public static final String TABLENAME = "categories";

	/**
	 * The caption of the category
	 * <P>Type: TEXT</P>
	 */
	public static final String TITLE = "title";
}

Next I have created a provider, nothing special just your basic provider.
I want to get the title of the category as a field when getting our books or one book.

First I will need to define the projection maps. But here a problem occurs. I want to get the book title and our category title in one query, but both field names are named the same.
Luckily In SQL you can specify your exact field name with a prefix of the table name. I will use this to map the category title to a more suitable field name. But in the helper class I only specified the field names. To keep my code clean I will modify the helper classes.

/**
 * Books table
 */
public static final class BookColumns implements BaseColumns {
	// This class cannot be instantiated
	private BookColumns () {}

	/**
	 * The table name of books = "books"
	 */
	public static final String TABLENAME = "books";

	/**
	 * The id of the book, includes tablename prefix
	 * <P>Type: INT</P>
	 */
	public static final String FULL_ID =  TABLENAME + "." + _ID;	

	/**
	 * The title of the book
	 * <P>Type: TEXT</P>
	 */
	public static final String TITLE = "title";     

	/**
	 * The title of the book, includes tablename prefix
	 * <P>Type: TEXT</P>
	 */
	public static final String FULL_TITLE =  TABLENAME + "." + TITLE;	

	/**
	 * The category of the book (ref to table categories)
	 * <P>Type: INT</P>
	 */
	public static final String CATEGORY = "category";        

	/**
	 * The category of the book, includes tablename prefix (ref to table categories)
	 * <P>Type: INT</P>
	 */
	public static final String FULL_CATEGORY =  TABLENAME + "." + CATEGORY;
}

/**
 * Categories table
 */
public static final class CategoriesColumns implements BaseColumns {
	// This class cannot be instantiated
	private CategoriesColumns () {}

	/**
	 * The table name of categories = "categories"
	 */
	public static final String TABLENAME = "categories";

	/**
	 * The id of the category, includes tablename prefix
	 * <P>Type: INT</P>
	 */
	public static final String FULL_ID =  TABLENAME + "." + _ID;	

	/**
	 * The caption of the category
	 * <P>Type: TEXT</P>
	 */
	public static final String TITLE = "title";        

	/**
	 * The caption of the category, includes tablename prefix
	 * <P>Type: TEXT</P>
	 */
	public static final String FULL_TITLE =  TABLENAME + "." + TITLE;
}

Now I can define the projection maps.

static {
	//Setup projection maps
	sCategoriesProjectionMap = new HashMap<String, String>();
	sCategoriesProjectionMap.put(CategoryColumns._ID, CategoryColumns.FULL_ID);
	sCategoriesProjectionMap.put(CategoryColumns.TITLE, CategoryColumns.FULL_TITLE);

	sBooksProjectionMap = new HashMap<String, String>();
	sBooksProjectionMap.put(BookColumns._ID, BookColumns.FULL_ID);
	sBooksProjectionMap.put(BookColumns.TITLE, BookColumns.FULL_TITLE);
	sBooksProjectionMap.put(BookColumns.COMMENT, BookColumns.FULL_COMMENT);
	sBooksProjectionMap.put(BookColumns.CATEGORY, BookColumns.FULL_CATEGORY);
	sBooksProjectionMap.put(BookColumns.PRICE, BookColumns.FULL_PRICE);
	sBooksProjectionMap.put(BookColumns.PRIORITY, BookColumns.FULL_PRIORITY);
	sBooksProjectionMap.put(BookColumns.CREATED_DATE, BookColumns.FULL_CREATED_DATE);
	sBooksProjectionMap.put(BookColumns.MODIFIED_DATE, BookColumns.FULL_MODIFIED_DATE);
	sBooksProjectionMap.put("category_title", CategoryColumns.FULL_TITLE);
}

Note that I map all the fields to the full field name with the table name prefix.
Also note this line:

sBooksProjectionMap.put("category_title", CategoryColumns.FULL_TITLE);

Here I’m mapping categories.title to a new field named “category_title”. The new fieldname is unique and has a proper name.

Now for the last part I will need to make the join when getting our data.

@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs,
		String sortOrder) {

	SQLiteQueryBuilder _QB = new SQLiteQueryBuilder();
	int _TableType = 0;

	switch (sUriMatcher.match(uri)) {
	case CATEGORIES:
		_QB.setTables(CategoryColumns.TABLENAME);
		_QB.setProjectionMap(sCategoriesProjectionMap);
		_TableType = CATEGORIES;
		break;

	case CATEGORY_ID:
		_QB.setTables(CategoryColumns.TABLENAME);
		_QB.setProjectionMap(sCategoriesProjectionMap);
		_QB.appendWhere(CategoryColumns.FULL_ID + "=" + uri.getPathSegments().get(1));
		_TableType = CATEGORIES;
		break;

	case BOOKS:
		_QB.setTables(BookColumns.TABLENAME +
				" LEFT OUTER JOIN " + CategoryColumns.TABLENAME + " ON " +
				BookColumns.FULL_CATEGORY + " = " + CategoryColumns.FULL_ID);
		_QB.setProjectionMap(sBooksProjectionMap);
		_TableType = BOOKS;
		break;

	case BOOK_ID:
		_QB.setTables(BookColumns.TABLENAME +
				" LEFT OUTER JOIN " + CategoryColumns.TABLENAME + " ON " +
				BookColumns.FULL_CATEGORY + " = " + CategoryColumns.FULL_ID);
		_QB.setProjectionMap(sBooksProjectionMap);
		_QB.appendWhere(BookColumns.FULL_ID + "=" + uri.getPathSegments().get(1));
		_TableType = BOOKS;
		break;

	default:
		throw new IllegalArgumentException("Unknown URI " + uri);
	}

	//Set your sort order here
	String _OrderBy;
	if (TextUtils.isEmpty(sortOrder)) {
		// If no sort order is specified use the default
		switch(_TableType) {
		case CATEGORIES:
			_OrderBy = CategoryColumns.FULL_TITLE + " ASC";
			break;

		case BOOKS:
			_OrderBy = BookColumns.FULL_TITLE + " ASC";
			break;

		default:
			throw new UnknownError("Unknown table type for sort order");
		}
	} else {
		_OrderBy = sortOrder;
	}

	// Get the database and run the query
	SQLiteDatabase _DB = fDatabaseHelper.getReadableDatabase();
	Cursor _Result = _QB.query(_DB, projection, selection, selectionArgs, null, null, _OrderBy);

	// Tell the cursor what uri to watch, so it knows when its source data changes
	_Result.setNotificationUri(getContext().getContentResolver(), uri);
	return _Result;
}

The actual join is done in this line:

_QB.setTables(BookColumns.TABLENAME +
		" LEFT OUTER JOIN " + CategoryColumns.TABLENAME + " ON " +
		BookColumns.FULL_CATEGORY + " = " + CategoryColumns.FULL_ID);

As you can see I have put a left outer join on the category table and specified the relation between the tables.

Here I use the full _id field name to specify which _id field I’m referring to:

_QB.appendWhere(BookColumns.FULL_ID + "=" + uri.getPathSegments().get(1));

And that’s it. I hope to have given you a better understanding of using joins with providers in Android.

1 Comment