Android cache SQLite

You can optimize your app performance by using cache data in Android using SQLite. We will be using Stetho library to see the cached data.

Basic use of cache in SQLite is to display frequently accessed data faster in your android app. And to reduce the overhead on the server. There are already tons of libraries available that automatically handles the API cache data for you. But nothing gives more control than writing the code by yourself. So in this tutorial, we will create a custom cache mechanism in SQLite. So we can decide when to expire the cache and which data needs to be cached in android system and which should not.

Volley default cache

By default, Volley does provide a method to cache the API response data. But it only caches the response which is already been received using Volley request. Consider this, you have a list of employee records which are being fetched from API. And then you have another API which receives employee ID and gives you detail about that employee. If you are using Volley’s cache mechanism then it only displays that employee data faster whose details you have already been seen. But using your custom technique, you can save all employees data in the SQLite cache. And get the detail of any employee using their ID.

1. Create an API

You might have your own API code based on your project, but for sake of this tutorial we have create a basic API which connects with database. Fetch all rows from employees table, push the data in an array and send the response back in JSON. We could have use the mysqli_fetch_all() function but it is not supported in some of the PHP versions. So following this technique you will be able to use this without having to worry about version of PHP.

<?php

$conn = mysqli_connect("localhost", "root", "", "classicmodels");
$result = mysqli_query($conn, "SELECT * FROM employees");

$data = array();
while ($row = mysqli_fetch_object($result))
{
    array_push($data, $row);
}
echo json_encode($data);

?>

Setup android project

We have created a new android project to demonstrate the working of SQLite cache. But you might be implementing this in your own project. So we will only discuss the libraries you need to install. And some minor settings you need to do in some of your project files. Open your app > build.gradle file and paste the following dependencies in it:

implementation 'com.android.support:design:28.0.0'
implementation 'com.android.support:cardview-v7:28.0.0'
implementation 'com.android.support:recyclerview-v7:28.0.0'
implementation 'com.android.volley:volley:1.1.1'
implementation 'com.google.code.gson:gson:2.8.5'
implementation 'com.facebook.stetho:stetho:1.5.1'

You can change the version number as per your project’s target SDK version. Android studio will highlight the dependencies if there is an update in Volley, Gson and Stetho libraries. After adding these dependencies, you need to sync the project. Then add internet permission in your AndroidManifest.xml file before starting of application tag. This will help you to call HTTP requests:

<uses-permission android:name="android.permission.INTERNET" />

Stetho library will be used to display SQLite inside google chrome inspect tools. Create a new class named MyApplication.java and extend it with Application:

package com.adnan.app.sqlitecache;

import android.app.Application;
import com.facebook.stetho.Stetho;

public class MyApplication extends Application {

    @Override
    public void onCreate() {
        super.onCreate();
        Stetho.initializeWithDefaults(this);
    }
}

This will initialize the Stetho library and whenever you have some data in SQLite database tables, you will be able to see it by running chrome://inspect in your browser address bar (where you put website URL). Then set this class as application main class by giving name attribute to application tag in AndroidManifest.xml:

<application
        android:name=".MyApplication"

Setup layouts

Now we will setup the layout to create a recycler view where all employees data will be displayed. Also an adapter layout where each single employee data will be displayed. Create an adapter class which will hold each employee item, and a model class which tells the structure of data received from server. Create a recycler view in your activity layout file and get its instance in activity class file and initialize it.

EmployeeModel.java

package com.adnan.app.sqlitecache.models;

public class EmployeeModel {

    private String firstName;

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
}

EmployeeAdapter.java

package com.adnan.app.sqlitecache.adapters;

import android.content.Context;
import android.support.annotation.NonNull;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;

import com.adnan.app.sqlitecache.R;
import com.adnan.app.sqlitecache.models.EmployeeModel;

import java.util.ArrayList;

public class EmployeeAdapter extends RecyclerView.Adapter {

    private Context context;
    private ArrayList models;

    public EmployeeAdapter(Context context, ArrayList models) {
        this.context = context;
        this.models = models;
    }

    @Override
    public ViewHolder onCreateViewHolder(@NonNull ViewGroup viewGroup, int i) {
        View view = LayoutInflater.from(context).inflate(R.layout.single_employee, viewGroup, false);
        return new ViewHolder(view);
    }

