What I have learned from adding Database Sync functionality to Android App

My name is Rohit and from past 1 year, I have been working as an Android Developer. Now you may think why should you read an article written by a guy who has only 1 year of experience? But wait what makes me different from others is my one of many habits. I think, my habit of learning at least one new thing every day makes me different. So I have learned 300+ new things last year. And I have already used many of it in practical. It’s enough about me, let's move on to the main topic.

I have been working on an app from few months. Basically, it showcases information stored on the server. And the app was completely online. Down the line, I felt like adding an offline feature to this app so as to make its usability better. So lately I decided to add an offline functionality support to my app. So taking this thought process of adding offline support to my app ahead, the first question that came to my mind was -

What are the available options?

In Android, we can sync server database to local storage mainly using

1. Sync Adapters
2. Custom Services
3. Other services like Firebase

Sync Adapter and Content Provider

If we want to share app’s private database with other apps, we have to use Content Provider. Content Provider is the one who decides which app can access a private database. The best example of this approach is our contacts which are accessible to all other apps that have permission. On top of Content Provider, there is a Content Resolver which has all functions useful to interact with the private database. Later comes the Sync Adapter. Sync Adapter synchronizes server and local database using Content Resolver. Content Resolver acts as an interface between Sync Adapter and private database. Sync Adapter is specially designed service which is useful to transfer data between server and device using predefined schedules and triggers. Sync adapter adds an option in “Settings — Account — Your App” to set your sync preferences. It's optimized and recommended way to sync server database continuously. So if you decide to use Sync Adapter in your app, your app’s structure will be like-

Online Database => Sync Adapter => Content Resolver => Content Provider => Private Database

 => Custom Services

Here I will write about normal Services and Intent Services only. Intent Services are simply awesome. It's already bound to your app. It creates its own worker thread so you don’t need to create your own thread to run in the background. It gets destroy on its own, you don’t need to call stopSelf() on intent service. But wait if you are already using Volley library in your app, then you should not make use of Intent Service. Because Volley processes API call in a separate thread and returns result to the calling thread. If you are using Volley and Intent Service simultaneously, you will end up main thread creating separate worker thread (by Intent Service) and that worker thread creating a separate thread (by Volley) for API request. So there are many scenarios where you will leak memory through your app.

So if you want to use Volley for synchronization efficiently you should use normal Service. Using Volley in Service you don’t need to create separate background thread so that you can be sure that app will not leak memory easily. So I have decided to use normal Service in my app.

Local Storage - SQLite

Although there are some alternatives to SQLite in Android, I have decided to go with the default option which is SQLite. Maybe next time I will think of other alternatives. So my plan was-

1. Create Helper class which will create or upgrade database and tables.
2. Create a Handler class which will have functions to interact with the database.

Here are some code snippets, you can refer -

My DatabaseHelper class



My DatabaseQueryHandler Class

Abstract Methods

SQLite Database Abstract Methods- Insert, Query, Update, Delete. Even though there are specific functions in Android, here I am going write about the best suitable approach for each method.

Insert -

For insert query, I have used Prepared Statements. It's the best way to insert/update/delete multiple rows at a time. Prepared statements improve the speed of transactions using the concept of Transactions and Compile-Statement. It's faster because the query will be compiled only once throughout the transaction.

Example -
 //SQL Prepared Statement
 String insertPreparedStatement = “INSERT INTO “ + DatabaseHelper.TABLE_CUSTOMER_DETAILS + “ VALUES (?,?,?,?,?)”;

Query/Search-

We cannot use Prepared Statements for queries which returns some data.

So you have to use "query()" function of SQLite database. This function returns Cursor object, which can be used to access values of each row.

Syntax of query() function

//Cursor cursor = sqLiteDatabase.query(String tableName, String[] tableColumns, String whereClause, String[] whereArgs, String groupBy, String having, String orderBy);

Update -

There are two optimized ways to update all /selected rows of a table in the database. And of course, both ways works with prepared statements.

1. Using “INSERT OR REPLACE INTO” keyword — used to insert/replace a row in a table using primary key only. It doesn’t allow you to use WHERE clause. If a primary key exists then replace row (delete and then insert row) else insert row directly. You can use this keyword instead of INSERT to insert rows in the table.

2. Using “UPDATE” keyword — used to replace selected values/columns from the table using WHERE clause. It is helpful when you have to update table data/row independent of a primary key using WHERE clause.

Example- UPDATE TABLE_NAME SET COLUMN_1=?, COLUMN_2=? WHERE COLUMN_3=?

Track Memory Leaks

Now you must have got an idea of, which type of query can be used where. After implementing this, next came to my mind was-

How can I track whether my app is leaking memory or not? & How can I be sure that my app will not leak memory?

So I had a look at some options to resolve this issue and I found a library called Leak Canary.

Even though the issue looks difficult, you can make it simple by using this library. This library alerts you whenever app leaks memory so you can keep track of memory leaks in your app.

Leak Canary — GitHub

After digging out every possible information about services, local storage, threads, volley, transactions, SQLite, its different queries and much more, I feel proud that I have achieved my target in predefined deadline. 

I hope my story will help others who are looking for a better solution to synchronize their app with the server database.

(Sources- Title Image: www.iconfinder.com) 

Originally Published on Medium January 29, 2017.