Logo Icon Logo
A Crowd-sourced Cookbook on Writing Great Android® Apps
GitHub logo Twitter logo OReilly Book Cover Art

Performing Advanced Text Searches on an SQLite Database

In Chapter: Data Persistence
Author: Claudio Esperanca ('cesperanc@')
Published? true
FormatLanguage: WikiFormat


How to build a data layer to store and search text data using wildcards or expressions like and, or, not, etc.


Using a SQLite Full Text Search 3 (FTS3) virtual table and match function from SQLite it's possible to build such mechanism.


Following these steps you will be able to create an example android project with a data layer where you will be able to store and retrieve some data using the SQLite.

  1. Create a new Android Project (AdvancedSearchProject)
    1. Select an API level equal or greater than 8
    2. Specify AdvancedSearch as the application name
    3. Use com.androidcookbook.example.advancedsearch as the package name
    4. Create an activity with the name AdvancedSearchActivity
    5. The Min SDK version should be 8 (for the android 2.2 - codename froyo)
  2. Create a new Java class DbAdapter within the package com.androidcookbook.example.advancedsearch on the src folder.

To create the data layer for the example application, enter the following source code in the created file:

package com.androidcookbook.example.advancedsearch;

import java.util.LinkedList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbAdapter {
    public static final String APP_NAME = "AdvancedSearch";
    private static final String DATABASE_NAME = "AdvancedSearch_db";
    private static final int DATABASE_VERSION = 1; // Our internal database version (e.g. to control upgrades)
    private static final String TABLE_NAME = "example_tbl";
    public static final String KEY_USERNAME = "username";
    public static final String KEY_FULLNAME = "fullname";
    public static final String KEY_EMAIL = "email";
    public static long GENERIC_ERROR = -1;
    public static long GENERIC_NO_RESULTS = -2;
    public static long ROW_INSERT_FAILED = -3;
    private final Context context;
    private DbHelper dbHelper;
    private SQLiteDatabase sqlDatabase;
    public DbAdapter(Context context) {
        this.context = context;
    private static class DbHelper extends SQLiteOpenHelper {
        private boolean databaseCreated=false;
        DbHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        public void onCreate(SQLiteDatabase db) {
            Log.d(APP_NAME, "Creating the application database");
                // Create the full text search 3 virtual table
                    "CREATE VIRTUAL TABLE ["+TABLE_NAME+"] USING FTS3 (" +
                        "["+KEY_USERNAME+"] TEXT," +
                        "["+KEY_FULLNAME+"] TEXT," +
                        "["+KEY_EMAIL+"] TEXT" +
                this.databaseCreated = true;
            } catch (Exception e) {
                Log.e(APP_NAME, "An error occurred while creating the database: "+e.toString(), e);
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.d(APP_NAME, "Updating the database from the version " + oldVersion + " to " + newVersion + "...");
            this.deleteDatabaseStructure(db); // in this example we purge the previous data on upgrade
        public boolean databaseCreated(){
            return this.databaseCreated;
        private boolean deleteDatabaseStructure(SQLiteDatabase db){
                db.execSQL("DROP TABLE IF EXISTS ["+TABLE_NAME+"];");
                return true;
            }catch (Exception e) {
                Log.e(APP_NAME, "An error occurred while deleting the database: "+e.toString(), e);
            return false;
     * Open the database; if the database can't be opened, try to create it
     * @return {@link Boolean} true if the database was successfuly opened/created, false otherwise
     * @throws {@link SQLException] if an error ocorred
    public boolean open() throws SQLException {
            this.dbHelper = new DbHelper(this.context);
            this.sqlDatabase = this.dbHelper.getWritableDatabase();
            return this.sqlDatabase.isOpen();
        }catch (SQLException e) {
            throw e;
     * Close the database connection
     * @return {@link Boolean} true if the connection was terminated, false otherwise
    public boolean close() {
        return !this.sqlDatabase.isOpen();
     * Check if the database is opened
     * @return {@link Boolean} true if it was, false otherwise
    public boolean isOpen(){
        return this.sqlDatabase.isOpen();
     * Check if the database was created
     * @return {@link Boolean} true if it was, false otherwise
    public boolean databaseCreated(){
        return this.dbHelper.databaseCreated();
     * Insert a new row on the table
     * @param username {@link String} with the username
     * @param fullname {@link String} with the fullname
     * @param email {@link String} with the email
     * @return {@link Long} with the row id or ROW_INSERT_FAILED (bellow 0 value) on error
    public long insertRow(String username, String fullname, String email) {
            // Prepare the values
            ContentValues values = new ContentValues();
            values.put(KEY_USERNAME, username);
            values.put(KEY_FULLNAME, fullname);
            values.put(KEY_EMAIL, email);
            // Try to insert the row
            return this.sqlDatabase.insert(TABLE_NAME, null, values);
        }catch (Exception e) {
            Log.e(APP_NAME, "An error occurred while inserting the row: "+e.toString(), e);
        return ROW_INSERT_FAILED;
     * The search method
     * Uses the full text search 3 virtual table and the MATCH function from SQLite to search for data
     * @see http://www.sqlite.org/fts3.html to know more about the syntax
     * @param search {@link String} with the search expression
     * @return {@link LinkedList} with the {@link String} search results
    public LinkedList<String> search(String search) {
        LinkedList<String> results = new LinkedList<String>();
        Cursor cursor = null;
            cursor = this.sqlDatabase.query(true, TABLE_NAME, new String[] { KEY_USERNAME, KEY_FULLNAME, KEY_EMAIL }, TABLE_NAME + " MATCH ?", new String[] { search }, null, null, null, null);
            if(cursor!=null && cursor.getCount()>0 && cursor.moveToFirst()){
                int iUsername = cursor.getColumnIndex(KEY_USERNAME);
                int iFullname = cursor.getColumnIndex(KEY_FULLNAME);
                int iEmail = cursor.getColumnIndex(KEY_EMAIL);

                        new String(
                            "Username: "+cursor.getString(iUsername) +
                            ", Fullname: "+cursor.getString(iFullname) +
                            ", Email: "+cursor.getString(iEmail)
        }catch(Exception e){
            Log.e(APP_NAME, "An error occurred while searching for "+search+": "+e.toString(), e);
            if(cursor!=null && !cursor.isClosed()){
        return results;

Now that the data layer is usable, the activity AdvancedSearchActivity can be used to test it.

  1. To define the application strings, replace the contents of the res/values/strings.xml file:
<?xml version="1.0" encoding="utf-8"?>
    <string name="label_search">Search</string>
    <string name="app_name">AdvancedSearch</string>
  1. The application layout can be set within the file res/layout/main.xml:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
  1. To finish, replace the contents of the AdvancedSearchActivity.java file by the following code:
package com.androidcookbook.example.advancedsearch;

import java.util.Iterator;
import java.util.LinkedList;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class AdvancedSearchActivity extends Activity {
    private DbAdapter dbAdapter;
    public void onCreate(Bundle savedInstanceState) {
        dbAdapter = new DbAdapter(this);
            dbAdapter.insertRow("test", "test example", "example_test@example.com");
            dbAdapter.insertRow("lorem", "lorem ipsum", "lorem.ipsum@example2.com");
            dbAdapter.insertRow("jdoe", "Jonh Doe", "j.doe@example.com");
        Button button = (Button) findViewById(R.id.btnSearch);
        final EditText etSearch = (EditText) findViewById(R.id.etSearch);
        final TextView tvResults = (TextView) findViewById(R.id.tvResults);
        button.setOnClickListener(new View.OnClickListener() {
            public void onClick(View v) {
                LinkedList<String> results = dbAdapter.search(etSearch.getText().toString());
                    tvResults.setText("No results found");
                    Iterator<String> i = results.iterator();
    protected void onDestroy() {

See Also:

[1] - to know more about the Full Text Search 3, including the search syntax

[2] - project with an implementation of this search mechanism


If you found this recipe useful, why not buy the book and have the whole collection always at hand?"