Sqlite android stopped processing update, delete

I have an activity with a listview and one button, I use sqlite as a database

private SQLiteDatabase db;
db = getBaseContext().openOrCreateDatabase("app.db", MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS tasx (id INTEGER primary key autoincrement, name TEXT, complOn INTEGER, steps INTEGER)");

button adds items to list

public void onClick(DialogInterface dialog,int id) {
String a = ("'");
Log.d("message",inputName.getText().toString() + inpuSteps.getText());
db.execSQL("INSERT OR IGNORE INTO tasx VALUES (NULL, " + a + inputName.getText().toString() +a +", "+a +inpuSteps.getText() +a+", "+a+ inputCompleted.getText()+a+");");
updateDB();
}

as well as the UpdateDB method, which simply updates the displayed list

private void updateDB(){
        ArrayList<DataModel> list = new ArrayList<>();
        Cursor query = db.rawQuery("SELECT * FROM tasx;", null);
        CustomAdapter adapter = new CustomAdapter(list, getApplicationContext());
        while(query.moveToNext()){
            String name = query.getString(1);
            String age = String.valueOf(query.getInt(2));
            String stp = String.valueOf(query.getInt(3));
            list.add(new DataModel(name, age + "/"+stp));
        }
        lv.setAdapter(adapter);
//        query.close();
    }

and the last thing I have problems with is clicking on a list item

lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                final ImageView photo = (ImageView) view.findViewById(R.id.plus);
                onCompliteTask((int) id);
                photo.setOnClickListener(new View.OnClickListener() {
                    @Override
                    public void onClick(View v) {

                    }
                });
        }});

private void onCompliteTask(Integer i){
        Runnable runnable = () -> {
            db.execSQL("UPDATE tasx SET complOn = complOn + 1 WHERE id = "+ i);
            Log.d("click","successuful" + i);
        };
        Runnable runnable1 = () -> {
            db.execSQL("DELETE FROM tasx WHERE id = "+i+" AND complOn >= steps");
            Log.d("click","successuful" + i);

        };
        Thread thread = new Thread(runnable);
        Thread thread1 = new Thread(runnable1);
        thread.start();
        thread1.start();
//        updateDB();
    }

Moreover, when I was simply checking the capabilities and performance, it worked, but for some reason unknown to me it stopped. I thought that sqlite does not support multiple queries at the same time, and first moved it to a separate class, then to separate threads, but this did not help. Logs are displayed, requests are not executed. I will be glad for any help or hint, this is my first time working with sqlite

Solution

The adapter is not a Cursor Adapter (it cannot be as a CursorAdapter requires a column name _id and also a Cursor passed to it not an Array/List).

As such, the long id WILL VERY LIKELY NOT be the id of the row, it will be the position of the displayed row in the List/Array with 0 as the first Item, 1 as the next and so on. Rarely will the position equate to the id of the underlying row.

perhaps use a breakpoint and inspect the values when running in debug mode.
and saying it worked, but for some reason unknown to me it stopped could well be because at some time there was a match between the position and the id of the row (or a row that appeared to result in an expected result e.g. delete deleted a row but perhaps not the expected row, then subsequent INSERTS were all way off due to using autoincrement follow the link and consider the very first statement and perhaps read on).

So you need to either get the id from the object at the position (either position or id, they effectively have the same value but id is a long) or use a CursorAdapter (requiring something like SELECT *,id AS _id .... or perhaps SELECT rowid AS _id,* ....) so that the int (should really be long) is the correct value passed to the onCompliteTask method.

Demo (for convenience using a CursorAdapter i.e SimpleCursorAdapters, also uses the main thread)

Note two tables and thus two ListViews. The difference being the tasx table uses AUTOINCREMENT, tasz does not.

The core code for the demo is within the Activity and is:-

public class MainActivity extends AppCompatActivity {
    SQLiteDatabase db;
    SimpleCursorAdapter sca,scaz;
    Cursor csr,csrz;
    ListView lv_c, lv_cz;
    Button insertButton;


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

        lv_c = this.findViewById(R.id.lv_c);
        lv_cz = this.findViewById(R.id.lv_cz);
        insertButton = this.findViewById(R.id.insert);


        db = getBaseContext().openOrCreateDatabase("app.db", MODE_PRIVATE, null);
        db.execSQL("CREATE TABLE IF NOT EXISTS tasx (id INTEGER primary key autoincrement, name TEXT, complOn INTEGER, steps INTEGER)");
        db.execSQL("CREATE TABLE IF NOT EXISTS tasz (id INTEGER primary key, name TEXT, complOn INTEGER, steps INTEGER)");

