12.4 DrySister View Girls App (Version 1) – 4. Adding Data Caching (Using SQLite)

Category Android Basic Tutorial

1. Some Intro

In the previous section, we created an asynchronous image loading and caching framework for DrySister – SisterLoader (Girl Loader). It successfully cached images from the network on disk and in memory. When we disconnect from the network, we can still view these images. However, you might have noticed an awkward issue: when we enter the app with an internet connection, we get image-related information such as URLs. If we exit the app, disconnect from the internet, and then re-enter, the images won't load because we don't have the URLs for the cached images, making it somewhat useless. Therefore, we need to store the data returned by the backend interface. Each time we load data, we store the returned information locally, and when there's no network, we can load the local data! So, in this section, we will add SQLite to DrySister to store related data. The tasks are clear, let's get started~

2. Start Coding

Create a new db branch on develop to write the code for this section.

First, add a utility class to check the network status: NetworkUtils.java, with a simple method to check if the network is available. We also need to add a permission in AndroidManifest.xml: android.permission.ACCESS.

public class NetworkUtils {
    /** Get network information */
    private static NetworkInfo getActiveNetworkInfo(Context context) {
        ConnectivityManager cm = (ConnectivityManager) context
                .getSystemService(Context.CONNECTIVITY_SERVICE);
        return cm.getActiveNetworkInfo();
    }

    /** Check if network is available */
    public static boolean isAvailable(Context context) {
        NetworkInfo info = getActiveNetworkInfo(context);
        return info != null && info.isAvailable();
    }
}

Next, define a class for database field constants: TableDefine.java, where we write the database name and fields:

public class TableDefine {
    public static final String TABLE_FULI = "fuli";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_FULI_ID = "_id";
    public static final String COLUMN_FULI_CREATEAT = "createAt";
    public static final String COLUMN_FULI_DESC = "desc";
    public static final String COLUMN_FULI_PUBLISHEDAT = "publishedAt";
    public static final String COLUMN_FULI_SOURCE = "source";
    public static final String COLUMN_FULI_TYPE = "type";
    public static final String COLUMN_FULI_URL = "url";
    public static final String COLUMN_FULI_USED = "used";
    public static final String COLUMN_FULI_WHO = "who";
}

Then, write the database creation class: SisterOpenHelper.java, to create the database:

public class SisterOpenHelper extends SQLiteOpenHelper{

    private static final String DB_NAME = "sister.db";  // Database name
    private static final int DB_VERSION = 1;    // Database version number

    public SisterOpenHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTableSql = "CREATE TABLE IF NOT EXISTS " + TableDefine.TABLE_FULI + " ("
                + TableDefine.COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + TableDefine.COLUMN_FULI_ID + " TEXT, "
                + TableDefine.COLUMN_FULI_CREATEAT + " TEXT, "
                + TableDefine.COLUMN_FULI_DESC + " TEXT, "
                + TableDefine.COLUMN_FULI_PUBLISHEDAT + " TEXT, "
                + TableDefine.COLUMN_FULI_SOURCE + " TEXT, "
                + TableDefine.COLUMN_FULI_TYPE + " TEXT, "
                + TableDefine.COLUMN_FULI_URL + " TEXT, "
                + TableDefine.COLUMN_FULI_USED + " BOOLEAN, "
                + TableDefine.COLUMN_FULI_WHO + " TEXT"
                + ")";
        db.execSQL(createTableSql);
    }

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

Next, we need to write a database operation class for adding, deleting, updating, querying, and paginating operations. We'll make this class a singleton: SisterDBHelper.java, with methods like these:

public class SisterDBHelper {

    private static final String TAG = "SisterDBHelper";

    private static SisterDBHelper dbHelper;
    private SisterOpenHelper sqlHelper;
    private SQLiteDatabase db;

    private SisterDBHelper() {
        sqlHelper = new SisterOpenHelper(DrySisterApp.getContext());
    }

    /** Singleton */
    public static SisterDBHelper getInstance() {
        if(dbHelper == null) {
            synchronized (SisterDBHelper.class) {
                if(dbHelper == null) {
                    dbHelper = new SisterDBHelper();
                }
            }
        }
        return dbHelper;
    }

