Android SearchView using SQLite fts3 module example

How to use SearchView along with SQLite fts3 module that implements SearchView.OnQueryTextListener, SearchView.OnCloseListener in an Android activity

When you're ready to add search functionality to your application, Android helps you implement the user interface with either a search dialog that appears at the top of the activity window or a search widget that you can insert in your layout. Both the search dialog and the widget can deliver the user's search query to a specific activity in your application. This way, the user can initiate a search from any activity where the search dialog or widget is available, and the system starts the appropriate activity to perform the search and present results.

Other features available for the search dialog and widget include:
  • Voice search
  • Search suggestions based on recent queries
  • Search suggestions that match actual results in your application data

This following example shows you how to set up your application to provide a search interface that's assisted by the Android system to deliver search queries, using the search widget implementing SQLite fts3 module for fast search results.


Android SearchView using SQLite fts3 module example

Android SearchView using SQLite fts3 module example

Android SearchView using SQLite fts3 module example
The search widget is an instance of SearchView that you can place anywhere in your layout. By default, the search widget behaves like a standard EditText widget and doesn't do anything, but you can configure it so that the Android system handles all input events, delivers queries to the appropriate activity, and provides search suggestions (just like the search dialog). However, the search widget is available only in Android 3.0 (API Level 11) and higher.

To set up your application for this kind of assisted search, you need the following:

  • A searchable configuration
    • An XML file that configures some settings for the search dialog or widget. It includes settings for features such as voice search, search suggestion, and hint text for the search box.
  • A searchable activity
    • The Activity that receives the search query, searches your data, and displays the search results.
  • A search interface, provided by either:
    • The search dialog
      • By default, the search dialog is hidden, but appears at the top of the screen when the user presses the device SEARCH button (when available) or another button in your user interface.
    • Or, a SearchView widget

Using the search widget allows you to put the search box anywhere in your activity. Instead of putting it in your activity layout, however, it's usually more convenient for users as an action view in the Action Bar.

If your data is stored in a SQLite database on the device, performing a full-text search (using FTS3, rather than a LIKE query) can provide a more robust search across text data and can produce results significantly faster...

The FTS3 and FTS4 extension modules allows users to create special tables with a built-in full-text index. The full-text index allows the user to efficiently query the database for all rows that contain one or more words, even if the table contains many large documents.

Source for searchable.xml, the searchable configuration

<?xml version="1.0" encoding="utf-8"?>
<searchable xmlns:android="http://schemas.android.com/apk/res/android"
    android:label="@string/search_label" android:hint="@string/search_hint" />

Source for strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="hello">Hello World, SearchViewActivity!</string>
    <string name="app_name">Search View</string>
    <string name="search_label">Customers</string>
    <string name="search_hint">Search Customers</string>
    <string name="settings_description">List of Customers</string>
</resources>

Source for AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
      package="com.as400samplecode"
      android:versionCode="1"
      android:versionName="1.0">
    <uses-sdk android:minSdkVersion="13" />

    <application android:icon="@drawable/icon" android:label="@string/app_name"
    android:theme="@android:style/Theme.Holo.Light">
   
        <activity android:name=".SearchViewActivity"
                  android:label="@string/app_name">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
            <intent-filter>
                <action android:name="android.intent.action.SEARCH" />
            </intent-filter>
            <meta-data android:name="android.app.searchable"
                android:resource="@xml/searchable" />
        </activity>

    </application>
</manifest>

