How to add & update data in SqliteDatabase by using RecyclerView Adapter

Question

I am just trying to make a list by using RecyclerView where each of the list Item will contain a checkbox button. So when user will click on checkbox, it will replace the value of a Table column such as column country get value "A", on the other hand, uncheck will replace the country column value from "A" to "B" or anything.

Can anyone please help me with this? any suggestion regarding this and other similar ways to add data in SQLite database by using Recyclable will be highly a lot helpful.

Below I have added my code for your reference and Thanks in advance.

My DatabaseHelper Class

    package com.hfad.ressql;

    import android.content.ContentValues;
    import android.content.Context;
    import android.content.Intent;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    import com.hfad.ressql.DatabaseContractor.*;

    import java.lang.reflect.Array;
    import java.util.ArrayList;
    import java.util.List;

    public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "workers.db";
    private static final int DATABASE_VERSION =7;

    SQLiteDatabase db;

    public DatabaseHelper( Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
     this.db=db;

     final String SQL_CREATE_TBALE="CREATE TABLE " +  EmployeeDetails.TABLE_NAME + "(" + EmployeeDetails._ID +" INTEGER PRIMARY KEY  AUTOINCREMENT, "+
             EmployeeDetails.COLUMN_FIRSTNAME+" TEXT,  "+EmployeeDetails.COLUMN_LASTNAME+" TEXT, "+EmployeeDetails.COLUMN_COUNTRY+" TEXT)";

     db.execSQL(SQL_CREATE_TBALE);
     fillquestion();

    }

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



        db.execSQL("DROP TABLE IF EXISTS " + EmployeeDetails.TABLE_NAME);
        onCreate(db);

    }


    public void fillquestion(){
    DataModel o4 = new DataModel("Earth","Soil","B");
    IntertData(o4);
    DataModel o5 = new DataModel("Sun","Light","B");
    IntertData(o5);
    DataModel o6 = new DataModel("Moon","Rock","B");
    IntertData(o6);

    }

    public void IntertData (DataModel data){

        ContentValues contentValues = new ContentValues();
        contentValues.put(EmployeeDetails.COLUMN_FIRSTNAME,     data.getFirstName());
        contentValues.put(EmployeeDetails.COLUMN_LASTNAME,   data.getLastName());
        contentValues.put(EmployeeDetails.COLUMN_COUNTRY, data.country);
        db.insert(EmployeeDetails.TABLE_NAME,null,contentValues);
    }

    public List<DataModel> object1() {
        ArrayList<DataModel> details = new ArrayList<DataModel>();
        db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + EmployeeDetails.TABLE_NAME, null );
        if (cursor.moveToFirst()) {

            do {
                DataModel object2 = new DataModel();
                object2.setFirstName(cursor.getString(cursor.getColumnIndex(EmployeeDetails.COLUMN_FIRSTNAME)));
                object2.setLastName(cursor.getString(cursor.getColumnIndex(EmployeeDetails.COLUMN_LASTNAME)));
                object2.setCountry(cursor.getString(cursor.getColumnIndex(EmployeeDetails.COLUMN_COUNTRY)));


                details.add(object2);

            } while (cursor.moveToNext());
        }
        cursor.close();
        return details;
    }
}

Here is my DataModel Class

    package com.hfad.ressql;

    public class DataModel {

    public String FirstName;
    public String LastName;
    public String country;



    public DataModel() {
    }

    public DataModel(String firstName, String lastName, String country) {
        this.FirstName = firstName;
        this.LastName = lastName;
        this.country = country;
    }

    public String getFirstName() {
        return FirstName;
    }

    public void setFirstName(String firstName) {
        FirstName = firstName;
    }

    public String getLastName() {
        return LastName;
    }

    public void setLastName(String lastName) {
        LastName = lastName;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }
     }

Database Constructor

       public final class DatabaseContractor {

       private DatabaseContractor (){}

        public static class EmployeeDetails implements BaseColumns {
        public static final String TABLE_NAME="employy";
        public static final String COLUMN_FIRSTNAME="First_Name";
        public static final String COLUMN_LASTNAME="Last_Name";
        public static final String COLUMN_COUNTRY="Country";
        public static final String COLUMN_FAVO="mfav";


        }

        }

Recycler Adapter