    @Override
    public void onBindViewHolder(@NonNull ViewHolder viewHolder, int i) {
        viewHolder.name.setText(models.get(i).getFirstName());
    }

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

    public class ViewHolder extends RecyclerView.ViewHolder {
        private TextView name;

        public ViewHolder(@NonNull View itemView) {
            super(itemView);

            name = itemView.findViewById(R.id.name);
        }
    }
}

We are passing the activity context and models array from activity to adapter using adapter’s constructor. Context will help to setup the single layout file and models array helps to tell how many items should be rendered in this adapter and each item’s index.

single_employee.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="100dp"
    android:layout_margin="20dp">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Name"
        android:id="@+id/name"/>

</RelativeLayout>

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <android.support.v7.widget.RecyclerView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:id="@+id/rv" />

</RelativeLayout>

MainActivity.java

public class MainActivity extends AppCompatActivity {
	private RecyclerView rv;
	private EmployeeAdapter adapter;
	private ArrayList models;

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

		rv = findViewById(R.id.rv);
		rv.setHasFixedSize(true);

		LinearLayoutManager layoutManager = new LinearLayoutManager(this);
		rv.setLayoutManager(layoutManager);

		models = new ArrayList<>();
		adapter = new EmployeeAdapter(this, models);
		rv.setAdapter(adapter);
	}
}

Get data from API

We will be using Volley library to send an HTTP request to API and fetch response as JSON string. Make sure you have added Volley dependency and synced the project. Also, make sure you have added internet permission in your android manifest. Create a new method in activity class and call it from onCreate method after the adapter has been set in recycler view.

private void getData() {
	String url = "Your API URL";
	RequestQueue requestQueue = Volley.newRequestQueue(this);

	StringRequest stringRequest = new StringRequest(Request.Method.GET, url, new Response.Listener<String>() {
		@Override
		public void onResponse(String response) {
			Log.i("my_log", response);
		}
	}, new Response.ErrorListener() {
		@Override
		public void onErrorResponse(VolleyError error) {
			Log.i("my_log", error.getMessage());
		}
	});

	requestQueue.add(stringRequest);
}

Here our request method is GET and you can place your API URL in a variable named url. At this point, if you run the app in debug mode and open your logcat, you will be able to see the response after a few seconds (depends on the server and query execution time). If you saw any error in logcat, make sure you have added internet permission and double-check your API URL set in url variable.

This function will be called when the app is ran for the first time. Because for the first time, the android cache will empty since there is no data in SQLite database.

Mirror android to PC or Mac

Sometimes you might want to see your android device in your PC or Mac. You can use Vysor app which is available for both Mac and Windows, however, we do not recommend that because of too many pop-up ads. But we recommend to use the scrcpy library. You can install it from instructions on GitHub site and to run it, simply attach your android device and run the following command in your terminal:

> scrcpy

Convert JSON to ArrayList

We will be using Gson library to convert JSON string into Java array list. In onResponse of Volley request, create a try catch block because converting JSON to array will throw an exception if the data is not in correct JSON format. Even if you have any special character in your database, it will not be able to parse JSON data. We need to use the TypeToken class to convert json to array, if it were simple object then we would have simply used EmployeeModel.class but in case of array, we have to use TypeToken.

try {
	Gson gson = new Gson();
	Type type = new TypeToken<ArrayList<EmployeeModel>>() {}.getType();
	models = gson.fromJson(response, type);
} catch (Exception e) {
	Log.i("my_log", e.getMessage());
}

Filter data in adapter

Create a new method named showData() in activity and call it after the JSON has been converted into array list of models.

MainActivity.java

private void showData() {
	adapter.setFilter(models);
}

And in adapter add a new method which will remove old model data and add new data, and update the adapter to render the items again. The reason we are doing this is because at the time of assigning adapter to recycler view, our arraylist was empty. So if we do not use this method, then we will not be able to see any data in recycler view.

EmployeeAdapter.java

public void setFilter(ArrayList data) {
	this.models.clear();
	this.models.addAll(data);
	notifyDataSetChanged();
}

Show progress bar

If you want to display progress bar inside each adapter item, you can place this progress bar tag in your adapter layout file. This is typically useful when you are displaying images from HTTP, as they may take some time to display, so you can show a progress bar when the image is fully loaded. But here we will be displaying one progress bar in center of the screen, so we are creating progress bar tag in activity layout.

