Android Health App Design : Saving Data in SQLite Database

android_sqliteThe last tutorial left our health android app ,Imirire usable by the user for the first time .Users could select foods , input gram counts and have the total calorie count displayed right away.Did we mention that it was more pleasing to the eye? 😉 ,In this tutorial we are going to improve the model of our application and let it save its data in an SQLite database.Along the way ,we ll discuss some practical tips to go by when debugging database applications on the android platform.

How Apps are stored on your android phone.

A friendly note ,to be able to work on this tutorial ,you need a fresh copy of the source code from the last tutorial .The code is available here . Build and run the app so that a debug version gets installed on your device.If you go to Settings->App and scroll to the Imirire app ,you will see something like the figure below.

android_sqlite_app_info

In the storage section ,you can see the amount of storage consumed by our application.App is the size of your source code compiled into binary code that is executable by the android system.Data …..well ,it is the data that your application uses.You can see that for our current version of Imirire ,the amount of consumed data storage is zero.That is because our application food data is stored in code .

Real practical applications don’t store their data in code ,android provides different ways to store persistent data.One of them is storing in a SQLite database that is built in the android system.The amount of data you store in that database adds to the data storage of your app,and the Data entry in the figure above doesn’t show zero.We should mention that not only the database is accounted for in the data that your application uses.For example if your application saves the data to some files ,then the size of these files is also taken into account.In the next sections ,we’ll talk about SQLite databases on the android system and how to use them to spice up your apps.

Databases in general.

In the most simplified manner ,a database is a set of tables stored on a given system.Each table is made up of rows and columns as shown in the figure below.

android_sqlite_database_image

The database is made up of tables ,each table has a name ,and is made up rows and columns.Each column has a label and the type of data that it stores.A database can virtually can have any number of tables.There are four operations you can do on a given database:

(1) You can CREATE a database

(2)You can READ data from a database

(3)You can UPDATE data stored in the database,lets say for example ,a user updating his phone number.

(4) You can DELETE the database.

You will hear these operations refered to as CRUD .If you think about it ,these are the operations we want to do on our data in Imirire.We want to create the database of foods when the application is run for the first time.We want for our FoodAdapter to read the data from that database and build a list that is displayed by our RecyclerView.We want to update that database when a user modifies information about a certain food and we want to give the user the ability to completely wipe out the database and start over.If you open up the Food class and look at its instance variables.It is not hard to figure out that we need a database with a table that looks something like the figure below for our food data.

android_sqlite_imirire_dblook

Building the Database Schema and Creating the Database.

Add a new class to your project and in the new class dialog input “database.FoodDbSchema” as shown in the figure below.

android_sqlite_add_database_class

This forces your new class to be created in a new package.Hit ok and your class is created.Modify your class so it looks like mine shown below.

FoodDbSchema.java

package com.blikoon.imirire.database;

/**
 * Created by gakwaya on 2/24/2016.
 */
public class FoodDbSchema {
    public static final class FoodTable
    {
        public static final String NAME = "foods";

        public static final class Cols
        {
            public static final String UUID = "uuid";
            public static final String NAME = "name";
            public static final String GRAM_COUNT = "gramcount";
            public static final String CAL_COUNT = "calcount";
            public static final String IS_CHECKED = "ischecked";
        }
    }
}

First notice that the class has been created inside a new package called database ,just one layer below our normal package name com.blikoon.imirire.This may look slightly different if you have chosen a diferent package name for your app ,but it is easy to adapt.FoodDbSchema is a blueprint of how our database is structured.Right now it just has one table to store our food data but nothing prevents us from adding other tables later on.FoodTable is our table .It contains a variable to store our database name and an inner class called Cols ,to store the columns that our table contains.This is just a way to keep things organized in our app.

Now we add the class that creates the real database.Android provides a class called SQLiteOpenHelper that makes your transactions with the database more easy.Create a new class in your database package and name it FoodOpenHelper.The class extends SQLiteOpenHelper and must implement two methods as shown in the code snippet below.

FoodOpenHelper.java

package com.blikoon.imirire.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by gakwaya on 2/24/2016.
 */
public class FoodOpenHelper extends SQLiteOpenHelper {

    private static final int VERSION = 1;
    private static final String DATABASE_NAME= "foodDb.db";

    public FoodOpenHelper(Context context)
    {
        super(context,DATABASE_NAME,null,VERSION);
    }

    @Override
    public void onCreate( SQLiteDatabase db)
    {
       
    }

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

    }
}