Here I am struggling hard to sort it out. All I need is, if I click on check box, one pre-given value will be updated in the database, at the same time check box will be checked until user uncheck it. And when user will uncheck it, database will replace previous value with a new value. Actually, I have just trying to have values in a table column, so that I can use it as a favorite or bookmark list.

    public class RecycAdapter extends       
    RecyclerView.Adapter<RecycAdapter.ViewHolder> {

    List<DataModel> dotamodeldataArraylist;
    Context context;
    SQLiteDatabase db;
    DatabaseHelper helper;
    ContentValues contentValues;
    Cursor cursor;

    public RecycAdapter(List<DataModel> dotamodeldataArraylist,Context context) {
        this.dotamodeldataArraylist=dotamodeldataArraylist;
        this.context=context;
    }

    @Override
    public ViewHolder onCreateViewHolder( ViewGroup parent, int ViewType) {
        View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.itemlist,parent,false);

        return new ViewHolder(view);
    }

    @Override
    public void onBindViewHolder(final RecycAdapter.ViewHolder holder, final int position) {

        DataModel obj3= dotamodeldataArraylist.get(position);
        holder.Fnam.setText(obj3.getFirstName());
        holder.Lname.setText(obj3.getLastName());
        holder.Country.setText(obj3.getCountry());
        holder.fav.();
      holder.fav.setChecked(fav);
      final int currentPosition = position;



           final boolean fav = 0==0;
      holder.fav.setChecked(fav);
      final int currentPosition = position;



        holder.fav.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {


                if(holder.fav.isChecked()){
                   try {
                       contentValues = new ContentValues();
                       contentValues.put(DatabaseContractor.EmployeeDetails.COLUMN_COUNTRY, "B");
                       db.update("DRINK", contentValues, "id_=?", new String[]{Integer.toString(currentPosition)});
                   } catch (SQLException e){
                       Toast.makeText(context,"error" + position , Toast.LENGTH_LONG).show();

                   }

                    Toast.makeText(context,"checked " + position , Toast.LENGTH_LONG).show();

                } if(!holder.fav.isChecked()){
                    Toast.makeText(context,"not checked" + position , Toast.LENGTH_LONG).show();
                }



            }
        });






    }

    @Override
    public int getItemCount() {
        return dotamodeldataArraylist.size();
    }

    public class ViewHolder extends RecyclerView.ViewHolder {

        TextView Fnam,Lname,Country;
        CheckBox fav;
        RelativeLayout relativeLayout;


        public ViewHolder(View itemView) {
            super(itemView);

            Fnam = itemView.findViewById(R.id.name1);
            Lname = itemView.findViewById(R.id.city1);
            Country = itemView.findViewById(R.id.country1);
            fav=itemView.findViewById(R.id.chk);
            relativeLayout = (RelativeLayout)     itemView.findViewById(R.id.layout);

        }
    }


    }

View Class

