SQLite Database not getting updated

So Ive created an app where the user can input details about the movies he has watched such as name,cast,rating…ect and the details are stored in a database inside a table names which is initialized in the DataBaseHelper class as

public static final String TABLE_NAME = "table1";

in the below segment of code Ive created a list view and displayed the names of the movies with a checkbox in front of each name. where the check box if ticked mean that its a favorite else not a favorite…initially the column in the table which holds if the movie is a favorite is set to “no” when ticked and button pressed I want all the movie names with the tick on to update to “yes” in the database.

DisplayActivity class with the list view

public class DisplayActivity extends AppCompatActivity {

DataBaseHelper myDb;
ListView movieNList;
Button addFavoritesB,button;

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

    movieNList =(ListView) findViewById(R.id.moviesLV);
    myDb=new DataBaseHelper(this);
    addFavoritesB=(Button) findViewById(R.id.addButton);
    button=(Button) findViewById(R.id.button);

    ArrayList<String> theList=new ArrayList<>();
    Cursor data=myDb.getData();

    if (data.getCount()==0){
        Toast.makeText(DisplayActivity.this,"The Database is empty",Toast.LENGTH_SHORT).show();
    }else{

        //Adds data to the list view
        while(data.moveToNext()){
            theList.add(data.getString(1));
            Collections.sort(theList);
            ListAdapter listAdapter=new ArrayAdapter<>(this, android.R.layout.simple_list_item_multiple_choice,theList);
            movieNList.setAdapter(listAdapter);
        }
    }

    buttonAction();
}

public void buttonAction(){
    myDb.getWritableDatabase();
    addFavoritesB.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            String itemSelected="Selected items : n";
            for (int i=0;i<movieNList.getCount();i++){
                if (movieNList.isItemChecked(i)){
                    itemSelected += movieNList.getItemAtPosition(i)+"n";
                    System.out.println(itemSelected);
                    myDb.updateFavorites(itemSelected,"yes");
                    
                }
            }
        }
    });
}

Method in DataBaseHelper class to update the favorites column

public boolean updateFavorites(String name,String favorites) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("FAVORITES", favorites);
    Cursor res = db.rawQuery("select * from table1 where name=? ", new String[]{name});
    if (res.getCount() > 0) {
        long result = db.update(TABLE_NAME, contentValues, "name=?", new String[]{name});
        if (result == -1) {
            return false;
        } else {
            return true;
        }
    } else {
        return false;
    }
}

when I try it like this, the columns wont update….Please help

Answer

Assuming that itemSelected is created correctly inside the onClick() listener, I suggest that you use a char like "|" as a delimiter instead of "n" for the movie titles and remove "Selected items : n" from the start of itemSelected.
Also move myDb.updateFavorites(itemSelected,"yes"); out of the for loop, so that the updateFavorites() is called only once for all selected movies:

public void buttonAction(){
    myDb.getWritableDatabase();
    addFavoritesB.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            String itemSelected="|";
            for (int i=0;i<movieNList.getCount();i++){
                if (movieNList.isItemChecked(i)){
                    itemSelected += movieNList.getItemAtPosition(i)+"|";
                }
            }
            itemSelected += "|";
            myDb.updateFavorites(itemSelected,"yes");
        }
    });
}

Then use the update() method to update the table with the operator LIKE in the WHERE clause:

public int updateFavorites(String name, String favorites) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("FAVORITES", favorites);
    return db.update(TABLE_NAME, contentValues, "? LIKE '%|' || name || '|%'", new String[] {name});
}

Note that I changed the return type of updateFavorites() from boolean to int() because db.update() returns the number of updated rows.

Leave a Reply

Your email address will not be published. Required fields are marked *