This is the class that represents your database.You don’t have to worry about managing the life of instances of this class.When you call its constructor ,android creates the database if it doesn’t exist or simply uses an already present reference to the object if the database was already there.Obviously , the implemented methods specify how your database is created /upgraded.For the table we want to be created ,we modify the onCreate() method as shown below.

@Override
public void onCreate( SQLiteDatabase db)
{
    db.execSQL("create table " + FoodDbSchema.FoodTable.NAME + "(" +
            "_id integer primary key autoincrement, " +
            FoodDbSchema.FoodTable.Cols.UUID + "," +
            FoodDbSchema.FoodTable.Cols.NAME + "," +
            FoodDbSchema.FoodTable.Cols.GRAM_COUNT + "," +
            FoodDbSchema.FoodTable.Cols.CAL_COUNT + ","+
            FoodDbSchema.FoodTable.Cols.IS_CHECKED+ ")");
}

If you have worked with databases before then you recognize the code inside our onCreate function as an SQL statement.It simply tells android to create a table of some name ,which has the rows specified within the parentheses.A simplified version of the statement is shown below.The code in our function looks complicated because we used the variables that we defined previously inside our database blueprint.

"create table TABLENAME ( _id integer primary key autoincrement COLUMN1 COLUMN2 COLUMN2)"

Modifying the Model.

With the code to create our database and table in place ,now we need to modify our model so it uses our database instead of the hard-coded list of food objects.Open up your FoodModel.java file and take out all references to List<Food> as shown below.

 

package com.blikoon.imirire;

import android.content.Context;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by gakwaya on 2/10/2016.
 */

//This class is a singleton and supposed to only have
//one instance across the whole application.
public class FoodModel
{
    private static FoodModel sFoodModel;
//    private List<Food> mFoods;COMMENT OUT WHERE THE LIST OF FOODS IS DECLARED.

    public static FoodModel get(Context context)
    {
        if(sFoodModel == null)
        {
            sFoodModel = new FoodModel(context);
        }
        return  sFoodModel;
    }

    private FoodModel(Context context)
    {
//        mFoods = new ArrayList<>(); //COMMENT OUT WHERE THE LIST IS INSTANTIATED.
//        populateWithInitialFoods();//THIS FUNCTION REFERENCES THE LIST OF FOODS.WE TAKE IT OUT ALSO.
    }

    private void populateWithInitialFoods()
    {
          //COMMENT OUT THIS ENTIRE FUNCTION ,DON'T DELETE IT ,YOU WILL NEED IT SHORTLY
    }

    public List<Food> getFoods()
    {
        //return mFoods;
        return new ArrayList<>();//RETURN AN EMPTY LIST SO THE CODE COMPILES AND RUNS.
    }

}

Run your code to make sure it compiles.It will just show the display widget with an empty list view which is exactly what we expect.We took out the code that added food objects to the model.If your code doesn’t compile ,please check that you followed all the steps correctly or look at the finished source code referenced at the end of this tutorial.Now you need to instantiate your FoodOpenHelper to create the database in the constructor of our FoodModel.Change your FoodModel class as shown below.The changed code is highlighted in bold red.

FoodModel.java

package com.blikoon.imirire;

//IMPORTS HERE.

//This class is a singleton and supposed to only have
//one instance across the whole application.
public class FoodModel
{
    private static FoodModel sFoodModel;
//    private List<Food> mFoods;
    private SQLiteDatabase mDatabase;
    private Context mContext;

    private FoodModel(Context context)
    {
        mContext = context.getApplicationContext();
        mDatabase = new FoodOpenHelper(mContext).getWritableDatabase();

//        mFoods = new ArrayList<>();
//        populateWithInitialFoods();
    }
}

The line

mContext = context.getApplicationContext();

gets the context that is attached to the whole application scope.We use this one because if we use one attached to some activity ,when that activity gets destroyed at some time our database which was created using that context has a good chance of using dead references and crashing our application.The applicationContext lives as long as the app is running and gets rid of that problem.We then use that context to create our database as shown below.

mDatabase = new FoodOpenHelper(mContext).getWritableDatabase();

This line creates the database if it doesn’t exist already or simply returns an already existing one.We call getWritableDatabase() because we have the intention of using this reference to populate our database with data.There also is getReadableDatabase() if you were wondering.If you run your application ,your database should be created the instant you first try to get an instance of FoodModel which causes our database to be instantiated.After you have run your app ,hit the home button and go to Settings->Apps and scroll down to Imirire.

android_sqlite_database_created

You can now see that the data consumed by our app is no more zero as we saw at the start of this tutorial.Another more convincing way of proving that our database was created is ,from Android Studio to navigate Tools->Android->Android Device Monitor ,choose your device on the left ,click on File explored on the right and navigage to data->data->com.blikoon.imirire and you can see that inside there is a database folder.Open it as shown below.

