Home > android > Renaming columns in Android database queries

Renaming columns in Android database queries

Today I had the need to do some renaming of database columns in a ContentProvider query.

I wanted to be able to specify a column, let’s call it “brumble“, to the ContentResolver, and then actually handle it as a specific column from a specific table inside the ContentProvider, let’s call it the table “apple“, and the column, “orange“.

This is very easy to do with SQL: “SELECT apple.orange AS brumble“.

It also looked like a pretty easy task for the projection map inside the ContentProvider.

After all, content providers use their own, internal, projection maps, specifically for this task, right? So I added the following to my projection map: map.add("brumble", "apple.orange") and used it with Android’s SQLiteQueryBuilder inside my content provider’s query method.

The query method worked, and returned a cursor, but then the code that made the query blew up: getColumnIndexOrThrow("brumble") actually threw an exception, because the brumble column was not found in the cursor.

Debugging inside the content provider made the reason clear. The code generated by SQLiteQueryBuilder looked like this:

SELECT apple.orange FROM apple WHERE ...

This code correctly uses “the real brumble” column, apple.orange, but fails to rename it to brumble. Now the code making the query has to know that brumble is actually orange or apple.orange.

To fix this issue, I wrote my own little SQLiteQueryBuilder – like class that automatically does the renaming of columns, based on the projection. It can be used the same way as the original Android class: first you set the table(s), then the projection, possibly with table-qualified column names, and then ask the object to build the query.

// Client code projection map:
String[] projection = new String[] { "brumble" };

// Internal ContentProvider projection map:
HashMap<String, String> gProjectionMap= new ...
gProjectionMap.add("brumble", "apple.orange");

// Use the query builder
MyQueryBuilder qb = new MyQueryBuilder();
qb.setTables("apple");
qb.setProjection(gProjectionMap);

// Build the query
String s = qb.buildQuery(projection, ... "color = ?", "red", ...);

// The result:
 s == "SELECT apple.orange AS brumble FROM apple WHERE color = ?";

Now the code that initiated the query in the first place can work with brumble.

The code for MyQueryBuilder is pretty simple, the real work is done inside buildProjection:

public class MyQueryBuilder {

	public MyQueryBuilder() {
	}

	public void setTables(String inTables) {
		mTables = inTables;
	}

	public void setProjectionMap(Map<String, String> columnMap) {
		mProjectionMap = columnMap;
	}

	public String buildQuery(String[] projection, String selection, String[] selectionArgs,
			String groupBy, String having, String sortOrder, String limit) {

		StringBuilder sb = new StringBuilder();

		String proj = buildProjection(projection);
		sb.append("SELECT ");
		sb.append(proj);

		sb.append(" FROM ");
		sb.append(mTables);

		appendClause(sb, " WHERE ", selection);
		appendClause(sb, " ORDER BY ", sortOrder);
		appendClause(sb, " GROUP BY ", groupBy);
		appendClause(sb, " HAVING ", having);
		appendClause(sb, " LIMIT ", limit);

		return sb.toString();
	}

	private static void appendClause(StringBuilder sb, String name, String clause) {
		if (!TextUtils.isEmpty(clause)) {
			sb.append(name);
			sb.append(clause);
		}
	}

	private String buildProjection(String[] projection) {
		StringBuilder sb = new StringBuilder();

		for (String pcol : projection) {
			String tcol = mProjectionMap.get(pcol);
			if (tcol == null) {
				throw new IllegalArgumentException("Invalid column: " + pcol);
			}

			if (sb.length() != 0) {
				sb.append(", ");
			}

			if (tcol.equals(pcol)) {
				// Table column is same as projection column, use as is
				sb.append(tcol);
			} else {
				// Columns are different, check for qualified name
				int i = tcol.indexOf('.');
				if (i != -1 && tcol.substring(i + 1).equals(pcol)) {
					// Selecting 'tab.col' as 'col' can be done as just that, 'SELECT tab.col'
					sb.append(tcol);
				} else {
					// Selecting 'tab.foo' as 'bar' has to be done as 'SELECT tab.foo AS bar'
					sb.append(tcol);
					sb.append(" AS ");
					sb.append(pcol);
				}
			}
		}

		return sb.toString();
	}

	private String mTables;
	private Map<String, String> mProjectionMap;
}

The renaming can also be achived with the standard SQLiteQueryBuilder by putting the ” AS ” part in the column name in the content provider’s internal projection map. It might look like this:

// Client code projection map:
String[] projection = new String[] { "brumble" };

// Internal ContentProvider projection map:
HashMap<String, String> gProjectionMap= new ...

// ***** THIS LINE IS DIFFERENT, NOTE THE "AS brumble"
gProjectionMap.add("brumble", "apple.orange AS brumble");
// ***** THIS LINE IS DIFFERENT

// Use the query builder
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
qb.setTables("apple");
qb.setProjection(gProjectionMap);

// Build the query
String s = qb.buildQuery(projection, ... "color = ?", "red", ...);

// The result:
 s == "SELECT apple.orange AS brumble FROM apple WHERE color = ?";

Which way is easier is for anyone to decide to himself, of course. Either way, you need to make sure that the SQL code contains the column renaming clause, ” AS “, and then the fact that brumble is actually apple.orange can stay encapsulated inside the content provider.

Advertisements
Categories: android
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s