SQLite seems not to be executed in Android RecyclerView with onCheckedChanged

Question

I have a list inflated by a RecyclerView, which is extracted by an SQLite database. I use a onCheckedChanged to manipulate the data.

If I click the CheckBox, method setItem() is called.

private void setItem(int position, boolean checked) {        
    // update entries in sqLiteDatabase at click position

    // set the database-cursor to current entry
    cursor.moveToPosition(position);

    // convert boolean to integer, because SQLite does not support boolean values
    int iSelected = checked ? 1 : 0;

    // create SQL-query-string
    String SQLQuery = "" +
            "UPDATE country " +
            "SET selected=" + iSelected +
            " WHERE id='" + cursor.getString(cursor.getColumnIndex("id")) + "'";

    // execute the query
    sqLiteDatabase.execSQL(SQLQuery);

    // create SQL-answer-string
    String SQLAnswer = cursor.getString(cursor.getColumnIndex("name")) +
            "; Id=" + cursor.getString(cursor.getColumnIndex("id")) +
            "; Selected=" + cursor.getString(cursor.getColumnIndex("selected"));

    Toast.makeText(context, SQLQuery + "\n\n" + SQLAnswer, Toast.LENGTH_LONG).show();
}

When I click it once on the CheckBox it shows:

SQLQuery => "UPDATE country SET selected=1 WHERE id=3"

SQLAnswer => "Czechia; id=3; selected=1"

When I click it twice:

SQLQuery => "UPDATE country SET selected=0 WHERE id=3"

SQLAnswer => "Czechia; id=3; selected=1"

...and this alters. So the query switches between 0 and 1 (depending on CheckBox), but the answer never becomes 0.

When I close the app and restart it, the database-entries are done well, but it's not updated during runtime.

Has someone an idea (never had such probs with PHP)?

It could be that the `execSQL` call is not synchronous in which case your entry may not be updated immediately. You can also try using `update` API directly: https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#update(java.lang.String,%20android.content.ContentValues,%20java.lang.String,%20java.lang.String[]) — johnheroy, Jan 01 '19 at 00:43
*When I close the app and restart it, the database-entries are done well* this means that the code you posted is working fine. Your problem is that you don't see the change in the RecyclerView right? Then post the code that calls `setItem()` and what you do after. Do you call `notifyItemChanged()`? — forpas, Jan 01 '19 at 00:45
@MikeT removed the answer. I am posting his answer as a comment here - You need to re-query your database table to get the updated value of the `selected` column. — Reaz Murshed, Jan 01 '19 at 00:55
@Raez: the initial-value was "1", so the "0" was only to demonstrate, that the state is not changing: 1 keeps 1 and 0 keeps 0. — Martin, Jan 01 '19 at 02:39

Martin · Answer 1 · 2019-01-01T02:48:43.713

I found a way:

Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM country  WHERE id='" + cursor.getString(cursor.getColumnIndex("id")) + "'", null);

c.moveToFirst();

System.out.println(c.getString(c.getColumnIndex("selected")));

Thank you for the comments and help.

I think, that "cursor" was generated in MainAktivity to inflate the adapter, so, fetching a row in the adapter from cursor, will retrieve the state, when the adapter was created and cannot be modified later by the adapter.

I cannot even understand this weird construct with the recyclerView or viewHolder. They changes checkboxes, which are cought by onClickListener, only by scrolling, and the programmer has to correct this mistakes. They are really stupid to handle. In my opinion, a list should be inflated, and the os (android) has to optimized it internally.

SQLite seems not to be executed in Android RecyclerView with onCheckedChanged

1 Answers1