Making queries easier with QueryBuilder

Introduction

Writing SQL queries (especially WHERE-clauses) can be tedious and redundant and it can be way too easy to make errors when manipulating the strings that comprise the clauses.

The answer

In order to simplify and streamline the query writing that I do up to, say, ten times an hour making content-intensive apps, I came up with the QueryBuilder. This class makes your queries more readable, reusable and less error-prone by exposing self-explanatory methods that take care of the string/object manipulation for you. Hell, it even has search multi-column free-text search capabilities!

Where can I download it?

It is available free of charge in the BuzzingAndroid GitHub repository right here: QueryBuilder.java

Now that you have it ready to use, let’s dive right in!

The interface

// Constructor
public QueryBuilder();

// Selection methods
public QueryBuilder whereId(int id);
public QueryBuilder whereId(long id);
public QueryBuilder whereColumnIsNull(String column);
public QueryBuilder whereColumnIsNotNull(String column);
public QueryBuilder whereColumnEquals(String column, Object value);
public QueryBuilder whereColumnNotEquals(String column, Object value);
public QueryBuilder whereColumnGreaterThan(String column, Object value);
public QueryBuilder whereColumnGreaterThanOrEqual(String column, Object value);
public QueryBuilder whereColumnLessThan(String column, Object value);
public QueryBuilder whereColumnLessThanOrEqual(String column, Object value);
public QueryBuilder whereColumnInSet(String column, Object[] set);
public QueryBuilder whereColumnInSet(String column, long[] set);
public QueryBuilder whereColumnInSet(String column, int[] set);
public QueryBuilder whereColumnNotInSet(String column, Object[] set);
public QueryBuilder whereColumnNotInSet(String column, long[] set);
public QueryBuilder whereColumnNotInSet(String column, int[] set);
public QueryBuilder addSelection(String extraSelection, Object... extraSelectionArgs);

// Search methods
public QueryBuilder setSearchColumns(String... columns);
public QueryBuilder setSearchQuery(String query);
public FilterQueryProvider createSearchFilterQueryProvider(final Context context, final Uri uri);

// Set projection
public QueryBuilder select(String... columns);

// Set sort order
public QueryBuilder orderBy(String sortOrder);

// Build selection string and argument array
public Pair<String, String[]> buildSelection();

// Query methods
public Cursor query(Context context, Uri uri);
public Cursor query(ContentProviderClient provider, Uri uri) throws RemoteException;
public void queryAsync(Context context, Uri uri, final AsyncQueryCallback callback);
public CursorLoader createCursorLoader(Context context, Uri uri);

// Update methods
public int update(Context context, ContentValues values, Uri uri);
public int update(ContentProviderClient provider, ContentValues values, Uri uri) throws RemoteException;
public ContentProviderOperation createUpdateOperation(ContentValues values, Uri uri);

// Delete methods
public int delete(Context context, Uri uri);
public int delete(ContentProviderClient provider, Uri uri) throws RemoteException;
public ContentProviderOperation createDeleteOperation(Uri uri);

Examples

So… how do you use this fine piece of code, you say? First, let’s create a sample SQL table, so we can get right to some examples:

CREATE TABLE users (
    _id INTEGER PRIMARY KEY AUTOINCREMENT, -- This column is named _id after BaseColumns._ID. The whereId() methods search this column
    firstname TEXT NOT NULL,
    lastname TEXT NOT NULL,
    email TEXT,
    age INTEGER NOT NULL
);

For the following examples, I assume that you have set up some kind of content provider for the table that supports query(), insert() and delete(), and
that this table can be reached through some Uri uri.

Basics

Let’s start by querying the id of all users with firstname ‘Jesper’ – easy:

Cursor c = new QueryBuilder()
    .select( "_id" )
    .whereColumnEquals( "firstname", "Jesper" )
    .query( context, uri );

In the same way, we can also update or delete users with firstname ‘Jesper’:

// Update
ContentValues values = new ContentValues();
values.put( "lastname", "Borgstrup" );
new QueryBuilder()
    .whereColumnEquals( "firstname", "Jesper" )
    .update( context, values, uri );

// Delete
new QueryBuilder()
    .whereColumnEquals( "firstname", "Jesper" )
    .delete( context, uri );

Null checks

The two methods whereColumnIsNull() and whereColumnIsNotNull() are used to check if a value is null or not.
If we want to delete all users that don’t have an email address (read: email is null), it is as easy as:

new QueryBuilder()
	.whereColumnIsNull( "email" )
	.delete( context, uri );

Chaining selections

We can chain more selections together – to find all users with firstname ‘Jesper’ and lastname ‘Borgstrup’ whose email address is not null:

Cursor c = new QueryBuilder()
    .whereColumnEquals( "firstname", "Jesper" )
    .whereColumnEquals( "lastname", "Borgstrup" )
    .whereColumnIsNotNull( "email" )
    .query( context, uri );

