How to multiply sql values

Question

I made SQL database and populated it with values. In TextView I need to show multiply result of specific rows. I made SQL statement and I hope that is correct.

public List<Food> multiplyFat(){
    String totalFat = "SELECT " +FoodEntry.COLUMN_FAT_TOTAL + " FROM " + FoodEntry.TABLE_NAME + " WHERE ( "
            +FoodEntry.COLUMN_FAT_TOTAL + " * " + FoodEntry.COLUMN_GRAM + " ) > 0";

    SQLiteDatabase db = this.getWritableDatabase();
    List<Food> storeTotalFat = new ArrayList<>();
    Cursor cursor = db.rawQuery(totalFat, null);
    if (cursor.moveToFirst()){
        do {
            double fat = Double.parseDouble(cursor.getString(0));
            storeTotalFat.add(new Food(fat));
        } while (cursor.moveToNext());
    }
    cursor.close();
    return storeTotalFat;
}

To be more clear I need to multiply values from row COLUMN_FAT_TOTAL with row COLUMN_GRAM and display result into the TextView. Or should I put these SQL statement:

String totalFat = "SELECT " +FoodEntry.COLUMN_FAT_TOTAL + " * " +FoodEntry.COLUMN_GRAM + " FROM " +FoodEntry.TABLE_NAME;

That is simplier way but I am not sure that it is correct way.

Anyhow I need to display this multiplyFat() function (result) into TextView. Any help or advice would be really helpfull.

Answer 1

public List multiplyFat(){

String totalFat = "SELECT " +FoodEntry.COLUMN_FAT_TOTAL + " FROM " + FoodEntry.TABLE_NAME + " WHERE ( "
        +FoodEntry.COLUMN_FAT_TOTAL + " * " + FoodEntry.COLUMN_GRAM + " ) > 0"; 

The above works perfectly in MySQL, I just tried it.

SQLiteDatabase db = this.getWritableDatabase(); --> db = this.getReadableDatabase();

Since you are not changing anything in the SQLite database you want a '.getReadableDatbase();' not '.getWritableDatabase();'

List<Food> storeTotalFat = new ArrayList<>();
Cursor cursor = db.rawQuery(totalFat, null);
cursor.moveToFirst();
  while (!cursor.isAfterLast()) {
    do {
        double fat = Double.parseDouble(cursor.getString(0));
        storeTotalFat.add(new Food(fat));
    } cursor.moveToNext();
  }
cursor.close();
return storeTotalFat;
}

See how I first moved to the first index, then I ask it to continue in a while loop until it has reached the final element in the SQL results. Your way was not actually incrementing the cursor, it would have only been able to add the first result to the storeTotalFat ArrayList.

-------- part 2 ---------

Now let's pretend we are back in the activity, woo! Let's say there is a class variable reference to the database helper and add a new ArrayList to house the results from the query result we just gained.

DBHelper myDBhelper = DBHelper.getInstance(MainActivity.this) 
ArrayList<String> sqlResultArray = new ArrayList<>();

If you want a Recyclerview you make a separate RviewAdapter class, in your case FoodFatRecyclerViewAdapter and add the following

RecyclerView rv = (RecyclerView) findViewById(R.id.foodFatRecView);
LinearLayoutManager llm = new LinearLayoutManager(this, LinearLayoutManager.VERTICAL, false);
recyclerView.setLayoutManager(llm);
FoodFatRecyclerViewAdapter foodFatAdapter = new FoodFatRecyclerViewAdapter(MainActivity.this, sqlResultArray);
rv.setAdapter(foodFatAdapter);

Now as for the adding of the result to the textview create a new class called FoodFatRecyclerViewAdapter

public class FoodFatRecyclerViewAdapter extends RecyclerView.Adapter<FoodFatViewHolder> {

ArrayList<String> mFoodFatItems;
Context mContext;

public FoodFatRecyclerViewAdapter(Context context, ArrayList<String> array) {
    mContext = context;
    mFoodFatItems = array;
}

@Override
public FoodFatViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
    View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.this_xml_layout_will_describe_the_arrangement_of_the_indi_item_in_the_list_not_the_master_view, parent, false);
    FoodFatViewHolder recyclerView = new FoodFatViewHolder(view);
    return recyclerView;
}

@Override
public void onBindViewHolder(FoodFatViewHolder holder, final int position) {
    holder.mFatTotalTextView.setText(mFoodFatItems.get(position));
}


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

Now the final piece to the puzzle, the View Holder so create a class called FoodFatViewHolder

public class FoodFatViewHolder extends RecyclerView.ViewHolder{

TextView mFatTotalTextView;

public FoodFatViewHolder(View itemView) {
    super(itemView);
    mFatTotalTextView = (TextView) itemView.findViewById(R.id.the_textview_id_within_the_individ_item_in_rv_xml_layout);
}
}

Voila! It should work