Search View On Sqlite Database Not Working

Question

I am working on application, that exists Sqlite Database. What I have done to implement the search on sqlite database using list, I have implemented the like query and I came to know; The searchview is not searching data from sqlite...

Here is my Sqlite File....

    public List<GetSetClientsDetail> SearchClientsData() {

    String[] columns = {
            fname,
            route,
            cnic,
            lname,
            contact
    };

    String sortOrder = fname + " ASC";

    List<GetSetClientsDetail> clientlist = new ArrayList<GetSetClientsDetail>();

    SQLiteDatabase db = this.getReadableDatabase();


    Cursor cursor = db.query(
            table_poducts_records,
            new String[] { fname, route, cnic, lname, contact},
            fname + " LIKE '%" + fname + "%'",
            null, null, null, null, null);


    if (cursor.moveToFirst()) {
        do {
            GetSetClientsDetail clientsDetail = new GetSetClientsDetail();

            clientsDetail.setFNAME(cursor.getString(cursor.getColumnIndex(fname)));
            clientsDetail.setROUTE(cursor.getString(cursor.getColumnIndex(route)));
            clientsDetail.setCNIC(cursor.getString(cursor.getColumnIndex(cnic)));
            clientsDetail.setLNAME(cursor.getString(cursor.getColumnIndex(lname)));
            clientsDetail.setCONTACT(cursor.getString(cursor.getColumnIndex(contact)));
            clientlist.add(clientsDetail);

        } while (cursor.moveToNext());
    }
    cursor.close();
    db.close();

    return clientlist;
}

Here is my viewpage class of search where I have implemented search..

public class Clients extends Fragment {
RecyclerView recyclerViewClients;
Button btnAll;
SearchView searchViewclient;
ClientRecyclerAdapter clientRecyclerAdapter;
List<GetSetClientsDetail> listclients;
DatabaseHelper databaseHelper;

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
    View view =  inflater.inflate(R.layout.clients, container, false);

    btnAll=view.findViewById(R.id.searchallclients);
    recyclerViewClients=view.findViewById(R.id.recyclerviewallclients);
    searchViewclient=view.findViewById(R.id.searchclient);
    listclients = new ArrayList<>();

    clientRecyclerAdapter = new ClientRecyclerAdapter(listclients,recyclerViewClients);
    recyclerViewClients.setItemAnimator(new DefaultItemAnimator());
    recyclerViewClients.setItemAnimator(new DefaultItemAnimator()); //multi copy paste!
    recyclerViewClients.setLayoutManager(new LinearLayoutManager(getContext()));
    recyclerViewClients.setHasFixedSize(true);
    recyclerViewClients.setAdapter(clientRecyclerAdapter);

    databaseHelper = new DatabaseHelper(getActivity());

    searchViewclient.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            SearchSQliteClientData();
        }
    });

    btnAll.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            getClientFromSqlite();
        }
    });

    return view;
}

@SuppressLint("StaticFieldLeak")
private void SearchSQliteClientData() {
    new AsyncTask<Void, Void, Void>() {
        @Override
        protected Void doInBackground(Void... params) {
            listclients.clear();
            listclients.addAll(databaseHelper.SearchClientsData());
            return null;
        }

        @Override
        protected void onPostExecute(Void aVoid) {
            super.onPostExecute(aVoid);
            clientRecyclerAdapter.notifyDataSetChanged();
        }
    }.execute();
}


@SuppressLint("StaticFieldLeak")
private void getClientFromSqlite() {
    new AsyncTask<Void, Void, Void>() {
        @Override
        protected Void doInBackground(Void... params) {
            listclients.clear();
            listclients.addAll(databaseHelper.getAllClientsData());
            return null;
        }

        @Override
        protected void onPostExecute(Void aVoid) {
            super.onPostExecute(aVoid);
            clientRecyclerAdapter.notifyDataSetChanged();
        }
    }.execute();
}

}

What I want to do is As I press A on searchview; It brings all data related to a or If I press a letter It bring that data in the list I have Implemented..

Answer 1

As an example

change public List<GetSetClientsDetail> SearchClientsData()

to

public List<GetSetClientsDetail> SearchClientsData(String mysearchstring)

• this allows the search argument to be passed to the method from the caller

Then change :-

Cursor cursor = db.query(
            table_poducts_records,
            new String[] { fname, route, cnic, lname, contact},
            fname + " LIKE '%" + fname + "%'",
            null, null, null, null, null);

to

String[] whereargs = new String[]{"%" + mysearchstring + "%"}; //<<<<<<<<<< ADDED
Cursor cursor = db.query(
            table_poducts_records,
            new String[] { fname, route, cnic, lname, contact},
            fname + " LIKE ?", //<<<<<<<<<< CHANGED
            whereargs, //<<<<<<<<<< ADDED
            null, null, null); //<<<<<<<<<< 3 nulls instead of 4 (as whereargs has replaced first null)

• this uses the search argument passed to the method in the LIKE clause protecting against SQL Injection

As a test try :-

 listclients.addAll(databaseHelper.SearchClientsData("A")); //<<<<<<<<<< look for all with A anywhere in the fname column

