SQLite for a Food Tasting app

Question

I have a class like this =>

public class food{
       private String foodName;
       private boolean isTasting;
       private int foodNum;

       food(String foodName,boolean isTasting){
            this.foodName=foodName;
            this.isTasting=isTasting;
       }
       public String getFoodName(){
            return foodName;
       }

}

In my MainActivity class, I create an food object Array and I add them to SQLite database.

public class MainActivity extends Activity {
    MyDBHandler dbHandler;
    food[] foods= new foods[4];

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

        dbHandler = new MyDBHandler(this, null, null, 1);

         foods[0]=new food("apple",false);    //line1
         foods[1]=new food("butter",false);    //line2
         foods[2]=new food("cheese",false);    //line3
         foods[3]=new food("eggs",false);      //line4

         dbHandler.addFood(foods[0]);        //line5
         dbHandler.addFood(foods[1]);       //line6
         dbHandler.addFood(foods[2]);       //line7
         dbHandler.addFood(foods[3]);       //line8
    }
}

And my MyDBHandler class = >

public class MyDBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME ="foodList.db";
    public static final String TABLE_FOODS = "foods";
    public static final String COLUMNS_ID = "foodNum";
    public static final String COLUMNS_FOODNAME = "foodName";

    public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query =  "CREATE TABLE "+ TABLE_FOODS+ "("+
                COLUMNS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMNS_FOODNAME + " TEXT " +
                ");";
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(" DROP TABLE IF EXISTS " + TABLE_FOODS);
        onCreate(db);
    }
    //Add new row to database
    public void addFood(food food){
        ContentValues values = new ContentValues();
        values.put(COLUMNS_FOODNAME,food.getFoodName());
        SQLiteDatabase db = getWritableDatabase();
        db.insert(TABLE_FOODS,null,values);
        db.close();
    }

}

I initialize isTaste datafield defaultly false.A person can check the checkBox and make it true if taste a that food.

But after the app closed and again launch the app checkBox are gone. Becasue line1 to line8 works again and checkbox filled defaultly false again.