Source for main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent" android:layout_height="fill_parent"
    android:orientation="horizontal">
    <LinearLayout android:layout_width="0dp" android:id="@+id/linearLayout1"
        android:layout_height="match_parent" android:layout_weight="30"
        android:orientation="vertical">
        <LinearLayout android:id="@+id/leftLayout"
            android:layout_width="match_parent" android:orientation="vertical"
            android:layout_height="wrap_content" android:paddingBottom="50dp">
            <SearchView android:layout_width="wrap_content"
                android:layout_height="wrap_content" android:id="@+id/search" />
            <ListView android:layout_width="fill_parent" android:id="@+id/list"
                android:layout_height="wrap_content" />
        </LinearLayout>
    </LinearLayout>
    <LinearLayout android:id="@+id/linearLayout2"
        android:layout_height="match_parent" android:orientation="vertical"
        android:layout_weight="70" android:layout_width="0dp">
        <LinearLayout android:id="@+id/linearLayout5"
            android:layout_height="wrap_content" android:orientation="horizontal"
            android:paddingBottom="10dp" android:layout_gravity="right"
            android:layout_width="match_parent">
            <TextView android:text="Customer Information" android:textStyle="bold"
                android:layout_height="wrap_content" android:id="@+id/textView6"
                android:textSize="25sp" android:layout_width="wrap_content"
                android:layout_weight="1" />
            <TextView android:text="Date: " android:textStyle="bold"
                android:layout_height="wrap_content" android:id="@+id/textView5"
                android:textSize="25sp" android:layout_width="wrap_content" />
            <TextView android:layout_width="wrap_content"
                android:layout_height="wrap_content" android:id="@+id/inspectionDate"
                android:textSize="25sp" android:paddingRight="25sp"/>
        </LinearLayout>
        <LinearLayout android:id="@+id/rightLayout"
            android:layout_width="match_parent" android:orientation="vertical"
            android:layout_height="wrap_content" android:paddingTop="25dp">
        </LinearLayout>
    </LinearLayout>
</LinearLayout>

Source for customerinfo.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/customerLayout" android:layout_width="match_parent"
    android:layout_height="wrap_content" android:orientation="vertical">
    <RelativeLayout android:id="@+id/relativeLayout1"
        android:layout_width="match_parent" android:layout_height="wrap_content">
        <TextView android:id="@+id/customer" android:layout_width="wrap_content"
            android:layout_height="wrap_content" style="@android:style/TextAppearance.Medium" android:paddingRight="5sp"/>
        <TextView android:id="@+id/name" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/customer"
            style="@android:style/TextAppearance.Small" />
        <TextView android:id="@+id/address" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/name"
            style="@android:style/TextAppearance.Small" />
        <TextView android:id="@+id/city" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/address"
            style="@android:style/TextAppearance.Small" android:paddingRight="2sp"/>
        <TextView android:id="@+id/comma" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/address" android:layout_toRightOf="@id/city"
            style="@android:style/TextAppearance.Small" android:paddingRight="2sp" android:text=","/>
        <TextView android:id="@+id/state" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/address" android:layout_toRightOf="@id/comma"
            style="@android:style/TextAppearance.Small" android:paddingRight="2sp"/>
        <TextView android:id="@+id/zipCode" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/address" android:layout_toRightOf="@id/state"
            style="@android:style/TextAppearance.Small" />   
    </RelativeLayout>
</LinearLayout>

Source for customerresult.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" android:padding="5dp">
    <RelativeLayout android:id="@+id/relativeLayout1"
        android:layout_width="match_parent" android:layout_height="wrap_content">
        <TextView android:id="@+id/scustomer" android:layout_width="wrap_content"
            android:layout_height="wrap_content" style="@android:style/TextAppearance.Medium"
            android:paddingRight="5sp" />
        <TextView android:id="@+id/sname" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_toRightOf="@id/scustomer"
            style="@android:style/TextAppearance.Small" />
        <TextView android:id="@+id/saddress" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/scustomer"
            style="@android:style/TextAppearance.Small" />
        <TextView android:id="@+id/scity" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/saddress"
            style="@android:style/TextAppearance.Small" android:paddingRight="2sp" />
        <TextView android:id="@+id/scomma" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/saddress"
            android:layout_toRightOf="@id/scity" style="@android:style/TextAppearance.Small"
            android:paddingRight="2sp" android:text=","/>   
        <TextView android:id="@+id/sstate" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/saddress"
            android:layout_toRightOf="@id/scomma" style="@android:style/TextAppearance.Small"
            android:paddingRight="2sp" />
        <TextView android:id="@+id/szipCode" android:layout_width="wrap_content"
            android:layout_height="wrap_content" android:layout_below="@id/saddress"
            android:layout_toRightOf="@id/sstate" style="@android:style/TextAppearance.Small" />
    </RelativeLayout>
