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)?

Answer 1

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.