Mohamed Gritli Mohamed Gritli - 3 months ago 19
Android Question

SearchView show result from database to ListView

I've managed to have an app that inserts data to a SQLite database and view it in a ListView that has a custom layout and Adapter.

However, when I tried to make a SearchView that filters the results based on the user's input, I got nowhere .

Below is my code, please point me to the solution

DbHelper

public class DbHelper extends SQLiteOpenHelper {

public static final String DB_NAME = "homeworks.db";
public static final int DB_VERSION = 1;

public static final String createTaksTableQuery =
"CREATE TABLE "+ DbInfo.TasksInfo.TABLE_NAME+"("+DbInfo.TasksInfo.taskId+
" INTEGER PRIMARY KEY AUTOINCREMENT, "+DbInfo.TasksInfo.taskName+" NVARCHAR(255), "
+DbInfo.TasksInfo.taskDate+" DATE, "+DbInfo.TasksInfo.taskTime+" TIME, "+ DbInfo.TasksInfo.taskNotes+" NVARCHAR(255));";

public static final String createNotesTableQuery =
"CREATE TABLE "+ DbInfo.NotessInfo.TABLE_NAME+"("+DbInfo.NotessInfo.noteId+
" INTEGER PRIMARY KEY AUTOINCREMENT, "+DbInfo.NotessInfo.noteName+" NVARCHAR(255), "
+DbInfo.NotessInfo.noteDate+" DATE, "+ DbInfo.NotessInfo.noteContents+" NVARCHAR(255));";


public DbHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
Log.e("Database Operation", "Database Created / Opened...");
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(createTaksTableQuery);
Log.e("Database Operation", "Tasks Table Created ...");
db.execSQL(createNotesTableQuery);
Log.e("Database Operation", "Notes Table Created ...");

}

public void insertToTasks(String tName, String tDate, String tTime, String taskNote, SQLiteDatabase db) {

ContentValues contentValues = new ContentValues();

contentValues.put(DbInfo.TasksInfo.taskName, tName);
contentValues.put(DbInfo.TasksInfo.taskDate, tDate);
contentValues.put(DbInfo.TasksInfo.taskTime, tTime);
contentValues.put(DbInfo.TasksInfo.taskNotes, taskNote);

db.insert(DbInfo.TasksInfo.TABLE_NAME, null, contentValues);

Log.e("Insertion OP", "Row inserted into Tasks Databases");
}

public void insertToNotes(String nName, String nDate, SQLiteDatabase db) {

ContentValues contentValues = new ContentValues();

contentValues.put(DbInfo.NotessInfo.noteName, nName);
contentValues.put(DbInfo.NotessInfo.noteDate, nDate);

db.insert(DbInfo.NotessInfo.TABLE_NAME, null, contentValues);

Log.e("Insertion OP", "Row inserted into Notes Databases");
}


public Cursor getTasks(SQLiteDatabase db) {
Cursor cursor;
String[] projections = {
DbInfo.TasksInfo.taskId, DbInfo.TasksInfo.taskName, DbInfo.TasksInfo.taskDate, DbInfo.TasksInfo.taskTime, DbInfo.TasksInfo.taskNotes};


cursor = db.query(DbInfo.TasksInfo.TABLE_NAME, projections, null, null, null, null, DbInfo.TasksInfo.taskId + " desc");

return cursor;
}

public Cursor searchTasks(SQLiteDatabase db, String searchTxt) {
Cursor cursor;
String q = "select * from tasksTable where taskName Like '"+searchTxt+"%'";
cursor = db.rawQuery(q, null);
Log.e("Database Op", q);
return cursor;

}


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}
}


Main Class