view all class

    public class Viewall extends AppCompatActivity {

    RecyclerView recyclerView;
    DatabaseHelper databaseHelper;
    RecycAdapter recycAdapter;
    List<DataModel> dotamodeldataArraylist;
    Context context;
    Button show;


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

        show = findViewById(R.id.view);
        recyclerView=findViewById(R.id.recycle);

        databaseHelper =new DatabaseHelper(this);
        dotamodeldataArraylist = new ArrayList<DataModel>();
        dotamodeldataArraylist=databaseHelper.object1();
        recycAdapter =new RecycAdapter(dotamodeldataArraylist,this);
        RecyclerView.LayoutManager reLayoutManager =new            
        LinearLayoutManager(getApplicationContext());
        recyclerView.setLayoutManager(reLayoutManager);
        recyclerView.setItemAnimator(new DefaultItemAnimator());
        recyclerView.setAdapter(recycAdapter);

Answer 1

I believe the following will do as you wish.

The were quite a few issues with code. One of the major issues is that you expected the position to correlate with the id (aka your _id column).

The position of the first item in the list is 0, unless you force/specifically set the value of 0, an alias of the rowid column (your _id column is an alias of the rowid column), the first value assigned will be 1, then likely 2, then likely 3 ...........

So at best position will be 1 less than the id.

If a row is deleted, other than the last row then position will be one less except up until the deleted row is passed and then position will be 2 less than the rowid. More deletions and an even more complex correlation between position and id. I guess somebody could come up with a fool proof conversion BUT the simpe way is to ensure that the DataModel has the vale of the respective _id column.

As such DataModel.java should be changed to include a member/variable for the id therefore the following was used :-

public class DataModel {

    public String FirstName;
    public String LastName;
    public String country;

    public long id; //<<<<<<<<<< ADDED also added gettter and setter

    public DataModel() {
    }

    public DataModel(String firstName, String lastName, String country) {
        this(firstName,lastName,country,-1);
    }

    //<<<<<<<<<< ADDED so ID can be set
    public DataModel(String firstName, String lastName, String country, long id) {
        this.FirstName = firstName;
        this.LastName = lastName;
        this.country = country;
        this.id = id;
    }

    public String getFirstName() {
        return FirstName;
    }

    public void setFirstName(String firstName) {
        FirstName = firstName;
    }

    public String getLastName() {
        return LastName;
    }

    public void setLastName(String lastName) {
        LastName = lastName;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public void setId(long id) {
        this.id = id;
    }

    public long getId() {
        return id;
    }
}

• see comments for changes

As you need to extract the id from the database, the object1 method was changed in DatabaseHelper.java (a few other changes have also been made) the following was used :-

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "workers.db";
    private static final int DATABASE_VERSION =7;

    SQLiteDatabase db;

    public DatabaseHelper( Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db=db; //<<<<<<< WRONG PLACE as onCreate only ever runs when there is no database

        final String SQL_CREATE_TBALE="CREATE TABLE " +  DatabaseContractor.EmployeeDetails.TABLE_NAME + "(" + DatabaseContractor.EmployeeDetails._ID +" INTEGER PRIMARY KEY  AUTOINCREMENT, "+
                DatabaseContractor.EmployeeDetails.COLUMN_FIRSTNAME+" TEXT,  "+ DatabaseContractor.EmployeeDetails.COLUMN_LASTNAME+" TEXT, "+ DatabaseContractor.EmployeeDetails.COLUMN_COUNTRY+" TEXT)";

        db.execSQL(SQL_CREATE_TBALE);
        fillquestion();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DatabaseContractor.EmployeeDetails.TABLE_NAME);
        onCreate(db);
    }


    public void fillquestion(){
        IntertData(new DataModel("Earth","Soil","B"));
        IntertData(new DataModel("Sun","Light","B"));
        IntertData(new DataModel("Moon","Rock","B"));
    }

    public void IntertData (DataModel data){

        ContentValues contentValues = new ContentValues();
        contentValues.put(DatabaseContractor.EmployeeDetails.COLUMN_FIRSTNAME,data.getFirstName());
        contentValues.put(DatabaseContractor.EmployeeDetails.COLUMN_LASTNAME,data.getLastName());
        contentValues.put(DatabaseContractor.EmployeeDetails.COLUMN_COUNTRY,data.country);
        db.insert(DatabaseContractor.EmployeeDetails.TABLE_NAME,null,contentValues);
    }

    public List<DataModel> object1() {
        ArrayList<DataModel> details = new ArrayList<>();
        //db = getReadableDatabase(); db has already been set when database was instantiated/constructed
        Cursor cursor = db.rawQuery("SELECT * FROM " + DatabaseContractor.EmployeeDetails.TABLE_NAME, null );
        while (cursor.moveToNext()) {
            details.add(new DataModel(
                    cursor.getString(cursor.getColumnIndex(DatabaseContractor.EmployeeDetails.COLUMN_FIRSTNAME)),
                    cursor.getString(cursor.getColumnIndex(DatabaseContractor.EmployeeDetails.COLUMN_LASTNAME)),
                    cursor.getString(cursor.getColumnIndex(DatabaseContractor.EmployeeDetails.COLUMN_COUNTRY)),
                    cursor.getLong(cursor.getColumnIndex(DatabaseContractor.EmployeeDetails._ID)) //<<<<<<<<< Added so id is available
            ));
        }
        cursor.close();
        return details;
    }
}

Pretty extensive changes were made to RecycAdapter.java, the following was used :-

public class RecycAdapter extends RecyclerView.Adapter<RecycAdapter.ViewHolder> {

    List<DataModel> dotamodeldataArraylist;
    Context context;
    SQLiteDatabase db;
    DatabaseHelper helper;
    ContentValues contentValues;