android_sqlite_device_monitor

Remember that foodDb.db is the database name we specified in the constructor of FoodOpenHelper?A word about android device monitor.You won’t be able to see the files of your application inside device monitor unless (1) you are using a virtual device or (2) you are running on a device that has been rooted. The reason is that Android Device monitor needs some permissions to see those files and those permissions are taken out on normal unrooted android devices.Beware.

Adding Data to the Database.

To add data to the database ,you use a ContentValues object.ContentValues is a class that stores data in value key pairs ,something lie Maps or Hash data structure if you have used these before.A typical usage of ContentValues to insert data into the database is shown below.

ContentValues mValues = new ContentValues();
mValues.put("ROW_NAME1","VALUE");
mValues.put("ROW_NAME2","VALUE");

databaseObject.insert("TABLENAME",null, mValues)

We need a way to take our Food objects and turn them into ContentValues objects we can directly add to our database.Why not write a function inside the model to just do that?The function called getContentValues() is shown below.

private static ContentValues getContentValues(Food food)
{
    ContentValues values = new ContentValues();
    values.put(FoodDbSchema.FoodTable.Cols.UUID,food.getFoodId().toString());
    values.put(FoodDbSchema.FoodTable.Cols.NAME,food.getFoodName());
    values.put(FoodDbSchema.FoodTable.Cols.GRAM_COUNT, food.getGramCount());
    values.put(FoodDbSchema.FoodTable.Cols.CAL_COUNT,food.getCalCount());
    values.put(FoodDbSchema.FoodTable.Cols.IS_CHECKED,food.isChecked());

    return values;
}

The function takes a Food object ,retrieves its instance variables we want to save into the database and puts them in a ContentValues object we instantiate inside the function.The ContentValues object is returned by the function.Now we can use this function to add foods to our database.Create a public method of your FoodModel as shown below.

private FoodModel(Context context)
{
    ...
}

public void addFood(Food f)
{
    ContentValues values = getContentValues(f);
    mDatabase.insert(FoodDbSchema.FoodTable.NAME, null, values);
}

The function can now be used to revive your populateWithInitialFoods() function.Change it so that it adds foods the database as shown below.

private void populateWithInitialFoods()
{
    //Cereals
    Food food1 = new Food("Rice",0,"RiceIcon",351.0);
    addFood(food1);

    Food food2 = new Food("Peanuts",0,"RiceIcon",567.0);
    addFood(food2);

    .....

    Food food38 = new Food("Sugar",0,"RiceIcon",406.3);
    addFood(food38);

}

Change your FoodModel constructor to directly add data after the database is created.

private FoodModel(Context context)
{
    mContext = context.getApplicationContext();
    mDatabase = new FoodOpenHelper(mContext).getWritableDatabase();
    populateWithInitialFoods();
}

If you run your app ,you will see that ,in the Android Device Monitor ,the size of the file foodDb.db-journal has increased compared to our previous runs.The data consumed by our app in Settings->App has also increased as shown in the figure below.

android_sqlite_data_added

Hopefully ,this proves that our data is being successfully added to the database.

Reading Data From the Database.

Our data is being saved in the database but the listView but he model doesn’t yet update the view about what data is available in the model.The getFoods() function ,that the Adapter uses to know about data in the model is still returning an empty list.We need some way to read the data in the database ,turn that data into a list of Food objects that we return through the getFoods() function.But how do we read from the database?We user queries and Cursors.A visualization of our table is shown below so we can build a quick query example.

android_sqlite_imirire_dblook

We use a query to search for data inside a database.A query like the one below

database.query("foods",//THE TABLE NAME
        new String [] {"ID","FOODNAME","GRAMCOUNT","CALCOUNT","ISCHECKED"},//THE COLUMNS WE WANT TO LOOK AT
        "FOODNAME = ?",//WHERE CLAUSE
        new String [] {"Rice"},//WHERE ARGS.
        null,null,null);//IGNORE THESE THREE FOR THE MOMENT.

would go through the database whose name is “foods” ,look for entries with the FOODNAME of “Rice” and return a Cursor with the data for the row names specified in the second argument.Namely “ID”,”FOODNAME”,”GRAMCOUNT”,”CALCOUNT”,”ISCHECKED”.

The query method of the SQLiteDatabase has several overloads you can use depending on what you want to achieve but the simplest is shown below for reference.