    /** Insert a girl */
    public void insertSister(Sister sister) {
        db = getWritableDB();
        ContentValues contentValues = new ContentValues();
        contentValues.put(TableDefine.COLUMN_FULI_ID,sister.get_id());
        contentValues.put(TableDefine.COLUMN_FULI_CREATEAT,sister.getCreateAt());
        contentValues.put(TableDefine.COLUMN_FULI_DESC,sister.getDesc());
        contentValues.put(TableDefine.COLUMN_FULI_PUBLISHEDAT,sister.getPublishedAt());
        contentValues.put(TableDefine.COLUMN_FULI_SOURCE,sister.getSource());
        contentValues.put(TableDefine.COLUMN_FULI_TYPE,sister.getType());
        contentValues.put(TableDefine.COLUMN_FULI_URL,sister.getUrl());
        contentValues.put(TableDefine.COLUMN_FULI_USED,sister.getUsed());
        contentValues.put(TableDefine.COLUMN_FULI_WHO,sister.getWho());
        db.insert(TableDefine.TABLE_FULI,null,contentValues);
        closeIO(null);
    }

    /** Insert a bunch of girls (using transaction) */
    public void insertSisters(ArrayList<Sister> sisters) {
        db = getWritableDB();
        db.beginTransaction();
        try{
            for (Sister sister: sisters) {
                ContentValues contentValues = new ContentValues();
                contentValues.put(TableDefine.COLUMN_FULI_ID,sister.get_id());

contentValues.put(TableDefine.COLUMN_FULI_CREATEAT, sister.getCreateAt());
contentValues.put(TableDefine.COLUMN_FULI_DESC, sister.getDesc());
contentValues.put(TableDefine.COLUMN_FULI_PUBLISHEDAT, sister.getPublishedAt());
contentValues.put(TableDefine.COLUMN_FULI_SOURCE, sister.getSource());
contentValues.put(TableDefine.COLUMN_FULI_TYPE, sister.getType());
contentValues.put(TableDefine.COLUMN_FULI_URL, sister.getUrl());
contentValues.put(TableDefine.COLUMN_FULI_USED, sister.getUsed());
contentValues.put(TableDefine.COLUMN_FULI_WHO, sister.getWho());
db.insert(TableDefine.TABLE_FULI, null, contentValues);
}
db.setTransactionSuccessful();
} finally {
if (db != null && db.isOpen()) {
db.endTransaction();
closeIO(null);
}
}
}

/** Delete a sister (by _id) */
public void deleteSister(String _id) {
db = getWritableDB();
db.delete(TableDefine.TABLE_FULI, "_id =?", new String[]{_id});
closeIO(null);
}

/** Delete all sisters */
public void deleteAllSisters() {
db = getWritableDB();
db.delete(TableDefine.TABLE_FULI, null, null);
closeIO(null);
}

/** Update sister information (by _id) */
public void updateSister(String _id, Sister sister) {
db = getWritableDB();
ContentValues contentValues = new ContentValues();
contentValues.put(TableDefine.COLUMN_FULI_ID, sister.get_id());
contentValues.put(TableDefine.COLUMN_FULI_CREATEAT, sister.getCreateAt());
contentValues.put(TableDefine.COLUMN_FULI_DESC, sister.getDesc());
contentValues.put(TableDefine.COLUMN_FULI_PUBLISHEDAT, sister.getPublishedAt());
contentValues.put(TableDefine.COLUMN_FULI_SOURCE, sister.getSource());
contentValues.put(TableDefine.COLUMN_FULI_TYPE, sister.getType());
contentValues.put(TableDefine.COLUMN_FULI_URL, sister.getUrl());
contentValues.put(TableDefine.COLUMN_FULI_USED, sister.getUsed());
contentValues.put(TableDefine.COLUMN_FULI_WHO, sister.getWho());
db.update(TableDefine.TABLE_FULI, contentValues, "_id =?", new String[]{_id});
closeIO(null);
}

/** Query the number of sisters in the current table */
public int getSistersCount() {
db = getReadableDB();
Cursor cursor = db.rawQuery("SELECT COUNT (*) FROM " + TableDefine.TABLE_FULI, null);
cursor.moveToFirst();
int count = cursor.getInt(0);
Log.v(TAG, "count: " + count);
closeIO(cursor);
return count;
}

/** Paginated query of sisters, parameters are the current page and the number per page, page number starts from 0 */
public List<Sister> getSistersLimit(int curPage, int limit) {
db = getReadableDB();
List<Sister> sisters = new ArrayList<>();
String startPos = String.valueOf(curPage * limit); // Data start position
if (db != null) {
Cursor cursor = db.query(TableDefine.TABLE_FULI, new String[]{
TableDefine.COLUMN_FULI_ID, TableDefine.COLUMN_FULI_CREATEAT,
TableDefine.COLUMN_FULI_DESC, TableDefine.COLUMN_FULI_PUBLISHEDAT,
TableDefine.COLUMN_FULI_SOURCE, TableDefine.COLUMN_FULI_TYPE,
TableDefine.COLUMN_FULI_URL, TableDefine.COLUMN_FULI_USED,
TableDefine.COLUMN_FULI_WHO,
}, null, null, null, null, TableDefine.COLUMN_ID, startPos + "," + limit);
while (cursor.moveToNext()) {
Sister sister = new Sister();
sister.set_id(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_ID)));
sister.setCreateAt(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_CREATEAT)));
sister.setDesc(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_DESC)));
sister.setPublishedAt(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_PUBLISHEDAT)));
sister.setSource(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_SOURCE)));
sister.setType(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_TYPE)));
sister.setUrl(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_URL)));
sister.setUsed(cursor.getInt(cursor.getColumnIndex(TableDefine.COLUMN_FULI_USED)));
sisters.add(sister);
}
closeIO(cursor);
}
return sisters;
}

