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.

The tables

For this example I will use a SQLite database with two tables:

books
_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";        
}

Modified helper class

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;	
}

Projection maps

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.CATEGORY, BookColumns.FULL_CATEGORY);        
	sBooksProjectionMap.put("category_title", CategoryColumns.FULL_TITLE + " AS " + "category_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 + " AS " + "category_title"); 

Here I’m mapping categories.title to a new field named “category_title”. The new fieldname is unique and has a proper name.
From now any caller to our provider can state they want “category_title” in the result with a projection array and our provider will translate that to: categories.title as category_title

Joining the tables

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.

avatar

About Martin

Martin van Zuilekom has 12 years experience working as a professional software developer. Main focus points are user interface design and framework architecture. Follow Martin on Google plus

3 comments on “Android: Using joins with a provider (SQLite)

    • I Can :)

      Its part of your provider class. It’s a initialize block.
      The following webpage will explain in detail the working of this section:
      http://www.developer.com/java/other/article.php/2238491/The-Essence-of-OOP-using-Java-Static-Initializer-Blocks.htm

      Within the block projection maps are defined. For more information about projection maps:
      http://www.mousetech.com/blog/?p=78
      In short, you define how the fields from your query are being returned. This is useful if you have have a join on two tables that contain the same fieldname, you can specify the name of the columns in your result set (projection). The key is the projection key and the value is the actual column in the select part of your query.
      So for example if I have a table “Books” with the field “Name”, I can specify that the value of the field “Books.Name” will be put in the result set as value of “Book_Name”, and any caller to the provider can use the column “Books_Name” without knowing the underlying column mapping.

      I Hope this explains it a bit better.

      Please note that I wrote this post with the assumption that someone who was reading it would already have a provider class and wanted to expand it with a join.
      Later I figured it might be a bit confusing for people to understand the post if you just wanted to know how to make a join in SQLite without a provider. So I wrote a second post that just covers the joining of two tables in SQLite:
      http://martin.cubeactive.com/?p=280

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>