I want to these line1 to line8 works one time in the first launch app.And after this if app closed and open again , contiune last state of the SQLite database.(I don't want to initialize again and again)

How can I do?

You can get the items from your database and check whether or not it is false or not and make changes accordingly.. Simple `if/else` statement.. — HB., Jul 03 '18 at 13:56
see this https://stackoverflow.com/questions/23024831/android-shared-preferences-example — N J, Jul 03 '18 at 13:57
@NJ , thank but I don't wan't to use `SharedPreferences`, I don't like it. — Be Studios, Jul 03 '18 at 14:05
I don't understand why you set it to false in `onCreate`, it's not necessary. Only change it when the user select a `checkBox` — HB., Jul 03 '18 at 14:09
@H.Brooks but, every app launch `line1` to `line8` works again and `isTaste` turn to false for all foods defaultly. — Be Studios, Jul 03 '18 at 14:09
Add on variable that maintains app is opened first time or not. In you DB check if it is true then execute line 1to line 8 else skip it — N J, Jul 03 '18 at 14:11
@H.Brooks I want to initialize them defaultly `false` , I want it like this. — Be Studios, Jul 03 '18 at 14:11

MikeT · Answer 1 · 2018-07-03T23:32:00.893

I believe the core issue you have is that you are under the impression that the Database will somehow automatically interact with or reflect a food object. It won't.

To preserve the isTaste status then you need to update the respective data in the database.

As such you will need a column into which the isTaste value will be placed. SQLite doesn't have a boolean column type so you could use an INTEGER column type using 0 to reflect false and any non-zero value to reflect true.

So I'd suggest adding the line :-

    public static final String COLUMN_ISTASTE = "foodIsTaste";

and then changing :-

@Override
public void onCreate(SQLiteDatabase db) {
    String query =  "CREATE TABLE "+ TABLE_FOODS+ "("+
            COLUMNS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMNS_FOODNAME + " TEXT " 
            ");";
    db.execSQL(query);
}

to :-

@Override
public void onCreate(SQLiteDatabase db) {
    String query =  "CREATE TABLE "+ TABLE_FOODS+ "("+
            COLUMNS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMNS_FOODNAME + " TEXT, " + //<<< COMMA ADDED after TEXT
            COLUMN_ISTASTE + " INTEGER DEFAULT 0" + //<<<< ADDED
            ");";
    db.execSQL(query);
}

Thus adding a column in which to store the isTaste value (which will default to 0 if no value is supplied for the column).

You may wish to change the addFood or add a second addFood method that allows the foodIsTaste column to be set. Or as a third alternative a method that is passe a food object that sets both values.

New addFood method to add a food and the state of the foodIsTaste column as a separate parameter.

//Add new row to database, specifying the value for isTaste
public void addFood(food food, boolean isTaste){
    ContentValues values = new ContentValues();
    values.put(COLUMNS_FOODNAME,food.getFoodName());
    values.put(COLUMN_ISTASTE,isTaste);
    SQLiteDatabase db = getWritableDatabase();
    db.insert(TABLE_FOODS,null,values);
    db.close();
}

New addFood method that adds food and state of the foodIstaste column based upon a food object being passed.

NOTE the isTasting getter method and also a setter method setTasting has been added to the food class as per :-

public boolean isTasting() { return isTasting; } public void setTasting(boolean tasting) { isTasting = tasting; }

:-

public void addFoodAlternative(food food) {
    ContentValues values = new ContentValues();
    values.put(COLUMNS_FOODNAME,food.getFoodName());
    values.put(COLUMN_ISTASTE,food.isTasting());
    SQLiteDatabase db = this.getWritableDatabase();
    db.insert(TABLE_FOODS,null,values);
    db.close();
}

You would also need methods to allow the isTaste status to be changed. e.g. here's two methods that allow the foodIsTaste column to be updated. One according to the id, the other according to the food name. Noting that the latter is not recommended as you can't be sure what row gets updated if there are multiple rows with the same food name.

public void updateTaste(long id, boolean isTaste) {
    ContentValues values = new ContentValues();
    values.put(COLUMN_ISTASTE,isTaste);
    SQLiteDatabase db = this.getWritableDatabase();
    db.update(TABLE_FOODS,values,COLUMNS_ID + "=?", new String[]{String.valueOf(id)});
    db.close();
}

public void updateTaste(String foodname, boolean isTaste) {
    ContentValues values = new ContentValues();
    values.put(COLUMN_ISTASTE,isTaste);
    SQLiteDatabase db = this.getWritableDatabase();
    db.update(TABLE_FOODS,values,COLUMNS_FOODNAME + "=?", new String[]{String.valueOf(foodname)});
    db.close();
}

Using the above you should be able to manage and preserve the isTaste status between runs.

Putting it together

If the above was followed then you'd have the following code:-

food.java

public class food{
    private String foodName;
    private boolean isTasting;
    private long foodNum;

    food(String foodName,boolean isTasting){
        this.foodName=foodName;
        this.isTasting=isTasting;
    }
    public String getFoodName(){
        return foodName;
    }

    public boolean isTasting() {
        return isTasting;
    }

    public void setTasting(boolean tasting) {
        isTasting = tasting;
    }
}

and MyDBHanlder.java

public class MyDBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME ="foodList.db";
    public static final String TABLE_FOODS = "foods";
    public static final String COLUMNS_ID = "foodNum";
    public static final String COLUMNS_FOODNAME = "foodName";
    public static final String COLUMN_ISTASTE = "foodIsTaste";

    public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query =  "CREATE TABLE "+ TABLE_FOODS+ "("+
                COLUMNS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMNS_FOODNAME + " TEXT, " + //<<< COMMA ADDED after TEXT
                COLUMN_ISTASTE + " INTEGER DEFAULT 0" + //<<<< ADDED
                ");";
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(" DROP TABLE IF EXISTS " + TABLE_FOODS);
        onCreate(db);
    }
    //Add new row to database
    public void addFood(food food){
        ContentValues values = new ContentValues();
        values.put(COLUMNS_FOODNAME,food.getFoodName());
        SQLiteDatabase db = getWritableDatabase();
        db.insert(TABLE_FOODS,null,values);
        db.close();
    }

    //Add new row to database, specifying the value for isTaste
    public void addFood(food food, boolean isTaste){
        ContentValues values = new ContentValues();
        values.put(COLUMNS_FOODNAME,food.getFoodName());
        values.put(COLUMN_ISTASTE,isTaste);
        SQLiteDatabase db = getWritableDatabase();
        db.insert(TABLE_FOODS,null,values);
        db.close();
    }

    public void addFoodAlternative(food food) {
        ContentValues values = new ContentValues();
        values.put(COLUMNS_FOODNAME,food.getFoodName());
        values.put(COLUMN_ISTASTE,food.isTasting());
        SQLiteDatabase db = this.getWritableDatabase();
        db.insert(TABLE_FOODS,null,values);
        db.close();
    }

    public void updateTaste(long id, boolean isTaste) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_ISTASTE,isTaste);
        SQLiteDatabase db = this.getWritableDatabase();
        db.update(TABLE_FOODS,values,COLUMNS_ID + "=?", new String[]{String.valueOf(id)});
        db.close();
    }

    public void updateTaste(String foodname, boolean isTaste) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_ISTASTE,isTaste);
        SQLiteDatabase db = this.getWritableDatabase();
        db.update(TABLE_FOODS,values,COLUMNS_FOODNAME + "=?", new String[]{String.valueOf(foodname)});
        db.close();
    }
}