query ( String table,//TABLE NAME
         String [] columns,//THE COLUMNS WHOSE DATA YOU WANT
         String  whereClause,//YOUR SELECTION CLAUSE(S)
         String [] whereArgs,//SELECTION ARGUMENTS
         String groupBy,//IGNORE FOR NOW
         String having,//IGNORE FOR NOW
         String orderBy//IGNORE FOR NOW
 )

We are interested in getting all the data in the database,so our query would look like

 mDatabase.query(
        FoodTable.NAME,
        null ,//Columns - null selects all columns
        null,//NULL CAUSES ALL THE ENTRIES TO BE RETURNED
        null,//NULL CAUSES ALL THE ENTRIES TO BE RETURNED
        null ,//groupBy
        null, //having
        null//orderBy
);

the querry method returns a Cursor object.A Cursor is like a pointer pointing to a certain row in the table of the database.Because we want our cursor to be able to directly return food objects ,we wrap it inside a CursorWrapper subclass.Create a class in your database package and call it FoodCursorWrapper.

FoodCursorWrapper.java

package com.blikoon.imirire.database;

import android.database.Cursor;
import android.database.CursorWrapper;

import com.blikoon.imirire.Food;

import java.util.UUID;

public class FoodCursorWrapper extends CursorWrapper {

    public FoodCursorWrapper(Cursor cursor)
    {
        super(cursor);
    }
}

Add a function to directly return a Food object from a CursorWrapper.

package com.blikoon.imirire.database;

public class FoodCursorWrapper extends CursorWrapper {

    public FoodCursorWrapper(Cursor cursor)
    {
        super(cursor);
    }

    public Food getFood()
    {
        String uuidString = getString(getColumnIndex(FoodDbSchema.FoodTable.Cols.UUID));
        String foodName = getString(getColumnIndex(FoodDbSchema.FoodTable.Cols.NAME));

        double gramCount = getDouble(getColumnIndex(FoodDbSchema.FoodTable.Cols.GRAM_COUNT));
        double calCount = getDouble(getColumnIndex(FoodDbSchema.FoodTable.Cols.CAL_COUNT));
        int isChecked = getInt(getColumnIndex(FoodDbSchema.FoodTable.Cols.IS_CHECKED));

        Food food = new Food(foodName,gramCount,"",calCount,UUID.fromString(uuidString));
        food.setIsChecked(isChecked != 0);

        return  food;

    }

}

Note that I have used a constructor that directly takes a UUID as argument and you should add it to your Food class for this code to work.

public Food(String foodName ,double gramCount,
            String foodIcon,double calCount,
            UUID uuid)
{
    mFoodName = foodName;
    mGramCount = gramCount;
    mCalCount=calCount;
    isChecked=false;
    mFoodId =uuid;
}

Our FoodCursorWrapper is in place and we can use it to navigate through the results returned by queries to our database.FoodCursorWrapper has functions like .moveToFirst() ,.moveToLast() ,moveToNext() that you can use to navigate to the exact row you want in the part of the database table returned by the query.Now we can go back to FoodModel class and add a function to do queries to the database as shown below.The function is called queryFoods() and it is right below the getContentValues() function we added previously and highlighted in bold red.

package com.blikoon.imirire;

public class FoodModel
{
  ...........

    private static ContentValues getContentValues(Food food)
    {
     ...........
    }

    private FoodCursorWrapper queryFoods(String whereClause ,String [] whereArgs)
    {
        Cursor cursor = mDatabase.query(
                FoodDbSchema.FoodTable.NAME,
                null ,//Columns - null selects all columns
                whereClause,
                whereArgs,
                null ,//groupBy
                null, //having
                null//orderBy
        );
        return new FoodCursorWrapper(cursor);
    }

}

The function simply wraps the query to the database so that we only pass in the whereClause and the whereArgs to fine tune our queries.If the function still doesn’t make much sense ,you should consult more focused documentation on SQLite database.The database web page is a good place to start.We use the queryFoods function to build the getFoods() function so our view has data to display.Add it as shown below.

package com.blikoon.imirire;
//IMPORTS
public class FoodModel
{


    private void populateWithInitialFoods()
    {
       ..........//CODE HERE OMMITED.
    }

    public List<Food> getFoods()
    {
        List <Food> foods = new ArrayList<>();

        FoodCursorWrapper cursor = queryFoods(null,null);
        try
        {
            cursor.moveToFirst();
            while( !cursor.isAfterLast())
            {
                foods.add(cursor.getFood());
                cursor.moveToNext();
            }

        }finally {
            cursor.close();
        }
        return foods;
    }

    private static ContentValues getContentValues(Food food)
    {
      .......//CODE HERE OMMITED
    }

