Search code examples
databasesqliteandroid-studiokotlinandroid-listview

After items selection in my ListView other items get randomly selected while scrolling Kotlin

I'm new to this. After scrolling my ListView, items in "same position" as items I selected before get auto selected. (Same position I mean position in the screen not in the database.) I had this prob before, because I was selecting items by their indexes of ListView in OnItemClickListener. However i face this prob again although I'm doing it right way I think.

When I click on item in ListView, I get its unique ID and based on that I change SELECTED value of this item (of this row in the database) to 0 or 1 (depending on this if it was clicked or not). After that I switch backgrnd color to grey (or back to white). This is handled in CursorAdapter which distinguishes SELECTED property.

Here's my code.

OnCreate in MainActivity.kt

    val dbHelper = DBHelper(this)

    val db = dbHelper.writableDatabase

    val myCursor = db.rawQuery("SELECT * FROM ${ContractClass.FeedReaderContract.TABLE_NAME}", null)
    val myAdapter = CursorAdapter(this, myCursor)
        myListView.adapter = myAdapter

    myListView.setOnItemClickListener { _, view, _, _ ->
            val text = view.txtName.text
            val select = "${ContractClass.FeedReaderContract.COLUMN_NAME_ENWORD} MATCH ?"
            val selectCursor = db.query(
                ContractClass.FeedReaderContract.TABLE_NAME,   // The table to query
                null,             // The array of columns to return (pass null to get all)
                select,              // The columns for the WHERE clause
                arrayOf("$text"),          // The values for the WHERE clause
                null,                   // don't group the rows
                null,                   // don't filter by row groups
                null               // The sort order
            )
            with(selectCursor) {
                while (moveToNext()) {
                    val itemSel = getInt(getColumnIndexOrThrow(ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED))
                    if (itemSel == 1){
                        val values = ContentValues().apply {
                            put(ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED, 0)
                        }
                        val count = db.update(
                            ContractClass.FeedReaderContract.TABLE_NAME, values, select, arrayOf("$text"))

                    }else{
                        val values = ContentValues().apply {
                            put(ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED, 1)
                        }
                        val count = db.update(
                            ContractClass.FeedReaderContract.TABLE_NAME, values, select, arrayOf("$text"))
                    }
                }
            }
        }

CursorAdapter.kt

class CursorAdapter(context: Context, cursor: Cursor) : CursorAdapter(context, cursor, 0) {

    // The newView method is used to inflate a new view and return it,
    // you don't bind any data to the view at this point.
    override fun newView(context: Context, cursor: Cursor, parent: ViewGroup): View {
        if (cursor.getInt(cursor.getColumnIndexOrThrow(ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED)) == 0){
            return LayoutInflater.from(context).inflate(R.layout.row_list_row, parent, false)
        }else{
            return LayoutInflater.from(context).inflate(R.layout.user_list_row_selected, parent, false)
        }
    }

    // The bindView method is used to bind all data to a given view
    // such as setting the text on a TextView.
    override fun bindView(view: View, context: Context, cursor: Cursor) {
        // Find fields to populate in inflated template
        val tvBody = view.findViewById<View>(R.id.txtName) as TextView
        val tvPriority = view.findViewById<View>(R.id.txtComment) as TextView
        val tvPriority2 = view.findViewById<View>(R.id.txtThird) as TextView
        val tvPriority3 = view.findViewById<View>(R.id.txtThi) as TextView
        // Extract properties from cursor
        val body = cursor.getString(cursor.getColumnIndexOrThrow(ContractClass.FeedReaderContract.COLUMN_NAME_ENWORD))
        val priority = cursor.getString(cursor.getColumnIndexOrThrow(ContractClass.FeedReaderContract.COLUMN_NAME_DEFN))
        val priority2 = cursor.getInt(cursor.getColumnIndexOrThrow(ContractClass.FeedReaderContract._id))
        val priority3 = cursor.getInt(cursor.getColumnIndexOrThrow(ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED))
        // Populate fields with extracted properties
        tvBody.text = body
        tvPriority.text = priority.toString()
        tvPriority2.text = priority2.toString()
        tvPriority3.text = priority3.toString()
    }
}