activity_main.xml

<ProgressBar
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerInParent="true"
        android:id="@+id/progressBar"/>

MainActivity.java

private ProgressBar progressBar;

// in onCreate after setContentView
progressBar = findViewById(R.id.progressBar);

// update showData method
private void showData() {
	adapter.setFilter(models);
	progressBar.setVisibility(View.GONE);
}

Setup SQLite

Create a new class named SQLiteManager and extend it from SQLiteOpenHelper. It has 2 abstract methods, onCreate and onUpgrade so you must implement them in your class. onCreate will be called only once, but onUpgrade will be called whenever you change your database version. Create a constructor for this class and call the super constructor, in super constructor we will tell the database name and version. In onCreate method, you will set your all tables structure (DDL). You can perform any function you want in onUpgrade method, but the common practice is, since we are upgrading the database version, it means that we may have remove some columns and have added some new columns, may be created a new table altogether. So the best practice is to remove all old tables structures and all data in them. Then call the onCreate method manually.

SQLiteManager.java

package com.adnan.app.sqlitecache.managers;

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

import com.adnan.app.sqlitecache.models.EmployeeModel;
import java.util.ArrayList;

public class SQLiteManager extends SQLiteOpenHelper {

    public SQLiteManager(Context context) {
        super(context, "android_cache", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE IF NOT EXISTS employees(" +
                "name TEXT NOT NULL)";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE employees");
        onCreate(db);
    }
}

Insert data in SQLite

Add the following method in your SQLiteManager class:

SQLiteManager.java

public void addData(EmployeeModel employeeModel) {
	SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
	ContentValues contentValues = new ContentValues();

	contentValues.put("name", employeeModel.getFirstName());
	sqLiteDatabase.insert("employees", null, contentValues);
	sqLiteDatabase.close();
}

Now in your activity class, in Volley onResponse method, when the JSON response has been converted to array list models, we will loop through all data in array and call this function for each employee. So create an instance of SQLiteManager class and initialize it and do the following:

MainActivity.java

private SQLiteManager sqLiteManager;

// in onCreate
sqLiteManager = new SQLiteManager(this);

// in Volley onResponse after models = gson.fromJson(response, type);
for (int a = 0; a < models.size(); a++) {
	sqLiteManager.addData(models.get(a));
}

This will add the data in SQLite database whenever we call an API request. But this will always append new data, so we need to find a way to delete old data whenever new data is received from API.

Delete data from SQLite

Create the following function in your SQLiteManager class:

SQLiteManager.java

public void deleteOldCache() {
	SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
	sqLiteDatabase.execSQL("DELETE FROM employees");
	sqLiteDatabase.close();
}

And call this function from your activity class:

MainActivity.java

// in Volley onResponse before for (int a = 0; a < models.size(); a++) and after models = gson.fromJson(response, type);
sqLiteManager.deleteOldCache();

View data from SQLite

At this point, the data has been saved correctly in SQLite. Now we need to make the app to read from SQLite database if there is any data, otherwise the data will be fetched from API. So the first time when app gets installed, it will not have any data in SQLite, so it will fetch from API and save in SQLite. Next time it will found the data, so it will read it from SQLite instead of sending the API request again.

SQLiteManager.java

public ArrayList getData() {
	ArrayList data = new ArrayList<>();
	SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
	Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM employees", null);

	if (cursor.moveToFirst()) {
		do {
			EmployeeModel employeeModel = new EmployeeModel();
			employeeModel.setFirstName(cursor.getString(0));
			data.add(employeeModel);
		} while (cursor.moveToNext());
	}

	return data;
}

MainActivity.java

ArrayList cache = sqLiteManager.getData();

if (cache.size() > 0) {
	models = cache;
	showData();
} else {
	getData();
}

Now it will only fetch the data from API once and saved it in SQLite. Next time it will fetch from SQLite rather than from API. But the only problem is, it will always fetch it from SQLite. So we need to find a way to expire the cache after some time.

Set cache expiry time

You can set the expiry time of cache in simple seconds and you can do the math of converting seconds into minutes and hours and days etc. For example, if you want to expire the cache after 18 hours, you can simply do (60 * 60 * 18 = 64800). We will be using android shared preferences to store the time when data was cached or saved in SQLite. Then before checking if to get data from cache or from API, we need to check if the cache has been expired. We can do that by taking the difference between current time and the time when data was cached. Since Java date function returns time in milliseconds, so we can simply convert them to seconds by dividing them with 1000. Then our condition will say:

Check if there is any data in cache AND the cache is not expired.

MainActivity.java

private SharedPreferences preferences;

// in onCreate
preferences = PreferenceManager.getDefaultSharedPreferences(this);

boolean isCacheExpire = false;
long cacheTime = preferences.getLong("cache", 0);

if (cacheTime > 0) {
	long currentTime = new Date().getTime();
	long difference = currentTime - cacheTime;
	long seconds = difference / 1000;

	if (seconds > 20) {
		isCacheExpire = true;
	}
}

if (cache.size() > 0 && !isCacheExpire) {
	models = cache;
	showData();
} else {
	getData();
}

And set the cache time in shared preference in Volley onResponse method, after data has been saved in SQLite:

preferences.edit().putLong("cache", new Date().getTime()).apply();

Complete MainActivity.java

package com.adnan.app.sqlitecache;

import android.content.SharedPreferences;
import android.preference.PreferenceManager;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.util.Log;
import android.view.View;
import android.widget.ProgressBar;

import com.adnan.app.sqlitecache.adapters.EmployeeAdapter;
import com.adnan.app.sqlitecache.managers.SQLiteManager;
import com.adnan.app.sqlitecache.models.EmployeeModel;
import com.android.volley.Request;
import com.android.volley.RequestQueue;
import com.android.volley.Response;
import com.android.volley.VolleyError;
import com.android.volley.toolbox.StringRequest;
import com.android.volley.toolbox.Volley;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;

import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Date;

public class MainActivity extends AppCompatActivity {
    private RecyclerView rv;
    private EmployeeAdapter adapter;
    private ArrayList models;
    private ProgressBar progressBar;
    private SQLiteManager sqLiteManager;
    private SharedPreferences preferences;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        progressBar = findViewById(R.id.progressBar);
        sqLiteManager = new SQLiteManager(this);
        preferences = PreferenceManager.getDefaultSharedPreferences(this);

        rv = findViewById(R.id.rv);
        rv.setHasFixedSize(true);

        LinearLayoutManager layoutManager = new LinearLayoutManager(this);
        rv.setLayoutManager(layoutManager);

        models = new ArrayList<>();
        adapter = new EmployeeAdapter(this, models);
        rv.setAdapter(adapter);

        ArrayList cache = sqLiteManager.getData();
        boolean isCacheExpire = false;
        long cacheTime = preferences.getLong("cache", 0);

        if (cacheTime > 0) {
            long currentTime = new Date().getTime();
            long difference = currentTime - cacheTime;
            long seconds = difference / 1000;

            if (seconds > 20) {
                isCacheExpire = true;
            }
        }

        if (cache.size() > 0 && !isCacheExpire) {
            models = cache;
            showData();
        } else {
            getData();
        }
    }

    private void getData() {
        String url = "Your API URL here";
        RequestQueue requestQueue = Volley.newRequestQueue(this);

        StringRequest stringRequest = new StringRequest(Request.Method.GET, url, new Response.Listener() {
            @Override
            public void onResponse(String response) {
                try {
                    Gson gson = new Gson();
                    Type type = new TypeToken<ArrayList<EmployeeModel>>() {

                    }.getType();
                    models = gson.fromJson(response, type);
                    sqLiteManager.deleteOldCache();

                    for (int a = 0; a < models.size(); a++) {
                        sqLiteManager.addData(models.get(a));
                    }

                    preferences.edit().putLong("cache", new Date().getTime()).apply();

                    showData();
                } catch (Exception e) {
                    Log.i("my_log", e.getMessage());
                }
            }
        }, new Response.ErrorListener() {
            @Override
            public void onErrorResponse(VolleyError error) {
                Log.i("my_log", error.getMessage());
            }
        });

        stringRequest.setShouldCache(false);
        requestQueue.add(stringRequest);
    }

    private void showData() {
        adapter.setFilter(models);
        progressBar.setVisibility(View.GONE);
    }
}

Run the app now. And you are all set in implementing SQLite cache in your android app. We have used android SQLite to save the data in the cache. But you can also use android’s shared preferences if the data that needs to be cached is relatively small.

[wpdm_package id=’211′]