</LinearLayout>

Source for Customer.java

package com.as400samplecode;

public class Customer {
   
    String customer = null;
    String name = null;
    String address1 = null;
    String address2 = null;
    String city = null;
    String state = null;
    String zipCode = null;
   
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getAddress1() {
        return address1;
    }
    public void setAddress1(String address1) {
        this.address1 = address1;
    }
    public String getAddress2() {
        return address2;
    }
    public void setAddress2(String address2) {
        this.address2 = address2;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public String getState() {
        return state;
    }
    public void setState(String state) {
        this.state = state;
    }
    public String getZipCode() {
        return zipCode;
    }
    public void setZipCode(String zipCode) {
        this.zipCode = zipCode;
    }
       
   
}

Source for CustomersDbAdapter.java

package com.as400samplecode;

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 CustomersDbAdapter {

    public static final String KEY_ROWID = "rowid";
    public static final String KEY_CUSTOMER = "customer";
    public static final String KEY_NAME = "name";
    public static final String KEY_ADDRESS = "address";
    public static final String KEY_ADDRESS1 = "address1";
    public static final String KEY_ADDRESS2 = "address2";
    public static final String KEY_CITY = "city";
    public static final String KEY_STATE = "state";
    public static final String KEY_ZIP = "zipCode";
    public static final String KEY_SEARCH = "searchData";

    private static final String TAG = "CustomersDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private static final String DATABASE_NAME = "CustomerData";
    private static final String FTS_VIRTUAL_TABLE = "CustomerInfo";
    private static final int DATABASE_VERSION = 1;

    //Create a FTS3 Virtual Table for fast searches
    private static final String DATABASE_CREATE =
        "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE + " USING fts3(" +
        KEY_CUSTOMER + "," +
        KEY_NAME + "," +
        KEY_ADDRESS1 + "," +
        KEY_ADDRESS2 + "," +
        KEY_CITY + "," +
        KEY_STATE + "," +
        KEY_ZIP + "," +
        KEY_SEARCH + "," +
        " UNIQUE (" + KEY_CUSTOMER + "));";


    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }


        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.w(TAG, DATABASE_CREATE);
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE);
            onCreate(db);
        }
    }

    public CustomersDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public CustomersDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        if (mDbHelper != null) {
            mDbHelper.close();
        }
    }


    public long createCustomer(String customer, String name, String address1, String address2, String city, String state, String zipCode) {

        ContentValues initialValues = new ContentValues();
        String searchValue =     customer + " " + 
                                name + " " + 
                                address1 + " " + 
                                city + " " + 
                                state + " " + 
                                zipCode;
        initialValues.put(KEY_CUSTOMER, customer);
        initialValues.put(KEY_NAME, name);
        initialValues.put(KEY_ADDRESS1, address1);
        initialValues.put(KEY_ADDRESS2, address2);
        initialValues.put(KEY_CITY, city);
        initialValues.put(KEY_STATE, state);
        initialValues.put(KEY_ZIP, zipCode);
        initialValues.put(KEY_SEARCH, searchValue);

        return mDb.insert(FTS_VIRTUAL_TABLE, null, initialValues);
    }


    public Cursor searchCustomer(String inputText) throws SQLException {
        Log.w(TAG, inputText);
        String query = "SELECT docid as _id," + 
        KEY_CUSTOMER + "," +
        KEY_NAME + "," +
        "(" + KEY_ADDRESS1 + "||" + 
        "(case when " + KEY_ADDRESS2 +  "> '' then '\n' || " + KEY_ADDRESS2 + " else '' end)) as " +  KEY_ADDRESS +"," +
        KEY_ADDRESS1 + "," +
        KEY_ADDRESS2 + "," +
        KEY_CITY + "," +
        KEY_STATE + "," +
        KEY_ZIP +
        " from " + FTS_VIRTUAL_TABLE +
        " where " +  KEY_SEARCH + " MATCH '" + inputText + "';";
        Log.w(TAG, query);
        Cursor mCursor = mDb.rawQuery(query,null);

        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }


    public boolean deleteAllCustomers() {

        int doneDelete = 0;
        doneDelete = mDb.delete(FTS_VIRTUAL_TABLE, null , null);
        Log.w(TAG, Integer.toString(doneDelete));
        return doneDelete > 0;

    }

}

