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

Author: Claudio Esperanca
Published? true
FormatLanguage: WikiFormat

Problem:

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

Solution:

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

Discussion:

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);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.d(APP_NAME, "Creating the application database");
            
            try{
                // Create the full text search 3 virtual table
                db.execSQL(
                    "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);
                this.deleteDatabaseStructure(db);
            }
        }
        @Override
        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
            this.onCreate(db);
        }
        public boolean databaseCreated(){
            return this.databaseCreated;
        }
        private boolean deleteDatabaseStructure(SQLiteDatabase db){
            try{
                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 {
        try{
            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() {
        this.dbHelper.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) {
        try{
            // 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;
        try{
            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);

                do{
                    results.add(
                        new String(
                            "Username: "+cursor.getString(iUsername) +
                            ", Fullname: "+cursor.getString(iFullname) +
                            ", Email: "+cursor.getString(iEmail)
                        )
                    );
                }while(cursor.moveToNext());
            }
        }catch(Exception e){
            Log.e(APP_NAME, "An error occurred while searching for "+search+": "+e.toString(), e);
        }finally{
            if(cursor!=null && !cursor.isClosed()){
                cursor.close();
            }
        }
        
        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"?>
<resources>
    <string name="label_search">Search</string>
    <string name="app_name">AdvancedSearch</string>
</resources>
  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"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
	<EditText 
		android:text="" 
		android:id="@+id/etSearch"
		android:layout_width="fill_parent" 
		android:layout_height="wrap_content"
	    android:singleLine="true"
		/>
	<Button 
		android:text="@string/label_search" 
		android:id="@+id/btnSearch"
		android:layout_width="fill_parent" 
		android:layout_height="wrap_content" 
		/>
	<TextView  
	        android:id="@+id/tvResults"
	        android:layout_width="fill_parent" 
	        android:layout_height="fill_parent" 
	        android:text=""
	        android:singleLine="false"
	        />
</LinearLayout>
  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;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        dbAdapter = new DbAdapter(this);
        dbAdapter.open();
        
        if(dbAdapter.databaseCreated()){
            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());
                
                if(results.isEmpty()){
                    tvResults.setText("No results found");
                }else{
                    Iterator<String> i = results.iterator();
                    tvResults.setText("");
                    while(i.hasNext()){
                        tvResults.setText(tvResults.getText()+i.next()+"\n");
                    }
                }
            }
        });
    }
    @Override
    protected void onDestroy() {
        dbAdapter.close();
        super.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

No records found.