    private FoodCursorWrapper queryFoods(String whereClause ,String [] whereArgs)
    {
        ........//CODE HERE OMITTED.
    }
}

The functions first declares a list of foods and instantiates it.We then call the queryFoods() function .

FoodCursorWrapper cursor = queryFoods(null,null);

Passing in null parameters ,this means that we have no specific selections and that we are interested in the entirety of the database.The returned cursor can be used to navigate through the entire database.We move the cursor to the first element and then go through all of them using the current cursor position to retrieve food items and add them to the list we just created.At the end ,the list of foods is returned.If you run your app ,you’ll see that the RecyclerView is now displaying data.

android_sqlite_read_database

Updating the Database with most Recent Data.

The data in the database is being read correctly and that is nice.But when the user changes food data ,lets say he clicks on the gram input field for Sugar in the figure above and inputs 100 grams ,we want for that data to be saved in the database ,so that when later ,when the view needs to display the sugar item ,it reads in a correct value.The SQLiteDatabase defines an update function that looks something like the code snippet below.

database.update(String tablename,ContentValues values,
        String whereClause,
        String[] whereArgs)

With this in mind ,add a function to update Food data in the database to your FoodModel class as shown below.

public void updateFood(Food f)
{
    String uuidString = f.getFoodId().toString();
    ContentValues values = getContentValues(f);
    mDatabase.update(FoodTable.NAME, values,
            FoodTable.Cols.UUID + " = ?",
            new String[]{uuidString});
}

The function takes the Food object passed as argument ,retrieves its UUID ,turns it into a String.We then turn the Food objects into ContentValues and call the update method on mDatabase ,our SQLiteDatabase instance.We use the UUID of the food object to pinpoint the correct row in the database to update.We update the database whenever Food data changes ,and currently that happens only in two places ,when the user clicks on the check box of a list item or when the user changes the data in the gram input field of the list item.Updating data in the listeners attached to foodItemCheckBox and gramInputField in MainActivity.java make sense.The code snippet below shows the changes we applied highlighted in bold red.

foodItemCheckBox.setOnCheckedChangeListener(new CompoundButton.OnCheckedChangeListener() {
    @Override
    public void onCheckedChanged(CompoundButton buttonView, boolean isChecked) {

        mFood.setIsChecked(isChecked);
        gramInputField.setText(String.valueOf(""));
        if (!isChecked) {
            //If the user unchecks a food
            // 1.clear its gram input field
            // 2.Set the food gram count to 0
            mFood.setGramCount(0.0);
            // mAdapter.notifyDataSetChanged();
            gramInputField.setText(String.valueOf(0.0));

        }
        FoodModel.get(getBaseContext()).updateFood(mFood);
        updateDisplay();
    }
});

foodNameTextView = (TextView) itemView.findViewById(R.id.list_item_food_name);
gramInputField = (EditText) itemView.findViewById(R.id.list_item_gram_input_field);
gramInputField.addTextChangedListener(new TextWatcher() {
    @Override
    public void beforeTextChanged(CharSequence s, int start, int count, int after) {

    }

    @Override
    public void onTextChanged(CharSequence s, int start, int before, int count) {

    }

    @Override
    public void afterTextChanged(Editable s) {
        String dataString = s.toString();

        if(dataString.isEmpty())
        {
            Log.d("Imirire","Empty string");
            if(gramInputField.hasFocus())
            {
                Log.d("Imirire","gramInputField Has Focus");
                mFood.setGramCount(0.0);
                FoodModel.get(getBaseContext()).updateFood(mFood);
                updateDisplay();

            }
        }else
        {

            double itemGramCount = Double.parseDouble(s.toString());
            mFood.setGramCount(itemGramCount);
            FoodModel.get(getBaseContext()).updateFood(mFood);
            updateDisplay();

        }

    }
});

We simply call the updateFood function on the singleton FoodModel class.Run your application and you should have your data saved to the database when you apply any change.

We covered how to create the database ,add data to it and update the already available data.The remaining operation we didn’t touch is deleting data and deleting the database.We will look at those when we add the new feature for users to add new Foods in next tutorials.The finalized source code for this tutorial is available at my git repository.I hope this has been helpful to you and thanks for reading.

Posted in android, Tutorials and tagged , , .

Daniel Gakwaya loves computer Hardware/Software.He is a Software Engineer at BLIKOON and lead developer of bliboard-The whiteboard system currently marketed by the company.He is known to hack around on any piece of tech that happens to pick his interest. More on his tech endeavors here
Follow him on Twitter

Leave a Reply

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

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.