If MainActivity.java had :-

    MyDBHandler dbHandler;
    food[] foods= new food[4]; //<<<< Correction food[4] not foods[4]
    dbHandler = new MyDBHandler(this, null, null, 1);
    SQLiteDatabase db = dbHandler.getWritableDatabase(); // Get Database

    //Only add rows for the first run
    if (DatabaseUtils.queryNumEntries(db,MyDBHandler.TABLE_FOODS) == 0) {
        Log.d("DB INIT INFO","The database contains no rows so adding 4 rows");
        foods[0]=new food("apple",false);    //line1
        foods[1]=new food("butter",true);    //line2
        foods[2]=new food("cheese",false);    //line3
        foods[3]=new food("eggs",false);      //line4

        dbHandler.addFoodAlternative(foods[0]);        //line5
        dbHandler.addFoodAlternative(foods[1]);       //line6
        dbHandler.addFoodAlternative(foods[2]);       //line7
        dbHandler.addFoodAlternative(foods[3]);       //line8
    }

    dbHandler.updateTaste("eggs",true);
    db = dbHandler.getWritableDatabase(); // Need to re-open database

    // Get the data from the database
    Cursor mCsr = db.query(MyDBHandler.TABLE_FOODS, null,null,null,null,null,null);

    // Loop through each row of the extracted data outputting the values to the log
    while (mCsr.moveToNext()) {
        boolean istatse = mCsr.getInt(mCsr.getColumnIndex(MyDBHandler.COLUMN_ISTASTE)) > 0;
        Log.d("ROWINFO","Row at position " +
                String.valueOf(mCsr.getPosition() + 1) +
                " " +MyDBHandler.COLUMNS_ID + " = " +
                String.valueOf(mCsr.getLong(mCsr.getColumnIndex(MyDBHandler.COLUMNS_ID))) +
                " " + MyDBHandler.COLUMNS_FOODNAME + " = " +
                mCsr.getString(mCsr.getColumnIndex(MyDBHandler.COLUMNS_FOODNAME)) +
                " " + MyDBHandler.COLUMN_ISTASTE + " = " +
                String.valueOf(istatse)
        );
    }
}

The when the App was first run (delete the App's data to reflect a first run so that the database is deleted) then the log would show :-

07-03 23:28:44.881 2566-2566/? D/DB INIT INFO: The database contains no rows so adding 4 rows
07-03 23:28:44.905 2566-2566/? D/ROWINFO: Row at position 1 foodNum = 1 foodName = apple foodIsTaste = false
    Row at position 2 foodNum = 2 foodName = butter foodIsTaste = true
    Row at position 3 foodNum = 3 foodName = cheese foodIsTaste = false
    Row at position 4 foodNum = 4 foodName = eggs foodIsTaste = true

When subsequently run the log would show the same information, except that the line The database contains no rows so adding 4 rows,

i.e. the data, including the isTaste value has been preserved in the database.

SQLite for a Food Tasting app

1 Answers1

Putting it together

food.java