        insertRows();
        insertButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                insertRows();
            }
        });
    }

    void insertRows() {
        ContentValues cv = new ContentValues();
        cv.put("name","NAME_" + String.valueOf(System.currentTimeMillis()));
        cv.put("complOn",100);
        cv.put("steps",300);
        db.insert("tasx",null,cv);
        db.insert("tasz",null,cv);
        setOrRefreshAdapters();
    }

    void setOrRefreshAdapters() {

        csr = db.rawQuery("SELECT id AS _id,* FROM tasx",null);
        int _id_ix = csr.getColumnIndex(BaseColumns._ID);
        int id_ix = csr.getColumnIndex("id");
        int name_ix = csr.getColumnIndex("name");
        int complOn_ix = csr.getColumnIndex("complOn");
        int steps_ix = csr.getColumnIndex("steps");

        csrz = db.rawQuery("SELECT id AS " + BaseColumns._ID + ",* FROM tasz",null);

        if (sca == null) {
            sca = new SimpleCursorAdapter(this,
                    R.layout.dm_listview_row,csr,
                    new String[]{"id","name","complOn","steps"},
                    new int[]{R.id.text1,R.id.text2,R.id.text3,R.id.text4},0
            );
            lv_c.setAdapter(sca);
            lv_c.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                @Override
                public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                    Log.d("LV_SCA","Clicked on pos=" +  String.valueOf(i) + "  l=" + l);
                }
            });
            lv_c.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
                @Override
                public boolean onItemLongClick(AdapterView<?> adapterView, View view, int i, long l) {
                    db.execSQL("DELETE FROM tasx WHERE id=?",new String[]{String.valueOf(l)});
                    setOrRefreshAdapters(); /* Refresh the list after the deletion */
                    return true;
                }
            });
        } else {
            sca.swapCursor(csr);
        }
        if (scaz == null) {
            scaz = new SimpleCursorAdapter(this,
                    R.layout.dm_listview_row,csrz,
                    new String[]{"id","name","complOn","steps"},
                    new int[]{R.id.text1,R.id.text2,R.id.text3,R.id.text4},0
            );
            lv_cz.setAdapter(scaz);
            lv_cz.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                @Override
                public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                    Log.d("LV_SCAZ","Clicked on pos=" +  String.valueOf(i) + "  l=" + String.valueOf(l));
                }
            });
            lv_cz.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
                @Override
                public boolean onItemLongClick(AdapterView<?> adapterView, View view, int i, long l) {
                    db.execSQL("DELETE FROM tasz WHERE id=?", new String[]{String.valueOf(l)});
                    setOrRefreshAdapters(); /* Refresh the list after the deletion */
                    return true;
                }
            });
        } else {
            scaz.swapCursor(csrz);
        }
    }
}

the dm_listview_row layout is a simple 4 textview with id's text1, text2, text3 and text4.

When a ListView item is:-

clicked it logs position and l values (which respect the difference between the position and the rows _id).
long clicked it deletes the row using the l value

When run (again note after a few runs so a few rows exist and some don't):-

note that the upper list (AUTINCREMENT) that rows 3 and 6 don't exist but that in the lower list that rows 3 and 9 don't exist/ But if you look at the timestamp (applied when inserted) they all match. - This is due to how AUTOINCREMENT works
- i.e. it will insert with an id that is 1 greater than any id ever used, even if the highest id row has been deleted
- without AUTOINCREMENT the id will be 1 greater than the highest id that exists.
this affects the differnece between the position and the id

Now if each row is clicked in turn (upper then the lower), the log includes:-

2024-02-03 18:14:45.425 D/LV_SCA: Clicked on pos=0  l=1
2024-02-03 18:14:47.159 D/LV_SCAZ: Clicked on pos=0  l=1

2024-02-03 18:14:49.249 D/LV_SCA: Clicked on pos=1  l=2
2024-02-03 18:14:50.895 D/LV_SCAZ: Clicked on pos=1  l=2

2024-02-03 18:14:52.591 D/LV_SCA: Clicked on pos=2  l=4
2024-02-03 18:14:54.258 D/LV_SCAZ: Clicked on pos=2  l=4

2024-02-03 18:14:56.601 D/LV_SCA: Clicked on pos=3  l=5
2024-02-03 18:14:59.100 D/LV_SCAZ: Clicked on pos=3  l=5

2024-02-03 18:15:03.112 D/LV_SCA: Clicked on pos=4  l=7
2024-02-03 18:15:04.747 D/LV_SCAZ: Clicked on pos=4  l=6

2024-02-03 18:15:06.970 D/LV_SCA: Clicked on pos=5  l=8
2024-02-03 18:15:08.393 D/LV_SCAZ: Clicked on pos=5  l=7

2024-02-03 18:15:10.145 D/LV_SCA: Clicked on pos=6  l=9
2024-02-03 18:15:11.637 D/LV_SCAZ: Clicked on pos=6  l=8

the pos value NEVER matches the l (id) value
from the 5th row, the SCA (AUTOINCREMENT) is higher than the equivalent SCAZ due to how AUTOINCREMENT works. So AUTOINCREMENT can eventually result in a greater disparity between pos and l.