public class TasksFragment extends Fragment {

ListView tasksListView;
SQLiteDatabase sqLiteDatabase;
Cursor cursor;
DbHelper dbHelper;

TasksListAdapter adapter;
SearchView searchView;
String searchQuery;
@Nullable
@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {

View view = inflater.inflate(R.layout.tasks_layout, container, false);
searchView = (SearchView) view.findViewById(R.id.searchView);
tasksListView = (ListView) view.findViewById(R.id.tasksListView);
adapter = new TasksListAdapter(getContext(), R.layout.tasks_row);

dbHelper = new DbHelper(view.getContext());
sqLiteDatabase = dbHelper.getReadableDatabase();


searchView.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
@Override
public boolean onQueryTextSubmit(String query) {


return true;
}

@Override
public boolean onQueryTextChange(String newText) {

tasksListView.setVisibility(View.GONE);
return true;
}
});



tasksListView.setAdapter(adapter);
cursor = dbHelper.getTasks(sqLiteDatabase);


if (cursor.moveToFirst()) {
do {
String name, date, time, note;
name = cursor.getString(1);
date = cursor.getString(2);
time = cursor.getString(3);
note = cursor.getString(4);

DataProvider dataProvider = new DataProvider(name, date, time, note);

adapter.add(dataProvider);

} while (cursor.moveToNext());
}
adapter.notifyDataSetChanged();



FloatingActionButton fab = (FloatingActionButton) view.findViewById(R.id.fab);
fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Intent intent = new Intent(getContext(), AddTask.class);
startActivity(intent);
}
});

tasksListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {


}
});
adapter.notifyDataSetChanged();


return view;
}

}


Answer

i have Managed to solve the Problem... i had to create onQueryTextChangedListner on the SearchView , create a new adapter and a new cursor , got the data from database then put in the adapter and set a new adapter to ListView

below is the new final code that has , insert , delete, search"select" and Delete , plus the ListView, searchView methods

DbHelper Class

package ly.edu.cet.www.myhomworks;

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;

/**
 * Created by newton on 11/19/15.
 */
public class DbHelper extends SQLiteOpenHelper {

    public static final String DB_NAME = "homeworks.db";
    public static final int DB_VERSION = 1;

    public static final String createTaksTableQuery =
            "CREATE TABLE "+ DbInfo.TasksInfo.TABLE_NAME+"("+DbInfo.TasksInfo.taskId+
             " INTEGER PRIMARY KEY AUTOINCREMENT, "+DbInfo.TasksInfo.taskName+" NVARCHAR(255), "
                    +DbInfo.TasksInfo.taskDate+" DATE, "+DbInfo.TasksInfo.taskTime+" TIME, "+ DbInfo.TasksInfo.taskNotes+" NVARCHAR(255));";

    public static final String createNotesTableQuery =
            "CREATE TABLE "+ DbInfo.NotessInfo.TABLE_NAME+"("+DbInfo.NotessInfo.noteId+
                    " INTEGER PRIMARY KEY AUTOINCREMENT, "+DbInfo.NotessInfo.noteName+" NVARCHAR(255), "
                    +DbInfo.NotessInfo.noteDate+" DATE, "+ DbInfo.NotessInfo.noteContents+" NVARCHAR(255));";


