How do I improve Searching in an AutoComplete text widget

Question

I have two AutoCompleteTextViews that are populated with data from sqlite database. I pull information from an ERP Server and store it locally in the sqlite database and populate the textview like in this:

equipment = (AutoCompleteTextView) findViewById(R.id.equipmentAutoCompleteTextView);
    if(equipment.getText().toString().length() == 0){
        equipment.setError("Equipment is required");
    }
    FieldInstallationDB sqlitedb1 = new FieldInstallationDB(this);
    sqlitedb1.openForRead();

    String[] items = sqlitedb.getAllItemNames();

    for(int i = 0; i < items.length; i++)
    {
        Log.i(this.toString(), items[i]);
    }

    ArrayAdapter<String> adapter1 = new ArrayAdapter<>(this, android.R.layout.simple_dropdown_item_1line, items);
    equipment.setAdapter(adapter1);
    equipment.setThreshold(1);

And here:

customerName = (AutoCompleteTextView) findViewById(R.id.customerNameAutoCompleteTextView);
    if(customerName.getText().toString().length() == 0){
        customerName.setError("Customer Name is required");
    }
    FieldInstallationDB sqlitedb = new FieldInstallationDB(this);
    sqlitedb.openForRead();

    String[] accounts = sqlitedb.getAllCustNames();

    for(int i = 0; i < accounts.length; i++)
    {
        Log.i(this.toString(), accounts[i]);
    }

    ArrayAdapter<String> adapter = new ArrayAdapter<>(this, android.R.layout.simple_dropdown_item_1line, accounts);
    customerName.setAdapter(adapter);
    customerName.setThreshold(1);

Assuming I have these:

Black Spoon
Red Spoon
Spoonful

And I start typying "spo", I want to see all three items listed to make a selection. Instead, I get only "Spoonful" coming up. How do I get it to recognise and display all instances(items) that have the letters "spo"?

UPDATE:

After the first comment to my question, I read more articles and updated my code to look like this:

equipment = (AutoCompleteTextView) findViewById(R.id.equipmentAutoCompleteTextView);
    if(equipment.getText().toString().length() == 0){
        equipment.setError("Equipment is required");
    }
    FieldInstallationDB sqlitedb1 = new FieldInstallationDB(this);
    sqlitedb1.openForRead();

    String[] items = sqlitedb1.getAllItemNames();

    for(int i = 0; i < items.length; i++)
    {
        Log.i(this.toString(), items[i]);
    }

    equipment.setThreshold(1);


    SimpleCursorAdapter itemNameAdapter = new SimpleCursorAdapter(
            this, android.R.layout.simple_dropdown_item_1line, null, items, toView, 0);



    itemNameAdapter.setFilterQueryProvider(new FilterQueryProvider() {

        public Cursor runQuery(CharSequence constraint) {
            return suggestItemCompletions(constraint);
        }
    });

itemNameAdapter.setCursorToStringConverter(new SimpleCursorAdapter.CursorToStringConverter() {
        public CharSequence convertToString(Cursor cur) {
            int index = cur.getColumnIndex(FieldInstallationDB.ITEM_NAME);
            return cur.getString(index);
        }});
    equipment.setAdapter(itemNameAdapter);

Here is the suggestitemcompletions method written in the activity class:

public Cursor suggestItemCompletions(CharSequence str) {

    return getContentResolver().query(null, new String[] {FieldInstallationDB.ITEM_NAME}, "(" + FieldInstallationDB.ITEM_NAME + " LIKE ? ", new String[] {"%" + str + "%"}, null);

}

This suggests to use getContentResolver() which does not make sense in my case since I am accessing the database of my application and not of another application. As a result it does not work because I set the URI parameter to null since my database table has no URI. However, if i decide not to use getContentResolver() and query the FieldInstallationDB directly, I get the error at .query() saying cannot resolve method.

UPDATE II:

I used rawQuery() as suggested and it works:

public Cursor suggestItemCompletions(CharSequence str) {
    fieldInstDatabase = openForRead();
    String sql = "Select "+ ITEM_NAME+ " FROM "+ TABLE_EQUIPMENT+ " WHERE "+ ITEM_NAME + " LIKE ?";
    String[]  selectArgs = new String[]{ "%" + str + "%"};

    return fieldInstDatabase.rawQuery(sql,selectArgs);

}

This method is in my db class and I am calling it in main activity here:

itemNameAdapter.setFilterQueryProvider(new FilterQueryProvider() {

        public Cursor runQuery(CharSequence constraint) {
            return sqlitedb1.suggestItemCompletions(constraint);
        }
    });

This is working except that I am getting an IllegalArgumentException here:

01-10 13:07:08.792 5361-5361/com.example.sweetiean.stlfieldinstallation1 E/AndroidRuntime: FATAL EXCEPTION: main Process: com.example.sweetiean.stlfieldinstallation1, PID: 5361 java.lang.IllegalArgumentException: column 'Bag(TypeC) : 5900016009' does not exist

which makes sense because "Bag(TypeC) : 5900016009" is not a column, it is the first item in the ITEM NAME column in the database.

I do not know why it is reading the first item as the column name. I have tried to debug but I cannot step into

public Cursor runQuery(CharSequence constraint) {
        return sqlitedb1.suggestItemCompletions(constraint);
    }

use `SimpleCursorAdapter` and setup its `FilterQueryProvider` like [this](http://stackoverflow.com/a/19860624/2252830), do not use `ArrayAdapter`, of course you need to return your database `Cursor` from `runQuery` method — pskink, Jan 06 '17 at 10:50
@pskink this put me in the right direction however, I have ran into a few more problems to which I will update my question — Sweetie Anang, Jan 06 '17 at 16:59
see the `from` and `to` parameters of `SimpleCursorAdapter` constructor — pskink, Jan 10 '17 at 13:16
I debugged again and `results.count = cursor.getCount();` in CursorFilter.java returns the right count. What exactly should I look out for in those parameters @pskink — Sweetie Anang, Jan 10 '17 at 13:21
@pskink see them where? I don't see your update to the question. — Sweetie Anang, Jan 10 '17 at 13:45
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132810/discussion-between-sweetie-anang-and-pskink). — Sweetie Anang, Jan 10 '17 at 14:28

score 0 · Accepted Answer · answered Jan 11 '17 at 13:12

As suggested in the comments, an IllegalArgumentException is occurring here because of the from parameter in my SimpleCursorAdapter. The from and to parameters of the SimpleCursorAdapter require the column name of the table and the textview to populate respectively.

In my case, I was populating the from parameter with a string array of the items I was getting from the database:

String[] items = sqlitedb1.getAllItemNames();

for(int i = 0; i < items.length; i++)
{
    Log.i(this.toString(), items[i]);
}

equipment.setThreshold(1);


SimpleCursorAdapter itemNameAdapter = new SimpleCursorAdapter(
        this, android.R.layout.simple_dropdown_item_1line, null, items, toView, 0);

Correcting it to:

SimpleCursorAdapter itemNameAdapter = new SimpleCursorAdapter(
        this, android.R.layout.simple_dropdown_item_1line, null, fromCol, toView, 0);

With fromCol defined as final static String[] fromCol = new String[] { FieldInstallationDB.ITEM_NAME }; fixed it.

How do I improve Searching in an AutoComplete text widget

1 Answers1