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
Advertisement
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.