    public DbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        Log.e("Database Operation", "Database Created / Opened...");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createTaksTableQuery);
        Log.e("Database Operation", "Tasks Table Created ...");
        db.execSQL(createNotesTableQuery);
        Log.e("Database Operation", "Notes Table Created ...");

    }

    public void insertToTasks(String tName, String tDate, String tTime, String taskNote, SQLiteDatabase db) {

        ContentValues contentValues = new ContentValues();

        contentValues.put(DbInfo.TasksInfo.taskName, tName);
        contentValues.put(DbInfo.TasksInfo.taskDate, tDate);
        contentValues.put(DbInfo.TasksInfo.taskTime, tTime);
        contentValues.put(DbInfo.TasksInfo.taskNotes, taskNote);

        db.insert(DbInfo.TasksInfo.TABLE_NAME, null, contentValues);

        Log.e("Insertion OP", "Row inserted into Tasks Databases");
    }

    public void insertToNotes(String nName, String nDate, SQLiteDatabase db) {

        ContentValues contentValues = new ContentValues();

        contentValues.put(DbInfo.NotessInfo.noteName, nName);
        contentValues.put(DbInfo.NotessInfo.noteDate, nDate);

        db.insert(DbInfo.NotessInfo.TABLE_NAME, null, contentValues);

        Log.e("Insertion OP", "Row inserted into Notes Databases");
    }


    public Cursor getTasks(SQLiteDatabase db) {
        Cursor cursor;
        String[] projections = {
            DbInfo.TasksInfo.taskId, DbInfo.TasksInfo.taskName, DbInfo.TasksInfo.taskDate, DbInfo.TasksInfo.taskTime, DbInfo.TasksInfo.taskNotes};


            cursor = db.query(DbInfo.TasksInfo.TABLE_NAME, projections, null, null, null, null, DbInfo.TasksInfo.taskId + " desc");

        return cursor;
    }

    public Cursor searchTasks(SQLiteDatabase db, String searchTxt) {
        Cursor cursor;

        String[] projections = {
                DbInfo.TasksInfo.taskId, DbInfo.TasksInfo.taskName, DbInfo.TasksInfo.taskDate,
                DbInfo.TasksInfo.taskTime, DbInfo.TasksInfo.taskNotes};

        cursor = db.query(DbInfo.TasksInfo.TABLE_NAME, projections, DbInfo.TasksInfo.taskName+" Like '"+searchTxt+"%'", null, null, null, null);

        return cursor;

    }


    public void updateTasks(SQLiteDatabase db, String id, String tname, String tdate, String ttime, String tnote) {

        ContentValues values = new ContentValues();

        values.put(DbInfo.TasksInfo.taskName, tname);
        values.put(DbInfo.TasksInfo.taskDate, tdate);
        values.put(DbInfo.TasksInfo.taskTime, ttime);
        values.put(DbInfo.TasksInfo.taskNotes, tnote);

        int cursor = db.update(DbInfo.TasksInfo.TABLE_NAME, values, DbInfo.TasksInfo.taskId+" = "+id, null);
    }

    public boolean deleteTasks(SQLiteDatabase db, String id) {

        return  db.delete(DbInfo.TasksInfo.TABLE_NAME, DbInfo.TasksInfo.taskId+" = "+id, null) >0;

    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

And here is my TasksFragment that gets Called on the MainActivity inside a SwipeView Tabs

package ly.edu.cet.www.myhomworks;

import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v4.app.Fragment;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;

import android.support.v4.app.Fragment;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SearchView;
import android.widget.Toast;

/**
 * Created by newton on 11/19/15.
 */
public class TasksFragment extends Fragment {

    ListView tasksListView;
    SQLiteDatabase sqLiteDatabase;
    Cursor cursor;
    Cursor scursor;
    DbHelper dbHelper;

    TasksListAdapter adapter;
    SearchView searchView;
    String searchQuery;
    @Nullable
    @Override
    public View onCreateView(LayoutInflater inflater, final ViewGroup container, Bundle savedInstanceState) {

        View view = inflater.inflate(R.layout.tasks_layout, container, false);
        searchView = (SearchView) view.findViewById(R.id.searchView);
        tasksListView = (ListView) view.findViewById(R.id.tasksListView);
        adapter = new TasksListAdapter(getContext(), R.layout.tasks_row);

        dbHelper = new DbHelper(view.getContext());
        sqLiteDatabase = dbHelper.getReadableDatabase();


        tasksListView.setAdapter(adapter);
        cursor = dbHelper.getTasks(sqLiteDatabase);

        if (cursor.moveToFirst()) {
            do {
                String name, date, time, note;
                name = cursor.getString(1);
                date = cursor.getString(2);
                time = cursor.getString(3);
                note = cursor.getString(4);

                DataProvider dataProvider = new DataProvider(name, date, time, note);

                adapter.add(dataProvider);

            } while (cursor.moveToNext());
        }
        adapter.notifyDataSetChanged();

        tasksListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

                if (cursor != null) {
                    if (cursor.moveToFirst()) {
                        cursor.moveToPosition(position);
                        String listId = cursor.getString(cursor.getColumnIndex(DbInfo.TasksInfo.taskId));
                        String listName = cursor.getString(cursor.getColumnIndex(DbInfo.TasksInfo.taskName));
                        String listDate = cursor.getString(cursor.getColumnIndex(DbInfo.TasksInfo.taskDate));
                        String listTime = cursor.getString(cursor.getColumnIndex(DbInfo.TasksInfo.taskTime));
                        String listNote = cursor.getString(cursor.getColumnIndex(DbInfo.TasksInfo.taskNotes));

                        Intent updateTasksIntent = new Intent(getContext(), AddTask.class);

                        updateTasksIntent.putExtra("editTask", true);
                        updateTasksIntent.putExtra("taskId", listId);
                        updateTasksIntent.putExtra("taskName", listName);
                        updateTasksIntent.putExtra("taskDate", listDate);
                        updateTasksIntent.putExtra("taskTime", listTime);
                        updateTasksIntent.putExtra("taskNote", listNote);

//                        Toast.makeText(getContext(), "List row " + position + " ID = " + listId, Toast.LENGTH_SHORT).show();

                        startActivity(updateTasksIntent);
                    }
                }

            }
        });


        tasksListView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {

                if (cursor != null) {
                    if (cursor.moveToFirst()) {
                        cursor.moveToPosition(position);
                        String listId = cursor.getString(cursor.getColumnIndex(DbInfo.TasksInfo.taskId));

                        Log.e("Database Op", "List row " + position + " ID = " + listId);

                        dbHelper.deleteTasks(sqLiteDatabase, listId);

                        TasksListAdapter newAdapter = new TasksListAdapter(getContext(), R.layout.tasks_row);

                        cursor = dbHelper.getTasks(sqLiteDatabase);

                        if (cursor.moveToFirst()) {
                            do {
                                String name, date, time, note;
                                name = cursor.getString(1);
                                date = cursor.getString(2);
                                time = cursor.getString(3);
                                note = cursor.getString(4);

                                DataProvider dataProvider = new DataProvider(name, date, time, note);

                                newAdapter.add(dataProvider);

                            } while (cursor.moveToNext());
                        }
                        newAdapter.notifyDataSetChanged();
                        tasksListView.setAdapter(newAdapter);
//                        Toast.makeText(getContext(), "List row " + position + " ID = " + listId, Toast.LENGTH_SHORT).show();

                    }
                }
                return true;
            }
        });

        searchView.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
            @Override
            public boolean onQueryTextSubmit(String query) {
                return false;
            }

            @Override
            public boolean onQueryTextChange(String newText) {

                TasksListAdapter searchAdapter = new TasksListAdapter(getContext(), R.layout.tasks_row);

                scursor = dbHelper.searchTasks(sqLiteDatabase, newText);

                if (scursor.moveToFirst()) {
                    do {
                        String name, date, time, note;
                        name = scursor.getString(1);
                        date = scursor.getString(2);
                        time = scursor.getString(3);
                        note = scursor.getString(4);

                        DataProvider dataProvider = new DataProvider(name, date, time, note);

                        searchAdapter.add(dataProvider);

                    } while (scursor.moveToNext());
                }
                tasksListView.setAdapter(searchAdapter);
                searchAdapter.notifyDataSetChanged();

                return true;
            }
        });


        FloatingActionButton fab = (FloatingActionButton) view.findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Intent addTaskIntent = new Intent(getContext(), AddTask.class);
                addTaskIntent.putExtra("addNewTask", true);
                startActivity(addTaskIntent);
            }
        });

        return view;
    }

}

Good Luck y'all :) Mohammed Gritli

Comments