    public RecycAdapter(List<DataModel> dotamodeldataArraylist,Context context) {
        this.dotamodeldataArraylist=dotamodeldataArraylist;
        this.context=context;
        helper = new DatabaseHelper(context);
        db = helper.getWritableDatabase();
    }

    @Override
    public ViewHolder onCreateViewHolder(ViewGroup parent, int ViewType) {
        View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.itemlist,parent,false);
        return new ViewHolder(view);
    }

    @Override
    public void onBindViewHolder(final RecycAdapter.ViewHolder holder, final int position) {

        //DataModel obj3= dotamodeldataArraylist.get(position); //<<<<<<<<<< NOT NEEDED
        holder.Fnam.setText(dotamodeldataArraylist.get(position).getFirstName());
        holder.Lname.setText(dotamodeldataArraylist.get(position).getLastName());
        holder.Country.setText(dotamodeldataArraylist.get(position).getCountry());
        holder.fav.setChecked(false); //<<<<<<<<< not stored so initially set to false

        holder.fav.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String newcountry = "B";
                if(holder.fav.isChecked()){
                    if (dotamodeldataArraylist.get(position).getCountry().equals("B")) {
                        newcountry  = "A";
                    }
                    contentValues = new ContentValues();
                    contentValues.put(DatabaseContractor.EmployeeDetails.COLUMN_COUNTRY, newcountry);
                    if (db.update(
                            DatabaseContractor.EmployeeDetails.TABLE_NAME,
                            contentValues,
                            DatabaseContractor.EmployeeDetails._ID +"=?",
                            new String[]{String.valueOf(dotamodeldataArraylist.get(position).getId())}
                            ) > 0) {
                        dotamodeldataArraylist.get(position).setCountry(newcountry);
                        notifyItemChanged(position);
                        Toast.makeText(context,
                                "checked and updated " +
                                        position+ dotamodeldataArraylist.get(position).getFirstName() +
                                        " ID is " + String.valueOf(dotamodeldataArraylist.get(position).getId()),
                                Toast.LENGTH_LONG
                        ).show();
                    } else {
                        Toast.makeText(context,"error" + position , Toast.LENGTH_LONG).show();
                    }
                } else {
                    Toast.makeText(context,"not checked" + position , Toast.LENGTH_LONG).show();
                }
            }
        });
    }

    @Override
    public int getItemCount() {
        return dotamodeldataArraylist.size();
    }

    public class ViewHolder extends RecyclerView.ViewHolder {

        TextView Fnam,Lname,Country;
        CheckBox fav;

        public ViewHolder(View itemView) {
            super(itemView);
            Fnam = itemView.findViewById(R.id.name1);
            Lname = itemView.findViewById(R.id.city1);
            Country = itemView.findViewById(R.id.country1);
            fav = itemView.findViewById(R.id.chk);
        }
    }
}

lastly a few minor changes were made to Viewall.java, the following was used :-

public class Viewall extends AppCompatActivity {

    RecyclerView recyclerView;
    DatabaseHelper databaseHelper;
    RecycAdapter recycAdapter;
    List<DataModel> dotamodeldataArraylist;
    Button show;

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

        show = findViewById(R.id.view);
        recyclerView = findViewById(R.id.recycle);

        databaseHelper = new DatabaseHelper(this);
        dotamodeldataArraylist = databaseHelper.object1();
        recycAdapter = new RecycAdapter(dotamodeldataArraylist, this);
        RecyclerView.LayoutManager reLayoutManager = new
                LinearLayoutManager(this);
        recyclerView.setLayoutManager(reLayoutManager);
        recyclerView.setItemAnimator(new DefaultItemAnimator());
        recyclerView.setAdapter(recycAdapter);
    }
}

Result

Note the layout(s) may be different, but your's should probably work and alter the presentation accordingly

When first run :-

After clicking the checkbox for Sun

Click again and back to Country B and so on.

• Note the check box isn't flipped, that's a bit of an issue as to correctly display the changed data (country) notifyItemChanged is used, which will reprocess the list and thus set the checkbox to false. You'd need to store the checkbox value somewhere (in short you should really use checkboxes in this way).

Closing the app and restarting maintains the changes made, thus confirming that the changes to the database have been made.