/** Query all sisters */
public List<Sister> getAllSisters() {
db = getReadableDB();
List<Sister> sisters = new ArrayList<>();
Cursor cursor = db.rawQuery("SELECT * FROM " + TableDefine.TABLE_FULI, null);
cursor.moveToFirst();
while (cursor.moveToNext()) {
Sister sister = new Sister();
sister.set_id(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_ID)));
sister.setCreateAt(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_CREATEAT)));

sister.setDesc(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_DESC)));
sister.setPublishedAt(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_PUBLISHEDAT)));
sister.setSource(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_SOURCE)));
sister.setType(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_TYPE)));
sister.setUrl(cursor.getString(cursor.getColumnIndex(TableDefine.COLUMN_FULI_URL)));
sister.setUsed(cursor.getInt(cursor.getColumnIndex(TableDefine.COLUMN_FULI_USED)));
sisters.add(sister);
}
closeIO(cursor);
return sisters;
}

/** Method to get writable database */
private SQLiteDatabase getWritableDB() {
return sqlHelper.getWritableDatabase();
}

/** Method to get readable database */
private SQLiteDatabase getReadableDB() {
return sqlHelper.getReadableDatabase();
}

/** Method to close cursor and database */
private void closeIO(Cursor cursor) {
if(cursor != null) {
cursor.close();
}
if(db != null) {
db.close();
}
}

}

It's quite straightforward, just some SQLite method calls. Next, let's modify the layout by changing activity_main.xml to the following code, which includes previous and next buttons:

<?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="match_parent">

<Button
android:id="@+id/btn_previous"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentBottom="true"
android:visibility="invisible"
android:text="Previous"/>

<Button
android:id="@+id/btn_next"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentBottom="true"
android:layout_toRightOf="@id/btn_previous"
android:text="Next"/>

<ImageView
android:id="@+id/img_show"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_above="@+id/btn_previous"/>

</RelativeLayout>

Finally, some logic changes are needed in MainActivity.java, focusing on:

1. Control and handling of curPos and page
1. Loading data from the network or database based on network availability
1. Avoiding duplicate insertion when storing network data into the database, etc.

Implementation:

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private Button previousBtn;
private Button nextBtn;
private ImageView showImg;