Database Table Create

private val SQL_CREATE_ENTRIES =
        "CREATE VIRTUAL TABLE ${ContractClass.FeedReaderContract.TABLE_NAME} USING fts4(" +
                "${ContractClass.FeedReaderContract._id}," +
                "${ContractClass.FeedReaderContract.COLUMN_NAME_ENWORD} TEXT," +
                "${ContractClass.FeedReaderContract.COLUMN_NAME_DEFN} TEXT," +
                "${ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED} INTEGER)"

I found similar post here: List view with simple cursor adapter items checked are un-checked during scroll but I think, they suggest what I've already done.

Thanks for any help.

Solution

  • You don't appear to be changing the ListView's Cursor after you have made changes to/updated the underlying data.

    Try, after making changes to the underlying data, updating the ListView's Cursor by

    1. re-querying the data and then
    2. using the Adapter's swapCursor(myCursor) (or the notiftDatasetChanged() method)

    Here's an equivalent of your App but in Java rather than Kotlin (not having any luck converting as I hardly ever use Kotlin).

    This I believe does what you want. That is,

    • If you select an unselected row then all rows that contain the enword are selected and greyed out and the selected column value is set to 1.

    • If you select a selected (grey) row then those rows that contain the enword are de-selected and are change to white with the selected column value being changed back to 0

    • Note that rather than create an FTS table I've mimicked the FTS and used LIKE instead of MATCH.

    Are toggled if they are selected the background is grey else white. e.g. initially it is :-

    enter image description here

    If cat is clicked (2nd row) then all other cat rows are also toggled and greyed as per :-

    enter image description here

    and so on.

    The Code

    MainActivity (the file I had issues converting)

    public class MainActivity extends AppCompatActivity {

    DBHelper dbhelper;
    ListView myListView;
    MyCursorAdapter myAdapter;
    Cursor mCursor;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myListView = this.findViewById(R.id.myListView);
        dbhelper = new DBHelper(this);
        addSomeTestingData();
        manageListView();
    }

    private void manageListView() {
        mCursor = dbhelper.getAllRows();
        if (myAdapter == null) {
            myAdapter = new MyCursorAdapter(this,mCursor);
            myListView.setAdapter(myAdapter);
            myListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                @Override
                public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                    dbhelper.updateSelected(mCursor.getString(mCursor.getColumnIndex(ContractClass.FeedReaderContract.COLUMN_NAME_ENWORD)));
                    manageListView();
                }
            });
        } else {
            myAdapter.swapCursor(mCursor);
        }
    }

    private void addSomeTestingData() {

        if (DatabaseUtils.queryNumEntries(dbhelper.getWritableDatabase(),ContractClass.FeedReaderContract.TABLENAME) > 1) return;
        for (int i=0; i < 10; i++) {
            dbhelper.addRow("Apple", "Thing that falls from trees");
            dbhelper.addRow("Cat", "Something that is furry and sits on mats");
            dbhelper.addRow("Bear", "Something that is furry that eats honey but doesn't ssit on a mat");
            dbhelper.addRow("Dog", "Something is furry and friendly");
            dbhelper.addRow("Echida", "An upside down hedgehog");
            dbhelper.addRow("Ferret", "Something that is furry and found up trouser legs");
            dbhelper.addRow("Giraffe", "Something that has 5 legs one pointing up");
            dbhelper.addRow("Hippo", "An animal that loves mud and water but not humans");
            dbhelper.addRow("Ibis", "A white feathered flying thing");
            dbhelper.addRow("Jaguar", "A car or a large black cat");
            dbhelper.addRow("Kangaroo", "A marsupial that boxes, skips and has a puch for shopping trips");
            dbhelper.addRow("Lizard", "A rock dweller");
            dbhelper.addRow("Mammoth", "A big hairy elephant now extinct");
            dbhelper.addRow("Nuthatch", "A small bird that does not customise nuts so they have hatches.");
            dbhelper.addRow("Ostrich", "A l argefast running bird that does not fly");
            dbhelper.addRow("Panther", "A skinny pink cat that walks on only two of it's four lehs");
            dbhelper.addRow("Queen", "A female rule of a country");
            dbhelper.addRow("Rhinocerous", "A Hippo like animal that has a name that is hard to spell");
            dbhelper.addRow("Tiger", "A live verion of Winnie the Pooh's friend Tigger");
            dbhelper.addRow("Stork", "A skinny ostrich that flies and delivers children through Dream World.");
        }
    }
}

    • Obviously the addSomeTestingData method is just for that.

    • Note that there's hardly any other code. The DB access has all been moved to the DBHelper class.

    • The crux of the matter is the manageListView method.

      • First the Cursor used by the adapter is populated.
      • The Adapter hasn't been instantiated and is therefore null is instantiated and then tied to the ListView.
      • The OnItemClickListener is added noting that it calls the manageListView method after the database has been updated.
      • If the Adapter has been instantiated then the swapCursor method is called that tells the adapter that underlying cursor has been changed.

    DBHelper.java

    public class DBHelper extends SQLiteOpenHelper {

    public DBHelper(@Nullable Context context) {
        super(context, ContractClass.DBNAME,null,ContractClass.DBVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(ContractClass.FeedReaderContract.CRTSQL);
    }

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

    }

    public Cursor getAllRows() {
        SQLiteDatabase db = this.getWritableDatabase();
         return db.query(ContractClass.FeedReaderContract.TABLENAME,null,null,null,null,null,null);
    }

    public void updateSelected(String selected) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("UPDATE "
                        + ContractClass.FeedReaderContract.TABLENAME
                        + " SET " + ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED
                        + "= CASE " + ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED +
                        " WHEN 1 THEN 0 ELSE 1 END " +
                        " WHERE " + ContractClass.FeedReaderContract.COLUMN_NAME_ENWORD + " LIKE ?",
                new String[]{selected}
        );
    }

    public long addRow(String enWord, String definition) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(ContractClass.FeedReaderContract.COLUMN_NAME_ENWORD,enWord);
        cv.put(ContractClass.FeedReaderContract.COLUMN_NAME_DEFN,definition);
        return db.insert(ContractClass.FeedReaderContract.TABLENAME,null,cv);
    }
}
    • Selecting all rows has been moved here as the getAllRows method
    • The addRow is just to allow some testing data to be added.
    • The updateSelected method, rather than extracting rows into a Cursor, to drive the updates instead uses a CASE WHEN ELSE END clause to toggle the value and should be more efficient.
    • Note that instead of MATCH as MATCH is FTS specififc (I believe) LIKE has been used as the underlying table is not an FTS table. You would use MATCH

    MyCursorAdapter.java

    public class MyCursorAdapter extends CursorAdapter {

    public MyCursorAdapter(Context context, Cursor c) {
        super(context, c, false);
    }

    @Override
    public View newView(Context context, Cursor cursor, ViewGroup parent) {
        return LayoutInflater.from(context).inflate(R.layout.row_list_row,parent,false);
    }


    @Override
    public void bindView(View view, Context context, Cursor cursor) {
        if (cursor.getInt(cursor.getColumnIndex(ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED)) < 1) {
            view.setBackgroundColor(0xFFF9F9F9);
        } else {
            view.setBackgroundColor(0xFFD9D9D9);
        }
        TextView tvBody = view.findViewById(R.id.txtName);
        TextView tvPriority = view.findViewById(R.id.txtComment);
        TextView tvPriority2 = view.findViewById(R.id.txtThird);
        TextView tvPriority3 = view.findViewById(R.id.txtThi);
        tvBody.setText(cursor.getString(cursor.getColumnIndex(ContractClass.FeedReaderContract.COLUMN_NAME_ENWORD)));
        tvPriority.setText(cursor.getString(cursor.getColumnIndex(ContractClass.FeedReaderContract.COLUMN_NAME_DEFN)));
        tvPriority2.setText(cursor.getString(cursor.getColumnIndex(ContractClass.FeedReaderContract._id)));
        tvPriority3.setText(cursor.getString(cursor.getColumnIndex(ContractClass.FeedReaderContract.COLUMN_NAME_SELECTED)));
    }
}
    • The main difference is that rather than juggle swapping layouts the background of the view is changed in the bindView method, rather than the newView method.