Searching a SQLite database using SearchView

Question

I have an actionbar SearchView that I'm trying to get working to let users search a database of wines.

I'm trying to get it to search while the user types, like if they type "na" it'll show all results that contain "na" and so on.

Here is my menu search button if statement:

if (id == R.id.action_wine_search) {
        SearchView searchView = getActivity().findViewById(R.id.action_wine_search);
        searchView.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
            @Override
            public boolean onQueryTextSubmit(String newText) {
                return false;
            }

            @Override
            public boolean onQueryTextChange(String newText) {
                mCursorAdapter.getFilter().filter(newText);
                //mCursorAdapter is selecting id,name,price,etc. columns from the table
                wineListView.setAdapter(mCursorAdapter);

                return false;
            }
        });
    }

As I understood it (which I might just be thinking about this in the wrong way) is that the filter is getting the text from the SearchView and is applying that text to the CursorAdapter.

This isn't doing anything though. The list doesn't change at all no matter what you type in.

I followed along to a youtube video for this. I don't quite understand how it was applying to the CursorAdapter though.

I ended up attempting it on my own (this also fails, the list doesn't change at all):

public boolean onQueryTextChange(String newText) {

                SQLiteDatabase db = mDatabaseHelper.getReadableDatabase();

                //Selecting what details I want to put into each row in the list
                String[] projection = {
                        WineContract.WineEntry.COLUMN_ID,
                        WineContract.WineEntry.COLUMN_WINE_NAME,
                        WineContract.WineEntry.COLUMN_WINE_PRICE,
                        WineContract.WineEntry.COLUMN_WINE_RATING,
                        WineContract.WineEntry.COLUMN_WINE_STORE,
                        WineContract.WineEntry.COLUMN_WINE_IMAGE,
                        WineContract.WineEntry.COLUMN_WINE_THUMBNAIL};

                //I want to return the row where the name is equal to newText (which is what the user types into the SearchView
                String selection = WineContract.WineEntry.COLUMN_WINE_NAME + " = ?";
                String[] selectionArgs = {newText};

                Cursor cursor = db.query(WineContract.WineEntry.TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder);
                mCursorAdapter = new WineCursorAdapter(getActivity(), cursor);
                wineListView.setAdapter(mCursorAdapter);

                return false;
            }
        });

Could I get some help in understanding how this is supposed to work?

Answer 1

I'm not sure if this would suit be all I do for a similar is have an EditText with a textChangedListener that just drives a a query, gets an updated cursor and uses swapCursor on the ListView adapter. The query itself uses Like to get all occurences e.g. that have na.

This the Listener setup Code :-

/**************************************************************************
 * addFilterListener - Add a Listener to filter the ListView
 *                      i.e. as characters are typed then the ListView
 *                      is filtered according to the characters input
 *                      This is done by using LIKE '%<characters>%'
 *
 */
public void addFilterListener() {

    inputproductfilter.addTextChangedListener(new TextWatcher() {
        @Override
        public void beforeTextChanged(CharSequence charSequence, int i, int i1, int i2) {

        }

        @Override
        public void onTextChanged(CharSequence charSequence, int i, int i1, int i2) {
            productfilter = DBProductsTableConstants.PRODUCTS_NAME_COL_FULL +
                    SQLLIKECHARSTART +
                    inputproductfilter.getText().toString() +
                    SQLLIKECHAREND;
            plcsr = dbproductmethods.getExpandedProducts(productfilter,orderby);
            productlistadapter.swapCursor(plcsr);
        }

        @Override
        public void afterTextChanged(Editable editable) {

        }
    });
}

It uses :-

public static final String SQLLIKECHARSTART = " LIKE '%";
public static final String SQLLIKECHAREND = "%' ";

and for below

public static final String SQLAS = " AS ";

Not that it's all that relevant here's getExpandedproducts :-

 /**************************************************************************
 *
 * @param filter    filter string less WHERE keyword
 * @param order     order String less ORDER and BY kewords
 * @return          Cursor containing Expanded products
 */
Cursor getExpandedProducts(String filter, String order) {
    Cursor rv;
    String[] columns = new String[] {
            DBProductsTableConstants.PRODUCTS_ID_COL_FULL,
            DBProductsTableConstants.PRODUCTS_NAME_COL_FULL,
            DBProductsTableConstants.PRODUCTS_NOTES_COL_FULL,
            DBProductsTableConstants.PRODUCTS_STORAGEORDER_COL_FULL,
            DBProductsTableConstants.PRODUCTS_STORAGEREF_COL_FULL,
            DBStorageTableConstants.STORAGE_ID_COL_FULL +
                    SQLAS + DBStorageTableConstants.STORAGE_ALTID_COL,
            DBStorageTableConstants.STORAGE_NAME_COL_FULL,
            DBStorageTableConstants.STORAGE_ORDER_COL_FULL
    };
    String table = DBProductsTableConstants.PRODUCTS_TABLE +
            SQLLEFTJOIN +
            DBStorageTableConstants.STORAGE_TABLE +
            SQLON +
            DBProductsTableConstants.PRODUCTS_STORAGEREF_COL_FULL +
            " = " +
            DBStorageTableConstants.STORAGE_ID_COL_FULL;
    rv = db.query(table,columns,filter,null,null,null,order);
    return rv;
}

e.g. full list :-

and then with na entered in the filter :-

Answer 2

I looked at the video you linked and unfortunately, it looks like the video demonstrates how to achieve this search functionality in a pretty different approach. Namely, they use an ArrayAdapter<String> that has some built-in implementation of a Filter that allows them perform substring matching on a simple list of results.

Your case of using a CursorAdapter to search against a database unfortunately won't work in this manner. A CursorAdapter returns a Cursor object which is really just an "facade" for retrieving the row data/results returned by a database query. In other words, the CursorAdapter doesn't know how to "filter" anything, you have to specify what to do when a filter is applied. For instance, you would need to implement something like this:

adapter.setFilterQueryProvider(new FilterQueryProvider() {
    @Override
    public Cursor runQuery(CharSequence query) {
        // Run some database query, given this "query" String and return a Cursor
        return searchDatabase(query);
    }
});

You sort of had the right idea your second snippet of code to implement a search query. However, a standard SQLite database table is only going to let you retrieve rows that match an exact criteria (basically what you've already coded above) and not a substring match like what you probably want.

Fortunately, there is a way to get full-text search in an SQLite database so you can search and match queries to substrings and partial queries, probably what you actually want. You can use something called an SQLite virtual table that implements a scheme like FTS3 to support searching. There is a great tutorial on the official Android docs linked below that will show you exactly how to achieve this:

https://developer.android.com/training/search/search.html

I've used this method in the past several times to implement searching across a database. It's probably a bit more work than you expected to change out your current database helper but this is the way I know how to do it. Let me know if anything doesn't make sense!

Also I do you hope take into account my comments above about restructuring your code because I'm sure you want to write an app that considers good performance!