Source for SearchViewActivity.java

package com.as400samplecode;

import java.util.Calendar;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.LinearLayout;
import android.widget.ListView;
import android.widget.SearchView;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.AdapterView.OnItemClickListener;

public class SearchViewActivity extends Activity implements SearchView.OnQueryTextListener,
SearchView.OnCloseListener {

    private ListView mListView;
    private SearchView searchView;
    private CustomersDbAdapter mDbHelper;

    private TextView inspectionDate;
    private TextView customerText;
    private TextView nameText;
    private TextView addressText;
    private TextView cityText;
    private TextView stateText;
    private TextView zipCodeText;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        searchView = (SearchView) findViewById(R.id.search);
        searchView.setIconifiedByDefault(false);
        searchView.setOnQueryTextListener(this);
        searchView.setOnCloseListener(this);

        mListView = (ListView) findViewById(R.id.list);
        inspectionDate = (TextView) findViewById(R.id.inspectionDate);
        displayDate();

        mDbHelper = new CustomersDbAdapter(this);
        mDbHelper.open();

        //Clean all Customers
        mDbHelper.deleteAllCustomers();
        //Add some Customer data as a sample
        mDbHelper.createCustomer("PIZZA1", "Pizza Hut", "1107 West Adams Boulevard", "", "Los Angeles", "CA", "90007");
        mDbHelper.createCustomer("PIZZA2", "Pizza Hut", "1562 West Pico Boulevard", "", "Los Angeles", "CA", "90015");
        mDbHelper.createCustomer("PIZZA3", "Pizza Hut", "718 South Los Angeles Street", "", "Los Angeles", "CA", "90014");
        mDbHelper.createCustomer("PIZZA4", "Pizza Hut", "2542 West Temple Street", "", "Los Angeles", "CA", "90026");
        mDbHelper.createCustomer("PIZZA5", "Pizza Hut", "4329 North Figueroa Street", "", "Los Angeles", "CA", "90065");
        mDbHelper.createCustomer("PIZZA6", "Pizza Hut", "4351 South Central Avenue", "", "Los Angeles", "CA", "90011");
        mDbHelper.createCustomer("SUB1", "Subway", "975 West Jefferson", "", "Los Angeles", "CA", "90007");
        mDbHelper.createCustomer("SUB2", "Subway", "2805 South Figueroa Street", "", "Los Angeles", "CA", "90007");
        mDbHelper.createCustomer("SUB3", "Subway", "198 South Vermont Avenue", "", "Los Angeles", "CA", "90004");
        mDbHelper.createCustomer("SUB4", "Subway", "504 West Olympic Boulevard", "", "Los Angeles", "CA", "90015");

    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        if (mDbHelper  != null) {
            mDbHelper.close();
        }
    }

    public boolean onQueryTextChange(String newText) {
        showResults(newText + "*");
        return false;
    }

    public boolean onQueryTextSubmit(String query) {
        showResults(query + "*");
        return false;
    }

    public boolean onClose() {
        showResults("");
        return false;
    }

    private void showResults(String query) {

        Cursor cursor = mDbHelper.searchCustomer((query != null ? query.toString() : "@@@@"));

        if (cursor == null) {
            //
        } else {
            // Specify the columns we want to display in the result
            String[] from = new String[] {
                    CustomersDbAdapter.KEY_CUSTOMER,
                    CustomersDbAdapter.KEY_NAME,
                    CustomersDbAdapter.KEY_ADDRESS,
                    CustomersDbAdapter.KEY_CITY,
                    CustomersDbAdapter.KEY_STATE,
                    CustomersDbAdapter.KEY_ZIP};   

            // Specify the Corresponding layout elements where we want the columns to go
            int[] to = new int[] {     R.id.scustomer,
                    R.id.sname,
                    R.id.saddress,
                    R.id.scity,
                    R.id.sstate,
                    R.id.szipCode};

            // Create a simple cursor adapter for the definitions and apply them to the ListView
            SimpleCursorAdapter customers = new SimpleCursorAdapter(this,R.layout.customerresult, cursor, from, to);
            mListView.setAdapter(customers);

            // Define the on-click listener for the list items
            mListView.setOnItemClickListener(new OnItemClickListener() {
                public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                    // Get the cursor, positioned to the corresponding row in the result set
                    Cursor cursor = (Cursor) mListView.getItemAtPosition(position);

                    // Get the state's capital from this row in the database.
                    String customer = cursor.getString(cursor.getColumnIndexOrThrow("customer"));
                    String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                    String address = cursor.getString(cursor.getColumnIndexOrThrow("address"));
                    String city = cursor.getString(cursor.getColumnIndexOrThrow("city"));
                    String state = cursor.getString(cursor.getColumnIndexOrThrow("state"));
                    String zipCode = cursor.getString(cursor.getColumnIndexOrThrow("zipCode"));
               
                    //Check if the Layout already exists
                    LinearLayout customerLayout = (LinearLayout)findViewById(R.id.customerLayout);
                    if(customerLayout == null){
                        //Inflate the Customer Information View 
                        LinearLayout leftLayout = (LinearLayout)findViewById(R.id.rightLayout);
                        View customerInfo = getLayoutInflater().inflate(R.layout.customerinfo, leftLayout, false);
                        leftLayout.addView(customerInfo);
                    }

                    //Get References to the TextViews
                    customerText = (TextView) findViewById(R.id.customer);
                    nameText = (TextView) findViewById(R.id.name);
                    addressText = (TextView) findViewById(R.id.address);
                    cityText = (TextView) findViewById(R.id.city);
                    stateText = (TextView) findViewById(R.id.state);
                    zipCodeText = (TextView) findViewById(R.id.zipCode);
               
                    // Update the parent class's TextView
                    customerText.setText(customer);
                    nameText.setText(name);
                    addressText.setText(address);
                    cityText.setText(city);
                    stateText.setText(state);
                    zipCodeText.setText(zipCode);
           
                    searchView.setQuery("",true);
                }
            });
        }
    }

    private void displayDate() {

        final Calendar c = Calendar.getInstance();

        inspectionDate.setText(
                new StringBuilder()
                // Month is 0 based so add 1
                .append(c.get(Calendar.MONTH) + 1).append("/")
                .append(c.get(Calendar.DAY_OF_MONTH)).append("/")
                .append(c.get(Calendar.YEAR)).append(" "));
    }
}

Tip: How to add voice search? See sample searchable configuration !
<?xml version="1.0" encoding="utf-8"?>
<searchable xmlns:android="http://schemas.android.com/apk/res/android"
    android:label="@string/search_label" android:hint="@string/search_hint"
    android:voiceSearchMode="showVoiceSearchButton|launchRecognizer" />


No comments:

Post a Comment

NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.