Working example

The following is a working example based upon the code in the question but simplified for convenience.

The core change is two fold as, the above code is in the database helper DatabaseHelper.java as per :-

That is the SearchClientData method is :-

public List<GetSetClientsDetail> SearchClientsData(String mysearchstring) {

    String[] columns = {
            fname, route, cnic, lname, contact
    };

    String sortOrder = fname + " ASC";
    List<GetSetClientsDetail> clientlist = new ArrayList<GetSetClientsDetail>();
    SQLiteDatabase db = this.getReadableDatabase();

    String[] whereargs = new String[]{"%" + mysearchstring + "%"}; //<<<<<<<<<< ADDED
    Cursor cursor = db.query(
            table_poducts_records,
            new String[]{fname, route, cnic, lname, contact},
            fname + " LIKE ?",
            whereargs,
            null, null, sortOrder, null
    );
    if (cursor.moveToFirst()) {
        do {
            GetSetClientsDetail clientsDetail = new GetSetClientsDetail();

            clientsDetail.setFNAME(cursor.getString(cursor.getColumnIndex(fname)));
            clientsDetail.setROUTE(cursor.getString(cursor.getColumnIndex(route)));
            clientsDetail.setCNIC(cursor.getString(cursor.getColumnIndex(cnic)));
            clientsDetail.setLNAME(cursor.getString(cursor.getColumnIndex(lname)));
            clientsDetail.setCONTACT(cursor.getString(cursor.getColumnIndex(contact)));
            clientlist.add(clientsDetail);
        } while (cursor.moveToNext());
    }
    cursor.close();
    return clientlist;
}

public List<GetSetClientsDetail> getAllClientsData() {
    return SearchClientsData("");
} 

• Note the freebie getAllClientsData which just uses the SearchClientData method passing "", which will select all rows.

The other core change is that instead of relying upon the SearchView's OnClickListener which may well get called due to the Views other Listener's stealing the focus-ability.

The SearchView's setOnQueryTextListener has been utilised. This allows the text to be passed to the SearchClientsData.

For convenience this example utilises an ArrayAdapter and the stock Simple_List_Item1 layout and does the work on the main thread and of activity.

The Activity code used was :-

public class MainActivity extends AppCompatActivity {

    ListView listviewClients;
    Button btnAll;
    ArrayAdapter mSCA;
    SearchView searchViewclient;
    List<GetSetClientsDetail> listclients;
    DatabaseHelper databaseHelper;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        btnAll=this.findViewById(R.id.searchallclients);
        listviewClients=this.findViewById(R.id.clients);
        searchViewclient=this.findViewById(R.id.searchclient);
        databaseHelper = new DatabaseHelper(this);
        addSomeData();
        manageListView("");

        searchViewclient.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                manageListView("zzz");
            }
        });
        searchViewclient.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
            @Override
            public boolean onQueryTextSubmit(String query) {
                return false;
            }

            @Override
            public boolean onQueryTextChange(String newText) {
                manageListView(newText);
                return false;
            }
        });

        btnAll.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                manageListView("");
            }
        });
    }

    private void manageListView(String searchArgument) {
        listclients =  databaseHelper.SearchClientsData(searchArgument);
        if (mSCA == null) {
            mSCA = new ArrayAdapter(this,android.R.layout.simple_list_item_1,listclients);
            listviewClients.setAdapter(mSCA);
        } else {
            mSCA.clear();
            mSCA.addAll(listclients);
            mSCA.notifyDataSetChanged();
        }
    }

    private void addSomeData() {
        databaseHelper.add("Albert","001","123456789","Smith","someone");
        databaseHelper.add("Freda","001","123456789","Jones","someone");
        databaseHelper.add("Mike","002","0987654321","Allenby","him");
    }



    /*
    private void SearchSQliteClientData() {
        new AsyncTask<Void, Void, Void>() {
            @Override
            protected Void doInBackground(Void... params) {
                listclients.clear();
                listclients.addAll(databaseHelper.SearchClientsData());
                return null;
            }

            @Override
            protected void onPostExecute(Void aVoid) {
                super.onPostExecute(aVoid);
                clientRecyclerAdapter.notifyDataSetChanged();
            }
        }.execute();
    }


    private void getClientFromSqlite() {
        new AsyncTask<Void, Void, Void>() {
            @Override
            protected Void doInBackground(Void... params) {
                listclients.clear();
                listclients.addAll(databaseHelper.getAllClientsData());
                return null;
            }

            @Override
            protected void onPostExecute(Void aVoid) {
                super.onPostExecute(aVoid);
                clientRecyclerAdapter.notifyDataSetChanged();
            }
        }.execute();
    }

    */
}

• The addSomeData method does as it says adds some testing data. The commented out code is original but unused code.

Result

When first run (not really the 1st, rather after a number of runs BUT freshly started, hence plenty of data) :-

Typing a or A and all the Mike's disappear

and so on, the list is instantly adjusted according to the text entered in the search field. Clicking the ALL button re-displays all.