Yavor is a PM at Snowflake working on developer experience. Previously at Docker, Auth0, Hulu, and Microsoft Azure.
28 March 2013
In today’s post we continue our Android blog series for Mobile Services. Here are the other posts in the series.
Today we will talk about the query model in the Android client. In our C# client we were lucky to be able to lean on LINQ, but after some research we found that there is no out-of-the-box equivalent in the Android platform. I’m glad to stand corrected here: please let me know in the comments if we missed something.
Given that, we decided to implement our own fluent query API that supports the same richness that you get with LINQ, so it deserves a thorough writeup. We’ll assume we’re working with our same reference type we used in our last tutorial, but we’ve added a field or two to make things more interesting to query against:
public class Droid {
@com.google.gson.annotations.SerializedName("id")
private Integer mId;
@com.google.gson.annotations.SerializedName("name")
private String mName;
@com.google.gson.annotations.SerializedName("weight")
private Float mWeight;
@com.google.gson.annotations.SerializedName("numberOfLegs")
private Integer mNumberOfLegs;
@com.google.gson.annotations.SerializedName("numberOfWheels")
private Integer mNumberOfWheels;
@com.google.gson.annotations.SerializedName("manufactureDate")
private Date mManufactureDate;
@com.google.gson.annotations.SerializedName("canTalk")
private Boolean mCanTalk;
public Integer getId() { return mId; }
public final void setId(Integer id) { mId = id; }
public String getName() { return mName; }
public final void setName(String name) { mName = name; }
public Float getWeight() { return mWeight; }
public final void setWeight(Float weight) { mWeight = weight; }
public Integer getNumberOfLegs () { return mNumberOfLegs; }
public final void setNumberOfLegs(Integer numberOfLegs) {
mNumberOfLegs = numberOfLegs;
}
public Integer getNumberOfWheels() { return mNumberOfWheels; }
public final void setNumberOfWheels(Integer numberOfWheels) {
mNumberOfWheels = numberOfWheels;
}
public Date getManufactureDate() { return mManufactureDate; }
public final void setManufactureDate(Date date) {
mManufactureDate = date;
}
public Boolean getCanTalk() { return mCanTalk; }
public final void setCanTalk(Boolean canTalk) {
mCanTalk = canTalk;
}
}
Let’s assume we have a few of these instances in a mobile services table. We will also grab a reference to the typed and JSON-based table, so we can show the differences in the query programming model.
MobileServiceTable<Droid> table = client.getTable(Droid.class);
MobileServiceJsonTable untypedTable = client.getTable("droid");
If these don’t seem familiar, please review the first post in my series.
Let’s start with sorting, here we show a simple ascending/descending sort using both the typed and JSON-based model.
table.orderBy("name", QueryOrder.Ascending).execute(new TableQueryCallback<Droid>() {
public void onCompleted(List<Droid> result, int count, Exception exception,
ServiceFilterResponse response) {
if(exception == null){
for (Droid d : result) {
Log.i(TAG, "Read object with ID " + d.getId());
}
}
}
});
untypedTable.orderBy("name", QueryOrder.Descending).execute(new TableJsonQueryCallback() {
public void onCompleted(JsonElement result, int count, Exception exception,
ServiceFilterResponse response) {
if(exception == null){
JsonArray results = result.getAsJsonArray();
for(JsonElement item : results){
Log.i(TAG, "Read object with ID " +
item.getAsJsonObject().getAsJsonPrimitive("id").getAsInt());
}
}
}
});
Note that the type returned by orderBy is MobileServiceQuery<T>. That is the entry point into this fluent query model. By adding do that query, you could compose sorting, paging, and filtering in any arbitrary order, as we will see further down. To terminate the query, just use the execute method. You will notice that the query stays constant regardless of whether you used the typed or JSON model, the only thing that changes is whether the execute method gets back a TableQueryCallback (typed) or TableJsonQueryCallback (JSON). So all the examples that follow will work in either case.
Paging is also simple:
table.top(10).skip(10).execute(/* callback */ );
You can easily compose this with sorting (or filtering as we will see below):
table.top(10).skip(10).orderBy("name", QueryOrder.Ascending)
.execute(/* callback */);
This is by far most interesting part of the model. To build an filter expression you have to somehow get a MobileServiceQuery<T> instance from the table you’re about to query, so you can start tacking predicates onto it. We already saw that the top, skip, and orderBy methods already provide that entry point. If you don’t want to bother with sorting and paging, we provide another “vanilla” entry point into the model, simply by calling where. You can use either of these to start building a filter. In the examples below I’ll simply show how to build the query part, it’s your job to remember to tack on execute at the end.
Consider the following examples for some basic comparison operators to fetch droid objects from our table given their number of legs.
// droid.numberOfLegs > 3
table.where().field("numberOfLegs").gt(3);
// droid.numberOfLegs >= 3
table.where().field("numberOfLegs").ge(3);
// droid.numberOfLegs < 3
table.where().field("numberOfLegs").lt(3);
// droid.numberOfLegs <= 3
table.where().field("numberOfLegs").le(3);
// droid.numberOfLegs === 3
table.where().field("numberOfLegs").eq(3);
// droid.numberOfLegs !== 3
table.where().field("numberOfLegs").ne(3);
You’ll note the use of the field method to pick the property on the server that we want to test against. One interesting case that comes up here is how to test if a value is set on the server (is not undefined). This can be a little awkward:
// droid.numberOfLegs === undefined
table.where().field("numberOfLegs").ne((Number)null);
You might get curious about what are the kinds of values that you can use on the right-hand side of your operators. You will notice that all of the above operators take a Number, boolean, String, or Date. Each of these has its own specific characteristics, let’s look at Date first.
// droid.manufactureDate == new Date(Date.UTC(2210, 6, 30));
table.where().field("manufactureDate").eq(getUTCDate(2210, 6, 30, 0, 0, 0);
private static Date getUTCDate(int year, int month, int day, int hour, int minute, int second) {
GregorianCalendar calendar = new GregorianCalendar(TimeZone.getTimeZone("utc"));
int dateMonth = month - 1;
calendar.set(year, dateMonth, day, hour, minute, second);
calendar.set(Calendar.MILLISECOND, 0);
return calendar.getTime();
}
Note that it is no so straightforward to work with UTC dates in Android, so the above example provides a convenience method to do that.
This is great if you want to compare the whole date, but what if you want to compare individual parts of the date (year, month, day). We have a solution for that as well:
// droid.manufactureDate.getUTCFullYear() === 2210
table.where().year(field("manufactureDate")).eq(2210);
// droid.manufactureDate.getUTCMonth() === 6
table.where().month(field("manufactureDate")).eq(6);
// droid.manufactureDate.getUTCDay() === 30
table.where().day(field("manufactureDate")).eq(30);
// droid.manufactureDate.getUTCHours() === 10
table.where().hour(field("manufactureDate")).eq(10);
// droid.manufactureDate.getUTCMinutes() === 15
table.where().minute(field("manufactureDate")).eq(15);
// droid.manufactureDate.getUTCSeconds() === 11
table.where().second(field("manufactureDate")).eq(11);
One thing to remember here is that these are server queries. What’s actually happening under the covers is that the left-hand and right-hand side of the query are being serialized on the wire and executed on the server. Just in case you were wondering why you can’t simply express this as a function: we need to have a way to easily parse the expression and send it to the server.
Next let’s look at Number. There are a few interesting things we may want to do to the left-hand side on the server: addition, subtraction, multiplication, division, modulus, as well as things like floor, celiling, and rounding for floats.
// droid.numberOfLegs + 2 === 18
table.where().field("numberOfLegs").add(2).eq(18);
// droid.numberOfLegs - 2 === 16
table.where().field("numberOfLegs").sub(2).eq(16);
// droid.numberOfLegs * 2 === 16
table.where().field("numberOfLegs").mul(2).eq(16);
// droid.numberOfLegs / 2 === 8
table.where().field("numberOfLegs").div(2).eq(8);
// droid.weight % 2 === 1
table.where().field("weight").mod(2).eq(1);
// Math.floor(droid.weight) > 10
table.where().floor(field("weight")).gt(10);
// Math.ceil(droid.weight) > 10
table.where().ceiling(field("weight")).gt(10);
// Math.round(droid.weight) > 5
table.where().round(field("weight")).gt(5);
Lastly let’s look at the things we can do with String on the server:
// droid.name.indexOf("c3") === 0
table.where().startsWith("name", "c3");
// showing how to do this in JavaScript is counterproductive in this case
table.where().endsWith("name", "po");
// droid.name.search("c3") !== -1
table.where().subStringOf("c3", "name");
// droid.name.concat(" rocks") === "c3po rocks"
table.where().concat(field("name"), val(" rocks")).eq("c3po rocks");
// droid.name.indexOf("3p") === 1
table.where().indexOf("name", "3p").eq(1);
// droid.name.substr(2) === "po"
table.where().subString("name", 2).eq("po");
// droid.name.substr(2, 3) === "po"
table.where().subString("name", 2, 1).eq("po");
// droid.name.replace("c3po", "r2d2) === "r2d2"
table.where().replace("name", "c3po", "r2d2").eq("r2d2");
// droid.name.toLowerCase() === "c3po"
table.where().toLower("name").eq("c3po");
// droid.name.toUpperCase() === "C3PO"
table.where().toUpper("name").eq("C3PO");
// droid.name.trim() === "c3po"
table.where().trim("name").eq("c3po");
// droid.name.length === 4
table.where().length("name").eq(4);
As you play around with these APIs you’ll notice that all of them take not just String types, but you can also specify field(“name”) and val(5). This is to enable you to interchange client and server values in your query for maximum flexibility. For example, if you want to check if one server column is a substring of another, simply use subStringOf(field(“column1”), field(“column2”)). Supercool!
We’ve shown how to do comparisons, now let’s look at how we can string those comparisons together into logical expressions. Here are some simple examples of and, or, and not.
// droid.numberOfLegs < 3 && droid.numberOfWheels > 3
table.where().field("numberOfLegs").lt(3).and().field("numberOfWheels").gt(3);
// droid.numberOfLegs < 3 || droid.numberOfWheels > 3
table.where().field("numberOfLegs").lt(3).or().field("numberOfWheels").gt(3);
// droid.numberOfLegs !== 3
table.where().not().field("numberOfLegs").eq(3);
These are great simple cases, but what if we want to support grouping (nesting) of the logical operators? For example if we want to select any droids that are named “c3po” or are similarly humanoid and have two legs and no wheels. Here we go:
// droid.name === "c3po" ||
// (droid.numberOfLegs === 2 && droid.numberOfWheels === 0)
table.where().field("name").eq("c3po")
.or(field("numberOfLegs").eq(2).and().field("numberOfWheels").eq(0));
What you see in this example is that the logical operators can also take parameters as a way to do grouping. Neat!
Last but not least, let’s talk about projection. There are two interesting scenarios here:
We can implement the first case simply by using the select method as part of our query:
// Without a query
table.select("name", "weight").execute(/* callback */);
// As part of a query
table.where().startsWith("name", "c").select("name", "numberOfLegs").execute(/* callback */);
Here is how to do the second case. Imagine we have a subclass of Droid called ProtocolDroid which contains only a subset of the properties we want to fetch:
MobileServiceTable subclassedTable = client.getTable("droid", ProtocolDroid.class);
subclassedTable.select("name", "numberOfLegs").execute(/* callback */);
This writes up today’s monster post! In the next post… auth.