Using LIKE constructs in Android SQLite Queries


Using LIKE constructs in Android SQLite Queries

2015/11/30

Tags: Android Programming SQLite

I was using SQLite Database to cache things around in an Android application. I had to implement a search interface for cache’s title column. I was using CursorLoader. So, how do we filter on CursorLoader? Answer was simple - reload the cursorloader with new selection arguments.

A signature for a variant of query in Android is:

Cursor
 query(String table, String[] columns, String selection, String[]
selectionArgs, String groupBy, String having, String orderBy, String
limit);

To use LIKE statements in Android SQLite queries, use following kind of construct:

mFilterTitle = ((TextView) findViewById(R.id.textview)).getText();
selection =  CacheContract.COLUMN_TITLE + " LIKE ?";
selectionArgs = new String[] {mFilterTitle + '%'};

So, if COLUMN_TITLE is col_title and mFilterTitle is happ(he was searching for happiness), it would generate a statement similar to :

SELECT .............. WHERE col_title LIKE "happ%";

We can now use these kinds of statements in CursorLoader as:

return new CursorLoader(this, articlesLoaderUri, ARTICLES_PROJECTION,
                         CacheContract.COLUMN_TITLE + " LIKE ?",
                         new String[] {mFilterTitle + '%'},
                         null);

LIKE is a very powerful way to search for strings in SQL Databases. Don’t use LIKE with arguments like %string% unless you absolutely need it. It’s too slow because the query engine may not use index at all. So, prefer string%.

If you are developing for the web, there exists multiple search solutions like Apache Solr, ElasticSearch, etc that can do these kinds of searches much more faster than SQL.

Happy Android Development :)