Dynamic search in Android Database

In my previous post , I explained on how to copy a SQLite database into the android internal memory .
In this , we will learn how use it to access database dynamically .

Prerequisites :

The basic programming in Android
My Previous Post : Using static database with android

So , once we have followed the previous post , for creating a dynamic search we need to do the following :

1. Set content view to a search layout that will contain a textbox and a list view 
2. Create a Cursor which is a more like a ResultSet in java
3. Create a Adaptor that will manage the Cursor (to view)
4. Set a filter query provider 
5. Set a text change listener for the edittext which will call the filer set in step 4
6. Add a OnItemClickListener which will be clicked when a list item is clicked

STEP 1

Define a file called search.xml in res/layout/ which will be in something like the following 

Please note that it is of uttermost importance that the id of the listview is list . 
now , in the activity write the following java code:

 
setContentView(R.layout.search);

STEP 2

Now , we need to define a Cursor using the following java code :

sql=db.getReadableDatabase();
        allcolumns=new String[]{"_id","Firstname||' '||Lastname"};
        selection=null;
        mCursor=sql.query(true, "Contact", allcolumns, selection, null, null, null, null, null);
   startManagingCursor(mCursor);

STEP 3

Now we need to use this mCursor with a adaptor , Adaptor is what will manage the views of the mCursor

  String[] array= {"Firstname||' '||Lastname"};
   adapter = new SimpleCursorAdapter(this, // Context.
  android.R.layout.simple_list_item_1,// Specify the row template
           // to use (here, two
           // columns bound to the
           // two retrieved cursor
           // rows).
    mCursor, // Pass in the cursor to bind to.
    // Array of cursor columns to bind to.
    array,
    // Parallel array of which template objects to bind to those
    // columns.
    new int[] { android.R.id.text1});

for this example we have used a predefined curson adapter named SimpleCursorAdapter , but if we want we can also define our own cursor adaptors .  The comments in the code are self explanatory .

STEP 4

Now we need to define a filter Query Provider which will run a Query on a given character sequence

 adapter.setFilterQueryProvider(new FilterQueryProvider() {
  
   @Override
   public Cursor runQuery(CharSequence s) {
    
    selection="Firstname||' '||LastName like '"+s+"%'" ;
    
    Log.d("Selection Query",selection);
    Cursor c=sql.query(true, "Contact", allcolumns, selection, null, null, null,null,null);
    return c;
  
    
   }
  });

STEP 5

Once the FilterQueryProvider has been defined , the next question arises is when should the filter be executed . So , we know that filter should be executed when the text in editText is change , so :

  et.addTextChangedListener(new TextWatcher() {
   
   @Override
   public void onTextChanged(CharSequence s, int arg1, int arg2, int arg3) {
    adapter.getFilter().filter(s);
    
   }
   
   @Override
   public void beforeTextChanged(CharSequence arg0, int arg1, int arg2,
     int arg3) {
    
 
    
   }
   
   @Override
   public void afterTextChanged(Editable editable) {
    
    
    
   }
  });

STEP 6

Last , but not the least , set a on Item Click Listener which will be executed when a item in the list is clicked :

l1=(ListView) findViewById(R.id.list);
  l1.setAdapter(adapter);
  l1.setOnItemClickListener(new OnItemClickListener() {
   
   @Override
   public void onItemClick(AdapterView arg0, View arg1, int arg2,
     long arg3) {
    Toast.makeText(SqlliteDatabaseSearchActivity.this, arg3+" ", 50).show();
   
   
   }
  });

And , the final product :)

You can find the whole project HERE .
Please note : If the link is broken , please mail me @ tapan.d.thaker@gmail.com . And i will put it back soon .