Everytime you call a where*()-method or addSelection(), the selection is added to the selections
with the SQL AND operator. That is, all of the chained selections must be true for a given row to be selected.

Comparisons

There are four comparison methods for the four comparison operators, greater-than (>), greater-than-or-equal (>=), less-than (<) and less-than-or-equal (lt;=).

So, in order to select the first and last names of all users that is at least 18 years old, the following expressing should be used:

Cursor c = new QueryBuilder()
    .select( "firstname", "lastname" )
    .whereColumnGreaterThanOrEqual( "age", 18 )
    .query( context, uri );

Set operations

The QueryBuilder can also query for values in a set/not in a set. If we wanted to delete all users whose firstname is neither ‘Casper’, ‘Jesper’ nor ‘Jonathan’, here is how we would do it:

String[] acceptedFirstnames = { "Casper", "Jesper", "Jonathan" };
new QueryBuilder()
    .whereColumnNotInSet( "firstname", acceptedFirstnames )
    .delete( context, uri );

The whereColumnInSet() methods are the opposites of whereColumnNotInSet(). (Pretty self-explainatory, right=)

More querying

Besides doing “normal” queries, QueryBuilder is also capable of doing asynchronous queries through the AsyncQueryCallback interface in the following way:

new QueryBuilder()
    .select( "_id", "firstname", "lastname", "email", "age" )
    .queryAsync( context, uri, new AsyncQueryCallback() {
        @Override
        public void queryCompleted(Cursor c) {
            // Do something with the data
        }
    });

Here, the query is being carried out in a background thread and the resulting cursor is brought to us in the callback – in the same thread that called the queryAsync() method.
Needless to say, this is great for querying on the UI thread.

Speaking of querying on the UI thread, the Android support library has a class CursorLoader, that is made for this purpose exactly. Of course, QueryBuilder supports creating these:

new QueryBuilder()
    .select( "_id", "firstname", "lastname", "email", "age" )
    .createCursorLoader( context, uri );

Free-text search

I promised you in the beginning of this article that QueryBuilder had multi-column free-text search capabilities, and I’m not going to disappoint you.

With QueryBuilder, you set one or more columns to search through, gives it a search query which is broken down into search tokens, delimited by whitespace (as by the java.util.StringTokenizer class), and each of these tokens has to appear in at least one of the columns in order for a row to be selected.

Let’s try it out:

new QueryBuilder()
    .select( "_id", "firstname", "lastname", "email", "age" )
    .setSearchColumns( "firstname", "lastname" )
    .setSearchQuery( "sper strup" )
    .query( context, uri );

To explain, this query would match the user with firstname “Jesper” and lastname “Borgstrup”, but not firstname “Jesper” and lastname “Dover”, nor firstname “Jonathan” and lastname “skibstrup”.
However the strangely named user with firstname “Casperitiustruppolous” would be selected solely on his firstname (as it contains both “sper” and “strup”)

This free-text searching can also be exposed through a FilterQueryProvider that fits nicely into a CursorAdapter:

CursorAdapter adapter;
adapter.setFilterQueryProvider( new QueryBuilder()
                                    .select( "_id", "firstname", "lastname", "email", "age" )
                                    .setSearchColumns( "firstname", "lastname" )
                                    .setSearchQuery( "sper borg" )
                                    .createSearchFilterQueryProvider( context, uri ) );

The less-often used methods

Lastly, QueryBuilder also supports building ContentProviderOperations for use in a ContentProvider.applyBatch() batch operation through the following methods:

public ContentProviderOperation createUpdateOperation(ContentValues values, Uri uri);
public ContentProviderOperation createDeleteOperation(Uri uri);

[/java]

Also, querying, updating and deleting by the use of a ContentProviderClient instead of a Context is supported. These functions become especially handy when
dealing with content in SyncAdapters.

Conclusion

QueryBuilder makes selection of SQL data more readable and less error-prone by exposing descriptively-named methods that abstracts the string manipulation of multiple selections.

Also, it looks cooler. I hope you can use it, because I sure can

2 thoughts on “Making queries easier with QueryBuilder

  1. Marco

    hi there.

    I’m learning Java and making an aplicattion where 1 use a lot database search, i found this and i think it’ll be really helpefull to me, but there is 1 thing i don’t understand, when you call the “query(contextx, uri)” query i know is for calling the query, but in the context i use “this” because i’m calling it from the mainactivity class, but uri…… what is it? is it the path to de DbHandeler?. I’ve got a new class that connects to the sqlite database called DbHelper, can you help me? everything i find on google it’s a real mess and nobody explains anything properly….

    Thanks

    Reply
    1. Jesper Borgstrup Post author

      Hi Marco.

      You use a URI to access a ContentProvider. Normally, different tables in your SQLite database corresponds to different URI’s in your ContentProvider.
      If you don’t know what a ContentProvider is, you should read up on it :-) I recommend Lars Vogel’s Android SQLite database and content provider – tutorial – it explains everything you need to know about best SQLite practices on Android.

      Best Regards, Jesper

      Reply

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>