private ArrayList<Sister> data;
private int curPos = 0; // Current position of the displayed image
private int page = 1;   // Current page number
private PictureLoader loader;
private SisterApi sisterApi;
private SisterTask sisterTask;
private SisterLoader mLoader;
private SisterDBHelper mDbHelper;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
sisterApi = new SisterApi();
loader = new PictureLoader();
mLoader = SisterLoader.getInstance(MainActivity.this);
mDbHelper = SisterDBHelper.getInstance();
initData();
initUI();
}

private void initData() {
data = new ArrayList<>();
sisterTask = new SisterTask();
sisterTask.execute();
}

private void initUI() {
previousBtn = (Button) findViewById(R.id.btn_previous);
nextBtn = (Button) findViewById(R.id.btn_next);
showImg = (ImageView) findViewById(R.id.img_show);

previousBtn.setOnClickListener(this);
nextBtn.setOnClickListener(this);
}

@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_previous:
--curPos;
if (curPos == 0) {
previousBtn.setVisibility(View.INVISIBLE);
}
if (curPos == data.size() - 1) {
sisterTask = new SisterTask();
sisterTask.execute();
} else if(curPos < data.size()) {
mLoader.bindBitmap(data.get(curPos).getUrl(), showImg, 400, 400);
}
break;
case R.id.btn_next:
previousBtn.setVisibility(View.VISIBLE);
if(curPos < data.size()) {
++curPos;
}
if (curPos > data.size() - 1) {
sisterTask = new SisterTask();
sisterTask.execute();
} else if(curPos < data.size()){
mLoader.bindBitmap(data.get(curPos).getUrl(), showImg, 400, 400);
}
break;
}
}

private class SisterTask extends AsyncTask&lt;Void, Void, ArrayList<Sister>> {

public SisterTask() {
}

@Override
protected ArrayList<Sister> doInBackground(Void... params) {
ArrayList<Sister> result = new ArrayList<>();
if (page < (curPos + 1) / 10 + 1) {
++page;
}
// Check if network is available
if (NetworkUtils.isAvailable(getApplicationContext())) {

result = sisterApi.fetchSister(10, page);
// Query the database for the number of sisters to avoid duplicate insertion
if (mDbHelper.getSistersCount() / 10 < page) {
    mDbHelper.insertSisters(result);
} else {
    result.clear();
    result.addAll(mDbHelper.getSistersLimit(page - 1, 10));
}
return result;
}

@Override
protected void onPostExecute(ArrayList<Sister> sisters) {
    super.onPostExecute(sisters);
    data.addAll(sisters);
    if (data.size() > 0 && curPos + 1 < data.size()) {
        mLoader.bindBitmap(data.get(curPos).getUrl(), showImg, 400, 400);
    }
}

@Override
protected void onCancelled() {
    super.onCancelled();
    sisterTask = null;
}
}

@Override
protected void onDestroy() {
    super.onDestroy();
    if (sisterTask != null) {
        sisterTask.cancel(true);
    }
}
}

3. Running Effect Diagram

Operation Steps:

After starting the project, keep pressing the "Next Page" button to cache a bunch of images. Then disconnect the network and enter DrySister. Duang~, magically, sisters appear, and you can switch pictures by pressing "Next Page" or "Previous Page". Mom no longer has to worry about me not being able to view DrySister when there's no internet!

Running Screenshots:

4. Summary

Alright, this section added SQLite to save backend data on top of the previous section, allowing you to view sister pictures even without an internet connection. The major code changes are as described above, with some minor tweaks, see the code for details. Finally, merge the db branch into the develop branch, then delete the db branch, and push the updated develop branch to Github!

Code Download:

https://github.com/coder-pig/DrySister/tree/develop Feel free to follow, star, and if you have any suggestions, please raise an issue!

-1.0 Android Basic Introduction Tutorial

-1.0.1 2015 Latest Android Basic Introduction Tutorial Table of Contents

-1.1 Background and System Architecture Analysis

-1.2 Development Environment Setup

-1.2.1 Developing Android APP with Eclipse + ADT + SDK

-1.2.2 Developing Android APP with Android Studio

-1.3 Solving SDK Update Issues

-1.4 Genymotion Emulator Installation

-1.5.1 Git Tutorial for Basic Local Repository Operations

-1.5.2 Git: Setting Up a Remote Repository on GitHub