Change SQL query based on button action

Question

I have a listview that displays all the items in my database. I am trying to create a button that will change the data displayed to only show items that match today's date.

What is the best way to change the query that is being run in the app based on a button push and update the listview?

I've played with setting a flag in the onclick() method paired with if-else statements that held the query call, but it did not seem to switch which was being called.

The flag is the boolean filterToday. set in the onClickListener of todayButton.

public class MainActivity extends AppCompatActivity {


public final static String KEY_EXTRA_CONTACT_ID = "KEY_EXTRA_CONTACT_ID";
private ListView listView;
DBHelper dbHelper;
boolean filterToday;
Cursor cursor;
String [] columns;
int [] widgets;


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

    Button addButton = (Button) findViewById(R.id.addNew);
    addButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            Intent intent = new Intent(MainActivity.this, AddTaskActivity.class);
            intent.putExtra(KEY_EXTRA_CONTACT_ID, 0);
            startActivity(intent);
        }
    });

    Button todayButton = (Button) findViewById(R.id.today);
    todayButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            filterToday = true;
        }
    });

    dbHelper = new DBHelper(this);

    if(filterToday == true){
        Calendar cal = Calendar.getInstance();
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String date = df.format(cal.getTime());
        Toast.makeText(this,date,Toast.LENGTH_LONG).show();
        cursor = dbHelper.getTodaysTasks(date);
    }

    else{
        cursor = dbHelper.getAllTasks();
    }

    columns = new String[] {
            DBHelper.TASK_COLUMN_NAME,
            DBHelper.TASK_COLUMN_TYPE,
            DBHelper.TASK_COLUMN_DATE

    };
    widgets = new int[] {
            R.id.taskName,
            R.id.taskType,
            R.id.taskDate
    };


    SimpleCursorAdapter cursorAdapter = new SimpleCursorAdapter(this, R.layout.task_info,
            cursor, columns, widgets, 0);
    listView = (ListView)findViewById(R.id.listView1);
    listView.setAdapter(cursorAdapter);

    listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

        @Override
        public void onItemClick(AdapterView<?> listView, View view,
                                int position, long id) {
            Cursor itemCursor = (Cursor) MainActivity.this.listView.getItemAtPosition(position);
            int taskID = itemCursor.getInt(itemCursor.getColumnIndex(DBHelper.TASK_COLUMN_ID));
            Intent intent = new Intent(getApplicationContext(), AddTaskActivity.class);
            intent.putExtra(KEY_EXTRA_CONTACT_ID, taskID);
            startActivity(intent);
        }
    });

}
}

These are the two queries I am trying to switch between:

public Cursor getAllTasks() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery( "SELECT * FROM " + TASK_TABLE_NAME, null );
    return res;
}

public Cursor getTodaysTasks(String date){
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res = db.rawQuery("SELECT * FROM " + TASK_TABLE_NAME + " WHERE " +
            TASK_COLUMN_DATE + " =?", new String[]{date});
    return res;
}

I added the code. I'm not sure why the flag never gets changed on button click, and is the flag even the best way to do this? — Jexoteric, Oct 23 '16 at 02:25

score 0 · Answer 1 · answered Oct 23 '16 at 02:50

OK so the first thing to notice is that your update filterToday = true; inside the onclick listener, the if(filterToday == true){...} outside doesn't know that. That part is only executed once in the oncreate()

If you'd like to perform that action I suggest a small change.. like below

Create a seperate function outside for the data loading

private void LoadMyData() {

if(filterToday == true){
    Calendar cal = Calendar.getInstance();
    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    String date = df.format(cal.getTime());
    //you might need to update this line
   //Toast.makeText(this,date,Toast.LENGTH_LONG).show();

   //assume dbHelper is ready at this point
    cursor = dbHelper.getTodaysTasks(date);
}

else{
    cursor = dbHelper.getAllTasks();
}

}

Call this function inside onclick listner

 filterToday = false;//initialize to false if necessary
 todayButton.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View view) {
        filterToday = !filterToday ;//by doing it like this you can switch back and forth. add/check extra logic if necessary
        LoadMyData();
    }
});
LoadMyData(); //Call this outside in the oncreate to load the data initially

I'm not suggesting a code improvement here. Just changes to what you already have. Hope it helps.

At the top of the program I created a boolean for the flag, so to my understanding it would be visible to both, correct? I did not define the boolean inside the onCreate() scope and so it should be using the object from the next scope up to my understanding. — Jexoteric, Oct 23 '16 at 16:58
No problem there. When u click the button it would ONLY update the flag doesn't do anything to the list. that's the change suggested, update the flag and also call the query again with the changed flag. — Searching, Oct 23 '16 at 19:29

score 0 · Accepted Answer · edited May 23 '17 at 10:27

There were two issues that I ran into, the first was comparing a string to to the SQL string[], which meant while the comparison was working, it did not behave as I expected. Once fixing this issue was updating the ListView object. This was handled by creating a new adapter and attaching it to ListView, which then updates itself.

Per this answer: Refresh Current Fragment (ListView Data) remaining in the same activity

The other option was to call notifyDataSetChanged() on the adapter, but this did not work well with the way I had my program set up.

Change SQL query based on button action

2 Answers2